Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, Power Apps, Power Automate, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
I have the needs to "fetch" the parameters input by the users in the standard query form highlighted below:
I clearly have easily the possibility to fetch my custom parameters , but due to performance reasons I still need the user to fill also the "standard query".
In fact, if we leave the "standard query parameters" empty, at runtime the ER engine:
-First, fetches the WHOLE TABLE RECORDS;
-Second, it applies the Filter based on a formula I write using my custom parameters.
i.e. If I want to fetch all main account transactions of a specific account from 01092019 to 30092019, with the query below it takes 5 minutes:
while if I "duplicate" the input parameters specifying them also in the standard query it takes 5 seconds!
this is clearly quite "UGLY" as we need to tell the user to input the parameters 2 times:
1)in my custom fields for being able to print them in the header and make some other calculations I want to do;
2)in the standard query for being able to filter properly the tables "at runtime" due to performance issues described above.
My desire is to at least being able to "fetch" properly the standard query parameters input by the user, to avoid telling the user to input them twice.
Kind Regards and Thanks
You can use the only GER user input parameter for both purposes - to filter table query and to fill in report's header
You need to implement this parameter in model mapping and use in in FILTER condition.
thanks for the reply, however I am aware of how to implement it in the model mapping, but i found out that if I use the filter condition(actually it makes me use the WHERE condition, not the filter), the performances are very bad as FIRST it fetches all records, then it filters them according to my query.
Am I making anything wrong ?
FILTER function can be used if we are talking about selection by field value:
Concur with Michal: the FILTER is highly performant (whereas WHERE is not). Recently I implemented a 2-stage filtering: first applied FILTER to narrow down the selection, then used WHERE to further refine by DataAreaId (which the FILTER cannot do).
In a FILTER you sometimes cannot use relative paths. To walk around this, wrap your predicate path into a Calculated field.
As per the original question, this is not possible directly, as the ER has no reflection functions. Yet you may let the query run and extract the result: i.e. for testing the Main account you let the query run and return the 1st record, then take the Main account from the record!
I followed your advise of "wrap your predicate path into a Calculated field" to workaorund the issue of not being able to use relative paths in FILTER, but it returns the validation error below:
what Am i doint wrong ?
Please note: if I compare my parameter with a field of generalJournalAccountEntry, it is working but this is not wath I want as I need GeneralJournalAccountEntry>mainAccount(table)>MainAccountID
What's the formula behind $MainAccountID? An atomic user parameter works, guaranteed. Beware the command
FILTER(ProjTransPosting, AND(ProjTransPosting.PostingType=Enums.LedgerPostingType.ProjCost, ProjTransPosting.LedgerTransDate <= $ToDateOrInfinity))
featured in my blog erconsult.eu/.../
This is what I have behing my $mainAccountId:
Ok, I finally understood what you are doing: filtering ledger transactions by the main account symbol selected by the user.
Try ALLITEMSQUERY with 2 datasources: docs.microsoft.com/.../general-electronic-reporting-formula-designer
using ALLITEMSQUERY didn't work, however using the "filter" by Main AccountID first, to narrow down the records helped the performances.
I still needed to use the WHERE condition to still select by fromdate/todate..
Business Applications communities