I have an SSRS, which is compiled of report tables, each tied to a stored procedure, all keying off the same ID. I pass the ID automatically, through the UI.
Because there is so much information in the report, For each section, I wanted to add a column to each table, with an expression. It would give the record count. This would mean, that if the table had an artist's albums, there would be a column for Album Record, and it would be something like this:
Arist: Pink Album Record Album Name Album Release Date Etc 1 of 5 2 of 5 3 of 5
To build the pieces of the Album record, I added a row counter to the stored procedure. This is that SQL line:
,ROW_NUMBER() OVER (PARTITION BY pc.ID ORDER BY ARID.ID) 'Album Record Number'
pc.id is the ID parameter that is passed, which means, we only pass and retrieve 1 pc.ID. The ARID.ID is for the sub-record ID's.
This creates the first piece. Record 1,2,3.
In the SSRS table, I add a column and I make an expression:
=Fields!Album_Record_Number.Value & " of " & Count(Fields!ARID.Value,"ID")
This should give me "1 of 3", "2 of 3", "3 of 3".
When I attempt to preview the report, I get an error:
An error occurred during local report processing. The definition of the report '/MainReport' is invalid. The Value expression for the text box 'XYZ' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string that is equal to either the name of a containing group, the name of a containing data region or the name of a dataset.
Anyone know how to do what I'm trying to do? I'm dreading that I may have to add the total count to the stored procedure, which means I would have to add that to all my stored procs. I was hoping an expression in a table column would do the trick.
Any suggestions would be appreciated!
I think you already using Groups so you can use a similar expression:
=RowNumber("YourAlbumGrouping") & " of " & CountRows("YourAlbumGrouping")
RowNumber function returns a running count of the number of rows for the specified scope.
CountRows function returns the number of rows in the specified scope, including rows with null values.