1 2 Previous Next 16 Replies Latest reply on Oct 12, 2015 1:08 PM by Thomas Dodds

    Need help in Obiee report calculation

    2725705

      I am facing calculation issue

      Direct Hrs     Avl hrs       %

      475               287           164

      253                287           87

       

      Direct hrs we are using

      sum(CASE WHEN “Debrief Detail”.”Debrief Activity Description” LIKE ‘%Return%’ THEN -1*”Activity Debrief Fact”.”Debrief Labor Hours” ELSE “Activity Debrief Fact”.”Debrief Labor Hours” END)

       

      Avl Hrs

      max(“Activity Debrief Fact”.”Available Hours”)

       

      %

      Direct Hrs / Avl hrs*100

        • 1. Re: Need help in Obiee report calculation
          Thomas Dodds

          Try as much to move your calculations towards the Database (it has all your power and capacity).  If you can't then consider using the BI server to do the calculation, so build the above in the RPD.  Doing calculations at report time is by far the worst case scenario; the trade is performance for flexibility
          :

          FLEXIBILITY > performance

          flexibility < PERFORMANCE

           

          Now, what specifically are you having an issue with in the above?

          • 2. Re: Need help in Obiee report calculation
            2725705

            I am new to OBIEE could you please elaborate the steps which i need to do from RPD side

            • 3. Re: Need help in Obiee report calculation
              Thomas Dodds

              Build 3 logical fact columns:

               

              Direct hrs

              Fx= CASE WHEN “Debrief Detail”.”Debrief Activity Description” LIKE ‘%Return%’ THEN -1*”Activity Debrief Fact”.”Debrief Labor Hours” ELSE “Activity Debrief Fact”.”Debrief Labor Hours” END; aggr rule = sum

               

              Avl Hrs
              Fx = 
              “Activity Debrief Fact”.”Available Hours”; aggr rule = max

               

              Direct % of Avl
              Fx = 100.00*(
              Direct Hrs / Avl hrs)



              Then put those columns in your Subject Area ... deploy RPD, restart BI server

              • 4. Re: Need help in Obiee report calculation
                2725705

                Capture1.JPG

                 

                Hi if i am using that calculation it is giving above  data

                 

                What i want is i want total direct Hr which is ok

                Avlb hrs is taking max value in total instead of sum in total it should give 789 instead of 82

                 

                Even in last totals also is should take correct calculations but it is taking avarage

                 

                Ex: 239/114  is wrong

                 

                it should be 82+91+114 = 287

                Then U% = 239/287 = 82.5

                 

                This is i want

                • 5. Re: Need help in Obiee report calculation
                  2725705

                  You mean to say put this columns in RPD with above functions ..

                  • 6. Re: Need help in Obiee report calculation
                    Thomas Dodds

                    1 - Avlb hrs is taking max value in total instead of sum in total it should give 789 instead of 82

                    This is because you have a MAX rule on the column (it will apply that aggr rule to the graularity of the query being run) ... what you want is to override the aggregation formula in the report and set it to sum to get the total to sum up the max values

                     

                    2 - Even in last totals also is should take correct calculations but it is taking avarage

                    You might have to put 2 analyses side by side so that you can show the SUM of the MAX values as the summary totals

                     

                    If this doesn't work, then we move towards the database ... we'll try to acheive the sum of the max's in the RPD, if not there, then a custom fact could be created to lay the data as you need it, then the RPD aggr rule is set to sum and voila you have it.

                    • 7. Re: Need help in Obiee report calculation
                      2725705

                      2 - Even in last totals also is should take correct calculations but it is taking avarage

                      You might have to put 2 analyses side by side so that you can show the SUM of the MAX values as the summary totals

                       

                      I didnt understand this line Brother

                      • 8. Re: Need help in Obiee report calculation
                        Thomas Dodds

                        Due to the measure having a MAX on it ... you see the summary columns show the max value of the measure, not the sum of the cell values .... you could try to change the report default aggregation rule OR you could build a different analysis that has the rule set to show the sum of the max values ...

                        • 9. Re: Need help in Obiee report calculation
                          2725705

                          Hi,

                           

                          Thanks for your help,i changed the aggregation rule to MAX for availble hrs in RPD.

                          After changing the agg the column is not populating any data ..

                          Please help me in this

                          • 10. Re: Need help in Obiee report calculation
                            Thomas Dodds

                            Reset your Avlb Hrs to what you had previously ...

                            Let try to get this one item at a time ... this should change the Total row at the bottom.

                            • 11. Re: Need help in Obiee report calculation
                              2725705

                              Hi ,

                               

                              I need one more favour in this report ,can we do

                              OBIEE can create reports based on results of other queries (or aggregations from other data areas with conditons).


                              For example :


                              we often do in Business Objects with multiple queries to ultimately be able to use information from various areas of data to combine together in a single table, graph or dashboard calculation.  Keep in mind, we can have 2 queries of data as illustrated in the third and fourth query from the same DSA but each with its own unique criteria.

                               

                              The separate queries will also have more columns than what I listed directly below but for the table combining the data together, we wouldn’t expect to use them.  Only when we drill to the data relevant only to that query will we require those further columns.

                              • 12. Re: Need help in Obiee report calculation
                                2725705

                                Even after change the rule ,It doesnt make any difference .

                                 

                                Capture.JPG

                                 

                                Still facing the issue

                                • 13. Re: Need help in Obiee report calculation
                                  Thomas Dodds

                                  The image above is:

                                  What i want is i want total direct Hr which is ok

                                  Avlb hrs is taking max value in total instead of sum in total it should give 789 instead of 82

                                   

                                  Even in last totals also is should take correct calculations but it is taking avarage

                                   

                                  Ex: 239/114  is wrong

                                   

                                  it should be 82+91+114 = 287

                                  Then U% = 239/287 = 82.5

                                   

                                  This is i want

                                  • 14. Re: Need help in Obiee report calculation
                                    2725705

                                    Is there any way to give correct results ..Please help me it is taking my life literally

                                    1 2 Previous Next