Adding Row Number in SSRS Report

Sometimes users demand that the output of entries in a tablix contains a column with sequential number of the rows.  Normally it would be sufficient to just add the RowNumber({Scope}) function.  However, if your DataSet has a grouping then you will find that the row number that is displayed is no longer sequential but each row has the total number of grouped items that is added to the previous row total for the previous grouping.  I spent a good two hours trying to figure out how to get around the issue but finally came to the following expression to use for my DataSet:-

=RunningValue(CountDistinct({Your Field Value to Group}, {Your Data Set Row Group Name}), Sum, {Your Tablix Name}))

The above expression seemed to do the trick for me – I was getting sequential row numbers in my row number column within the Tablix.

After doing the above you may wish to alternate the row colours – a good article on this can be found at:-

https://msdn.microsoft.com/en-us/library/dd255249.aspx

In the above article you would have found the standard modulus way of achieving the alternating colours by:-

=IIF(RowNumber({Your Data Set Row Group Name}) Mod 2, "White", "PaleGreen")

When I ran the above using my DataSet the alternating row background colours did not produce the desired result – again there was excessive skipping before row background colours would change. So to overcome this issue – here is the expression I had to use:

=IIF(RunningValue(CountDistinct({Your Field Value to Group}, {Your Data Set Row Group Name}), Sum, {Your Tablix Name}) MOD 2, “Silver”, “LightSteelBlue”)

In the above you can see that I had to substitute the original expression to apply the Modulus, and that produced the alternating rowcolours in my Tablix.

:

Leave a Reply