question

ChandrasekaranR-1196 avatar image
0 Votes"
ChandrasekaranR-1196 asked Joyzhao-MSFT commented

row numbers only for Visible rows not hidden rows in SSRS

I have created one SSRS and hide few rows. While applying Rownumber(nothing), its givning all the hidden rows as well.

I have to excluding that hidden rows.. I need row numbers only for Visible rows.

Kindly help on this.

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT converted comment to answer

Hi @ChandrasekaranR-1196
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:
102389-05.jpg
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)

102413-06.jpg
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:
102308-07.jpg
The information comes from:https://stackoverflow.com/questions/18953301/ssrs-row-number-within-table-excluding-hidden-rows
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




05.jpg (18.9 KiB)
06.jpg (9.1 KiB)
07.jpg (8.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT commented

Hi @ChandrasekaranR-1196 ,
Here is my local test.

  • I have a simple table like this:

102451-12.jpg

  • I sort all the rows of the table by using the following expression

102453-08.jpg

  • Hide the visibility of certain rows (when "Employee ID"<6000 hide rows)

102462-09.jpg

  • Modify the expression of the RowNumber column:

102464-10.jpg

 =RunningValue(IIf(Fields!EmployeeID.Value > 6000, Fields!EmployeeID.Value, Nothing) , CountDistinct, "Tablix5")
  • Preview:

102310-11.jpg
Please follow the steps in my answer above, it will resolve your issue perfectly. If you have any questions, please feel free to let me know.
Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



12.jpg (31.3 KiB)
08.jpg (27.2 KiB)
09.jpg (67.3 KiB)
10.jpg (32.7 KiB)
11.jpg (16.0 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Thanks for Responding. The Problem is

I am using the below expression for hiding the rows.

=IIf(count(Fields!customerid.Value,"customerid") >1,True,False)

If i will use in RunningValue expression i got error. becoz aggrage function not accpting in rrunning expression

=RunningValue(IIf(count(Fields!customerid.Value,"customerid") >1,Fields!customerid.Value,Nothing), CountDistinct, "Tablix5")

Could you pls help on this

0 Votes 0 ·

Hi @ChandrasekaranR-1196 ,
Sorry to reply so late.
"Tablix5" is the TableName in my test table, you need to replace it here.

0 Votes 0 ·

becoz aggrage function not accepting in running expression

Here you use the count function, I am not sure if this is the cause of the error. Is your report allowed to change Row Visible conditions.


0 Votes 0 ·