OBIEE 11g - set different level of calculation than it is on a report

Question

Hi All,

I am trying to make a calculation that would omit one level from the report.

I have to calculate difference between days based on the particular Stage - between Closed and In Progress, per product. The report would look like that and what I need is shown in the last column " Stage_Closed_Start - Stage_InProgress_Start (Days) "

----------------------------------------------------------------------------------------------------------------

1                   |  Submitted     |    01.01.2017              |    02.01.2017                    |    05.01.2017             |          3

1                   |  In progress   |    02.01.2017              |    02.01.2017                    |    05.01.2017             |          3

1                   |  Resolved      |    04.01.2017              |    02.01.2017                    |    05.01.2017             |          3

1                   |  Closed          |    05.01.2017              |    02.01.2017                    |    05.01.2017             |          3

So as you can see, I am trying to get the result by firstly calculating Stage_InProgress_Start  and Stage_Closed_Start  which need to omit the Stage and Stege_start_date levels and calculate it on Product level.

Product, Stage and Stage_start_date are 3 separate dimensions.

Of course calculation will work if I remove Stage and Stage_start_date from the report, but I need them there.

Any ideas how to calculate Stage_InProgress_Start , Stage_Closed_Start  and  Stage_Closed_Start - Stage_InProgress_Start (Days)?

My definition of physical column for calculating Stage_InProgress_Start is:

CASE WHEN  "MIB Data Warehouse"."Catalog"."OLAP"."Dim_WC_SSTAGE_Stage"."STG_NAME" = 'In progress' THEN "MIB Data Warehouse"."Catalog"."OLAP"."Dim_WC_TIME_D_StageStart"."CAL_DATE" END

I played here with levels, but if I set Stage and Stage start levels to Total, then it's doing an OUTER JOIN and I cannot filter for example by stage... :/

Thank you in advance!

Anna

3126915 · Answer

Thanks George, I will do it in etl if there is no chaince, but I was hoping Oracle BI can handle such requests...

3126915 · Answer

Hi Srini, thank you for your answer, but firstly I need to calculate Stage_InProgress_Start and Stage_Closed_Start - this is my main concern, how to calculate them on Product level?

SriniVEERAVALLI · Answer

You need to use the formula something like below in the BMM layer

TIMESTAMPDIFF(SQL_TSI_DAY,"Stage_InProgress_Start" , "Stage_Closed_Start")