8 Replies Latest reply: Sep 25, 2013 8:18 PM by Rudy Ising RSS

    Percentage on Grand Total is calculated incorrectly

    Sariel-SW

      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

          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

            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


              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

                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

                  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

                    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

                      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

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