How to Fetch User input parameters from ER query (Electronic Reporting)

(0) Share

Report

Posted on by Daniele.Selvi

160

Dear All,

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

I have the same question (0)

All responses (12)

Answers (2)

Michal Krejza on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

Hello Daniele,

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.

Was this reply helpful? Yes No
Daniele.Selvi 160 on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

Hi Michal,

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 ?

Was this reply helpful? Yes No
Michal Krejza on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

FILTER function can be used if we are talking about selection by field value:

Was this reply helpful? Yes No
Verified answer

Eugen Glasow 6,359 on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

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!

Was this reply helpful? Yes No
Daniele.Selvi 160 on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

Hi Eugen,

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

Was this reply helpful? Yes No
Verified answer

Eugen Glasow 6,359 on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

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/.../

Was this reply helpful? Yes No
Daniele.Selvi 160 on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

This is what I have behing my $mainAccountId:

Was this reply helpful? Yes No
Eugen Glasow 6,359 on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

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

Was this reply helpful? Yes No
Daniele.Selvi 160 on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

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..

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to Fetch User input parameters from ER query (Electronic Reporting)

Hi Daniele,

How exactly did you manage to solve your issue? I also need to filter the GeneralJournalAccountEntry by MainAccount using the FILTER function.

Thanks

Was this reply helpful? Yes No