cancel
Showing results for 
Search instead for 
Did you mean: 

Understanding of Rank Function in Webi Report

0 Kudos

Hi Guys,

I have seen many forums but didn't get the Rank function Syntax in webi report. I have query in sql as Rank() over( partition by Dimension1,Dimension2 order by Dimension3 asc , Dimension4 desc, Dimension5 desc.

I need to implement the same in Webi report. But unable to understand the syntax. Do anyone has any idea?

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi ,

You can apply rank formula as shown below:

int Rank(measure;[ranking_dims][;Top|Bottom][;re

set_dims])

Where

1) measure : The measure to be ranked Measure Yes

2) rank-ing_dims : The dimensions used to rank the mea-sure Dimension No list

3) Top|Bot: to Sets the ranking order:

Top - descending

Bottom - ascending

4) reset_dims The dimensions that reset the rankingNo ( Top is default)

Rank() over( partition by Dimension1,Dimension2 order by Dimension3 asc , Dimension4 desc, Dimension5 desc.


So as per your requirement your formula should be:


Rank1 = Rank([Measure];(Dimension4;Dimension5);Top;(Dimension1;Dimension2))

Make Rank1 as Dimension


Now Rank2 = Rank([Measure];(Dimenson3;Rank1);Bottom;(Dimension1;Dimension2))


That's how Rank2 will give you the ranking as per the Database query mentioned above.


Explanation : Here Rank1 Dimension object is created to generate rank in descending order as per the Dimension 4 & Dimension 5 &then

Rank2 Object is created to Generate rank on Basis of Dimension 3 & Rank1 in ascending order.


Thanks,

Swapnil

0 Kudos

Hi Swapnil, Appreciate your quick response. You can see in my requirement that, there is no measure. But in formula you suggested [Measure] is included. I want to know why Measure is to be included in Webi Rank funcction where as in sql or universe we can do it without measure.

Can you explain below points in brief,

1) measure : The measure to be ranked Measure Yes

2) rank-ing_dims : The dimensions used to rank the mea-sure Dimension No list

Former Member
0 Kudos

Yes. In WEBI you will be generating rank on some measure. So you need to provide the measure on which you need ranking.

In case of database you apply rank irrespective of measure because database Rank function supports that kind of formula. But in case of BO you have to apply Rank with respect to some measure function.

To apply the rank function as required, you can create the object with the formula in Universe & drag the object on report.

This object will give you ranking as required.

Thanks,
Swapnil

Former Member
0 Kudos

Hello Shesidher,

I would like to point out that when doing Rank we require some measure value to be ranked based on some dimensions. The SQL that you have posted is correct but is missing some part of Select Statement which would be somewhat like below

Select Dimension1,Dimension2, Measure1

Rank() over( partition by Dimension1,Dimension2 order by Dimension3 asc , Dimension4 desc,Dimension5 desc

From TableName



So you see in the Select clause of the SQL we have to specify a measure for which the SQL Engine will designate the Rank based on measure Value else it will be meaningless.


Now coming to your questions I guess first one is answered for the other one the list of dimension on which a measure is ranked by is shown in the highlighted part of the below code.


=Rank([Revenue];([Country];[State]);Top)


so here the Revenue will be ranked based on Country and State and for the Order by you can go to block and apply the Sorting desired in your requirement.


Hope I was able to explain.


Regards

Niraj

0 Kudos

Hi Swapnik,

I cannot create Rank object in universe because of some restrictions. I can make Dimension3 , Dimension4 ,Dimension5 as measures. So can you please suggest me which object i should make as measure and use in first parameter of Rank function.

Former Member
0 Kudos

What is your backend database?

Can you please give me some data with snapshot of report.

Thanks,

Swapnil