Question

Advanced Criteria not working

  • 27 January 2023
  • 1 reply
  • 54 views
P
Rank
Badge +2

Hi,

 

I am trying to create a Business Report in APP 10 using advanced criteria for a cell. It is not working as expected. Appreciate any support in solving this.

 

Advanced Criteria:

DIM_ACCOUNTING_PERIOD.ACCOUNTING_YEAR = 2022 AND

(DIM_ACCOUNT.CODE IN '60900';'61900' OR ((DIM_CODE_G.CODE LIKE '10%' OR DIM_CODE_G.CODE IN '4000';'4100') AND

DIM_ACCOUNT.ACCOUNT_GROUP = 'G13' AND

DIM_ACCOUNT.CODE NOT  IN '60900';'61900';'60999';'61999'))

Report Result is -915,006.71.

Expected result (Querying through SQL) is as below.
 


When you analyze the data set in business report it shows without the brackets.


 

/Bhagya (www.linkedin.com/in/bhagya-witharana)

1 reply

S
Rank
Userlevel 2
Badge +4

Hi!

I am sorry I cannot exactly say why your advance criteria is not working.

But in general I would recommend not to use advance criterias. It becomes very difficult to administrate and update reports that includes complex advance criterias. It is also bad from a performance point of view because the IFS BR client cannot optimize the excution by combining data sets when advance criterias are used.

Instead I would try to create attributes based on the accounts and connect needed accounts to the attribute values. Then create a filter based on the attribute. Perhaps also fetch data into two datasets and then calculate the sum of the datasets by using MS Excel formulas if possible.

/Stefan

Reply


Terms & ConditionsCookie settings