I have a analysis created with union query, one of the col. is percentage calculation. As I checked now, the value is incorrect
For example, the following is one of the result from OBIEE now
|Plan Amount||PO Amount||Actual Amount||Balance||Balance (%)|
As you see, Balance (%) is calculated as Balance / Plan Amount, however, the grand total on Balance (%) should be 0.28%. I think the error should be due to the aggregation rule is set to sum.
If I set to server complex aggregate, the Balance (%) will all show 0.00
Most appreciated if anyone can suggest the way to fix it
I would create a computed expression in the datamodel to calculate the sum line, not do a sum of the percent column.
Also, given that most of the detail lines have no percent because of no plan amount, I would suggest hiding zeros on those rows, so that only the rows with a percentage calculation will display.
Thanks for suggestion.
Do you mean there is a new column in the analytics for calculation of the total balance (%)
|Plan Amount||PO Amount||Actual Amount||Balance||Balance (%)||Balance (Total %)|
Note: The last row is grand total generated from OBIEE, and this is table view
In additional, those no plan amount lines should not be hidden because of business requirement.
Well, not exactly what I meant.
1. I would have just one % column, with the original % calc in the detail rows, and the "total %" calc in the grand total row.
2. Having two different columns, is displaying for the most part the correct value, but I think would be confusing to most. Also, the 0.00% in the Grand Total row is incorrect; as that should be 0.28% for that row.
3. When I suggested suppressing zeros, I'm talking about only hiding the "0.00%" display in that one column, NOT to hide the entire row. However, if your client wants the zeros there, that's fine.
Hope this helps.
Not sure if I can describe this, but I'll try.
1. Data model
You have two elements in the data model a) the expression calc % of the detail lines b) the expression calc that uses the sum values.
If you don't have this, then you'd need to modify the data model (not the layout) to do this.
If you have inserted a "table" and then dragged data elements into the table, then you'd only be able to have two columns as you illustrated.
If insert a layout grid for the detail rows and insert the detail % there. Then, you can insert this grid into a "repeating group" and below the repeating group add another layout grid with the dollar columns (and use the "summation" feature) and then use the second data element (the % calc of the sum)
I would highly suggest that you a) refer to the online help available in the OBIEE site b) think about taking some courses on BI.
You also may want to pose your immediate needs question to Oracle support if this doesn't get you where you need to go. Of course, you can also try staying with the layout you last had and see if your client is satisfied with this.