Oracle Analytics and AI

Obiee 12c. How to set the right complex calculation for the measure?

I have one measure with sum agg rule on it. But there is one specific condition. I should show the sum when one condition is met and 0 otherwise.

I have dimension Product and Table Product (prod_code2 varchar2(2), prod2_name varchar2(100), prod2_status number, prod_code4 varchar2(4), prod4_name varchar(100), prod4_status number, prod_code6 varchar2(6), prod_name6 varchar2(100), prod6_status number)

Total

-01(status=0)

-0101(status=0)

-010101(status=0)

-010102(status=1)

-0102(status=1)

-010201(status=1)

-010202(status=1)

......

When user doesn’t choose any member from product dim I have to show 0 as value for that measure.

When user choose member(code or name) for which the status=0 I should also show 0.

But when the status for the chosen member is 1 i should calculate the sum.

Is there any way to write such rule for that measure?

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Let me rephrase that:

You want the measure to aggregate (SUM) if "status = 1" in the dimension but you want the grand total to NOT aggregate if "no member is selected"? That won't ever work in any sensible for in the RPD. The Grand Total is an aggregation of is children. It's hierarchical. Not a member-based checking all the members which are currently in the data stream.

deniska

"You want the measure to aggregate (SUM) if "status = 1" in the dimension but you want the grand total to NOT aggregate if "no member is selected""

Yes. Grand Total is always 0.

Regarding above example

If user chooses Product members = 010201, 010202, 0102 or 010102 -- The measure shows aggregate value.

If user chooses another member or no member from this Dim -- The measure shows 0.

[Deleted User]

*should* show. Again: The grand total doesn't go and check "which of my children are currently coming back in the data stream"?

What you want happens ex-post (after the fact) after all the data has arrived in the analysis if you look at it relationally and with dynamic aggregation.

Your requirement is something that's normally tackled in cubes. Essbase. Not a database. Cubes are perfect for that.

deniska

I see. I can do this in Essbase Cubes.

Probably there is another way: hide or show the value of related measure?

First measure "Model"."Measures"."Measure_To_Count" has values for all members Across all Dims. And I don't add it on Pres Layer.

Next measure "Model"."Measures"."Measure_To_Show" has Calc Formula with Case and If expressions smth like this

if ("Model"."Product"."Prod_code6" is not null or "Model"."Product"."Prod_name6" is not null) and "Model"."Product"."Prod_status6"=1

then "Model"."Measures"."Measure_To_Count"

ELSE 0

END

This has incorrect syntax. Just for explaining

Gianni Ceresa

deniska wrote:This has incorrect syntax. Just for explaining

Are you writing that thing in OBIEE? (Admintool in the RPD I hope)

"IF ..." doesn't exist in OBIEE the UI tells you that when you look at the list of available control expression.

You need to write a CASE WHEN ...

deniska wrote:I see. I can do this in Essbase Cubes.

Is your source Essbase?

deniska

1. I wrote that "This has incorrect syntax. Just for explaining". I was trying to explain what i want to get

2. Nope. My source is Oracle DB EE 12C. I just tell that I did this in Essbase v.9-11

[Deleted User]

tl;dr - won't work like that in a relational and normal analytics approach. Would have to be a dynamic member in an Essbase cube.