Every now and then I get the requirement to add row numbers to a table in SQL Server Reporting Services. Usually this is simple enough using the row number function - http://msdn.microsoft.com/en-us/library/ms159225(v=SQL.100).aspx. However, typically becomes a lot less straight forward when requirements are changed slightly!
In this case I was developing a monthly report which returned summarised information by account code for input into a finance system and included several detail sections for auditing and reconciliation. The summary table required a sequence number, which reflected the row number of the table. I had no joy scoping the row number function to the table or group, so started thinking about alternative solutions. The report contained multiple sections using the same source query, so returning multiple queries was inefficient and created duplicate code. Given the tablix is grouped by account code, it’s effectively returning a distinct list of account codes. Therefore, the row/sequence number is actually the row number of the account code in a distinct list of the account codes. Once you start looking to utilise this, it’s actually fairly simple to get the desired row numbers.
For this example I created a simple table of sample data containing two columns – Account Code and Amount.
I then created a simple tablix grouped by account code – displaying the account code, amount, and a distinct count of the account code. Looking at the output below it’s pretty easy to recognise that the row number is a cumulative sum of the distinct count of account codes.
Conveniently there’s a function in SSRS to do just that! RunningValue - http://msdn.microsoft.com/en-us/library/ms159136.aspx. Running value accepts three parameters…
expression - the expression or field to aggregate, in this case the account code field.
function - the aggregate function to apply, in this case count distinct.
scope - the name of the dataset, data region, etc… in this case the tablix.
Having not renamed the tablix, I replaced the distinct count field with the following expression…
=RunningValue(Fields!AccountCode.Value, CountDistinct, "Tablix1")
…and there we have it, row numbers in a tablix group.