How to add a variance into a pivot table

Robert Angel

I know you got your answer, so probably are not as bothered now...

But 'on how to do this in the rpd'; -

1. Copy any of your existing measures that make up an element in the formula in the business model layer i.e. - Tran Amount - Including Reversals

2. edit the formula on the properties for the field and reflect your formula as above, except using the case logic you had previously, note you can use the formula palette to pull the elements of the formula in - forgive me if I teach grandma to suck eggs i.e. case when "Transactions"."Debit Credit" = 'Debit' then "Transactions"."Tran Amount - Including Reversals" when "Transactions"."Debit Credit" = 'Credit' then -1 * "Transactions"."Tran Amount - Including Reversals" else 0 end

3. Set your aggregation on the measure to SUM

4. Rename the column to something business meaningful, say, Net Tran Amount - Including Reversals

On all of the above make sure this is a physical calculation, not the logical 'based on existing columns' calculation or you will run into the same issues with spurious results

Adam Wickes

Hi Robert, 

That would definitely be a cleaner solution.
In the interest of time, I will be leaving what I have but appreciate your suggestion nevertheless.

Asim,

In my criteria, I have one query that retrieves the raw data for each branch, product and their debit/credit columns.
I have then added a union where I have added 'Variance' as Product and Debit/Credit.
I have then added the filter calculation mentioned above on my measure column.
To ensure that debit appeared first, credit second and variance last, I created a "sort" column and used a case statement to order each selection, 1, 2 and 3.
I dragged the sort value into the columns of the pivot, sorted by it and then hid that column from appearing in the pivot.

We're currently transitioning around 200 reports from Hyperion 9.3 (BQY and SQR) into OBIEE and I can tell you, BQY's especially handled pivots 1000x better than OBIEE does.
It has been one frustrating ride!!