7 Replies Latest reply: Jan 21, 2013 12:05 AM by 901916 RSS

    calculated rows

      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.



      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
          Robert Angel

          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.


          sum(region1 by type) / sum(national)

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


          • 2. Re: calculated rows
            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
              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.

              • 4. Re: calculated rows
                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

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

                  Region Empcount Percentage

                  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

                  • 6. Re: calculated rows
                    Hi Anitha,

                    I am very much grateful for your reply.

                    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'))
                    • 7. Re: calculated rows
                      yes. you have to define them in the column formula.