Pro SQL Server 2012 Reporting Services

CHAPTER 6  BUILDING REPORTS

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

Interactive Sorting

It never fails when deploying a report to a large audience: someone will ask that the report be sorted in a
certain way that usually differs from the way it was originally designed. When this scenario happens,
typically the report designer is torn between creating a second, almost identical, report with custom
sorting to appease the requestor and placing the request in queue for a future enhancement to the
report. Interactive sorting allows users to sort the report at runtime on any number of fields that have
been defined to use this functionality.
In the sample report, you know that you have a broad audience that may use this report for different
purposes. A chief financial officer (CFO), for example, may want to view the report to see which
diagnosis has the most number of visits. In contrast, another user may need to understand how many
patients have a certain diagnosis and would like the report sorted by patient count, rather than visit
count, at the Diagnosis group level. In this section, you will add interactive sorting to the report to meet
these two needs, knowing that it is possible to sort the report using any other criteria if requested
without having to create additional reports based on a user subdivision.
Since you know that the interactive sorting you will apply to your report will be patient count and
not visit count, all that you have to do is add this criterion to each of the textboxes where the users will
click to change the sorting based on their needs. Using the
EmployeeServiceCost_InteractiveSort_Start.rdl as our starting point, add the interactive sorting to the
header cells Patient Count and Visit Count by following these steps:

1. On the Design tab, right-click the Patient Count header textbox, and select
   Textbox Properties. Click the Interactive Sorting tab.


2. Check the box titled Enable Interactive Sorting on This Text Box.


3. In the Choose what to sort area, select the Groups radio button and add
   Diagnosis as the group expression. In the Sort by area enter
   =CountDistinct(Fields!PatID.Value).


4. Check the option Apply This Sorting to all Groups or Data Regions in and then
   select the Emp_Svc_Cost data region, or type in that region name. Click OK


5. Right-click the Visit Count header cell, and perform steps 1 through 4,
   replacing the sort expression with =Sum(Fields!Visit_Count.Value), as shown
   in Figure 6-12.