Working with XY Scatter Charts in Reporting Services 2008


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 (8)

  1. DanAgonistes says:

    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".

  2. Manish Patel says:

    Itis it possible to create xy scatter chart from report items or tablix instead of dataset.

  3. Hussein Yousef says:

    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 !

  4. Ron Claghorn says:

    Thanks for this tutorial.  I was missing something and I couldn't figure it out until I saw this.

  5. Jamie says:

    Well done!  Very helpful.  Thank you.

  6. Vinsen says:

    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 .

  7. Shruti says:

    How to change bubble radius by assigning one of the field

Skip to main content