This discussion is archived
7 Replies Latest reply: Jan 20, 2013 10:05 PM by 901916

# calculated rows

Currently Being Moderated
Hi All,

I am trying to create a simple report in obiee 11g dashboard. I created the National column through selection steps ( Region1+ Region2 + Region3).I am thinking how to create the calculated measures ( B%). Do we need to create this measure in rpd? or is it possible to create this calculation in obiee 11g answers.The new calculated item can be created from one particular table column only like region or type.

Thanks,

Uday

Type Region1 Region 2 Region 3 National
A 40     10          30     80
B 10     20          40     70
Total 50     30          70     150

Regional Total % of 50/150 30/150 70/150

Edited by: 929521 on Jan 8, 2013 11:46 AM

Edited by: 929521 on Jan 8, 2013 11:46 AM

Edited by: 929521 on Jan 8, 2013 1:47 PM

Edited by: 929521 on Jan 8, 2013 2:01 PM
• ###### 1. Re: calculated rows
Currently Being Moderated
Hi,

If you calculate the measure in the repository then it will be a fixed measure, which is to say that if you apply a filter to reduce the total data then the answer you get from the measure in the repository will still be based on the total data population, not the reduced population based on your filter.

If this is what you want then go with the repository, else use sum by to achieve your needs.

i.e.

sum(region1 by type) / sum(national)

The above being based on a guess based on the data you posted...

regards,

Robert.
• ###### 2. Re: calculated rows
Currently Being Moderated
The problem is National is the group I created from answer using selection steps. I need percentage ( 10/grandtotal 30) in the new row. Whenever I create the new calculated column in selection pane it comes as new column.

Type A B Total(National)
10 20 30

Percentage .33 .67

Edited by: 872073 on Jan 9, 2013 7:47 AM
• ###### 3. Re: calculated rows
Currently Being Moderated
Hi,
Mine may not be a feasible solution, but a tricky way to achieve such an output.

Type Region1 Region 2 Region 3 National
A 40 10 30 80
B 10 20 40 70
Total 50 30 70 150
percentage% 34 20 46

Create three combined request,

1. Type,Region1,Region2,Region3,National(region1+region2+region3)
2. 'Total',sum(region1),sum(region2),sum(region3),...
3. 'Percentage%',(sum(r1) / sum(r1+r2+r3)) * 100)..............

It's giving the expected result. Correct me if I am wrong.

Regards,
Anitha.B
• ###### 4. Re: calculated rows
Currently Being Moderated
All the values Region1,Region2,Region3 come from region table.

There are only two column in report. Region EmpCount.

I need percentage of empcount for regions over grandtotal.
• ###### 5. Re: calculated rows
Currently Being Moderated
Hi,

If there are only two columns probably percentage will come as a column and not a row as below.

Region Empcount Percentage
r1,3,40
r2,2,20
r3,3,40
total,8,100

Create a logical aggregation column emp_count(count(employee)) in RPD. If there are three regions r1,r2,r3

"Region"."Region_name", filter ("emp"."emp_count using "Region"."Region_name" in ('r1','r2','r3')), (filter ("emp"."emp_count using "Region"."Region_name" in ('r1','r2','r3'))*100)/sum(filter ("emp"."emp_count using "Region"."Region_name" in ('r1','r2','r3'))

and apply grand total

Regards,
Anitha.B
• ###### 6. Re: calculated rows
Currently Being Moderated
Hi Anitha,

Do we apply this formula in answers in the new columns. I already created the emp_count measure in rpd.
``````filter ("emp"."emp_count using "Region"."Region_name" in ('r1','r2','r3')), (filter ("emp"."emp_count using "Region"."Region_name" in ('r1','r2','r3'))*100)/sum(filter ("emp"."emp_count using "Region"."Region_name" in ('r1','r2','r3'))
{code}

Regards,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ``````
• ###### 7. Re: calculated rows
Currently Being Moderated
yes. you have to define them in the column formula.

#### Legend

• Correct Answers - 10 points