Skip to main content



No record found.

Dynamics 365 Community / Forums / Finance forum / Electronic reporting -...
Finance forum

Electronic reporting - Filter main accounts in export

Posted on by 30
Hi all. In Electronic Reporting, I have a file export which contains ledger transactions. I want to filter the export based on for example a range of main account, e.g. 30000..39999, or for example if the main account starts with either 4, 5, 6 or 7. I've tried different things using a calculated field and the filter function, but I don't get it to work.
Can anyone help me out? Thanks in advance.
  • Adis Profile Picture
    Adis 256 on at
    Electronic reporting - Filter main accounts in export
    It didnt have such a case, so I cant test and check what works, so its hard to tell the exact way how it works, but I would try the following:
    1.: You said you could already filter your revenue accounts with STARTSWITH('TR.MainAccount'.MainAccountId, "3"), cant you exclude Main account "39500" with:
    If StartsWith(3) AND(Not((StartsWith(39500))) then StartsWith(3) or something similar?
    2.: The SQL query doesnt work as you have strings, therefore, you need to convert to Number. Therefore, I would try using a List(StartsWith(3)) To create a list, convert with INTVALUE() and then Filter or Where should be possible. 
    Maybe a developer can help looking at the available functions.
    Sorry, if I cant give answers, but I try to give ideas.
  • tnaess Profile Picture
    tnaess 30 on at
    Electronic reporting - Filter main accounts in export
    Hi Adis. Thank you for your answer. As mentioned, I've tried to filter using a calculated field. I have for example filtered my revenue accounts which starts with the digit 3 by using this formula: 
    STARTSWITH('TR.MainAccount'.MainAccountId, "3")
    What I'm looking for is something that equals this sql query:
    SELECT *
    FROM LedgerTrans
    WHERE MainAccountId BETWEEN 30000 AND 39999
    AND MainAccountId <> 39500;

  • Adis Profile Picture
    Adis 256 on at
    Electronic reporting - Filter main accounts in export

    What filter function do you mean?
    I would go either to the format or model mapping designer and edit the formula.
    I guess you will need on of the logical functions:
    Kind regards, Adis

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .


André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

nmaenpaa Profile Picture

nmaenpaa 101,148


Featured topics

Product updates

Dynamics 365 release plans