24 October 2006

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

Part 3 of the series takes us through the chart control and some of Steve's favourite features that ship with SSRS.

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 at the bottom of the article. 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:

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:

297-gif1.gif

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.

297-gif2.gif

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:

297-gif3.gif

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:

297-gif4.gif

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

297-ScreenShot62.gif

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

297-ScreenShot63.gif

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

297-ScreenShot64.gif

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

297-ScreenShot65.gif

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

297-ScreenShot66.gif

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

297-ScreenShot67.gif

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.

297-ScreenShot69.gif

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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

Downloads

This post has been viewed 229243 times – thanks for reading.

Tags: , , , , , , , , ,

  • Rate
    [Total: 424    Average: 4.1/5]
  • Share

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.

View all articles by Steve Joubert

  • Anonymous

    Beginning SQL Server 2005 Reporting
    Looks Good !!

  • Anonymous

    congratulations
    good course, come on, congratulations

  • Inoco

    Great
    That better than any handbook that I have read bevore.

    Thank from Germany

    Fred

  • Anonymous

    Great
    Good job again.

    Look forward for your Part 4 and many more articles.

  • Dayashankar

    Beginning SQL Server 2005 Reporting Services
    It is really best Samples with example.Excellent for self learning.Thanks a lot

  • Dayashankar

    Beginning SQL Server 2005 Reporting
    Steve include adhoc report in Beginning SQL Server 2005 Reporting part-4. Thanks a lot

  • Anonymous

    Good One
    Thanks

    Joe

  • Anonymous

    Beginning SQL Server 2005 Reporting
    Great Job, Awaiting eagerly for the Part 4. When it will be released ?

  • Anonymous

    Beginning SQL Server 2005 Reporting
    It is really best Samples with example.

  • Anonymous

    great
    good job congratulations

  • charbel

    thanks
    just great! best samples …

    cant wait for the 4th part

  • Anonymous

    A challenge . . .
    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.

  • Anonymous

    two charts mergning
    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.

  • Anonymous

    erer
    for the last question , if you have an answer plz email me @ kishorekodru@gmail.com

    for two merging charts

  • N.srinivasan

    Regarding the article there is a need
    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

  • Anonymous

    Part 4??
    where is part 4?

  • Anonymous

    Dynamic y axis scale

    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

  • Anonymous

    Part 4 ?
    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

  • Tony Davis

    anonymous commenting
    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.

  • Sundar

    Copying and Pasting Charts in Report Builder
    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

  • Sundar

    Copying and Pasting Charts in Report Builder
    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

  • NTalwar

    Great as usual!
    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.

  • NTalwar

    About the Purchase table
    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.

  • sksupriya

    Pagination Button controls in SQL Server 2005 reports

    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

  • mjswart

    Need better examples.
    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.

  • mjswart

    Need better examples.
    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.

  • faalli

    Sql Server: Creating Charts in reports
    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,

  • chilukuri

    Wonder ful Article……………..
    Its wonder ful……….
    Thanks Alot

  • jkopsa

    Part 4
    Outstanding tutorial! Thank you. Is part 4 available yet?

  • LostFromTheStart

    Multiple graphs.. one per server on the same page
    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

  • ST John

    Multiple Graphs
    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

  • ST John

    a solution (to a challenge)
    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

  • wenbin

    Nice article
    This is an amazing article.

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

  • richarddj

    programming chart control
    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

  • cherukuri

    Great article on Graphical Reports
    This is a great and simple article for the reports developers to develop Graphical reports using Charts. Explanation is in easy way to follow.

  • cherukuri

    Great article on Graphical Reports
    This is a great and simple article for the reports developers to develop Graphical reports using Charts. Explanation is in easy way to follow.

  • agrawars

    You helped me lot.
    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

  • isha

    your article helped me
    dude,
    really your article really helped me thanks n keep going

    isha.

  • Kalyan

    Great work!
    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/

  • budi bong

    Must Read
    Thanks for sharing good article.

    Regards from Indonesia

  • ppwicho

    where are the images?
    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)

    • Dave Convery

      Re: where are the images?
      Sorry about that – they seem to have been moved in a site update. They’re back in place now.