May 23, 2011 at 5:12 pm
Hello,
I am using a store procedure in several reports and it returns all the records in a dataset, however I have a "Top 20" report that displays the top 20 records in a "Location" group. How can i limit the records being displayed in each group to 20 only.
Thanks!
R.
May 24, 2011 at 7:10 am
Use ROW_NUMBER(), partitioned by your group. Return only rows numbered 1 - 20.
May 24, 2011 at 10:58 am
Thanks for the reply and sorry for the ignorance on this, but how do you tell SSRS to only display records 1 to 20? I am sure the Visibility property is not an option in this case. I am able to use the rowNumber() function as you suggested, but I dont know exactly how to apply it in this case and to what expression or property.
Thanks again.
R.
May 24, 2011 at 11:21 am
I would only return records 1 - 20 from the dataset to SSRS. If you do not want to restrict your sp to the top 20 records in each group then you have to filter your dataset in SSRS. It has been a while since I used SSRS on 2005, but I still think you can add a filter to the dataset. In SSRS right click the Dataset and choose properties. Look for a tab labeled Filter.
May 24, 2011 at 1:40 pm
Yes, I definitely would like to do the filtering in SSRS as the same procedure is being utilized alot in the same report set and don't want to filter within SQL. I tried using the RowNumber function within SSRS under the Filters tab and it does not allow me to use it by reporting an error. I tried the following :
Filter Expression: =RowNumber("Table1_Group1") >= Cint(20)
When I tried using this, it gives me an error. I have a RowNumber for each record being returned from the dataset and I cant really utilize the row number and filter >= 20 as those numbers change based on the way the main query places the records.
Any other ideas on how I can limit the number of records being displayed within SSRS? I have 5 groupings based on a type field and I only want to display 20 records per group as the report is a "Top 20" report.
May 25, 2011 at 10:00 am
Good Question! I've been trying a bunch of things and nothing has even come close to working.
The only way I can see working is using multiple datasets, one for each of your grouping. Potentially you could use a Stored proc and build the query dynamically based on a parameter.
Set @CMD = "Select top 20 Columns, n...
from ...
where ...
group by " + @Type
order by ...
Then have 5 tables that link to the datasets, each of which has a different @type passed to the SP.
It`s a kludge and I feel like there should be an easier way but I don`t see it at the moment.
May 25, 2011 at 10:06 am
I agree! That is the only solution I can think of and what I am trying to implement right now. I wonder if SSRS 2008 has the ability to limit the number of records display within the table or field properties.
Thanks for the reply!
R.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply