Click here to monitor SSC
  • Av rating:
  • Total votes: 420
  • Total comments: 42
Steve Joubert

Beginning SQL Server 2005 Reporting Services Part 3: The Chart Control

24 October 2006

This is Part 3 of a four-part article series on SQL Server Reporting Services 2005 (SSRS). Part 1 provided a step-by-step guide to basic report creation and Part 2 took a tour of some of the core SSRS features and functions that you'll need to develop dynamic reports. Here, we turn our attention to the chart control that ships with SSRS.

Microsoft's chart control is, in fact, a scaled-down version of the Dundas Chart Control. Scaled down or not, as far as built-in controls go, I have to say that this one is pretty impressive and feature-rich. For many people, this feature alone constitutes a pretty good reason to adopt SSRS.

In this article we will set the chart control on a report and populate it. We will also cover the different charting display options and I'll demonstrate some rather cool formatting techniques.

Getting Started

Now that we have reached Part 3, I'm going to assume that you've installed all the software you need to follow through the examples, that you have created the ReportingDemo database in SQL Server and have also mastered the basics of creating basic reports, data sources and datasets. If any of these assumptions make you feel nervous, please work your way through Part 1 (at least) and then come back.

NOTE:

You can download the code file for this article from the Code Download link in the speech bubble to the right of the article title. The code file contains a sample Visual Studio project with all the reports from this article, along with a .bak file (or, alternatively, a SQL Script) for creating the ReportingDemo database.

Ed.Note:

The BAK file provided with part 1 of this series accidentally omitted the Purchase table. If you created the ReportingDemo database from that BAK file (as I did) then you will need to run the provided ReportingDemo_Update script to create and populate the Purchase table.

Creating a basic chart

Start by creating a new report server project (called ChartProject, or similar) and add a shared data source that points to your ReportingDemo database. Add a new report called MyChart.rdl to the project. Set up a data set for the report using the following query, which fetches various bits of information pertaining to customer purchases:

SELECT
Purchase.PurchaseID,
Purchase.CustomerID,
Purchase.PurchaseDate,
Purchase.PurchaseType,
Purchase.PurchaseAmount,
Customer.FirstName + ' ' + Customer.LastName AS CustomerName

FROM Purchase INNER JOIN
Customer
ON Purchase.CustomerID = Customer.CustomerID

On your new report, drag the open content area out so that we have room to work and then drag a chart control onto the content area. It should look like this:

From the Dataset fly-out window expand the dataset you created. Drag the PurchaseAmount field into the area above the chart labeled Drop data fields here. Drag the PurchaseDate field into the area below the chart labeled Drop category fields here.

We have now designed a report that will display the total value of customer purchases on a given date. Save the report and navigate to the Preview tab to run the report. It should look like this:

If your requirements are fairly basic, then charting can be this easy.

Chart types

Before we delve deeper into the chart control, it's worth noting that this control can produce a variety of the different chart types. Simply right-click on the chart (in the Layout tab) and use the Chart Type option to select a new chart type. For the time being, switch to a Smooth Line chart.

Add a series

The Data fields and the Category fields make up the horizontal and vertical settings on the chart. The chart control can also layer multiple sets of data onto the same chart. Each layer is called a series. In this case the purchase data we are looking at pertains to two different customers. Drag the CustomerName field into the area to the right of the grid called Drop series fields here. Switch to the Preview tab and view the report, which displays each customer's spending patterns over the various dates:

Setting report properties

Before we get started, change the chart type back to Simple Bar. Then, right-click on the chart again and select Properties. Here you can set all of the properties of the grid and you can also control nearly every aspect of how your data is displayed. Note that you can also manually set up Data, Categories and Series data, which we did via drag and drop previously. We are going to look at each tab of the Properties dialog.

Please notice that as we look through the various options, you will see the 'fx' button nearly everywhere. This is used to set a property value with an expression rather than with a hard-coded value, as described in Part 2.

General tab

On this tab you can give your chart a Name. You can also set the chart type. When you click on Chart Area Style you can set the properties for the background of the chart. The Plot Area Style button allows you to modify basic properties for the chart itself. You can also add a Title to your chart. You can set the title properties such as Font and Color, using the button just to the right of the Title field. Another neat option is that you can apply a palette to the chart. Let's set the palette to Pastel. Click on Chart Area Style and select the Fill tab. Set the color to Thistle and the Gradient to TopBottom. The chart is easier on the eye already!

Data tab

