Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Scatter Chart for Data Correlation


By:   |   Last Updated: 2011-07-19   |   Comments (7)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | > Reporting Services Charts

Problem

Correlation analysis is a method of data analysis where the relation between two measures are determined in addition to identifying outliers in the data set. For this type of analysis, a scatter chart is very helpful. In this tip we would look at how to create a scatter chart based on a X and Y coordinate system in SQL Server Reporting Services.

Solution

Before we start creating a scatter chart in SQL Server Reporting Services, we need to have a dataset that can serve the purpose of our analysis using the scatter chart. For this purpose, consider a hypothetical example of a dataset, containing values for a city, the temperature and carbon emission. The purpose for the analysis is to correlation the temperature versus the carbon emission. Follow the steps below to create the scatter chart.

Step 1: Create a table and populate it with the data as shown in the screenshot below. Consider how much data you can analyze by just looking at a grid based representation of the data and consider how complex it would be to analyze a large dataset.

using ssrs to create a scatter chart

Step 2: Create a new SSRS report and configure it to use data from this table. Add a SSRS Scatter Chart to this report.

Step 3: Configure the chart with the fields from the dataset as shown in the screenshot below. This would plot the data on the chart, where the temperature field would be on the Y-axis and the carbon emission field would be on the X-axis.

create a new ssrs report

Step 4: Say, we know that the range of temperature would be from -20 to +50 degrees and the range of carbon emission would be from 0 to 100. Select horizontal axis and vertical axis properties, by selecting the axis, right-click and selecting properties and configure it as shown in the screenshots below.

add and configure the chart

select axis properties

Step 5: We have already done the configuration for plotting the data and configuring the length of the scale. As this point, we need to arrange the scale such that all the normal data points should be at the center of the graph. So both axes of the graph should cross at the ideal values for the X and Y axis. In our example, we consider 25 degrees as ideal temperature and 50 as the ideal value for carbon emission, these are the values where the axis should cross. To configure this, select the chart and from the properties window under the Axis section, you would find CategoryAxis and ValueAxis properties. Configure the CrossAt property for both these axis as shown in the below screenshot.

chartaxis collection editor

Step 6: After all these steps, preview the graph and you should find a result similar to the screenshot below. If you look carefully at the graph, you can see that the entire cluster of points revolve around ideal value i.e. near the intersection of both axis. Outliers would be very far from this intersection and you would also be able to derive the correlation between the temperature and carbon emission.

completed scatter chart in ssrs
Next Steps


Last Updated: 2011-07-19


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, September 23, 2016 - 5:33:03 AM - Avilash Back To Top

how to adjust the labels of two 2 graphs/charts in a single SSRS chart if the labels of two are merging on one another.


Friday, June 20, 2014 - 4:35:17 PM - Rob Back To Top

Glenn, you are incorrect, temperature is spelled temperature.  I don't see anything wrong with the screen shot and it all works like a charm.  As for the average and center of a quadrant, instead of SUM(Y_Temperature) just do AVG(Y_Temperature), etc.  That way you can see the outliers to what would be the average performance.  Cheers.


Tuesday, June 25, 2013 - 4:46:27 AM - gaurav Back To Top

Hi,

I would like to create scatter graph and want to find avarage for X-axis values and Y-axis values for getting the center of quadrant.Could you please help me out for this.


Friday, May 31, 2013 - 12:49:21 AM - Anil Back To Top

Very helpfull Thanks Siddharth


Saturday, February 16, 2013 - 1:29:02 PM - Prasad Back To Top

This is awesome demonstration of correlation with scatter graphs....I like the way you use ssrs series and category to show two dependent variables, bubble chart is anothe tweak of this. But excellent demonstration.


Tuesday, July 19, 2011 - 9:50:49 AM - Siddharth Mehta Back To Top

Spelling mistakes do happen. Even in your comment you have made a typo, "work" instead of "word". :)

Thanks for drawing out attention to this, we would rectify the same.

I do not see where data has been misrepresented, as the intention is to show the correlation. There can be lots of datasets which can be used to interesting correlation analysis and you would not get a smooth linear correlation everytime. Focus of the tip is the approach of configuring a scatterplot graph and not global warming / carbon dioxide.

Regards,

Siddharth Mehta


Tuesday, July 19, 2011 - 8:48:36 AM - Glenn Back To Top

just a note: as the global warming / carbon dioxide data has been proven to be misrepresented by poor data handling, picking another data topic might be a better choice for improving your credibility. :-) also, the work "temperature" is spelled incorrectly in your screenshot. thanks for the useful tutorial on using scatter charts!


Learn more about SQL Server tools