Display Cumulative Total as Grand Total in Pivot Table

OBIEE_Drool

Hello Peers - Is there a way we can display Cumulative Total instead of Grand Total in a Pivot Table? I understand having another column to display Running Total. But, I don't want to add additional column as per my requirement.

Just so you guys know, I referred to this earlier similar community discussion but couldn't exactly understand what @Ehab Emara-Oracle suggested. He said: "Dear Customer , one workaround , is to use Sum to get grand total , and then use RSum".

Link: https://support.oracle.com/epmos/faces/CommunityDisplay?resultUrl=https%3A%2F%2Fcommunity.oracle.com%2Fthread%2F3495281&_afrLoop=405750564230994&resultTitle=Cumulative+Total+of+grand+totals&commId=3495281&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=kmf6uslse_4

Can someone please elaborate on the workaround sequence or share a new solution. Appreciate any inputs. Thanks in advance.

SriniVEERAVALLI

its like max(rsum(Metric))

OBIEE_Drool

Thanks @Srini VEERAVALLI for the input. But it doesn't work in my case. For example:

   

Customer	Fiscal Period	Amount
A	Jan, 2016	5
		2
		-6
Total		1
A	Feb, 2016	3
		11
		-4
		-2
Actual Total		8
Cumulative Total		9

I need the cumulative total. Like in the above example, I need the report to display Cumulative Total but not actual total. With the logic you mentioned, I don't see it happening on my end. Thanks.

cesar.advincula.o

Hello,

I think that you have the "Grand Total" column depends on what "aggregation rule" you use in your pivot table,

the only aggregation rule that you could get is using "Server Complex Aggregate", but this helps to aggregate, if you want some acumulative function,

you have to make a formula.

OBIEE_Drool

Sorry correction to the table data:

   

Customer	Fiscal Period	Fiscal Date	Amount
A	Jan, 2016	1-Jan	5
		10-Jan	2
		15-Jan	-6
Total			1
A	Feb, 2016	2-Feb	3
		13-Feb	11
		14-Feb	-4
		25-Feb	-2
Actual Total			8
Cumulative Total			9

OBIEE_Drool

@cesar.advincula.o True, but what formula would you apply in the column formula to make pivot table understand to pick up Grand Total as Cumulative Total. Can you please share an example with a screenshot? Appreciate your input.

cesar.advincula.o

Hello,

1.I saw the template of your report:

- You dont need a acumulative, you just need a subtotal and show your SUBTOTAL by PERIOD, check this image:

 

   

2. If you need acumulative sum for any reason

   

      

Kind Regards,

OBIEE_Drool

Sorry @cesar.advincula.o, I think there is a slight confusion in what I said I needed. Your approaches work good for Grand Total but my final output from your example should look like this as shown below:

Yours looks like this:

Mine should look like below is what I meant. See, it is becoming cumulative at the end of 2012. Well, I do know that the total for 2012 is 27653.59 but I don't want to display that amount. Rather I want to display amount as shown below:

Appreciate any inputs. TIA!

SriniVEERAVALLI

IF you want Cumulative text as you said then go or case statement for Year column by pulling one more year

case when year>0 then 'Cumulative' end

set aggregation in pivot and then hide

Thomas Dodds

Looks like you are asking for the measure at the date and period levels AND the YTD 'version' of the measure at the bottom of the report ... you are dealing with two separate measures (logically speaking).

OBIEE_Drool

@Thomas Dodds That is true in most cases, it is like 2 measures in a table. In this specific case, I was wondering if we can do this as my user really doesn't understand the limitations which I will anyhow address with him soon regarding this. Thanks for the message though.