Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Create a chart showing current and previous year

  • 9 March 2013
  • Author: Kathi Kellenberger
  • Number of views: 15566
  • 0 Comments

SQL Server Reporting Services (SSRS) has several features that you can use to create visualizations of your data like charts, gauges, KPIs, and maps. These features are great for creating dashboards.

The chart feature can be used to show multiple data points or series on the same chart. For example, you might want to show this year’s sales compared to last year’s sales. This is how the report might look:

Recently, I decided to create such a report and wondered how to write a query that would return the current sales for the month along with the sales for the same month the previous year. The challenge with implementing this feature is that both fields must be in the same dataset. There is a workaround, however, you could use the SSRS LOOKUP function that was introduced with 2008 R2.  Since I am working with SQL Server 2012, I realized that I could use some of the new T-SQL features to help me out.

The first thing I did was create a query calculating the total sales by year and month. By the way, I am using the AdventureWorks2012 database.

SELECT SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS OrderYear, 
	MONTH(OrderDate) AS OrderMonth
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate);

Here are the partial results of the first query:

Since I am using SQL Server 2012, I can use the new LAG function to return previous rows in the results returned without doing additional scans of the data. The LAG function will return the previous row by default, but I can specify a number of rows to go back. LAG can operate on a column, but it can also operate on the results of an expression as shown in the next example:

SELECT SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS OrderYear, 
	MONTH(OrderDate) AS OrderMonth, LAG(SUM(TotalDue),12) OVER(ORDER BY YEAR(OrderDate),MONTH(OrderDate))
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate);

Here are the partial results. Notice that 2005 and the first six months do not have previous sales so the value returned is NULL. It is possible to include a third parameter with LAG specifying a default value to replace NULLs.

The report I am working on requires that the user can choose the year to display. Since window functions operate after the WHERE clause, I can use a Common Table Expression and add the WHERE clause to the main query.

;WITH Sales AS (
SELECT SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS OrderYear, 
	MONTH(OrderDate) AS OrderMonth, 
	LAG(SUM(TotalDue),12) OVER(ORDER BY YEAR(OrderDate),MONTH(OrderDate)) AS PrevSales
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
)
SELECT TotalSales, OrderYear, OrderMonth, PrevSales
FROM Sales
WHERE OrderYear = 2007
ORDER BY OrderMonth;

In SSRS, I'll replace the hard-coded value with a parameter (@Year). Here are the results for 2007:

This particular query will only work properly if there are no months missing from the data since it is just going back 12 rows each time.  

Now that I have the query figured out, let’s take a look at the report definition. I have added two summary fields to the chart and the category group is OrderMonth.

If my data is in SQL Server 2012, it is pretty easy to create this report!

 

 

 

Print
Categories: SQL Server
Tags:
Rate this article:
3.0

Please login or register to post comments.