CODEWITHSUNDEEP
sql-serverssrs-2012recordssrs-tablixrowcount
missscripty
missscripty

Reputation: 537

SSRS Report Table Add Column Expression Record x of y?

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!

Upvotes: 4

Views: 628

Answers (1)

tezzo
tezzo

Reputation: 11105

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.

Upvotes: 4

Related Questions