Finance | Project Operations, Human Resources, ...

Filter data on electronic reporting

(0) Share

Report

Posted on by Apix

305

Hello,

Here is a very basic question but i'm trying to learn how to use ER.

I created a vendor export (two fields : VendName, VendAccount) and now I want to add a filter based on the Vendor Account. I wondering either it's on the data model mapping or in the export format mapping that i must add the condition.

Also I don't know which function is best appropriate. As my VendAccount is a string type I guess that a formula type WHERE (list, condition) or FILTER(list,condition) because VendAccount is not a list but a string.

Thanks for your help,

All responses (10)

Answers (0)

Fernitudela 5 on at

Like (0)

Report

RE: Filter data on electronic reporting

If you have any calculated field or other ER object in the list that you try to FILTER(), it is going to throw that error, because it is something that can't be translated to SQL. Where should work, but works in memory, after all the data is loaded, that's why the best approach is to move your calculated fields and try using Filters.
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Filter data on electronic reporting

Hi,

If you enable the cross company parameter then you don't need an expression to filter for a company.

That is because once this parameter is enabled you can select the companies in the standard filter field that you can use when running the report.

There is a special company table available there which allows you filtering one or more companies - provided that the cross company filter is enabled.

Best regards,

Ludwig
Apix 305 on at

Like (1)

Report

RE: Filter data on electronic reporting

I find out !

You must create a calculated field based on your data source and bind your data to the calculated field instead of the datasource.

Thanks to the peoples who helped me !
Apix 305 on at

Like (0)

Report

RE: Filter data on electronic reporting

Hello,

Again it's not working.

Sorry :(
Suggested answer

MhaU on at

Like (0)

Report

RE: Filter data on electronic reporting

Dear Piccin,

Can you please try the following approach to add the formula in the format something like this, add a calculated field:

FILTER(VendRecordList, <path_to_AccountNum>="FRSI")

The path would be the one from the model.

Let me know how it goes.
Apix 305 on at

Like (0)

Report

RE: Filter data on electronic reporting

Hi Ludwig,

It work well if I enable the "ask for query" functionality. The reason why I'm trying to use the formula designer is to learn how to use it.

As I'm a beginner on ER, I have chosen to do this on a basic data model (2 fields : VendorAccount / VendorName) to keep it as simple as possible.

So my purpose here is to filter my export, let's say on every Vendor Account starting by "FRSI"

here my data model :

Here my model mapping :

Eventually I Select in my data sources Vendtable, go to Functions > Calcultated field and I try this formula :

And here I'm stuck ! I also tried @.AccountNum instead of the first @. I tried to select a record like FILTER(@,@.AccountNum='FRSI-0001"). None of them work.

Thank you for your help,

Regards
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Filter data on electronic reporting

Hi Piccin,

If you enable the 'ask for query' parameter that Manish shared in his screenprint then you should be able to filter for vendor accounts.

This can be done by a 'normal' filter that you select when you run the report.

You don't have to use an expression/formula for that.

Have you tried applying the normal filter function at the time the report is run?

Best regards,

Ludwig
Apix 305 on at

Like (0)

Report

RE: Filter data on electronic reporting

Hi I did it and add a calculated field to filter my vendor account :

FILTER (VendTable,VendTable.'nameAlias()'="Contoso Asia")

D365 sent me an error message : Syntax error: We found a problem with this formula; syntax error near '('

I don't catch what's wrong, I used the Add data source on the left pane and the add function on the right to be sure to get the right syntax.
Suggested answer

Community Member Microsoft Employee on at

Like (1)

Report

RE: Filter data on electronic reporting

Hi,

There was a similar request from another community member earlier last month. Could you please check this thread.

https://community.dynamics.com/365/financeandoperations/f/dynamics-365-for-finance-and-operations-forum/378154/i-created-my-desired-data-model-in-formula-designer-in-electronic-reporting-i-want-to-use-where-clause-in-formula-designer-how-can-do-it

In the model mapping designer, edit the root properties. You can enable "Ask for query". Whenever this configuration is run it will pop the Query form in which you can select records to include.

Suggested answer

Sergei Minozhenko 23,089 on at

Like (1)

Report

RE: Filter data on electronic reporting

Hi PICCIN AYMERIC,

In my point, data model mapping is the right place for user input parameters.

About WHERE and FILTER there is a usage note:

This function differs from the FILTER function, because the specified condition is applied to any Electronic reporting (ER) data source of the Record list type that is present in memory.

If the arguments that are configured for this function (list and condition) allow this request to be translated to the direct SQL call, a warning message is thrown at design time. This message informs the user that performance might be improved if the FILTER function is used instead of WHERE.

In your case, you need to use Filter to reduce the amount of data selected from the database.