SSRS row number in table excluding hidden rows

Question

I use the following expression to get the 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 expression Visibility

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

My expression for the line number does not take into account whether the line is visible or not.

I could change my query to a dataset, but can I just change the Row Number expression to include only rows that are not 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, hide some lines using an expression based on two other fields:

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

This breaks the RowNum, but we can change the expression to ignore hidden lines. We do this with NULLing out (i.e., for SSRS set to Nothing ) - CountDistinct will not consider Nothing values:

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

Now RowNum ignores hidden strings as necessary: