Recently I’ve been asked by a client to look at an issue they had with a report they’d built using the RSUM (running sum) function within a Union Report. A union report is where you have two separate sets of data, specified individually in the Criteria tab and each with their own sources and filters but OBIEE will join the columns of the two sets into a single report. This is useful when you want to combine data from different sources into a single report, especially if you want to perform some calculations on it.
In my example I have sales transactions, of which there are many per day, from one source and a forecast of sales, which is broken down by day and product type, from another source. The exact details of the data sources are not relevant here, but my aim is to have a single report showing the running sum of daily sales though the month compared with the running sum of total daily forecast sales, with a percent difference calculation to highlight how sales compare to forecast throughout the month.
I probably made that sound way too complicated – two columns of data, sales by day, forecast by day, running sums of each and a percentage difference. How hard can it be?
So, my report looks like this:
My first union criteria set is three columns from my Sales source – Calendar Date, Actual Sales (this is just a count of sales records) and the usual Union report trick of including a 0 column for the data from the second criteria set.
If you’ve never created a union report before it’s worth mentioning that OBIEE will add the columns of the union together – this is fine when you need that, but if you want to display the data separately then you need to add “zero” fields – so the data from the first set is “added” to the zero of the same column from the second criteria set and the zero column from the first set is “added” to the data column from the second set.
Here “Actual sales” is the real data from Sales, “Forecast Sales” has a column formula of 0.
My Second criteria set has the same three columns but comes from the Forecast sales data source. Actual sales has a formula of 0 and Forecast Sales is real data:
My Result Columns looks like this:
The first three columns are the real data from the data sources. The fourth column, Actuals RSum I’ve given the formula: rsum(saw_1) – again for those new to union reports, columns in the report are referred to as saw_X, starting 0, so saw_1 is the second column, in this case Sales Count. So, my Actuals RSum field is the running sum of the number of sales.
The Fifth column, Forecast RSum is the same but for the Forecast numbers with a formula of rsum(saw_2).
Finally, I have my percent difference column, “% Actuals/Forecast” which has a formula of rsum(saw_1)/rsum(saw_2)
All quite straight forward, nothing too complex. I can then create a pivot table and line-bar graph of this data:
All looks good initially, until you look closely at the numbers. The two RSum columns look ok, they are adding up correctly across each day, but the % column is wrong. The number on the first record is correct – 249/220 = 1.13%, but on the second line 492/440 is not 2.24% – and every other record is wrong too. What it seems to be doing is taking the forecast from the day before: 492/220 = 2.24%.
The cause of this isn’t obvious and I did plenty of googling and didn’t find an answer, but after playing around I discovered the problem is the aggregation method. This is set to Max by default – Max being the default for RSUM formulae, but when performing the percentage divide the aggregation needs to be set to Min:
Now the numbers look correct:
A second issue we came across is that the RSum doesn’t always work properly if a sort order has been specified. If you specify a sort order on the first column, calendar date in my case:
Then the RSum on the first field, Actuals, just goes to zero – and this replicated in any columns which refer to it – the % column is wrong too. Oddly the second RSum field, Forecast, is unaffected – I suspect this is because the sort is being applied to the first data source, not the second.
Also this only seems to apply if you specify a sort order on in the criteria tab – using the column sorting within the pivot table works fine:
So, if you do need to specify a sort order I would suggest you add a column that is already in the correct order (such as data in my example). If you don’t want to include that column in the report then add it as the first column in the report and set its column properties to hidden.
So, if you are stuggling with RSum in a Union report I hope the above helps!