Filtering on a calculated field

Question

Hi,

 

Ok i have a table that holds people and scores i just want to see the top 10 now the filter for top N doesnt give me the right things because there may be duplicate scores so what i did is assign it a running value now i want to show only where that value is <= 10 but im not sure how to do this ?

 

Any ideas or better ways to filter

 

Regards

Curt

Answer 1

http://msdn.microsoft.com/en-us/library/dd220417.aspx

---

Remember to mark as an answer if this post has helped you.

Answer 2

Are you writing a SQL query or MDX to fetch the data ?

If SQL use dense_Rank and bring it as a Column and filter in reports with a parameter or Use a Outer Query to filter Depending on the rank column.

If MDX use Orderby along with sort,Rank functions to obtain the same.use this link for Rank functions in MDX.

 

---

Rakesh M J

Dont forget to mark it as Answered if found useful

MCTS,MCITP,MCSS

http://myspeakonbi.blogspot.com/

Answer 3

Hi Curt,

 

Thanks for your post and Rakesh’s professional reply.

 

According to your description, you would like to filter the top 10 scores of the table data. If you use a T-sql to retrieve the dataset, you can use dense_rank function setting order by score to generate the sequence ID, please refer to the steps below:

First to read: I assume that your table name is GRADE, the column to filter is SCORE.

1. Edit query string blow in your dataset:

select DENSE_RANK() over(order by SCORE DESC) AS Number,PERSONNAME,SCORE

from GRADE

2. Drag a table to the report design surface, drop the fields PERSONNAME, GRADE to the table.

3. Click any textbox in the table, and then right-click cross handle of the table, select tablix Properties.

4. Switch to Filters tab, click Add.

5. Choose Number item in the Expression drop down list, set data type to Integer, Opertator to <=, and Value to 10.

6. Click ok.

 

After you complete the steps above, click preview to verify whether it works as you expected.

 

If you have anything unclear, please feel free to let me know.

 

Thanks,

Bill Lu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.