use ago, todate function dynamically

[Deleted User] · Jan 30, 2018 4:46PM

By just switching out the columns based on the variable:

CASE WHEN VALUEOF(NQ_SESSION."LY_SHIFT") = 'GUNSHIFT' THEN

"Core"."Fact - Retail Inventory Receipts WAGO Fiscal" ELSE

"Core"."Fact - Retail Inventory Receipts WAGO Gregorian" END

User729543-Oracle · Jan 30, 2018 5:10PM

We tried before and it did not work. Basically this is the same approach as the old one. When you have gregorian week on the report, it will fail on WAGO Fiscal metric even though the case statement for that is false.

[Deleted User] · Jan 30, 2018 5:40PM

I don't get why it shouldn't work for you. As long as you switch out the attributes so that you have attributes tied to the correct hierarchy it works just fine.

That last part is what kills you. I just built it and it works just fine.

This obviously crashes.

Why? Because the "Year" column is not part of the fiscal hierarchy! That's what I'm trying to say since the beginning.

Conversely this also fails:

Cause now the "Fiscal Year" column doesn't play with the base hierarchy. It ONLY works if the dimensional attribute is part of the correct hierarchy compared to the measure.

So in your case you can never display things like "Fiscal Week" && ""Fact - Retail Inventory Receipts WAGO Gregorian" or "Gregorian Week" && "Fact - Retail Inventory Receipts WAGO Fiscal".

If you switch things around dynamically you also have to switch the dimensionality!

Again I just built this and it works like a charm. Took me longer to write this post than configure the requirement...

User729543-Oracle · Jan 30, 2018 6:01PM

Thanks for the reply. I did use the Gregorian week on the report. The issue here is the case statement. It would not switch when OBIEE convert it to SQL. It will convert both cases and then let DB run it.

If I just put

IFNULL( AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal Calendar"."Gregorian Week", 1), 0)

in the metric, then the report works fine just like what you did. However, if i put the following case statement in the metric, then it gives error even the session variable has value of 'GUNSHIFT'. Can you also try to use case statement, so the metric name will be the same for both Gregorian and Fiscal?

CASE WHEN VALUEOF(NQ_SESSION."LY_SHIFT") = 'GUNSHIFT' THEN

IFNULL( AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal Calendar"."Gregorian Week", 1), 0)

ELSE IFNULL( AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal Calendar"."Fiscal Week", 1), 0)

END

[Deleted User] · Jan 30, 2018 6:10PM

The error message does not come from the measure itself it comes from the whole request construct! It all depends on what is passed to the BI server as dimension/fact combination.

User729543-Oracle · Jan 30, 2018 10:45PM

So did you get the same error when you put both fiscal ago and Gregorian ago in one metric by using case statement? Any suggestion on how to solve the problem?

Thanks

[Deleted User] · Jan 31, 2018 5:21AM

No I didn't. I got the error in conjunction with the DIMENSIONAL ATTRIBUTE which didn't match the measure!

Not sure how else to put this. Yuu even have the screenshots above.

You know what a dimensional attribute is, right? And you know that they are part of one dimensional hierarchy or another, right?

So the dimensional attribute used in the query must match the one which is used by the measure depending on the session variable's instantiated value.

User729543-Oracle · Jan 31, 2018 11:57AM

The screen you provided is using 2 different metrics for gregorian LW and fiscal LW. That is no issue for me. The requirement here is to only have 1 metric and this metric should pick either gregorian week level or fiscal week level dynamically based on a session variable. Not sure if you can create a similar metric.

Thanks

[Deleted User] · Feb 1, 2018 4:56AM

Listen...OBI is like any other software. It is a toolbox. It is a set of functionalities. Depending on what your background is you will use or misuse them. It's normal. it's human.

Why do you insist on the CASE WHEN?

I just used "Fact - Sales Base"."Revenue @{pv_yago_case}{YAGO}" as a measure column formula and the formula fetched will dynamically call "Revenue YAGO" or "Revenue YAGO Fiscal".

There are many more ways to do this. You can construct the column formula with your CASE WHEN if you insist on it...

'"F0 Sales Base Measures"."Paid Amount' ||

CASE

when '@{pv_yago_case}{Normal}' = 'Normal'

then 'YAGO'

when '@{pv_yago_case}{Fiscal Calendar}' = 'YAGO Fisc'

then 'YAGO Fisc'

else

'Invalid selection'

end

||

'"'

Again. Think of what you are doing for a second. That's what will diffferentiate you as a *professional* from some random code writer.

User729543-Oracle · Feb 1, 2018 12:05PM

Thanks for the reply. It looks like your logic is on the report side. So how to implement that inside of rpd instead of report? The requirement here is to only build one metric Revenue ago in rpd instead of 2 metrics "Revenue YAGO" or "Revenue YAGO Fiscal". That is our requirement. The single metric from rpd should behave differently based on a session variable.

Thanks

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

use ago, todate function dynamically

Answers