SSRS: Conditionally Set Visibility of Group Based on Group Count

On occasion there is a need for rows on a report to be conditionally hidden or visible based upon an expression that is determined when the report is run.  One example of this type of functionality can be shown within a report displaying the assessments for a patient. Each of the assessments can have a set of questions attached. 

Because a patient may have multiple assessments, the user requires the ability to display or hide the questions for any assessment.  For instance, if there is only one assessment, the user would like for the questions to be displayed automatically.

For the example report, the MainDs dataset was created as seen below.

The dataset has two identifier (ID) columns, bcr_id and QuestionID, that define a specific assessment, bcr_id, and each question within the assessment.

On the canvas of the report, a table was created and linked to the MainDs dataset. Within the table a parent group was created, on the Details group, based on the field bcr_id. When the parent group was created, the add header and footer option was selected to add spacing between the groups.

Showing all rows initially can make for an overly busy presentation, when there are several assessments done on a patient; and hiding the detail if only one assessment exists is rather extreme.

For the ability to dynamically display or hide the detail, a few additional steps are required.

• Since the visibility of the details is based on the count of assessments, the need for a variable is required to capture the number of distinct assessments within the dataset.  To create this variable, in the white area outside of the report body, right-click and choose the option Report Properties.

 

• From the Report Properties page, the Variables tab was selected.  A variable named GroupCount as created with its Value set to the distinct count of assessments.

The Expression for the value was set by selecting the fx icon and entering the below value.

= CountDistinct(Fields!bcr_id.Value, “MainDs”)

Note that the following steps were followed for each of the rows (Question, detail, and group footer).

• Right-click on the row and select Row Visibility.

In this window the initial visibility can be set and the ability to define a cell that will allow the group to be expanded/collapsed at runtime.  The toggle shows a [+] or [-] icon in the cell to toggle the visibility.  By selecting the Show or Hide option determines whether the row is visible initially.

• Update the Row Visibility settings to display the detail for a single assessment and hide the detail if there are several assessments
• The radial option Show or hide based on an expression was
• The fx icon was selected and the below expression was entered.

         = IIF(Variables!GroupCount.Value = 1, False, True)

• To set the Toggle report item, the Display can be toggled by this report item was selected and the report item txtAssessmentDate was chosen.

Now that the rows dynamic visibility has been set, the appropriate icon [+] or [-] needs to be set depending on whether the detail is visible when the report is run.

• The txtAssessmentDate textbox was selected, which allows for its properties to be displayed.  In the Properties window the InitialToggleState property was selected.  The ellipsis (…) was selected and the below expression was entered

       = IIF(Variables!GroupCount.Value = 1, True, False)

Below is a preview for multiple assessments.

Below is a preview for a single assessment.

As ever, if you need help with MEDITECH DR optimization, reporting, extract, index creation or analysis please feel free to give your iatricSystems Account Executive a call!