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
You can easily achieve this with a little bit of vbcode. Go to Report - Properties - code and type something like:
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.
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.
You can do this with a RunningValue expression, something like:
DataSet1
being the name of the underlying dataset.Consider the data:
Creating a simple report and comparing the
RowNumber
andRunningValue
approaches shows thatRunningValue
gives your required results: