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.

Students, Apple, VMWare and Microsoft DreamSpark

I am a genuine student of the Open University (www.open.ac.uk) having so far completed three out of lots of modules in a bid to achieve a Masters Degree in Computing for Commerce and Industry. As a general rule of thumb, so far, this aim has cost me a lot of money (Open University courses are not cheap!) but recently I have been pleasantly surprised by the discounts/freebies that are available to students. So without further ado, and as an introduction to the fact that in future you may well see posts relating to Apple Macs appearing here, I thought I would share a couple of the discounts I have recently received:

Apple iMac

I finally took the plunge and bought an Apple iMac 24″ 3.06GHz Intel Core 2 Duo with 4GB 800MHz DDR2 SDRAM. The retail price for this either in an Apple shop or online for us UK customers is £1457.00. In case anyone is interested by the way, it is absolutely gorgeous and I don’t think I’ll be using a PC anytime soon for anything other than work!

Anyway, I got 12% off the Apple iMac and 11% off the 2GB RAM upgrade resulting in a saving of £184.64. Granted it is still a lot of money but hey, £184.64 is worth having!

VMWare Fusion

I do a lot of work on Microsoft Windows (both servers and desktops) and consequently, at least for the time being, I need access to these environments. VMWare sell a very cool product in the form of VMWare Fusion 2! Essentially this provides virtual environments for the Mac in the same way as their Workstation product provided for Windows. Microsoft have a similar (FREE) product called Virtual PC which is also rather neat. However, the really cool thing here is that a) I can happily run Linux machines as well as Windows ones and b) I can place links on my Apple desktop to files and applications on my virtual Windows machines using the amazing Unity feature! Take a look at some of the videos on YouTube regarding VMWare Fusion 2.

Anyway, I got 50% off VMWare Fusion 2 resulting in a saving of £33.29.

Microsoft DreamSpark

I mentioned I did a lot of work on Microsoft Windows – usually in the form of Service Desk Express stuff or .NET software development in general. Whilst hunting around for something else on the Internet (as you do) I came across a result offering “Microsoft Gives Students Access to Technical Software at No Charge to Inspire Success and Make a Difference” alternatively known as DreamSpark.

Now, subject (of course) to certain licensing agreements (e.g. not using the software for commercial purposes), students can download and use the full version of the following Microsoft products:

• Microsoft Visual Studio 2008 Professional
• Microsoft Visual Studio 2005 Professional
• Microsoft XNA Game Studio 2.0
• Microsoft Expression Studio 2
• Windows Server 2003 R2
• Windows Server 2008 Standard
• Microsoft SQL Server 2008 Developer

This is a brilliant initiative by Microsoft and an excellent way of students developing essential new skills e.g. Silverlight 2.0 in the forthcoming year.

Hope everyone had a great holiday period and I look forward to receiving your comments throughout this year.