November 12, 2007 at 4:03 am
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)
November 12, 2007 at 11:04 am
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...
November 13, 2007 at 3:04 am
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.
November 14, 2007 at 8:11 am
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.
November 14, 2007 at 8:22 am
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
November 14, 2007 at 9:33 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply