BusinessObjects Board

how to apply rank at designer level?

building semantic layer
#1

Hi Guys,

i have requirement where i suppose to apply rank on salepersons based on premium at designer level.iam currently using BO 6.5 version.my requirement suchthat i have to calculate TOP 5 salepersons data based on premium.iam uanble to see any RANK ()OVER…function inthe designer of BO 6.5.how to acheive this in desiner level and using MS SQL SERVER As backend databse.

I have tried using the function "TOP 5 saespersons "…but inthe object definition …but it doesnt work properly.

is there any other method apply rank in the designer.plz help me in this .

srinath (BOB member since 2007-09-12)

how to apply rank at designer level?
#2

Hi,

What version of BO you are using, If it is XI R2 then you can make use of Database rank function which is available on the designer level.
if it is the version earlier to XI R2, then it is always good to do it on the reporter level.
When do you say Top 5 rank functions does not work properly. hen what is exactly happening. Does your sales person have distinct values or there are multiple occurances of sales person depending on some other dimension value.
So it will be better if you elaborate more.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

#3

hi…thanks for reply…

iam using BO 6.5 and i have distinct values in salesperson column…i want to calculate top 5 salespersons depending on the top premiums…i applied rank over function…but its confusing how to apply in the object definition in the designer…is there any way to do …provide me the steps…

srinath (BOB member since 2007-09-12)

#4

What do you mean ‘It Doesn’t work properly’…that doesn’t really give us very much to go on does it? What SQl is generated? What results do you get? What were you expecting.

Nick Daniels :uk: (BOB member since 2002-08-15)

#5

i mean to say the results are not correct with “TOP 5 function”.
how to do the ranking thing at designer level using “Rank over()…” function.i cant see RANK fuction inthe object definition in BO 6.5. Help me in providing any other altenative solution for this …iam using BO 6.5…

srinath (BOB member since 2007-09-12)

#6

Hi,
In the designer most of the functions which are supported by the database you are using will be supported by the designer.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

#7

Thanks omkar for replying…iam using BO6.5 and sql server as database

i just want to know how to rank(top 5) of the salespersons based premium at designer level.my requirement is such that i cant apply at report level and also where i have to apply ranking at object level or in the derived table…plz provide me the steps …

srinath (BOB member since 2007-09-12)

#8

Read this recent thread discussing the same issue

.

haider :es: (BOB member since 2005-07-18)

#9

Found from google for SQL SERVER

Example for TOP 3:

SELECT TOP(3) OVER(ORDER BY OrderID)
  OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate;

Search search… search… search first…

BO_Chief :us: (BOB member since 2004-06-06)