SQL Server Reporting Service: can anyone explain this strange behaviour when counting rows with RowNumber?

Question

While my initial problem counting the running number of rows in a table has already been solved, I would still like to know the origin of the following behaviour in SQL Server Reporting Service (I hope the example is clear enough):

I created a report with the following dataset:

select 1 AS Column1, 'First' AS Column2
union
select 2 AS Column1, 'Second' AS Column2
union 
select 2 AS Column1, 'Third' AS Column2
union
select 3 AS Column1, 'Fourth' AS Column2
union
select 3 AS Column1, 'Fifth' AS Column2

In my report I have created a List with a Group called 'list1_Details_Group' which is based on 'Column', I have added a table in this List with two columns; one with a row number ('=RowNumber("list1_Details_Group")') and one with 'Column2'. When I put a 'Page break at end' of my Group the output is as expected:

However when I add a Textbox containing the following: '="Number of items: " + ReportItems("RowNumber").Value.ToString', the first rownumbers on the next pages go wrong:

The first rownumber of the table gets the same value as the value of the textbox from the previous page! I did not expect this behaviour, can anyone explain why this occurs?

BTW: when the contents of the Textbox is '="Number of items: " +RowNumber("list1_Details_Group").ToString', the report is printed without problems!

Answer 1

Where are you adding the textbox? In the detail group? Under the table?

I would take a different approach to this. I would present it all in a single table show the group header and footer (with merged columsn) and do a count of the number of items in the group by making use of the scope function. This way it is always consistent and there won't be any issues with the row counts.

The Rownumber is very brittle and small changes to your layout or expressions could break it due to different subtle details.