The Data tab allows you to manually set up which data is displayed in the grid and how it is displayed. Just like on the chart, there are three areas for data: Values (called Data Fields in the drag and drop), Categories and Series. You can add and remove items in the different areas. Click on the Edit buttons to set the specific property for each group you created.

Now we are going to take a closer look at the Values properties in the Data tab. Click the Edit button next to the Values field. The dialog will open to the Edit Chart Value tab. On this tab you can set the field from the dataset the values of which will be displayed on the chart, and you can also give the series a name. The Appearance tab allows you to set point markers on your chart and control the style for the series. The Point Labels give you the ability to set a data field or other expression to label the points on your grid. The Action tab turns values into a hyperlink. You can jump to different places in the report or indeed to any valid URL. The URL can be hard-coded or set with an expression.

Let's open up the Point Labels tab so that we can display the values of the purchases being charted right on the bars in the chart. Set the Data Label to the expression =Fields!PurchaseAmount.Value and set the Format Code to C for currency. This will display the purchase amount directly on the grid bars in the $X.00 format. You can use the Label Style to manipulate the font and the color of the label.

X and Y axis tabs

These tabs allow you to set the values for the X and Y display. You can add a title which appears outside the chart area. You can also control the gridlines and the label formatting. Our X Axis is the purchase date. Let's get rid of the time in the purchase date by placing a 'd' into the Format code textbox. This tells the chart to format the date time value as mm/dd/yyyy. Let's check the Side Margins and Reversed checkboxes. This will add some margin space to the chart and place the values on the top. On the Y Axis tab check the Interlaced strips option to break up the chart's background.

Legend tab

This tab allows you to control the legend on the chart. The first option you have is whether or not to show the legend at all. The layout option allows you to display the legend in either a single column, single row, or in a table of columns and rows. The position you use for your legend will likely determine which layout is best. For example, if you move the legend to the bottom position, you might want to use the Row layout. Give it a go. The Legend Style button opens a dialog that allows you to control the style of the legend itself.

3D effect tab

This tab allows you to turn your flat chart into an exciting 3D experience.

I love this tab! I could play around with these settings for hours. For the most part the default settings for each chart type look great. The four values that control the 3-D aspects of the chart are Horizontal Rotation, Perspective, Wall Thickness and Vertical Rotation. While it is fun to work with these to see what you can do, I suggest that you start with the defaults and use the slider for each value to see if you make the values on the chart readable. They work particularly well when you are working with a multi-layered 3-D chart like the one in this example.

For this example set the following values:

Horizontal Rotation

65º

Perspective

0%

Wall Thickness

5%

Vertical Rotation

-5º

Set the Shading to Realistic. Check the Clustered option to display each series on a different plane. Check the Cylinder option to change the square bar on the chart into cylinders.

After you've had a chance to preview these settings, come back and play with the various settings. Believe me, you can make some incredible looking charts with the 3D settings.

Filter tab

This screen allows you to use the columns in your datasets, or expressions, to filter the data that is displayed on the grid. This could be applicable if you were displaying multiple charts on a single report. The important thing about the filter is that it allows you to show a subset of data from an existing dataset. For example, you could have multiple charts report off the same dataset, but each shows the purchases of a different purchase type.

Let’s display two charts in our report. One will report on Internet Sales and the other on In Store Sales. First copy the chart and paste the copy below the existing chart.

For the top chart, open the properties tab and on the General tab set the Title to 'Internet Sales'. Now go to the Filters tab and set the filter:

Expression

Operator

Value

=Fields!PurchaseType.Value

=

Internet

Click Ok and close the properties window.

For the bottom chart, open the properties tab and on the General tab set the Title to 'In Store Sales'. Now go to the Filters tab and set the filter:

Expression

Operator

Value

=Fields!PurchaseType.Value

=

In Store

The Report

Navigate to the Preview tab and let's take a look at the final reports. As you can see, we create a report with two 3-D Charts displaying subsets of data from a single dataset. While the data for this article was kept necessarily simple to allow us to focus on the chart control itself, imagine what you can accomplish in your own reporting system.

Wrap up

I hope this article has given you some insight into the chart control. Between the control's rich features and SSRS's ability to set nearly every property with an expression, you can now see that Microsoft has delivered an extremely powerful and user-friendly charting component in their reporting tool.

Stay tuned for Part 4 of this article series when we peel back the layers on RDL (Report Definition Language) and take a look at Report Builder.

Steve Joubert

Author profile:

