7 Replies Latest reply on Oct 2, 2013 9:22 PM by Alejandro Suarez

# Pivot with Union and RSUM - Column totals not ok

Hi All,

I need to build a report like this:

ProductRegionWeek1Week2Week3Week4Total
Prod ARegion 112348
Prod ARegion 211114
Prod BRegion 122228
Prod BRegion 3333312
Total7891034
Cumulative Total715243434

I am able to achieve something similar using 3 "union all"

ProductRegionWeek1Week2Week3Week4Total
Prod ARegion 112348
Prod ARegion 211114
Prod BRegion 122228
Prod BRegion 3333312
Total7891034
Cumulative Total715243480

The problem, as far as I can tell, is that in the Pivot View, the default "aggregation rule" is setting to SUM all the rows, even thought each measure (in each union) has the proper set, the first two have SUM, and the last one (the RSUM one) has MAX.

Is there a way to achieve this? Another option would be to have a column to Calculate the totals by Row, but I can't place it at the right of the reports in a Pivot, is this something possible?

Any ideas?

Thanks,
Alejandro

• ###### 1. Re: Pivot with Union and RSUM - Column totals not ok

Did you try using the column totals for the totals on the last column.

And why are the totals in the Total Column incorrect? the first row does not sum to 8

• ###### 2. Re: Pivot with Union and RSUM - Column totals not ok

B4 answering your question, there might be something wrong in your data analysis. I see the total of the very first row is wrong, did u copy and paste or you just wrote the table on the fly on OTN?

But, for totals there are multiple ways: One will be to duplicate one of your rows then change the calculations and set it to whatever you want then add to the end as you wish.

Two, under row if you click whatever row you are trying to adjust there is an option to for "new calculated item".. this should also help you to manipulate the row as you wish.

Mark if it helps.

• ###### 3. Re: Pivot with Union and RSUM - Column totals not ok

Hi,

Yes, the first row total is wrong, I did it on the fly on OTN, the value I need to fix is the "80", the last value, the Total of the "Running Sum" row.

I cannot see what you mean by "under row if you click whatever row you are trying to adjust there is an option to for "new calculated item""

Can you pls explain this a bit more?

Also, what do you mean by Duplicate the row?

My report is

Select rows from table
UNION ALL

Select Total from table

UNION ALL

Select Cumulative Total from table

All this in a pivot view.

Thanks,
Alejandro

• ###### 4. Re: Pivot with Union and RSUM - Column totals not ok

Ok, I hope I understood you correctly: my understanding is that '80' is the columns' total, so on your report click on the edit button.. then it will take you to the 'Editing from: Compound Layout screen'... At the bottom under layout there is many options: Go to the columns one, this has a 'Sum' icon and and column properties; if u click on the sum icon and choose 'Totals after' it should give you the sum total...

If that options didn't work you can click one of the columns in the "Columns'  it should give you and option to duplicate. Duplicate it, change it to be your Rsum total as you wish..

Probably it is easy to show than to explain, but I have done it before.

I hope that helps

• ###### 5. Re: Pivot with Union and RSUM - Column totals not ok

I know it is a lot to ask, but can you send me a screenshot of those properties you mention? I cannot find them.

Thanks,

Alejandor

• ###### 6. Re: Pivot with Union and RSUM - Column totals not ok

I don't think Oracle forums allow us to share screen shots, what's email address?

• ###### 7. Re: Pivot with Union and RSUM - Column totals not ok

Hi, it is alesm83@gmail.com

Thanks,
Ale