Creating Chart Reports in SSRS 2008

Source: http://www.databasejournal.com/features/mssql/creating-chart-reports-in-ssrs-sql-server-2008-r2.html

SQL Server Reporting Services provides several ways to analyze data; one of them is creating Chart reports. With Chart reports you can analyze the aggregated information (either on small or large volumes of data) at a glance. But before you start creating Chart reports it’s essential to understand and prepare the data to be analyzed so that you can choose the appropriate chart type and create the report quickly and efficiently.

In this article, I am going to demonstrate how you can go about creating, modifying and beautifying the chart report easily and quickly.

Creating a Chart Report in BIDS (Business Intelligence Development Studio)

If you are new to SSRS or you are creating an SSRS project for the first time, the first thing that you need to do is to create an SSRS project. To do that, go to START -> All Programs -> SQL Server 2008 R2 (if you have installed SQL Server 2008 R2) and click on SQL Server Business Intelligence Development Studio. On the New Project dialog box select the "Report Server Project" template and then specify the name and location for the project as shown below:

In the Solution Explorer, right click on the "Reports" folder and click on Add -> New Item as shown below:

Solution Explorer
In the Add New Item dialog box, select the "Report" template and specify the name of the report that you are creating, as shown below:

Add New Item
Once you have created a blank report as above, the next step is to create the data source, which specifies the source of data for the report. To create a data source, right click on the "Data Sources" folder in the Report Data pane and click on "Add Data Source" as shown below. This will take you to a dialog box where you can either use Shared Data Source or create and/or embedded a new Data Source in the report.

Add Data Source
Next you need to create a dataset; to create it right click on the "Datasets" folder in the Report Data pane and click on "Add Dataset" as shown below.

Add Dataset
Clicking on the above menu will take you to a dialog box where you can select the data source and specify the query, fields, filters and parameters to get the data from the source.

Dataset Properties
This is the query that I have used for this demonstration; this brings the sales data for individuals for the years 2002, 2003 and 2004:

USE   AdventureWorks
SELECT Fullname AS Name, [2002] AS [Year   2002], [2003] AS   [Year 2003], [2004] AS   [Year 2004] FROM [Sales].[vSalesPersonSalesByFiscalYears]

Now as we are done creating the SSRS project, the Report, the Data Source and the Dataset, the next step is to create the Chart in the report. To add a Chart to your report, go to Toolbox and under Report Items you will see the Chart icon as shown below; drag it to the report designer area.


Drag the Chart icon to the report designer area
Dragging the Chart from Toolbox will take you to a dialog box where you need to specify the type of chart (bar chart, pie chart, range chart, polar chart, etc.) that you want to add to the report. Depending on the type of data that you want to analyze with the report, select the appropriate chart type here and click on the OK button. If you are not sure which chart type to choose or you want to change it later, the good news is that you can do it later, if you would like to, quite easily.

Select Chart Type
Adding a chart to the report is not enough, you also need to specify what data or values you want to analyze in the chart. To do that, select the chart in the report designer; this will make the Chart Data dialog box appear. Click on the tiny "+" sign for adding values in the Values box and for adding category in the Category box. In my case I want to analyze the year wise sale data for each individual; I have added the years (2002, 2003 and 2004) sale values in the Values column and Individual name in the category column as shown below:

Chart Data
That’s all; we are now good to go and preview the report with chart for data analysis. Click on the Preview tab or deploy and browse the report on the report server. The preview will look like this; as you can see there are three bars set for each individual and each bar represents the sale for a particular year for that individual. Nice, isn’t it?

Chart Preview

Beautifying the Chart or Making It Look Better

There are several elements of a chart like the Chart Title, Legend, Vertical Axis, Horizontal Axis, Series, Grid Lines and Data Point etc.. To learn more about these elements refer to Charts (Report Builder and SSRS).
If you notice, the last preview of the report does not look that good, especially if you notice only two individual names are visible on the horizontal axis because of the default setting. Let’s fix this up. Go back to the Design tab and select the horizontal axis and click on the "Horizontal Axis Properties" menu as shown below:

Horizontal Axis Properties
In the "Horizontal Axis Properties" dialog box, go to the "Axis Options" page and specify the interval to 1 as shown below:

Configure the horizontal axis options
Changing the Interval property on "Axis Options" page to 1 will ensure all the individuals are shown:
 
Chart Preview
Let’s now do some formatting and make the report looks attractive. Right click on the chart, click on "Chart Area Properties," which will bring up a dialog box like this. If you want to give a 3D look to your chart, check the Enable 3D option. Next change the Visibility, Fill, Border and Shadow properties as needed:

Chart Area Properties
I modified some of these properties to make my chart report look attractive and here it goes:

Chart Preview
As I said earlier, not only you can modify the layout, look and feel of the chart type that you selected in the beginning but also you can change the chart type as needed and this is what I have done here; I have changed my bar chart to 3D line chart. To do this, right click on the chart, click on "Change Chart Type" and then choose the appropriate chart:

Change Chart Type
Now let me show some other chart type. What I am trying to do now is to analyze total sales year wise and I want data to be represented as a pie chart. I kept the values to be analyzed as is and removed the Individual Name from Category (as I want to analyze data year wise without considering an individual):

Pie Chart

Conclusion

SQL Server Reporting Services allows you to embed charts in your report to analyze the aggregated information (either on small or large volumes of data) at one glance. In this article, we learned about creating a chart report easily and efficiently. Next we looked at making the charts look attractive, easily understandable or giving them an enterprise appearance.