SSRS Row Number within table excluding hidden rows

Question

I use the following expression to obtain a row number for a table in SSRS:

=RunningValue(CountDistinct("Table1"),Count,"Table1")

I also use an expression for the row visibility property. Lets just say that the Visibility expression is

=IIf(Fields!MyField.Value + Fields!MyField.Value <> 0, False, True) 

My expression for the row number does not consider if the row is visible or not.

I could obviously change my dataset query, but is it possible to just alter my Row Number expression to only include rows that aren't hidden? Thanks

Answer 1

You can probably achieve this by combining the logic of your two expressions.

Say you have a simple DataSet and a simple Tablix based on this:

Here, RowNum is calculated as:

=RunningValue(Fields!val1.Value, CountDistinct, "Tablix1")

Next, let's hide some rows using an expression based on the other two fields:

=IIf(Fields!val2.Value + Fields!val3.Value <> 0, False, True)

This breaks RowNum, but we can amend the expression to ignore the hidden rows. We do this by NULLing them out (i.e. for SSRS set as Nothing) - CountDistinct will not consider any Nothing values:

=RunningValue(IIf(Fields!val2.Value + Fields!val3.Value <> 0, Fields!val1.Value, Nothing)
    , CountDistinct
    , "Tablix1")

Now RowNum is ignoring the hidden rows as required: