Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calculated Measure in AGO Function

Ebin CherianFeb 22 2018 — edited Feb 23 2018

Hi All,

Oracle Business Intelligence 12.2.1.1.0

This is a question I received from 1 of the users. Users use AGO function at the report level on top of RPD measures and results are fine. Question is will AGO work for a calculated measure in answers. ?

For example , user created a new measure in analysis say COUNT(DISTINCT "Transaction Details"."Document Number"). Then created a new column in report and gave the below mentioned formula.

AGO(COUNT(DISTINCT "Transaction Details"."Document Number"), "Time"."Time"."Fiscal Year", 1)

pastedImage_0.png

Thanks

Ebin

Comments

Uttam.Singh

Hey Ebin,

Yes Ago function works from Answers too. You need to ensure you have the Hierarchy Tree pulled in to answers to do this calculation and reference the "level" with right level of field.

In the example below the level would be "Time"."Time"."Year".

pastedImage_0.png

cesar.advincula.o

It depends on your business model, can you check the query behind

Ebin Cherian

Hi,

If I create a measure in the RPD for the formula COUNT(DISTINCT "Transaction Details"."Document Number") and use that column in the analysis, AGO works fine. But when I try to do count distinct in answers, it is not working and just displays the same value as shown in the above screenshot.

Uttam.Singh

Hey Ebin,

This seems to be a defect in 12c. I faced the same issue and Oracle Provided a workaround to fix this.

You can add one of the below in the Advance Tab --> Prefix

SET VARIABLE OBIS_REDUNDANT_DISTINCT_OPTIMIZATION = 'DISABLED' ;

OR

SET VARIABLE OBIS_REDUNDANT_DISTINCT_OPTIMIZATION = 'LEAF_ONLY'

pastedImage_0.png

Ebin Cherian

Hi,

My issue is not related to distinct. It is with using formula in AGO in report. I face the same issue even if I just use count instead of count distinct.

Just want to confirm if there is any restriction on measure that it should be defined in the RPD ?

Thanks

Ebin

Uttam.Singh

Check this SR 3-16820476571 I created for the Ago issue.

Bug 26642083 was reported in 12c version. I dont think you need to do anything specific in the RPD for Ago function to work.

1 person found this helpful
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 23 2018
Added on Feb 22 2018
#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee
6 comments
495 views