Report Builder and TOP or ROW_NUMBER()

Question

Post reply

Report Builder and TOP or ROW_NUMBER()

Peter Wilde

Old Hand

Points: 334
More actions
November 12, 2007 at 4:03 am

#181039

Is there a way to create a report using SSRS's Report Builder tool that allows you to return just the TOP 10 results.
In T-SQL you'd achieve this by specifiying [font="Courier New"]select top 10 * from sometable order by somecolumn[/font]
I'm trying to create a report based on an existing model and can't seem to find where to specify that I only want the top 10 results. Any pointers for a Report Builder newbie would be appreciated. (btw by Report Builder I mean the ad-hoc query tool that allows end users to create their own reports based on a model)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alex Cooper SSC-Addicted Points: 499 More actions · Answer 1

From Query analyzer the same command can be used:

Select Top 10 *

From Some-table

Where Whatever-you-want

I render all reports through QA before trying to process in RS

Hope this helps...

Peter Wilde Old Hand Points: 334 More actions · Answer 2

Yeah - I know how to use query analyzer to get the top 10 results. But I want the end users of my model to be able to create their own reports and be able to limit the results to the top 10 or top 20 etc.

Just wondering if there was some sort of report filter in the report builder tool that allowed this.

John Stephenson Old Hand Points: 324 More actions · Answer 3

The only way I can think how to do this is to build the top 10 query into the model - add another copy of the table in Data Source Views and then replace it with a select top 10 query. You can do this by right clicking on the table and then picking Replace Table -> With New Named Query.

si_downes SSC Eights! Points: 920 More actions · Answer 4

Depending how you populate your fact table you could add an auto increment column then have a filter in report builder to return WHERE autoincr < 10. just a thought

Peter Wilde Old Hand Points: 334 More actions · Answer 5

Thanks for the replies.

Unfortunately the replacing of a table with a named query approach means I have to "hard code" that I want 10 results. Ideally I'd like my end users to be able to limit results to the top 5, 10, 20 etc.

The second approach of an auto-incrementing column seems ok in a simple model, but I don't think it would work when you have several tables that make up the model.

Basically it seems like there is no easy way to achieve this on the end-user Report Builder.... quite a shame as I think it would be a useful feature.

Thanks all.