How can I work out Row numbers when Row_Number doesn’t work?

For a variety of reasons (such as this one, where the alteration of the underlying dataset is not allowed) it is sometimes not possible to just calculate the row number for a table using Row_Number.

In these instances you can instead use a combination of CountDistinct and RunningValue, as I mentioned in my original answer.

CountDistinct will give you the number of rows that match in the dataset, for each row (so, usually 1 assuming Serial Numbers are unique)

RunningValue will give you an increasing total of values across each row in a table… and with this you can add up all those `CountDistincts’.

In this example the OP wanted to slit a single dataset into two tables, so the proposed solution was to set the Row Visibility for the two tablixes as follows

For Tablix

=(RunningValue(CountDistinct(Fields!SerialNo.Value), Sum, "DataSetName") mod 2) = 0

For Tablix2

=(RunningValue(CountDistinct(Fields!SerialNo.Value), Sum, "DataSetName") mod 2) = 1

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.