Pro SQL Server 2012 Reporting Services

CHAPTER 6  BUILDING REPORTS

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
that 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 into the details of the
  patients and employees 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. Start
this example by opening up the EmployeeServiceCost_Visibility_Start.rdl report in the Pro_SSRS project.
Then, you’ll just need to follow these design steps before modifying the visibility properties:

1. On the Design tab, right-click the entire column labeled Diagnosis  Service 
   Patient  Employee, and select Insert Column  Inside Group - Right.
   Complete this step one more time so that we have two empty columns
   between the groups and the Estimated Cost and Visit Count columns.


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


3. Resize the second and third columns in the table from right to left so that they
   are approximately 1 inch each. If, by default, you do not see the ruler, you can
   right-click a blank area of the design environment and select Ruler under the
   View Menu.


4. 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 the 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 Employee_Name group in the Row Groups section and select
   Group Properties. Then click the Visibility tab.


2. On the Visibility tab, select the Hide radio button.


3. Enable the checkbox to Display Can Be Toggled by This Report Item.


4. In the Report Item drop-down list, select Patient_Name.


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