Freigeben über

Working with XY Scatter Charts in Reporting Services 2008

  • Artikel

Recently, I’ve been fielding a few questions regarding XY Scatter Charts in Reporting Services 2008 (see MSDN Forum Question).  We have definitely noticed that working with XY Scatter charts is a bit difficult, and we do appreciate the feedback and will work to improve them in a future release.  The remainder of this post will detail how you can successfully build these charts in SSRS 2008.

Option 1: Use a Unique Identifier on Rows.

This example relies on using the Category Grouping feature of the chart, but requires a unique identifier in your result set for each row.  Star with this example query:

SELECT 1 as X, 1 as Y, 100 as RowNumber UNION ALL SELECT 2 as X, 3 as Y, 200 as RowNumber UNION ALL SELECT 2 as X, 7 as Y, 300 as RowNumber UNION ALL SELECT 3 as X, 10 as Y, 400 as RowNumber

Next, insert an XY Scatter chart into the report.  Place the RowNumber on Category Groups, and add “Y” to the Data Fields area.  At this point the chart will look like the image below.

image

Finally, right click on the [Sum(Y)] token in the data fields area and bring up the property page for the series.  Here, change the Category field value to the X field.

image

Now, run the report, and your chart should look like the one below.

image

Option 2: Use a Detail Group as the Category Group

Instead of returning a unique identifier on your records, another option is to rely on a detail group for the category group for the chart.  From a UI perspective, this one is very difficult to discover.

First, let’s start with the same SQL query provided above, and insert a new XY Scatter chart into the report.

Again, place the Y field in the data fields area of the chart flange, but this time place the X field as the Category Group (it actually doesn’t matter which field you put here as you will see in a moment).

Next, right-click on the Category Group in the chart flange and bring up its property page.  It should look like this:

image

With the page open, select the single group (shown outlined in red) and press the “Delete” button.  This will empty the group box.  Hit “OK” when done.  Believe it or not, you have just created a detail group for the chart.

Once the detail group is created, there is an issue where the chart flange is no longer available.  To edit the series, select the series in the plot area and bring up its property page.  On this page, set the Category Field equal to [X].

image

Run the report.  The XY scatter chart should display correctly.

I agree that both of these solutions are effectively work-arounds for a feature that should be much easier to use, and this is something we will work to address in a future release of Reporting Services.

Comments

  • Anonymous
    June 02, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/working-with-xy-scatter-charts-in-reporting-services-2008/

  • Anonymous
    September 08, 2010
    Thanks, works great. However, in my case the chart doesn't show any points when in design mode and so I can't turn on data lables or format them etc. How can I turn on data labels when the designer doesn't show any "sample data".

  • Anonymous
    October 03, 2012
    Itis it possible to create xy scatter chart from report items or tablix instead of dataset.

  • Anonymous
    January 08, 2013
    Hi, first thank you for your example, but that's really fairly a simple and easy example, this chart won't work if you have two groups, for example years and months or weeks, if you try to do that it'll be a mess ! this chart really needs so much improvements !

  • Anonymous
    June 24, 2013
    Thanks for this tutorial.  I was missing something and I couldn't figure it out until I saw this.

  • Anonymous
    May 12, 2014
    Well done!  Very helpful.  Thank you.

  • Anonymous
    September 29, 2015
    Thank you . I change the query like this SELECT 1 as X, 1 as Y, 'Apple' as seri,  100 as RowNumber UNION ALL SELECT 2 as X, 3 as Y, 'Grape' as seri, 200 as RowNumber UNION ALL SELECT 2 as X, 7 as Y, 'Strawberry' as seri, 300 as RowNumber UNION ALL SELECT 3 as X, 10 as Y, 'Orange' as seri, 400 as RowNumber and use the column seri as series in chart .

  • Anonymous
    January 21, 2016
    How to change bubble radius by assigning one of the field