SSRS Report Builder 2012 - How to hide list based on field value?

Question

I'm using Report Builder 2012 to create a report. I have inserted multiple text boxes and other controls inside a list box so that I can hide all the controls at once just by hiding the list box. I'm using a SQL Server stored procedure to fetch rows of data. I'm using below expression to hide/show the list box.

=iif(Fields!certificateType.Value = "CT", False, True)

It works fine but it only checks the first row of data. If certificateType field is "CT" in the first row of data, it shows the list box but it doesn't hide the list box back for the next row of data in which certificateType is not "CT". It seems like list box visibility only checks the first row of data and applies it for all the other rows as well. How can i check the visibility of list for all the data rows?

Any possibility of getting some screen caps of the report in design mode, and preview mode added to this question? Can't completely visualize what you are describing. — R. Richards
I'll have to add a lot of details to the question for that which will make it too complex to understand for others. Can we take the discussion to Stackoverflow chat so that I can show you? — Jaspreet Singh
No problem, we may not have to do that. Let me ask a question, or two. Is your list box embedded in the table/matrix so that the expression is evaluated row by row? Or, is it just sitting out in the report body, by itself? That is all I was hoping to see. — R. Richards
I'll try my best to make it simple. There's this one main list box(rectangle) which just holds 5 other sub-list boxes (Each sub box has it's own controls). Now, based on the value of the field CertificateType, I need to show only 1 sub-listbox out of 5. When i run the preview mode, it checks the first row of data and shows a sub-box based on the value of CertificateType. It's supposed to show some other sub-box based on the next row of data but it doesn't. Only one sub-box stays on for the rest of the data. There's no table/matrix in my report. — Jaspreet Singh

R. Richards R. Richards · Accepted Answer · 2017-05-28T20:13:22

Okay, based on our chat I have updated this solution.

I mocked up some data that looks like this:

certificateType
---------------
AT
BT
CT
DT
ZT

I created a quick and dirty report with a list. In that, I added a rectangle with a textbox in it. I set the dataset for the list to the main dataset (DataSet1 in my case). I set the expression for the textbox to this:

=Fields!certificateType.Value

Image in design mode:

I clicked on the list, and in the Row Groups pane, I right-clicked the Details rows, and chose Group Properties. On the General section, I clicked Add to add a new group expression. Then I chose certificateType from the dropdown.

I moved to the Page Break section of the Group Properties dialog and ticked the Between each instance of a group check box. Click OK.

Now, the report will break for each instance of a certificate type that comes in the dataset. So, if you have ten different cert types in the data, you will get one page for each.

You can't see it in my image below, but there are 5 pages now.

Hope this helps!!

SSRS Report Builder 2012 - How to hide list based on field value?

1 Answers