OBI EE group by problem

3316721

Hi All

I am facing a bit of a roadblock with grouping and don't have the best SQL skills/OBIEE experience to do it.

                                                         

CURRENT RESULT

Opportunity number	MIN(Date of sale)	MAX(Date of realization)	Business Line Items	Revenue
34562BIS	8-5-2016	8-5-2017	X	5000
	8-5-2016	8-9-2016	Y	4000
	8-5-2016	8-10-2016	Z	3000
78652BIS	9-11-2016	9-11-2017	Y	1000
	17-5-2016	17-9-2018	Z	3000

I have an analysis for which I'm viewing as a pivot table currently. I have Opportunities (indicated by Opportunity number) which contain multiple 'Business line items' and corresponding revenues.

The business user wants this information condensed and only wants to see one record for each opportunity number. (Like in Desired result)

This record should select the business line item that has the maximum delta between max data and min date but in the revenue field - it should sum up the revenues for the entire opportunity.

DESIRED RESULT

                            

Opportunity number	MIN(Date of Sale)	MAX(Date of relization)	Business Line Items	Revenue
34562	8-5-2016	8-5-2017	X	12000
78652	9-11-2016	9-11-2017	Z	4000

It is a bit contorted and i am not able to implement this. could you please help.

Thanks a ton!

Thomas Dodds

1 - You need a level-based measure for Revenue pinned at the Opportunity dimensional hierarchy level.

2 - You need a ranking measure so you can select the appropriate delta row

Both of the above should be built in the RPD.