Referencing Summation row in Pivot Table

640678

Hey All,
Was hoping that I could find some help with an issue that I'm having. We're creating some financial reports for a client. The reports, due to government regulations, must look a certain way, so they have been designed to use Pivot Tables. In addition, they are using the summation function within the pivot table tool.

We're trying to put a data control onto the reports to enable an Ibot to run and compare the summations of two seperate reports. Because they are financial in nature, the reports are not all calculated in the same way.

We have thought about hard coding the calculation in the metadata, but that is a dual effort in terms of maintenance. We've through about exporting to Excel, pulling in the data from the Pivot Table summation row and adding that back into our EDW...but that's not really a viable solution in terms of complexity.

Thought about writebacks to put the actual summation into the EDW, but from what I tell, there's no way to create the writeback without first re-writing the calculation. Our main goal is to make sure we compare the exact number that's in the pivot table....not a clone of the calculation, as we're worried about future state maintnenace issues.

Perhaps I'm forgetting something that will allow me to do this. If you can think of ANYTHING, please let me know.

Again in recap:

Report 1's pivot table summation row must match to Report 2's pivot table summation. If they don't match, we need to know. That's pretty much it. Seems simple, but it's not really ending up that way. Thanks!

599618

I think you can create a union report with difference as the operand and then create an alert/ibot out of it. If it returns a row then you know that there is a difference.

Arjun

640678

The problem with using a Union query is the reports will often use different sources for the fields that they will be doing the summation on, so there will be many many differences...

For example:

Report 1 uses 2+4+6 = 12
Report 2 uses 6+6 = 12

In the first report summation would need to be compared to Report 2 summation. Using a Union with differences would show all of the fields in both reports. I really just need to do a union by difference for just the summation.

599618

You can do a union report provided you have the same number of columns and same data type of the columns. The underlying columns can come from any data source.

Lets say you have report 1 which has columns

Name and Sales, with values

Name Sales
A 200

Report 2 has the same columns with data as
Name Sales
A 100

Then the output of Union Report with Diff operand will give

Name Sales
A 100


Arjun

640678

For most of the reports that may work, but for many, they are using different metrics. For example:

Report 1

Plan Balance
A $1200.00

Report 2
Plan Participant Balance
A Jim $5000.00
A Sam $7000.00

That's just a basic example. Other reports don't even use the same fields. I think the problem is that the client is trying to use OBIEE as a financial reporting tool instead of a BI tool. Balancing out financial reports just isn't straight forward, especially from an auditing prespective.

599618

I see what you are saying, looks like you have complex reporting requirements.In the example you provided the work around would be to create a dummy column called participant with a Null Value( ' ') in Report 1 ( Fooling the Server to have same number of columns to do the union)and then do a union of the two reports. After getting the final results column generate a pivot out of it and put the participant column in the excluded section.

-- Experts please comment if there are any easier techniques.

Arjun

Edited by: arjunmaradani on Oct 1, 2008 1:17 PM