Building Reports in SQL Server 2005—Adding a Chart

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

Adding a Chart

SSRS provides a Chart data region that has a style similar to Microsoft Excel. Charts can be scoped within the current dataset or can use their own dataset. For this example, you will add a stacked bar chart to the beginning of the report that will show the top ten diagnoses and a count of the number of services for each diagnosis. This will essentially mirror the data provided in the report thus far. This report thus far is now also grouped by Branch, which will automatically separate the values in the Branch group you have defined. You will want to emulate this for your chart. You have only three branches in this particular dataset, so the result should be in line with the details of the report. Follow these steps to add the chart to the report:

1. On the Layout tab, click and drag the table you have already defined to make room for the chart.

2. Click and drag the Chart data region to an area above the table.

3. Right-click anywhere on the chart, and select Chart Type -> Bar -> Stacked Bar.

4. Using the same Emp_Svc_Cost dataset defined for your report already, drag the Visit_Count to the Data area of the chart.

5. Drag the Diagnosis field to the Category area of the chart.

6. Drag the BranchName field to the Series area of the chart.

7. Resize the chart so that it aligns with the table below it. You can select both report elements, and on the toolbar select the Make Same Width icon.

8. Right-click the chart, and select Properties. On the Data tab, select Edit in the Category groups for chart1_CategoryGroup1. Because you want to make the report show only the top ten diagnoses, you need to add a filter to this grouping. You will use the Top N operator to make this happen, as shown in Figure 4-22, based on the top ten diagnoses by a sum of the visit count.

Figure 4-22. Filter value for top ten diagnoses

Finally, you can preview the report. Sometimes a report needs a chart up front for a quick view of the data prior to analyzing the details. Someone perusing this report might find it interesting in the chart, for example, that the Physical Therapy NEC diagnosis seems to be more prolific in the Nested Valley branch. This initial preview might warrant more investigation that can be gleaned from the details in the report. In Chapter 10, when we discuss Analysis Services and SSRS, we will go into more detail about analyzing and reporting on these data trends.

When previewed, the report should look like Figure 4-23.

Figure 4-23. Employee Service Cost report with a chart

The Chart data region has many properties that you can apply, as covered in Chapter 3; however, the appearance of the stacked bar is suitable for your report and can be deployed as is.