How to calculate top 100 in obiee 11g analytics report?

Question

Hi,

I am creting analytics report in obiee 11g.

I have 5 columns branch, district, outstanding balance, loan status, loantype.

I want to display the data of above 5 columns in the analytics report..

For this report, i want  to add custom rows..

My requiremt is, I want to dispaly the top 100 outstanding balances, total outstanding balance

and i want to add custom calculation like (total outstanding balance/ top 100 outstanding balance)*100

Please help me,

I have tried, i am not getting .

Please help me, How to calculate these?..

Thanks & Regards,

A.kavya

cesar.advincula.o · Answer

Hello Kayva,

First we have to define, the rank, do you want, ask yourself, do you want rank by period, branch etc, so you have to use "RANK ( BY DIMENSIONS)".

I did a sample, with the similar columns that you use, please follow up.

I did 4 criterias using by "UNION" (not "UNION ALL", not "INTERSTECT", not "MINUS"):

And my results are:

Check it out:

1st  criteria:  -I have the sum 187 157 538.57 as the sum of the top 10 projects, by the Company(Empresa) and by the Period (MesAñoDocum)

2do criteria: -Check the second Row "SumaTotalProyectos" which is the Total Sum of the Company in Ene-2015  (January 2015) for all projects. Which is 211 249 318.42

3rd criteria:  -Check the 187 157 538.57 which is the sum of the top 10 projects

4rd criteria:  -Which is the percentage of it,  187 157 538.57 / 211 249 318.42 = 88.60%

How do you setting it?

1. Lets check Criteria 1: the sum of the top 10 projects

Check the RANK Filter

2. Lets check Criteria 2: Total Sum of the Company

3. Lets check Criteria 3: Sum Ranking

4. Lets check Criteria 4: the Percentage

Note:

As you see i have two projects "columns" i need it to get the sum of ranking, for example, you have to do a ranking by branch and get the same, you have to put twice this column in your criterias, and after it you could exclude.

Kind Regards,

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD · Answer

Hi,

as you said, i have tried.. But i didnt get correct data..

I used unions also.. i didnt get correct. I am getting same results for total outstanding bal, total top 100 outsatnding bal, and  %ge share from npl.

please see the below screen shot.

Please help me

Thanks in advance,

A.kavya

cesar.advincula.o · Answer

Yes,

1 column: outstanding balance

2 column: sum of the rank 100

case when rank(outstading balance) < 101 then outstaibd balance else 0 end

3.the column that you want to get:

Total outstanding / (case when rank(outstaibd balance) < 101 then outstaibd balance else 0 end) *100

Please, check in your formula your "aggregation rule" with SUM. That all.

Kind regards,