on 06-01-2016 7:36 AM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.