Steve Joubert is a Microsoft Certified Professional in developing web applications in C#. He has spent nine years developing Microsoft technologies, including building .NET applications, and has a background in such diverse markets as pharmaceuticals, biotech, banking, finance and entertainment. He currently works for ASPSOFT in Orlando, Fla.

Search for other articles by Steve Joubert

Rate this article:   Avg rating: from a total of 420 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Beginning SQL Server 2005 Reporting
Posted by: Anonymous (not signed in)
Posted on: Wednesday, November 08, 2006 at 7:27 AM
Message: Looks Good !!

Subject: congratulations
Posted by: Anonymous (not signed in)
Posted on: Thursday, November 09, 2006 at 11:38 AM
Message: good course, come on, congratulations

Subject: Great
Posted by: Inoco (view profile)
Posted on: Friday, November 10, 2006 at 9:20 AM
Message: That better than any handbook that I have read bevore.

Thank from Germany

Fred

Subject: Great
Posted by: Anonymous (not signed in)
Posted on: Monday, November 13, 2006 at 9:03 AM
Message: Good job again.

Look forward for your Part 4 and many more articles.

Subject: Beginning SQL Server 2005 Reporting Services
Posted by: Dayashankar (view profile)
Posted on: Friday, November 17, 2006 at 5:02 AM
Message: It is really best Samples with example.Excellent for self learning.Thanks a lot

Subject: Beginning SQL Server 2005 Reporting
Posted by: Dayashankar (view profile)
Posted on: Friday, November 17, 2006 at 6:30 AM
Message: Steve include adhoc report in Beginning SQL Server 2005 Reporting part-4. Thanks a lot

Subject: Good One
Posted by: Anonymous (not signed in)
Posted on: Tuesday, December 05, 2006 at 2:56 PM
Message: Thanks


Joe

Subject: Beginning SQL Server 2005 Reporting
Posted by: Anonymous (not signed in)
Posted on: Wednesday, December 13, 2006 at 9:11 PM
Message: Great Job, Awaiting eagerly for the Part 4. When it will be released ?

Subject: Beginning SQL Server 2005 Reporting
Posted by: Anonymous (not signed in)
Posted on: Thursday, December 14, 2006 at 2:02 AM
Message: It is really best Samples with example.

Subject: great
Posted by: Anonymous (not signed in)
Posted on: Monday, December 18, 2006 at 1:49 PM
Message: good job congratulations

Subject: thanks
Posted by: charbel (view profile)
Posted on: Tuesday, December 26, 2006 at 4:43 AM
Message: just great! best samples ...

cant wait for the 4th part

Subject: A challenge . . .
Posted by: Anonymous (not signed in)
Posted on: Thursday, January 04, 2007 at 6:44 PM
Message: In the section "Add a series" ...
what happened if one of the series had just 2 points that making up a vertical line ? Say for John Locke on the 12/8/2005 the dataset points are (12/8/2005, 0) and (12/8/2005, 1000) you will be seeing no line chart for him or if you show point label just 1 point.
The reason for this is to create a vertical line at a particular date so that you can look at the chart and focus on a particular day (from a report parameter) instead of scanning the whole X-axis for the particular day that you are looking for.

Subject: two charts mergning
Posted by: Anonymous (not signed in)
Posted on: Friday, January 05, 2007 at 3:03 PM
Message: can we merge two charts, what i am trying to say can we display data something like male on oneside of the chart and female data on other side of chart looks like reversed chart placed next to a chart.

Subject: erer
Posted by: Anonymous (not signed in)
Posted on: Friday, January 05, 2007 at 3:04 PM
Message: for the last question , if you have an answer plz email me @ kishorekodru@gmail.com

for two merging charts

Subject: Regarding the article there is a need
Posted by: N.srinivasan (not signed in)
Posted on: Monday, January 08, 2007 at 12:03 AM
Message: Respected
I have read your article
Its pretty good
but I am not clear to run a dynamic chart bu using coding

please send the necessary coding to me in the below mail id

srinivas_tvl@yahoo.com

Subject: Part 4??
Posted by: Anonymous (not signed in)
Posted on: Wednesday, January 10, 2007 at 12:37 PM
Message: where is part 4?

Subject: Dynamic y axis scale
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 23, 2007 at 1:10 AM
Message:
Hi,

your article was quite good.
I am having small doubt..
I embeded the chart in table and grouped that row..
so that I can get multiple rows... but my
y axis scale values are 10,20,300,500 like that...
how can I resolve this scale issue......
could u plz help me out in this regard......

