Building Reports in SQL Server 2005—Visibility

CSharp-Online.NET:Articles

Database Articles

Building Reports Report Wizard Build Report from Scratch Format the Output Add Subtotals Interactivity § Document Mapping Visibility Interactive Sorting Hyperlink Actions Bookmark Link URL Link Link with Parameter Jump to Report Hyperlink Formatting Set Report Parameters Apply a Filter Add a Chart Final Touches Multivalued Parameters Summary

© 2006 R. Landrum, W. J. Voytek

Visibility

Another feature of SSRS is the ability to show or hide areas of the rendered report based on user input. Often users want to see only summary information on a report but be able to drill into the summary data to see the detail information if necessary. Report designers might make two reports, a summary and a detail report, which have to be updated and maintained separately. The reports are often based on the same query. Fortunately, SSRS’s ability to show or hide report data does away with the need to create separate reports. The visibility properties for report items control showing and hiding report items.

Let’s assume you have distributed your report to your intended audience, and they have come back with "suggestions" for how to improve the report—this is real-world reporting, after all. They indicate they would like to see the following:

• Summary totals for the visit count and the estimated cost of each diagnosis when the report is first rendered, but with the ability to drill to the detail of the patient and employee if warranted

• The number of patients who have a specific diagnosis

• The number of individual employees who have provided care for these patients

With SSRS this is fairly straightforward, and you can knock out an improved report quickly. You just need to follow these design steps before modifying the visibility properties:

1. On the Layout tab, right-click the entire Employee Name column, and select Insert Column to the Right.

2. Highlight and delete the Employee Name column header text. With the drill-down feature you will be adding with visibility properties, this column header will no longer be needed.

3. Enter Employee Count and Patient Count as the new column header text in the second and third columns, respectively.

4. Resize the second and third columns in the table from right to left so that they are approximately 1 inch each.

5. Highlight every cell in the Service Type, Patient Name, and Details rows. You can accomplish this by holding down the Control key and clicking the row marker to the left of the first column in the table. Once all the rows are highlighted, select an 8-point font size from the formatting toolbar.

You can control the visibility state of report items, hidden or visible, by setting visibility property values. You can hide report items at any level in the report and toggle their visibility property values when a user clicks the + or – icon to show or hide them. The toggle point of the hidden items is another report level, such as a group. In this example, you would like to hide every level except the Diagnosis and Service_Type fields but give the user the ability to show or hide the details. To begin, hide every group except Diagnosis and Service_Type. The steps to accomplish this are as follows:

1. Right-click the detail row icon, and select Edit Group.

2. On the Visibility tab, select Hidden.

3. Check Visibility Can Be Toggled by Another Report Item.

4. In the Report Item drop-down list, select Patient_Name. If Patient_Name is not displayed in the drop-down list, you may have to type it in.

5. Perform steps 1 through 4 for the Patient group, selecting or typing Service_Type as the toggle report item.

The other two requests were to be able to see the patient and employee totals for each diagnosis. You can add an expression, CountDistinct, to the report that will count each unique patient and employee and calculate the amounts at the diagnosis level. The syntax used for the patient count is as follows:

=CountDistinct(Fields!FieldName.Value)

By adding the CountDistinct expression for the field PatID (which you know to be unique per patient) as well as for the field EmployeeID, it will be much easier to see at a glance how many patients with a specific diagnosis have been cared for.

Place the following two expressions for the Diagnosis group in the cells just below the Employee Count and Patient Count header cells:

=CountDistinct(Fields!EmployeeID.Value)
=CountDistinct(Fields!PatID.Value)

Though the report is still similar to the noninteractive report, with the drill-down additions it will look much different when previewed (see Figure 4-8).

Figure 4-8. Report with interactive drill-down

The EmployeeServiceCost_Visibility.rdl report in the Pro_SSRS project includes the visibility properties.