Thursday, 19 November 2009

Hierarchies, ParallelPeriod, and the Tablix

I’ve had an interesting week or so doing some proof-of-concept work trying to replicate some QlikView reports in SQL Server Reporting Services 2008 (which is a story for another day).  One of the things that came up was a tabular report that should have been oh-so-simple but wasn’t.

To cut a long story short here’s roughly what it needed to look like (except that there would be values in more of the month cells):

Country 0506 0607 1 2 3 4 5 6 7 8 9 10 11 12
United States 3,079,806 19,471,989 1,321,439 2,497,472                    
Canada 13,208,634 5,615,169                        

The easy way to do this would have been to return a dataset with 14 columns – 2 for the annual totals and 12 for the monthly totals – and put them into a table.  However, this would have been a maintenance nightmare as well as quite slow to render.  The alternative is a matrix-like report.

We need the measures for each month by country so we can feed it into the matrix.  We can use a row total in SSRS to produce the current year total, but we need some way of producing the previous year total.  Because we’re grouping the previous year annual total by country, it makes sense to repeat it for each occurrence of country in the dataset regardless of the actual month.  So our dataset should look something like this:

Country Month Reseller sales amount Previous year total
Canada 1 438,822 3,079,806
Canada 2 658,166 3,079,806
United States 1 1,321,439 13,208,634

Incidentally, this is a very good example of why the Tablix is reason enough to upgrade to SSRS 2008.  A matrix would allow us to dynamically expand the columns to include all the months (without having to create 12 columns), but a Tablix makes it easy to add the two annual total columns.

The problem I struggled with was how to get the previous year total for that country to appear in every row.  It didn’t take long to head in the direction of the ParallelPeriod function, but it would only return data for the month in the previous year (rather than the whole year).  In the end the answer was obvious – somehow I needed it to work not against the current member (which was a month) but against the year to which it belonged, in other words it’s parent year.  Actually the year is the great-grandparent or in MDX parlance, the Parent.Parent.Parent.

So here's the MDX:

WITH
    MEMBER [Measures].[LastFiscalYearResellerSalesAmount]
        AS
        (
            ParallelPeriod
            (
                [Date].[Fiscal].[Fiscal Year],
                1,
                [Date].[Fiscal].CURRENTMEMBER.PARENT.PARENT.PARENT
            ),
            [Measures].[Reseller Sales Amount]
        ), FORMAT_STRING = "Currency"
SELECT
    {
        [Measures].[Reseller Sales Amount],
        [Measures].[LastFiscalYearResellerSalesAmount]
    } ON COLUMNS,
    NonEmpty
    (
        (
            [Date].[Fiscal].[Month].ALLMEMBERS,
            [Geography].[Country].[Country]
        ),
        [Measures].[Reseller Sales Amount]
    ) ON ROWS
FROM
    (
        SELECT
            [Date].[Fiscal].[Fiscal Year].&[2003] ON COLUMNS
        FROM
            [Adventure Works]
    )

It selects against the 2003 fiscal year using a sub-select.  The advantage of this is that you can easily replace it with an SSRS parameter (by using StrToSet).

I’ve put together a sample report which can be downloaded from my SkyDrive here.

No comments:

Post a Comment