Adding Charts to SQL Server Reporting Services Reports

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

Often adding a chart can help give perspective mere numbers cannot. A quick glace can allow users to quickly focus on the important areas of data. Reporting Services supports many chart types, all of which are highly customizable. In this lab we’ll add a simple chart to the executive summary report we created in the previous lesson.

Step 1. Clone an existing report

Often we start new reports from an existing one. In solution explorer, right click on the “Subreport – Executive Summary” and pick Copy. Now go to the base of the project tree and pick Paste. In the Solution Explorer you’ll now see “Copy of Subreport – Executive Summary”. Rename it “Subreport – Executive Summary with Graph”. (Note, if you don’t have this you’ll need to do yesterday’s lesson, or download the code from the Getting Started post, mentioned at the top of this post).

Step 2. Make room for the chart.

Expand the body to double the width.

Step 3. Add the chart control.

In the toolbox, grab the Chart and drag it into the empty area in the body. You’re then asked what type of chart to create, pick a pie chart (it will be the top right one in the “Shape” category). Click OK.

Click inside the pie chart. You’ll now see small boxes slide out to the side and top/bottom of the chart control. Go to the Report Data tab. In the Contoso Dataset1 click on CategoryTotal and drag it above the chart to the area that reads “Drop data fields here”.

Next, click on the ProductCategoryName and drop it below the chart where it reads “Drop category fields here”.

Right click on the title, and pick “Title properties”, then for title text click the fx expression button. For the expression enter:

=Sum(Fields!CurrentYear.Value, "CurrentFiscalYear") & " Values"

(To see an explanation on the logic for this see the previous lesson).

Step 4. Preview the report

Click on the Preview tab. You should now see your chart, linked to the data.

clip_image002

There are many more customizations you can do to a chart, this lab demonstrated how quick and easy it is to add charts of all types to your reports.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: