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

# Need help in Obiee report calculation

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

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

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

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

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

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

• ###### 6. Re: Need help in Obiee report calculation

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

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

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

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 ..

• ###### 10. Re: Need help in Obiee report calculation

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

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

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

Still facing the issue

• ###### 13. Re: Need help in Obiee report calculation

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

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

1 2 Previous Next