Thank u

Subject: Part 4 ?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 07, 2007 at 6:02 AM
Message: Hi,

I found the 3 parts to this article to be very helpful especially since i have just begun working with Reporting Services.

Where is Part 4 though ?

Thanks,
Sonia

Subject: anonymous commenting
Posted by: Tony Davis (view profile)
Posted on: Friday, June 01, 2007 at 7:59 AM
Message: This article is experiencing large volumes of SPAM so I've had to disable anonymous commenting.

If you wish to comment or ask a question, please sign in (or join if you're not already a member). It only takes 2 minutes and your email address is secure, and is shared with no-one.

Best,

Tony, Simple-Talk Ed.

Subject: Copying and Pasting Charts in Report Builder
Posted by: Sundar (view profile)
Posted on: Tuesday, August 14, 2007 at 2:28 AM
Message: Hi,

In Report Builder, I am unable to copy a chart and paste it below the first chart

Can you tell how to do this ?

Most Appreciated,

Sundar

Subject: Copying and Pasting Charts in Report Builder
Posted by: Sundar (view profile)
Posted on: Tuesday, August 14, 2007 at 3:06 AM
Message: Hi,

In Report Builder, I am unable to copy a chart and paste it below the first chart

Can you tell how to do this ?

Most Appreciated,

Sundar

Subject: Great as usual!
Posted by: NTalwar (view profile)
Posted on: Thursday, October 18, 2007 at 5:57 PM
Message: Thanks, Steve. I learnt a lot about charting through this tutorial.

Now, perhaps it is just me, but I find myself wondering, that since your script for insertion of records into the Purchase table has two purchase records of $815.00 each, for James Sawyer, Customer ID 105 for the date 8/15/2004, shouldn't the graph be showing the point value as $815 * 2 and the Y-axis scaling into the thousands? My chart does show the graph going to $1630, but the point value shows up as $815, and doesn't total for JS so I guess I would have to do a expression to sum for that date to correctly depict the point value.

The concepts are pretty clear through the tutorial. Thank you.

Subject: About the Purchase table
Posted by: NTalwar (view profile)
Posted on: Thursday, October 18, 2007 at 6:19 PM
Message: I think I can see that you meant to have only one record for $815.00 for the Purchase table for that date for ID 105, from your Part 4 script for the same table.

Subject: Pagination Button controls in SQL Server 2005 reports
Posted by: sksupriya (view profile)
Posted on: Thursday, November 01, 2007 at 3:02 PM
Message:

Hi

I am very new to the SQL 2005 reports. I am trying to build sitemap html files using reporting services.

I could not find controls to enable the paging buttons.

Any help on this would be appreciated. Thanks


Subject: Need better examples.
Posted by: mjswart (view profile)
Posted on: Thursday, January 31, 2008 at 9:47 AM
Message: The first chart in your example has "Purchase Date" as the category. The fact that the dates are not sorted make the actual data worse than useless. The line chart shows increases and dips which are at best meaningless and at worse misleading.

I know that it's just an example of how to create a simple chart, but you should use realistic examples.

The "Scatter / Lines" chart is probably called for in this case. I'm having trouble implementing the "Scatter / Lines" chart and the google search "reporting.services time graph" led me here.

Subject: Need better examples.
Posted by: mjswart (view profile)
Posted on: Thursday, January 31, 2008 at 9:49 AM
Message: The first chart in your example has "Purchase Date" as the category. The fact that the dates are not sorted make the actual data worse than useless. The line chart shows increases and dips which are at best meaningless and at worse misleading.

I know that it's just an example of how to create a simple chart, but you should use realistic examples.

The "Scatter / Lines" chart is probably called for in this case. I'm having trouble implementing the "Scatter / Lines" chart and the google search "reporting.services time graph" led me here.

Subject: Sql Server: Creating Charts in reports
Posted by: faalli (view profile)
Posted on: Thursday, May 29, 2008 at 7:54 PM
Message: Hello,

Your guide helped me understand creating charts in reports a lot better than actually trying to figure it out on my own. I do have a few questions. I've creatd a report that uploads a table unto the server, but I've added a chart below the table in the layout section, previewed it, but cannot upload it to the server. Is there a way to upload the chart in the same layout of the table? Or do have, really have to, create a new rdl for the chart? Also, in the chart i've created, it calculates data received per month and displays the counts on the chart(using point label). My thing about this for the months not generated as yet, it puts the number "0" because there are not data to count and i don't want to see that. Is there a way i can get ride of the "0" showing? I appreciate you help on this.
Thanks,

Subject: Wonder ful Article.................
Posted by: chilukuri (view profile)
Posted on: Monday, June 09, 2008 at 10:02 AM
Message: Its wonder ful..........
Thanks Alot

Subject: Part 4
Posted by: jkopsa (view profile)
Posted on: Wednesday, June 25, 2008 at 9:38 AM
Message: Outstanding tutorial! Thank you. Is part 4 available yet?

Subject: Multiple graphs.. one per server on the same page
Posted by: LostFromTheStart (view profile)
Posted on: Wednesday, February 04, 2009 at 8:15 AM
Message: Hi Steve,

I have read all your Beginning SQL Server 2005 Reporting Services 1-4 articles. THEY ARE GREAT STUFF. I don't know if you answer questions but I thought it was worth a try.

I am having trouble creating a report with multiple graphs. I want to display 'CPU usage' over a 24 hour period. I can get this to work for a single server or for all servers but what I am looking for is 1 graph per server. I would like to have the report be dynamic ( if we add a server we should not have to change the report )

I I were doing this with a standard report it wouldn't be a problem, I could break on a group.

Any ideas

Subject: Multiple Graphs
Posted by: ST John (view profile)
Posted on: Monday, March 09, 2009 at 11:50 AM
Message: List control.

place this container control on the report layout, then assigne a dataset and grouping to it, anything you put in the list control will be repeated once per 'grouping level'

J

Subject: a solution (to a challenge)
Posted by: ST John (view profile)
Posted on: Monday, March 09, 2009 at 11:55 AM
Message: SSRS will 'hide' a line that has 0 width. to show a vertical line showing, for example, a point in time use dateadd i9n the base SQL to make the line 1 second wide.

eg dateadd(second, 1, getdate())

and supply the max and min 'Y' values. this will produce a reference line that is visualy vertical.

hth

J

Subject: Nice article
Posted by: wenbin (view profile)
Posted on: Sunday, March 29, 2009 at 8:21 AM
Message: This is an amazing article.

Well, there is still a lot to say on chart.

Subject: programming chart control
Posted by: richarddj (view profile)
Posted on: Wednesday, April 01, 2009 at 10:58 PM
Message: Hi, is there a way to programatically set the Maximum value of Y Axis in Chart properties?
Something like this chart.yaxis.maximum = max(Fields.Revenue.Value)
Any hints would really be appreciated!

Richard

Subject: Great article on Graphical Reports
Posted by: cherukuri (view profile)
Posted on: Friday, August 07, 2009 at 1:06 AM
Message: This is a great and simple article for the reports developers to develop Graphical reports using Charts. Explanation is in easy way to follow.

Subject: Great article on Graphical Reports
Posted by: cherukuri (view profile)
Posted on: Friday, August 07, 2009 at 1:09 AM
Message: This is a great and simple article for the reports developers to develop Graphical reports using Charts. Explanation is in easy way to follow.

Subject: You helped me lot.
Posted by: agrawars (view profile)
Posted on: Friday, March 12, 2010 at 12:22 AM
Message: Dude,

This is really a best article ever seen on internet. Even in the books, it is not described in such manner.

Keep it up.

Thanks & Regards,
Rishi

Subject: your article helped me
Posted by: isha (view profile)
Posted on: Thursday, July 15, 2010 at 2:35 AM
Message: dude,
really your article really helped me thanks n keep going


isha.

Subject: Great work!
Posted by: Kalyan (view profile)
Posted on: Friday, February 18, 2011 at 2:55 AM
Message: Great post very informative. You know in SQL 2008 more new chart types have been added. More about them can be read at http://www.techbubbles.com/sql-server-2008/configuring-sql-server-reporting-services-in-sharepoint-2010/

Subject: Must Read
Posted by: budi bong (view profile)
Posted on: Wednesday, November 02, 2011 at 9:54 AM
Message: Thanks for sharing good article.

Regards from Indonesia

Subject: where are the images?
Posted by: ppwicho (view profile)
Posted on: Friday, April 06, 2012 at 1:56 PM
Message: I've read the 2 first parts and are very interesting.

I don't know if the author or somebody of the staff can help us checking the IMAGES (they are not showed)

Subject: Re: where are the images?
Posted by: Dave Convery (view profile)
Posted on: Tuesday, April 10, 2012 at 2:49 AM
Message: Sorry about that - they seem to have been moved in a site update. They're back in place now.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.