Scatter Line Charts in SSRS

January 28, 2013
Share this...
Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Pin on PinterestShare on Reddit

Sometime during the last month, I saw an image of an Archimedean Spiral and was wondering whether I could recreate the same in SSRS. The Archimedean Spiral is actually a scatter line chart (a combination of scatter chart and line chart). I have over 6+ years of experience creating visualizations using SSRS, but the closest I had come to such a visualization was the Scatter Chart.

Scatter Line charts in SSRS

A scatter line chart is used either to show the relationship among the items in several distinct series of data or to plot a line using x and y coordinates. The x and y coordinates of the data marker are determined by two data values. A scatter line chart draws attention to uneven intervals in data. This type of chart is often used to plot scientific data, and can highlight the deviation of collected data from predicted results. A scatter line chart can also be used to organize data chronologically (even if the data is not in chronological order). For eg, consider the Scatter Chart below

Scatter chart

A scatter chart doesn’t tell the full story as we don’t know the chronology in which the data was collected.

For the same scatter chart, the scatter-line charts can be different

A scatter line chart can give that extra bit of information as you can see from the image above. To create a scatter line chart, you need to have at least two measures (for the X & Y coordinates) and a category group. Follow the steps below to create an Archimedean spiral using the scatter chart:-

1) Create a dataset with the two measures (X & Y coordinates) and a category. Since I am trying to replicate the Archimedean spiral, I used the formula given below to come up with the coordinates and category. , r=a+btheta

Dataset query for Archimedean spiral

You can see that my category (the Pos field) is just a running number used for determining the order.

2)  Drag and drop a Smooth Line with Markers chart into the layout. Use Y as the value and Pos as the Category group.

Smooth line chart with markers

3) Once you are done with that, click on the values and press F4 to bring up the chart series properties. Under Data, expand DataPoint property and then expand Values. Now, type the following formula in the X property as shown in the image below


Modifying X axis properties

Note that X is the name of the field in our dataset and should be replaced by whatever is the field name that should be present in the X axis.

4) Preview it, and you should be able to see our Archimedean Spiral.

Archimedean spiral in ssrs

One thing to note here is that the lines are connected based on the sorting order of the Category group field. So in case you are using a name field like month (January, February, etc), ensure that the sorting order is based on the month number (else you might not get the right results). Feel free to download the report file from here. Though scatter line charts might not be as useful as sparklines, they have their place in data visualization and it is always good to know that you can make one using SSRS.

Posted by SQLJason

Leave a Reply