Not able to find rank() or dense rank() function in IDt adn UDT

[former_member531208]

Hi,

in BI 4.1 UDT and IDT not bale to find the rank().Please let me know how can use this functionality at IDT/UDT level.

Regards

Neetha

[PPaolo · Accepted solution]

Hello

you don't need to actually see those SQL functions in the function list to use them.

The list is there only as a helper to remind of the function name and syntax.

If your database supports Rank and Dense_Rank then you can directly use them in the object SQL definition or in a derived table.

An exemple of Rank usage to list employees by salary could be : 

Rank() over order by (salary)

the rank will be applied to the list of records you have in your query (with a salary value per record)

Best regards

PPaolo

[former_member531208 · Accepted solution]

Hello PPaolo,

i tried that but no luck the function is not at working  and am getting syntax error messgae.

regards

Anitha

[PPaolo · Accepted solution]

Hello

what database are you using?

Can you provide here an example of the table(s) you want to do the ranking onto and tell us how you would want to rank?

Best regards

PPaolo

[former_member531208 · Accepted solution]

Hello PPaolo,

The data base is Sql Server 2008.

Am trying ton achieve find the top 5 based on the Rank function in the universe using derived table instead of using the Rank() at webi report level.

I tried both Rank() and Dense rank() function both is not working,throwing the below error

Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'order'.

The sql statement have used is

select MaterialId,MonthId,Value,Rank() over order by (Value) as rank from TOMAS_P.TOMASBO.MaterialDemands where rank<5

Regards

Anitha

[PPaolo · Accepted solution]

Hello,

SQL Server 2008 supports the ranking function.

I guess that you have a problem because you are missing the parethesis after the over keyword (I didn't specify them in my original answer).

Try with:

  Rank() over (order by (Value))

But the SQL you created won't work anyway as you cannot just use the rank value in teh where clause to select the top 5. You should build a subselect.

WHat you could do:

in your universe data foundation build a derived table containing the ranking function for your table:

"select *, rank() over(order by (Value) as MyRank from TOMAS_P.TOMASBO.MaterialDemands"

Then you use this table to build an object on the MyRank column and use that object to filter in the query panel, this generates a subselect.

In the following picture you can see a sample of a derived table (RankingTable) with the ranking function:

And here the query panell with the object created on the rank column

Hope that it helps

Regards
PPaolo

[former_member531208 · Accepted solution]

Thanks PPaolo, adding bracket has reolved the issue