I need to create a report using SQL Server Reporting Services (SSRS) and want to show row number which can ignore hidden rows of table.One possible solution is to use RowNumber built-in function, but things gown worst when we hide some rows in table.
RowNumber returns a running value of the count of rows within the specified scope.
Now we want to hide rows which contains â€œaâ€ in short name column by following expression.
=IIF(InStr(Fields!MONTH_S_NAME.Value,"a") > 0 , True, False)
We can see that this breaks RowNumber. So what can we do now.
Solution is to use RunningValue function with Sum up with condition as shown below.
RunningValue function returns a running aggregate of all non-null numeric values specified by the expression, evaluated for the given scope.
=RunningValue(IIF(InStr(Fields!MONTH_S_NAME.Value,"a") > 0 , 0, 1) , Sum , "Tablix1")
=RunningValue(IIF(Fields!MONTH_S_NAME.Value.Contains("a") , 0, 1) , Sum , "Tablix1")
Here is final output.