RowNumber for group in SSRS 2005

问题:

I have a table in a SSRS report that is displaying only a group, not the table details. I want to find out the row number for the items that are being displayed so that I can use color banding. I tried using "Rowcount(Nothing)", but instead I get the row number of the detail table.

My underlying data is something like

ROwId   Team      Fan

1       Yankees   John
2       Yankees   Russ
3       Red Socks Mark
4       Red Socks Mary
...         
8       Orioles   Elliot
...         
29      Dodgers   Jim
...
43      Giants    Harry 

My table showing only the groups looks like this:

ROwId   Team
2       Yankees
3       Red Socks   
8       Orioles
29      Dodgers 
43      Giants  

I want it to look like

ROwId   Team
1       Yankees
2       Red Socks   
3       Orioles
4       Dodgers 
5       Giants  

回答1:

You can do this with a RunningValue expression, something like:

=RunningValue(Fields!Team.Value, CountDistinct, "DataSet1")

DataSet1 being the name of the underlying dataset.

Consider the data:

Creating a simple report and comparing the RowNumber and RunningValue approaches shows that RunningValue gives your required results:

回答2:

You can easily achieve this with a little bit of vbcode. Go to Report - Properties - code and type something like:

Dim rownumber = 0
Function writeRow()
  rownumber = rownumber + 1
  return rownumber
End Function

Then on your cell, call this function by using =Code.writeRow()

As soon as you start using groups inside the tables, the RowNumber and RunningGroup functions start getting some weird behaviours, thus it's easier to just write a bit of code to do what you want.

回答3:

I am not convinced all suggestions above provide are a one for all solution. My scenario is I have a grouping that has has multiple columns. I could not use the agreed solution RunningValue because I don't have a single column to use in the function unless I combine (say a computed column) them all to make single unique column.

I could not use the VBA code function as is for the same reason and I had to use the same value across multiple columns and multiple properties for that matter unless I use some other kind of smarts where if I knew the number of uses (say N columns * M properties) then I could only update the RowNumber on every NxM calls however, I could not see any count columns function so if I added a column I would also need to increase my N constant. I also did not want to add a new column as also suggested to my grouping as I could not figure out how to hide it and I could not write a vba system where I could call function A that returns nothing but updates the value (i.e. called only once per group row) then call another function GetRowNumber which simply returns the rownumber variable because the colouring was done before the call so I always had one column out of sync to the rest.

My only other 2 solutions I could think of is put the combined column as mentioned earlier in the query itself or use DENSE_RANK and sort on all group columns, i.e.

DENSE_RANK() OVER (ORDER BY GroupCol1, GroupCol2, ...) AS RowNumber