Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Filter data on electronic reporting

Posted on by 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,

  • Fernitudela Profile Picture
    Fernitudela 5 on at
    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 Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    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 Profile Picture
    Apix 305 on at
    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 Profile Picture
    Apix 305 on at
    RE: Filter data on electronic reporting

    Hello, 

    Again it's not working. 

    Sorry :( 

  • Suggested answer
    MhaU Profile Picture
    MhaU on at
    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 Profile Picture
    Apix 305 on at
    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 : 

    pastedimage1580888964416v1.png

    Here my model mapping : 

    pastedimage1580888979224v2.png

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

    pastedimage1580889044767v3.png

    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 Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    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 Profile Picture
    Apix 305 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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.



    pastedimage1580776155676v1.png

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,900 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,297 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans