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

 

How to Create a Slope Chart in SQL Server Reporting Services Part 2


By:   |   Updated: 2018-06-20   |   Comments   |   Related: > Reporting Services Development

Problem

A slope chart is a variation of a line chart, in which two points in time are compared for different series. It allows you to easily compare the rate of change between the two periods in a glance. In the first part of this tip series, we created the basics for a slope chart in Reporting Services. Now we’re going to finish the chart with some advanced formatting.

Solution

The goal of this tip is to create the following slope chart using the built-in capabilities of SQL Server Reporting Services (SSRS):

final chart

It is advised to go over the first part of this tip, if you haven’t already. There we retrieve the sample data from the Wide World Importers data warehouse and create the first iteration of our slope chart with basic formatting. This is the result and the starting point of this part:

starting point

Positioning the Data Labels

The hardest part is the position the labels correctly. We want the labels for 2013 to appear left of the gridline and the labels for 2014 to appear right of the gridline. Unfortunately, we can only configure one setting: it is either left, right or any of the other available options:

position dropdown

The Outside option seems the perfect fit, however it results in an error. The graph becomes blank and the following message is displayed:

outside error message

The solution to the problem is to create an additional series by adding the quantity measure twice. The second series will be hidden. From the first series, we’ll display only the labels for 2013 and position them left of the gridline. For the second – invisible – series, we’ll display only the data labels for 2014 and position them to the right of the gridline.

To duplicate the series, simply drag the Quantity field from the data set to the Values region:

duplicate series

We’re going to set two expressions on the data labels of the first series:

  • One to hide the label if the year is not 2013
  • One to concatenate the name of the sales territory to the value of quantity. By adding the name, we can delete the cluttered legend at the top.

Right-click the data label and go to properties. In the General pane, hit the fx button to go to the expression editor for the label data:

expression editor for label value

The Format function is used to show the value of Quantity as thousands, like we configured before. Close the properties window. While the labels are still selected, hit F4 to bring up the properties pane. For the Visible property, the following expression is set:

visible property left

The Position property is set to Left. Now, we need to enforce SSRS to actually put the labels to the left. We can do this by using SmartLabels. Instead of selecting the data points, select the chart series itself. In the properties pane, we can find SmartLabels in the Labels section. Open the node and set AllowOutsidePlotArea to True.

smartlabels

With SmartLabels, we can also control the layout of the arrows that are sometimes displayed when a label is a bit too far from the actual series. In this example, the color is set to Gray in the CalloutLineColor property. In the NoMoveDirections node, we can configure all of the directions we want the labels to move to.

nomovedirections left

A value of True means the label cannot move in that direction. We’ll set everything to True, except the directions Left, DownLeft and UpLeft. When we render the graph, the labels have disappeared. What happened?

labels are gone

We configured the labels to go left and they can be positioned outside the plot area. However, there’s simply no room at the left to actually place the labels, so they have “fallen off” the chart. It’s also possible that in your case the labels are only partially displayed. The solution is to configure the CustomInnerPlotPosition of the chart area. This property determines the position of the plot area (the visualization except labels, axis, legends and titles) within the entire chart. Make the chart a bit wider and then select the chart area.

custominnerplotposition

Give the Left a higher number so there’s enough room for the labels. Also, make the Width smaller so there’s room for the labels on the right. In the screenshot above you can see the values I used for the example. When rendering the report, the labels on the left are now displayed properly:

quantity by sales territory

We can repeat the same steps for the right labels. The expression on the extra series to only show data labels for 2014:

expression to show labels only on the right

Instead of hard-coding a value for the year, you could also determine the minimum and maximum values in your source query. An example using windows functions:

determine min and max year

The NoMoveDirections are configured to only allow positioning to the right. Don’t forget to set AllowOutsidePlotArea to true and to change the color of the labels and the arrows to gray.

nomovedirections to the right

The chart now has the following layout:

labels on the right!

Formatting Line Colors

At this point you can probably call it a day. However, when you have more series (here we have only 8), the slope chart can become a bit of a spaghetti mess. A good solution is to only highlight the lines with the highest and the lowest gradient. With some T-SQL window functions, we can determine those series in the source query:

WITH cte_basedata AS
(
SELECT
     c.[Sales Territory]
    ,d.[Calendar Year]
    ,Quantity = IIF([c].[Sales Territory]='Plains'AND [d].[Calendar Year]= 2014
                       ,SUM(f.[Quantity])* 0.75-- make sure at least one territy has declining numbers
                       ,SUM(f.[Quantity])
                   )
    ,MinYear  = IIF([d].[Calendar Year]=MIN(d.[Calendar Year])OVER(),1,0)
    ,MaxYear  = IIF([d].[Calendar Year]=MAX(d.[Calendar Year])OVER(),1,0)
FROM [Fact].[Order] f
JOIN [Dimension].[City] c ON [c].[City Key] = [f].[City Key]
JOIN [Dimension].[Date] d ON [d].[Date]     = [f].[Order Date Key]
WHERE [d].[Calendar Year] IN (2013,2014)
  AND [c].[Sales Territory] <> 'External'
GROUP BY [c].[Sales Territory]
        ,[d].[Calendar Year]
)
,   cte_CalculateDelta AS
(
SELECT
    [Sales Territory]
   ,[Calendar Year]
   ,[Quantity]
   ,[MinYear]
   ,[MaxYear]
   ,Delta     =   FIRST_VALUE([Quantity])OVER(PARTITIONBY [Sales Territory]ORDERBY [Calendar Year]DESC)
                - FIRST_VALUE([Quantity])OVER(PARTITIONBY [Sales Territory]ORDERBY [Calendar Year])
FROM [cte_basedata] c
)
SELECT
    [Sales Territory]
   ,[Calendar Year]
   ,[Quantity]
   ,[MinYear]
   ,[MaxYear]
   ,[Delta]
   ,Lowest            = IIF(Delta=MIN(Delta)OVER(),1,0)
   ,Highest           = IIF(Delta=MAX(Delta)OVER(),1,0)
FROM [cte_CalculateDelta] c
ORDER BY [Sales Territory], [Calendar Year];

The Delta column assumes there are only 2 time periods. It first fetches the most recent one by grabbing the first value when sorted descending. Then it fetches the oldest time period by fetching the first value when sorted ascending. In the outer query, the lowest and highest delta are determined by comparing the delta with the overall maximum and minimum. Keep in mind this code doesn’t take ties into account. If two series have exactly the same delta and it’s the highest, both will be flagged as having the highest gradient.

Now we can use this data to highlight only the important lines. First we hide all lines of the second series by setting its line color to No Color.

hide lines

Next, we select the other lines and enter the following expression for the line color:

expression

The slope chart now has the final layout we wanted to configure:

final slope chart!
Next Steps
  • Make sure you read part 1 to get the basics right of the slope chart.
  • You can follow along with the instructions in this tip and part 1 to create your own slope graph. You can download the SSRS project with the report here. There are multiple charts in the report, where each chart corresponds with a major step of this tip.
  • For more Reporting Services tips: Reporting Services Tips.


Last Updated: 2018-06-20


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips




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.



    



Learn more about SQL Server tools