Building Reports in SQL Server 2005—Building Reports from Scratch

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

Building Reports from Scratch

When working with a blank report, the first decision will be to choose which data regions to use in the body of the report. This decision is driven mostly by the type of data you are working with and by the report audience. For example, a chief executive officer (CEO) might not be concerned with details, preferring to see summary information about the status of the business products and services, and therefore would be more inclined to view a matrix report with column and row totals. However, in the initial report, you will be working with the Table data region because you want to show the interrelationships between patients and employees with multiple groupings in tabular rows, not columns.

In this section, you will follow specific steps to get your report to a basic starting point by adding a Table data region, and then you will continue to add formatting and functionality. When you are finished, the report will contain many SSRS features, including interactive drilldown and navigation links, custom formatting, interactive sorting, populated drop-down parameters, and a chart that displays the top ten diagnoses by cost. You will finish the report by adding several design touches, such as page numbers and execution times. You will also examine how to modify the report to work with multivalue parameters.

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 ease, 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 and not the stored procedure. The dataset you will use to begin with in 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 Layout tab. The following steps get you to your starting point in the report, where you will begin to apply more advanced formatting and logic:

1. Drag the Table report element to a blank section of the design grid.

2. Right-click the center table column, and select Insert Column to the Right.

3. Drag the last three fields—Employee_Name, Estimated_Cost, and Visit_Count—to the three last three columns on the detail row in the order listed. Notice that the column headings—Employee Name, Estimated Cost, and Visit Count—were automatically created for you for each field dragged to the detail row. You will leave these here for now and add formatting in the following sections.

4. Edit the Visit_Count and Estimated_Cost field expressions to be sums, as in =Sum(Fields!Estimated_Cost.Value).

5. Right-click the detail row, and select Insert Group.

6. In the Group On -> Expression drop-down list, select =Fields!Diagnosis.Value. Uncheck the Group Footer checkbox for the Diagnosis group. Enter Diagnosis for the group name, and select OK. Drag the field Diagnosis into the first column of the new Diagnosis group header.

7. Perform steps 5 and 6 for ServiceType and PatientName, replacing the Group On expression with their respective field values for step 6, =Fields!Service_Type.Value and =Fields!Patient_Name.Value. Enter ServiceType and PatientName for the respective group names, uncheck Group Footer, and click OK. Drag the respective fields Service_Type and Patient_Name into the first column of each of the groups you just created.

8. Right-click the detail row, and select Edit Group. In the Group On drop-down list, select =Fields!Employee_Name.Value.

After these eight steps, the report is starting to take form, as you can see in the preview. Though not yet aesthetically appealing, it displays the data in the appropriate, hard-fixed groupings and is tabulated so that it is easy to discern the detailed service information, such as the cost and counts of services for each patient (see Figure 4-4).

Figure 4-4. Employee Service Cost report details