Home » Developer & Programmer » Reports & Discoverer » Want to Calculation based on Summary item (Oracle BI Discoverer 11g Desktop)
Want to Calculation based on Summary item Sun, 08 September 2013 01:07
 jainarayan5484 Messages: 38Registered: August 2006 Location: DELHI Member
See my data

Location--------Employee-------------Sales-----------------------Sales per emp
1------------------ 4-----------------------100------------------------------25
2------------------ 5-----------------------120------------------------------20
3------------------ 7-----------------------210------------------------------30
Total---------------16-----------------------430-----------------------------27

The total 27 achieve by (430/16)=27 How can I get using ORacle BI Discoverer 11g Desktop

Re: Want to Calculation based on Summary item [message #595201 is a reply to message #595156] Mon, 09 September 2013 03:50
 Lalit Kumar B Messages: 3123Registered: May 2013 Location: World Wide on the Web Senior Member
430 is the sum of total sales, and 16 is sum of all employees. You just need to do sum of sales against total employees to get average sales per employee as 27. So, what is the issue you are facing? Are you new to BI?

Oracle® Business Intelligence Discoverer Plus User's Guide
Re: Want to Calculation based on Summary item [message #595203 is a reply to message #595201] Mon, 09 September 2013 04:46
 jainarayan5484 Messages: 38Registered: August 2006 Location: DELHI Member

But How can i get this using Oracle Discoverer BI 11g

Bi giving mo option like

Sum
Cell Sum
Average
Count
Maximum
Minimum
Varient

When I put calculation it generate new Column like

Location--------Employee-------------Sales-----------------------Sales per emp New column
1------------------ 4-----------------------100------------------------------25 ???
2------------------ 5-----------------------120------------------------------20 ???
3------------------ 7-----------------------210------------------------------30 ???
Total---------------16-----------------------430------------------------------27

Re: Want to Calculation based on Summary item [message #595207 is a reply to message #595203] Mon, 09 September 2013 06:10
 Lalit Kumar B Messages: 3123Registered: May 2013 Location: World Wide on the Web Senior Member
Few clarifications -
1. Is the sales per emp column rounded off?
2. How is the sales per emp for 2nd record 20 instead of 24?

To get the sum totals for emp and sales, you could get it by using SUM or CELL SUM function. But to do 430/16, you can't do it directly, you can only use these functions directly over aggregated values. What you need to do is, use AVERAGE function over the sales per emp column, which is nothing but average of 25,24 and 30.
Re: Want to Calculation based on Summary item [message #595208 is a reply to message #595207] Mon, 09 September 2013 06:58
 jainarayan5484 Messages: 38Registered: August 2006 Location: DELHI Member
1.Yes Sir sales per emp column rounded off
2.sales per emp for 2nd record should come 24 instead of 20 ,it is typing mistake

thnaks
Re: Want to Calculation based on Summary item [message #595209 is a reply to message #595208] Mon, 09 September 2013 07:15
 Lalit Kumar B Messages: 3123Registered: May 2013 Location: World Wide on the Web Senior Member
OK. So, did you use the AVERAGE function over the sales per emp column to get desired output?
Re: Want to Calculation based on Summary item [message #595211 is a reply to message #595209] Mon, 09 September 2013 07:18
 jainarayan5484 Messages: 38Registered: August 2006 Location: DELHI Member
yes it giving 25 not 27
Re: Want to Calculation based on Summary item [message #595214 is a reply to message #595211] Mon, 09 September 2013 07:46
 Lalit Kumar B Messages: 3123Registered: May 2013 Location: World Wide on the Web Senior Member
jainarayan5484 wrote on Mon, 09 September 2013 17:48
yes it giving 25 not 27

You must be doing something wrong. Simple mathematics, with rounding off you should get 26(not 27, as 26.33 would be rounded off). If you are getting 25, it means you took the sales per emp value for 2nd row as "20" instead of "24".