Rank() in WEBI for BOXIR3.1

[Former Member]

Hi,

I want to use Rank function in webi BOXIR3.1 SP3 FP5. DataBase used is SQL server2005.

I am using the function : Rank(Total;Machine)

"Total" is measure and "Machine" is dimension based on which ranking is done. So I get result in the below mentioned way.

Eg:

Machine Total Rank

M1 1000 1

M2 1000 1

M3 800 2

M4 800 2

M5 600 3

M6 200 4

But I need that each row should be ranked with different number.

Machine Total Rank

M1 1000 1

M2 1000 3

M3 800 4

M4 800 6

M5 600 7

M6 200 8

Previously in BOXIR2, there use to be Rank() and Dense_Rank() as two seperate functions, but in XIR3.1 the Rank() is working as Dese_Rank()...!!!

How do I implement to have different rank values for each row?

Regards,

Nisha

[former_member213493]

Hello Nisha,

i would like to know how the dense rank will work exactly. Foremost, In your example table 1 you used the rank function whereas in table 2 did you dense ranking..??? please let me know..!!

Many Thanks,

Vinodh

[Former Member]

Did you try using forEach with Rank().

Click the arrow next to the Based on measure if the For Each box is not already visible.

Set the For Each dimension to Department by dragging and dropping the dimension.'

Hope this will work out for you.

[Former Member]

Hi,

I tried to use Foreach(Dimension). but still not working.

[Former Member]

Try this :

- Open the PRM file for your RDBMS in a text editor.

- Scroll to the RDBMS section of the PRM file.

- Verify that the following parameters and values are present:

OVER_CLAUSE = Y

RISQL_FUNCTIONS = <list of functions used>

If you want to use an analytic function that is not listed, type the name of the function at the end of the list.

Save any modifications and close the file.

You need to restart Designer for any changes to the PRM file to take effect.

Now use custome SQL in the Report

like

RANK() OVER (PARTITION BY ColumnName ORDER BY SUM(measure)DESC)