How to Generate Row Number Of only Visible Rows In SSRS

How to Generate Row Number Of only Visible Rows In SSRS

Added by Nasir Mahmood updated on Friday, June 28, 2019

Problem:

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.

ssrs table report

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)				
				

ssrs expression

Result

ssrs expression

We can see that this breaks RowNumber. So what can we do now.

Solution:

Solution is to use RunningValue function with Sum up with condition as shown below.

ssrs expression

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")				
				

OR

=RunningValue(IIF(Fields!MONTH_S_NAME.Value.Contains("a") , 0, 1)
    , Sum
    , "Tablix1")				
				

Here is final output.

ssrs hide rows count

Related Tags

About

32 Tutorials
27 Snippets
6 Products

More

Contact Us

Contact us

Stay Connected