CHAPTER 6 BUILDING REPORTS
as page numbers and execution times. You will examine how to modify the report to work with multi-
value parameters. You will also explore another feature added in SSRS 2008: report variables. For this
project, you will add a new report from the Solution Explorer and create a dataset that uses the same
query from the previous section of this chapter for the Report Wizard. For simplicity, we have included
the starting-point report in the Pro_SSRS project. The EmployeeServiceCost_Start report already has the
datasets and initial query defined for the localhost SQL Server, which should match your environment.
You will begin by using just the basic query, not the stored procedure. The dataset you will use to begin
within the EmployeeServiceCost_Start report is called Emp_Svc_Cost. Later, in the “Setting Report
Parameters with Stored Procedures” section, you will modify the dataset to use the stored procedure and
see how the parameters defined in the stored procedure will automatically create the report parameters.
In the following sections, you will go through several steps to add functionality to a single report.
The steps are provided so that you can walk through the process of building the report, starting with the
EmployeeServiceCost_Start report; however, at several intervals, you may choose to open one of the
several sample reports that reflect the completed steps. If a report is available, we will point it out in the
text.
With the EmployeeServiceCost_Start report open in BIDS, move to the Design tab. The following
steps get you to your starting point in the report, where you will begin to apply more advanced
formatting and logic:- Drag the Table report element to a blank section of the design grid.
- On the Report Data pane, drag the fields—Estimated_Cost and Visit_Count
from the Emp_Svc_Cost dataset—to the detail row in the order listed. Delete
the extra column by right clicking on the column and selecting Delete
Columns. Notice that the column headings—Estimated Cost and Visit Count—
were automatically created for you for each field dragged to the detail row. If
you do not see the Report Data Pane, you can pull it up by using CTRL + ALT +
D or by selecting it under the View menu. - Edit the Visit_Count and Estimated_Cost field expressions to be sums, as in
=Sum(Fields!Estimated_Cost.Value) by right clicking on each detail field and
choosing Expression in the submenu. You will notice that when you develop
your report in BIDS, each value that you enter into the report regions will be
enclosed with brackets or be represented as “<>”. For example, the
expression =Sum(Fields!Estimated_Cost.Value) will be visually represented as
[Sum(Estimated_Cost)] in the table cell because the expression is a known
value, even though it is an expression itself. You can also highlight the field,
right click, and then select Sum under the Summarize By... submenu. - Drag the Employee_Name field down to the Row Groups pane and release it
above the (Details) group. This will create a grouping on
Fields!Employee_Name.Value and add a new column to the Table. By default,
the group is named the same as the field being grouped on. You will also notice
that a vertical dashed line is placed between the details section and the new
Employee_Name column of our Tablix: this gives you a visual indicator of
where the group section ends and the details section begins. - Next, drag the Patient_Name field and release it above the Employee_Name
group to create a row group on Patient_Name. Do this step for Service_Type
and Diagnosis with each one being above the former. This will create a
hierarchy of Diagnosis, Service_Type, Patient_Name, and Employee_Name,
respectively.