Problem
What exactly is a SQL Server Reporting Services Range Chart and how do I implement a range chart?
Solution
A range chart is a powerful way to show two discrete values related to a single category point while at the same time showing the variance between those two values. Some data sources are well suited to showing how data values vary within a single category value. A continuous range such as a date often makes up the category; of course, several data sources come to mind when think about potential uses of range charts. One example of its use would be a chart to display the high and low temperature for a given data range; furthermore, you could add a series for locations or zip codes to such a chart. Other uses of range charts include 1) showing the daily range of stock market prices over the course of day, month, or year 2) show the high (peak) and low CPU or memory usage of a server over an hour, day, month, or year 3) showing a river’s or reservoir’s high and low levels over the course of a day, week, month, or year. Finally, one of the neat uses for a range chart is to display a Gantt chart.
We will use the Adventure Works databases as the basis for our SSRS report examples. The 2014 versions of the regular and data warehouse databases are available. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.
Utilizing SSRS Range Charts
When you design a range chart you need to be aware of a few caveats. First, if there are problems with your data in that your high value is lower than your low value, SSRS will still plot the range in its flipped form. Secondly, if you only include one value in the data set, only a single line will be shown; basically it becomes a line chart. Finally, although data markers can be turned on, they do not display correctly.
You have four choices for range chart types: 1) range chart 2) column range chart 3) bar range chart 4) smooth range chart.
Let us get started with an example; we will use the high and low temperatures for two cities in the US, Westerville, OH and Kennesaw, GA. A snippet of the dataset is displayed below.
Next, we will drag the chart object onto the report design grid as noted next. For this example, we will develop a regular range chart as shown in the second screen print.
Once the Range Chart has been added, we need to add three data point items, like most charts. First, we will add the data values to appear on the chart. Second, we will select a category, and third, select a series data item (this item is optional).
In step 1, we add the high value first as shown in the first screen print below. You will notice after adding the high value that a second value line item shows up with a designation of Low. This setup can be confusing as some may assume the low value was also selected.
However, you actually need to select the low value to be included in the range, LowTemp for our example.
Finally, we add the category of Date and the select Location for the series.
To finish our chart, we add / adjust the following items as shown in the below screen print:
- Add a Chart Title
- Add Axis Title for Temperature
- Format the Horizontal Axis to display the dates appropriately. We also adjust the interval to 1in order to show all dates within our dataset.
- We move the series legend to the bottom of the chart
- Finally, the chart size is increased to allow for a good view of the chart.
The final result of our range chart is displayed next.
In the above screen print it is easy to see why the range chart is a good way to see extremes. Two items should be noted. First, as noted with #1 in the above screen print, the January 10th high and low values for Kennesaw, GA are inverted ( the high is lower than then low), but SSRS still displays the values with what I call a squeeze effect (like a hard candy wrapper). Notice how the range goes to zero on either side of the January 10th values. Second, when using multiple series, it is possible, as shown in #2 above, to have one range “cover over” a second range. A good alternative, shown below, is to convert this particular chart to a column chart; another alternative would be a bar chart, as illustrated in the second screen print below, although it is a bit busy.
Gantt Chart… too!
Following a similar pattern to the bar range chart, we could actually create a Gantt chart using a bar range chart. Using a simple table with project and task info, as displayed below, we can demonstrate one way to make a Gantt Chart.
The below SQL code does several things including finding the minimum start date of the project and then determining how many days are between the first date in the SQL Server calendar (12-30-1899) and the project start date. Next the query simply retrieves the project, task, start date, end date, and duration. Next, we calculate the start date of each task as compared to the start date of the project and then add the days since 12-30-1899. We do the same calculation for the end date. These figures will be what gets plotted on the Gantt chart. Finally, the query returns the project start date value and the number of days between the start date and 12-30-1899. We will use this last value for our horizontal axis “begin value” (hint: we do not start at 0, but at the number which relates to the project start date or 42154 in the query results screen print). For the range chart we need to have numeric equivalent values, not dates; by getting the days since the beginning of the SQL Calendar, we can quickly get a high and low values for each of days, all in reference to the beginning of the SQL Server calendar. Secondly, these numeric equivalents can easily be tied back to the true date.
DECLARE @PROJECTSTARTDATE DATE DECLARE @PROJECTSTARTDATEINT AS INT SELECT @PROJECTSTARTDATE = MIN(STARTDATE), @PROJECTSTARTDATEINT = DATEDIFF(DAY, 0, MIN(STARTDATE)) FROM ProjectListGantt SELECT Project ,Task ,StartDate ,EndDate ,DATEDIFF(DAY, StartDate, EndDate) AS Duration ,DATEDIFF(DAY, @PROJECTSTARTDATE, StartDate) + @PROJECTSTARTDATEINT AS StartPoint ,DATEDIFF(DAY, @PROJECTSTARTDATE, StartDate) + DATEDIFF(DAY, StartDate, EndDate) + @PROJECTSTARTDATEINT AS EndPoint ,@PROJECTSTARTDATE AS ProjectStartDate ,@PROJECTSTARTDATEINT FROM ProjectListGantt
This query provides the following results. You can quickly see that our start and end points are converted to a numeric value as compared back to the beginning of the SQL Server Calendar.
Now, we create a Bar Range chart using the EndPoint field for the high value and the StartPoint field for the low value. Furthermore, we add Task to the Category Group (we do not use a series for this example).
The next step is to make several adjustments to the horizontal axis properties as illustrated next. For the axis properties, the “Always include zero” check box is unchecked as we do not want to include day 0 (aka 12/31/1899). Next we set the Minimum value to the Project Start Date numeric equivalent or 42154. Since the dataset includes multiple rows, we use the first function to grab the first rows value. Next we adjust the Interval to 1 and the Interval type to Weeks (this value could be days or months of course); this allows us to have the horizontal axis to show weekly dates beginning with the project start date.
The last step for this simple example is to adjust fill color for each task. Without making this change, all the bars will have the same color. Of course there are various ways to complete this task, however, in the below example, I simply used the Switch function to hard code the fill color based on the task name.
The last steps are to add axis and chart titles and increase the size of the chart. The final result is displayed below.
Conclusion
Range charts provide an excellent method to display both the high and low values for a category of data points. Often the category is over a date range, such as the high and low temperature during a group of days in a month. However other categories can exist, such as tasks on a Gantt chart. SSRS provides us with 4 different range charts: range, smooth range, bar, and column. Each type has its own advantages and disadvantages, but all types require a high and low value. Furthermore, although you can add data markers to your range chart, SSRS does not support their use. Range charts provide another graphical tool in your SSRS tool chest.
Next Steps
- Review all the SSRS Charting tips – https://www.mssqltips.com/category/reporting-services-charts/
- Check out all of the SQL Services Reporting Services tips
