Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSRS Row Number within table excluding hidden rows

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

like image 897
Cooper Cripps Avatar asked Sep 23 '13 06:09

Cooper Cripps


1 Answers

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:

enter image description here

enter image description here

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)

enter image description here

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:

enter image description here

like image 179
Ian Preston Avatar answered Sep 18 '22 13:09

Ian Preston