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

Percentage on Grand Total is calculated incorrectly

Sariel-SW Newbie
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 AmountPO AmountActual AmountBalanceBalance (%)
(1,669.30)0.00%
70,356.00 (82,715.69)0.00%
(193.68)0.00%
835,789.0068,010.000.00767,779.0091.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.00776,840.000.002,348.2391.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
    Rudy Ising Explorer
    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
    Sariel-SW Newbie
    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 AmountPO AmountActual AmountBalanceBalance (%)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.0068,010.000.00767,779.0091.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.00776,840.000.002,348.230.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
    Rudy Ising Explorer
    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
    Sariel-SW Newbie
    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
    Rudy Ising Explorer
    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
    Sariel-SW Newbie
    Currently Being Moderated

    Thanks for advise.

     

    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
    Rudy Ising Explorer
    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
    Sariel-SW Newbie
    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
  • Helpful Answers - 5 points