Aggregation: DECODE & CASE calculations returns different values between Discoverer Plus and Discoverer Desktop 10.1.2

(This post does not want to discuss how aggregation works, it assume that you already knows aggregation behavior, but it gives you solution to most known scenarios)

From Discoverer 9.0.2 and above i.e Discoverer 9.0.4 and Discoverer 10.1.2 has enhanced roll-up and calculation capabilities a feature called Enhanced Data Aggregation where when connected to a 9.0.1.4 or higher database, the Discoverer server will rely on the CUBE, ROLLUP and GROUPING SET database functionality to compute roll-up values. This new capability correctly handles many of the restrictions described in the provided note, so that a roll-up value can be computed where the 4i server would have to resort to using the "N.A" string.

Additionally, enhanced Data Aggregation is present only in the Discoverer Middle-tier(Plus and Viewer) server -- Not Discoverer Desktop. You will see aggregations in Plus and Viewer that show up as "N/A" in Desktop.

By the way, we can define:

Linear Calculation
A calculation for which the aggregate value is same as the value obtained by summing up the individual values is a linear calculation.

Locally computable Calculation
Discoverer can locally compute all primitive arithmetic operations (+, -, *, / etc), a subset of aggregates (Sum, Sum_Squares, Count, Max, Min, Average and Variance), some string operators, and Decode. Discoverer can locally compute most calculations that can be broken down into linear parts and that involve the above set of operators.
Calculations such as Sum (Profit) / Count (Profit) can be locally computed as it can be broken down into two Linear Parts Sum (Profit) and Count (Profit) combined by the division operator. Calculations such as Sum (Profit) / Count_Distinct (Profit) cannot be locally computed as one of the parts (Count_distinct (Profit)) is not Linear.

Non-Aggregable Calculation
A pre Oracle 9i database allows data to be fetched at only one level of aggregation. From this data discoverer has to compute values at higher levels of aggregation to support things like totals, outline values etc. This is possible only if the calculation is either Linear or Locally computable.
If a calculation is neither linear nor locally computable it will be marked as non-aggregable. The value specified in the option “Show non aggregable values as “ is displayed for such cells.

Case / Decode
Totals on aggregate calculations involving a case expression might yield non-intuitive results. This is because the item values involved in the condition part of the case expression will become NULL when they are rolled up on that item.



In most of cases the solution is to add in the PC Client where Discoverer Desktop is installed regedit entries, like:

o Add two DWORD values named: AggregationBehavior & AllowAggregationOverRepeatedValues = 1 under the key: HKEY_CURRENT_USER\Software\ORACLE\Discoverer 10\Application\ and set both to 1;

Then, Discoverer Desktop should be closed and re-launched.

Database View and Custom Folders does not returns the same results in Discoverer

When running a workbook against a Database View with the same SQL as a Discoverer Custom Folder, the Workbook returns the expected results. If however, the same Workbook is built on top of a Discoverer Custom Folder, which is exactly a copy of the Database View, the value returned is different and it is not correct.

Changes

Here is an example of how to recreate the problem :

1. Create the Database View :
   
   CREATE VIEW DISCO_CALCULATION_VW AS SELECT 1950 AS annual_hours , 52 AS period_divisor , 37.5 AS normal_hours , 10000 AS fte_annual_salary FROM dual
   
   
2. Create a Discoverer Database Folder on this created View ;
   
3. Create a Discoverer Custom Folder with exactly the same code ;
4. On both the folder add the following Calculated Item :
   
   ( Normal Hours/Standard Hours )*Fte Annual Salary
   
   where
   Standard Hours = Annual Hours/Period Divisor
   
   
5. Create a Workbook on both the Folders using Discoverer Desktop. The results for the calculated items will be different.
   

Cause

For the Workbook built on the Discoverer Custom Folder the sql executed is

"SELECT 1950, 10000, 37.5, 52, 1950/52, ( 37.5/1950/52 )*10000 FROM DUAL DUAL"

This means that the order given to the calculation is not working logically as expected and as returned in the database view.

Solution

To make the behavior to be the same on both the folders, the order of the calculation evaluated can be forced by changing the calculation from

"( 37.5/1950/52 )*10000"

to

"( 37.5/(1950/52))*10000"

which in turns began a modification of the Calculation item Standard Hours from :

Annual Hours/Period Divisor

to

(Annual Hours/Period Divisor)