This discussion is archived
8 Replies Latest reply: Sep 25, 2013 6:18 PM by Rudy Ising

# Percentage on Grand Total is calculated incorrectly

Currently Being Moderated

Dear All,

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 (%) (1,669.30) 0.00% 70,356.00 (82,715.69) 0.00% (193.68) 0.00% 835,789.00 68,010.00 0.00 767,779.00 91.86% (1,993.95) 0.00% 106,362.00 (106,362.00) 0.00% 156,198.00 (170,350.58) 0.00% 1,666.00 (1,666.00) 0.00% (3,247.96) 0.00% 358,675.00 (381,381.35) 0.00% 5,452.00 (5,729.26) 0.00% 3,333.00 (3,333.00) 0.00% 5,004.00 (5,004.00) 0.00% 1,784.00 (1,784.00) 0.00% 835,789.00 776,840.00 0.00 2,348.23 91.86%

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

• ###### 1. Re: Percentage on Grand Total is calculated incorrectly
Currently Being Moderated

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.

• ###### 2. Re: Percentage on Grand Total is calculated incorrectly
Currently Being Moderated

Hi,

Thanks for suggestion.

Do you mean there is a new column in the analytics for calculation of the total balance (%)

e.g.

 Plan Amount PO Amount Actual Amount Balance Balance (%) Balance (Total %) (1,669.30) 0.00% 0.28% 70,356.00 (82,715.69) 0.00% 0.28% (193.68) 0.00% 0.28% 835,789.00 68,010.00 0.00 767,779.00 91.86% 0.28% (1,993.95) 0.00% 0.28% 106,362.00 (106,362.00) 0.00% 0.28% 156,198.00 (170,350.58) 0.00% 0.28% 1,666.00 (1,666.00) 0.00% 0.28% (3,247.96) 0.00% 0.28% 358,675.00 (381,381.35) 0.00% 0.28% 5,452.00 (5,729.26) 0.00% 0.28% 3,333.00 (3,333.00) 0.00% 0.28% 5,004.00 (5,004.00) 0.00% 0.28% 1,784.00 (1,784.00) 0.00% 0.28% 835,789.00 776,840.00 0.00 2,348.23 0.00% 0.28%

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.

• ###### 3. Re: Percentage on Grand Total is calculated incorrectly
Currently Being Moderated

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.

• ###### 4. Re: Percentage on Grand Total is calculated incorrectly
Currently Being Moderated

hi,

Thanks for suggestion. But I am not too understand how to hv 1 col to display all my wanted information, can you elaborate more on this or provide me steps to do so?

sorry that I am fresh in OBIEE and my company no one knows on that

• ###### 5. Re: Percentage on Grand Total is calculated incorrectly
Currently Being Moderated

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.

2.  Layout

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.

• ###### 6. Re: Percentage on Grand Total is calculated incorrectly
Currently Being Moderated

Can you let me know what is layout grid? As I cannot see the option in the list on compound view

• ###### 7. Re: Percentage on Grand Total is calculated incorrectly
Currently Being Moderated

I am looking in the BI Publisher web page with the layout design open.

Oracle BI Publisher 11.1.1.6.6

Could be that you are accessing a different model of BI, for a specific application reporting.

• ###### 8. Re: Percentage on Grand Total is calculated incorrectly
Currently Being Moderated

Hi, I am not using BI Publisher, I am just achieving the item in general analysis, table view

#### Legend

• Correct Answers - 10 points