Financial Year To Date Figures in SQL Reporting Services Grouped By Week

I guess I should lay my cards on the table and say up front, that I love Microsoft SQL Reporting Services 2005 and can’t wait for a business opportunity to use SQL Reporting Services 2008! That said, there are some things that are just plain difficult to do (or at least I’ve found them difficult) and as such thought I’d pen a quick post about producing reports showing financial years grouped by week with/without running values – one of the things that I have found challenging in the past.

As usual I like to explain by example and for the purposes of this post I am going to use the AdventureWorks database that ships with SQL Server 2005 – in particular the Sales.SalesOrderHeader table.

The SQL bit

It is not difficult to get a simple grouping by week of year:

SELECT DatePart(wk, OrderDate) As 'Week', Sum(TotalDue) FROM Sales.SalesOrderHeader WHERE OrderDate BETWEEN '2003-04-01' AND '2004-04-01' GROUP BY DatePart(wk, OrderDate) ORDER BY 1

There are immediately three issues with this query:

1. Week number 1 is the first week in January 2004 NOT the first week in April 2003 as it should be.
2. The display is a week number not a start date/end date i.e. Week Beginning.
3. If there were (which in this case there are not) gaps in sales such that there was a Null week then that week wouldn’t appear at all in the list of results.

I don’t claim to be a SQL guru and as such there may be a really simple solution to the above three issues – I just don’t know what it is. I do however, have a workaround that I find is not only effective but flexible and has a couple of simple advantages:

YearWeeks Table

What I do is create a simple table with the following structure:

CREATE TABLE dbo.YearWeeks ([Year] int NOT NULL, [WeekNo] int NOT NULL, [DateFrom] datetime NOT NULL, [DateTo] datetime NOT NULL)

Into that table I import (using an Excel spreadsheet like this) all the weeks of my financial year such that even though the date might be 2004-02-02 (in the example above) the Year column would be set as 2003 and the WeekNo column would be 45. As I want the DateTo column to be the last second of the day in question I run a little update script:

UPDATE dbo.YearWeeks SET DateTo = DATEADD(ss, 86399, DateTo) … as there are 86400 seconds in a day.

Financial Weeks Query

Now I can write a slightly different query that, whilst I know is less efficient, produces a very elegant and easy result:

SELECT YW.[DateFrom], YW.[DateTo], YW.[WeekNo], (SELECT SUM(SOH.[TotalDue]) FROM Sales.SalesOrderHeader SOH WHERE SOH.[OrderDate] BETWEEN YW.[DateFrom] AND YW.[DateTo]) As 'SalesForWeek' FROM dbo.YearWeeks YW WHERE YW.[Year] = 2003 ORDER BY 1

And that resolves all three of the above issues. Before the next financial year comes round you simply upload another set of dates and all the preparation work is already done for you.

Better Solution?

If anyone reading this has a better solution to this please post a comment and let me know as I would love to know what it is and judging by some of the posts on forums around the Internet, others may welcome it too.

The Reporting Services bit

So I promised that I would show the reporting part of this – well hopefully it is pretty simple to understand now that above workaround is in place:

ExampleFinancialReport (available for download here)

As you can see from the image above it is a very simple report with a table and five columns:

• Week No: =Fields!WeekNo.Value
• Date From: =Fields!DateFrom.Value
• Date To: =Fields!DateTo.Value
• Sales For Week: =Fields!SalesForWeek.Value
• Cumulative Sales: =RunningValue(Fields!SalesForWeek.Value, Sum, Nothing)

Charts and RunningValues

As a final thought, let’s say you want a graph that shows the cumulative sales over the weeks of the year like the one below:

The trick here is that you need a “Dummy” category group before the actual (in our case WeekNo) category group to provide scope for the running value as shown here:

 

Then the expression for the Cumulative Sales values becomes:

=RunningValue(Fields!SalesForWeek.Value, Sum, “DummyGroup”)

Summary

Hope this helps someone – any better ideas would be very welcome and as always, feedback (positive or negative) is always welcome.

• grouping by week
• reporting
• runningvalue
• sql
• SQL Reporting Services
• year to date