Pivot table Sub Total for records within each Accounting Period.

User_UT27A

I want to achieve Total column only for records with price mentioned above for each AP excluding columns with "WAC".

Ex: For AP 01, I Want to have Total column Before "WAC", which totals values with prices 5.111 and 5.112 excluding values in "WAC".

Please help me how to get this done in OBIEE 11g.

Thanks

Prabhu

Gianni Ceresa

You can't use the automated totals/subtotals options of the pivot, because they would be on everything or nothing.

Depending on what your "5.111", "5.112" and "WAC" are (measures, values of a dimension attribute etc.) you will need to add an element there with your calculation.

If it's measures add a new one with the required formula, if it's values of a dimension attribute you could try adding a new group entry or a calculated item.

Joel

Adding to @Gianni Ceresa response, you will need to create a column with a formula with logic similar to this:

FILTER(SUM(measure BY AP dimension column) USING (2nd dimension column <> 'WAC'))

User_UT27A

Hi

Thanks, I will try this, Yes values "5.111", "5.112" and "WAC" are measures. Yes automated subtotals are for everything or nothing.

Thanks

Prabhu

User_UT27A

Thanks Joel, I will try this option and let you know what happened.

User_UT27A

I created a column with formula which is another measure, But how does this work as pivot subtotal?

User_UT27A

How this works as pivot column?

Thanks

Prabhu

Gianni Ceresa

How can the new measure you created be a row?

How can that be a measure if few post ago you said "5.111", "5.112", "WAC" are the measures? That's impossible!

The measure it's either your rows or your columns, it can't be both at the same time, you better figure out exactly what is what.

User_UT27A

Sorry my mistake, Price, quantity and value are all part of same Aggregate Fact table with item and Calendar as dimension tables. But here we are using quantity and value as measures while price is used as a pivot column,that's the reason i am having this issue.

Thanks

Prabhu