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
=(RunningValue(CountDistinct(Fields!SerialNo.Value), Sum, "DataSetName") mod 2) = 0
=(RunningValue(CountDistinct(Fields!SerialNo.Value), Sum, "DataSetName") mod 2) = 1
Leave a Reply