Often you'd like to view a row number, or rank on a report in SQL Server Reporting Services. This is simple enough on a line item detail report with the RowNumber function. The reference to Nothing sets our scope.
Just use:
=RowNumber(Nothing)
But now how about something a little more challenging. Perhaps we have a list of 1000 Customers and we group them by State on a table. Perhaps the results will be sorted in order of Sales in decending order by State. Perhaps this is a Top 10 Report? Generally on these types of reports, it's nice to illustrate a ranking next to each row, not just the resulting data. To do so we'll need a different combination of functions to get the desired result.
First, create your detail, and a simple grouping by State. In the State Row, add a column. To this we will add the expression:
=RunningValue(THEFIELDYOUGROUPON, CountDistinct, Nothing)
or, in this example
=RunningValue(Fields!State.Value, CountDistinct, Nothing)
What might have you shaking your head is the use of CountDistinct. That's essentially what a grouping will do, in T-SQL, in SSRS...it's going to distinct your values and do some magic. Samples on the web usually illustrate the use of the Count function, but that will not produce the desired result in any grouping row.
