6 Replies Latest reply on Jan 13, 2016 5:36 AM by Saro

    Subtotal on Group by

    Saro

      Hi friends,

       

      Im in OBIEE 11.1.1.7. I have a report with the following columns like below

       

      A.png

      If u can see @ the above 419 is my employee no which has two records for one of the column as 26116.93 and 0.00 for self and child. i want to display another column seperately with a sum of total of 26116.93+0.00. As i need this another column that calculates the sum of(26116.93+0.00) according to the employee number wise(group by).

       

      Is it possible to achieve this scenario.

       

      Thanks in advance.

       

      Regards,

      Saro

        • 1. Re: Subtotal on Group by
          Felipe_Idalgo

          Hi,

           

          Yes, you can add a sub-total directly on column.

           

          1 - Edit your table or pivot-table view

          2 - Note there is an icon next your "Employee Number" column like that

          sum_icon.png

          3 - If you apply a total there, all values will be SUM/AVG./MIN... by this column

           

          Felipe Idalgo

           

          Edit: I review your question and if you can create another column instead of sub-total lines, you can create a column with this formula:

           

          SUM(<COLUMN_VALUE> by <EMPLOYEE_NUMBER> )

           

          Update <> by your columns

          1 person found this helpful
          • 2. Re: Subtotal on Group by
            cesar.advincula.o

            Yep, is like a SUM(<COLUMN_VALUE> by <EMPLOYEE_NUMBER> ), or you could use GROUP BY FUNCTION, in th avanced TAB.

             

            avanzado.png

            1 person found this helpful
            • 3. Re: Subtotal on Group by
              Saro

              Hi guys thanks for the reply.

               

              Indeed i tried with the below formula

               

              sum("Result Value" by "Employee Number") for one of the column Result value total and i get the below results

               

              If you can notice @ the above for 384 the Result value total need to be sum of Result value which is (2857.86+26116.93+2891.10+6366.90) = 38232.79 but in the results im getting 41090.65 which seems to be wrong. And also for the next row 408 has only one record and it has a result value as 0 but the result value total is 2857.86 as it needs to be 0.

               

              What could be the missing factor in this.

               

              Thanks,

               

              Regards,

              Saro

              • 4. Re: Subtotal on Group by
                cesar.advincula.o

                Hello Saro,

                 

                This will be enough for your requirments, please, in the agreggation rule (Totals Row) ensure the combo box with "sum" option is selected.

                 

                 

                sum2.png

                This give the sum by

                 

                Sum.png

                 

                 

                And ensure this, in blank

                 

                sum3.png

                • 5. Re: Subtotal on Group by
                  cesar.advincula.o

                  If the problem persist, please, could you put an image of your star schema RPD, in the "Business Model and Mapping Layer".

                   

                  Kind Regards,

                  1 person found this helpful
                  • 6. Re: Re: Subtotal on Group by
                    Saro

                    Dear Cesar,

                     

                    Thanks for the reply again.

                     

                    I do noticed that my employee number column is in varchar type and due to that it resulted in incorrect numbers it seems. After that i changed the formula to below to number format and it worked like expected.

                     

                    sum ("Airfare Adjustment-fact"."Result Value" by EVALUATE('to_number(%1)' AS INT, "Airfare Adjustment-dim"."Employee Number"))

                     

                    Thanks for your timely help:-)

                     

                    Regards,

                    Saro