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:
- Week number 1 is the first week in January 2004 NOT the first week in April 2003 as it should be.
- The display is a week number not a start date/end date i.e. Week Beginning.
- 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:
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.
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”)
Hope this helps someone – any better ideas would be very welcome and as always, feedback (positive or negative) is always welcome.
Having just been working on a bunch of similar YTD reports (based on incident in SDE) I found a couple of articles you may find interesting for SSRS.
Although these do require alot more work to initially set them up – it does mean they become dynamic with no annual maintenance.
Firstly – look at using a matrix rather than table output for your report. These are somewhat limited within SSRS however the following article I found very useful in expanded their functionality to better suit the needs of the report:
SQLServer central requires logon but its free registration
This article here will show you how to create a function which allows you to specify the start week of a fiscal year and returns a week number based upon that. You can also use SQL standard Date functions to calculate first day of week.
Just thought I’d give you a bit of food for thought to bypass the need for the 1st of Jan admin overhead 🙂
Thanks very much for the useful resources and feedback – always much appreciated.
I make heavy use of Matrix controls in SSRS especially once I discovered the little green triangle properties! The function is cool too but I am not sure that it addresses the issue of if there is no data in the week, then the week will not be displayed.
Hope this makes sense. Happy to be corrected.