Tuesday, 24 November 2009

SSRS gauge custom labels

I’m not an artistic person – hardly the best attribute for a sometime report designer, so most of my dashboard efforts are primarily functional and secondarily aesthetic. For this reason I have thus far avoided the gauge control on the grounds that if something business critical (e.g. expenditure exceeding income) needs to be spelt out using pretty colours and dials then the consumer of that information probably shouldn’t be in a decision making capacity. Back in reality, where the world does not revolve around my dogmatic prejudices and the fact is that gauges are a very useful way of indicating critical success factors in a business without frying the mind of the busy executive.

So after years of avoidance I found myself having to use these dreaded controls in a SSRS 2008 project against an SSAS 2008 data source. All I had to do was display cost v income – 2 measures. This should be simple thought I foolishly only to be still working on the same task 2 days later. Much of this time was spent getting the range of the gauges to be dynamic according to the upper and lower limits of income and expenditure – but one of my biggest bugbears was displaying anything other than numbers on the scale.

Basically, I wanted numbers to be displayed in the most concise format possible so that very large values don’t crowd the scale - for example:

2500 should be displayed as 2.5k

1,000,000 should be displayed as 1m

1,000,000,000 should be displayed as 1bn

First thing I did was create an SSRS (VB) function that converted a double value to this concise representation:

Public Shared Function DoubleToConcise(valueToConvert As Double) As String

Dim negator As Double = 1
Dim value As Double = Math.Abs(valueToConvert)

If valueToConvert < 0 Then negator = -1

If value >= 1000 Then
   If value < 10000 Then
       Return ((value/1000)*negator).ToString() & “k”
   Else
       If value < 100000000 Then
           Return ((value/1000000)*negator).ToString() & “m”
       Else
           Return ((value/1000000000)*negator).ToString() & “bn”
       End If
   End If
End If

Return (value*negator).ToString()

End Function
(Note the use of the negator value and Abs function to save having to include negative integer ranges in the If statements) The only problem with this function is that the gauge still expects to be given (and display) numerical ranges, which means I’d have to return a double (rather than a string) value – which is fine except for the fact that 100,000 is now being shown as 0.10 with no qualifying suffix. After much gnashing of teeth and wailing I realised the solution was actually quite simple – basically split the function into two:
  • one to return the numeric value
  • another to return the suffix

Like so:

Public Shared Function DoubleToConciseValue(valueToConvert As Double) As Double

Dim negator As Double = 1
Dim value As Double = Math.Abs(valueToConvert)

If valueToConvert < 0 Then negator = -1

If value >= 1000 Then
   If value < 10000 Then
       Return (value/1000)*negator
   Else
       If value < 100000000 Then
           Return (value/1000000)*negator
       Else
           Return (value/1000000000)*negator
       End If
   End If
End If

Return value*negator

End Function

Public Shared Function DoubleToConciseFormat(valueToConvert As Double) As String

Dim value As Double = Math.Abs(valueToConvert)

If value < 1000 Then
   return String.Empty
Else
   If value < 10000 Then
       Return "k"
   Else
       If value < 100000000 Then
           Return "m"
       Else
           Return "bn"
       End If
   End If
End If

End Function

We then apply the DoubleToConciseValue function to all values passed into the gauge (pointer, scale limits, etc) so instead of:

=Fields!MyValue.Value

We do so:

=Code.DoubleToConciseValue(Fields!MyValue.Value)

But what about the suffix on the scale? This is where that old chestnut the format string comes in. Basically we can pass literal values into format strings and they are displayed verbatim – e.g. applying the format string “0.00hello” to a value of 156 would give us “156.00hello”. So we put the following formula in the FormatString property of the scale’s LabelStyle property:

=”£0.00” & Code.DoubleToConciseFormat(Fields!MyValue.Value)

Which gives us the finished gauge:

There are limitations to this approach in that it pretty much requires that at least some of the output is numerical, although even this limitation could be overcome with innovative use of IFormatProvider in a custom assembly.

Monday, 23 November 2009

Wonderful Windows 7

As I’ve been using it since the RTM became available on MSDN in August, it’s high time I put some praise Windows 7’s way.  Apart from all the snazzy UI features, much more efficient use of hardware (than either Vista or XP), and the oh-so-easy ad-hoc networking - the feature that I’m currently in love with is Windows XP mode.

Some time ago I bought a copy of Paint Shop Pro X2 for my copy of Vista.  It would run first time and then crash without fail thereafter.  Something to do with ntdll apparently – although given that (apart from PSP) I never had a single application compatibility problem under Vista, I suspect it’s more to do with Corel than Microsoft.  As I did buy X2 because it was compatible with Vista I found this very annoying to say the least.  Anyway, I decided to try it under Windows XP mode and it works a treat.

What I like about Windows XP mode is that it’s a classic case of using existing technologies in a different way to solve an otherwise difficult problem.  The integration is flexible and (almost) seamless.  Not only that, but it’s a great way to sandbox applications of less than stellar quality.

Microsoft get a lot of criticism (oddly enough from people who still choose to use their products despite the prevalence of alternatives) simply because they’re Microsoft.  I think they deserve some kudos for Windows 7 because it really is a pleasure to work with.

Multicoloured column charts in SSRS 2008

Perhaps I’m being as dumb as a bag of spanners but I’ve really struggled with getting multi-coloured column charts in Reporting Services 2008 without putting the category data on the series axis, which then creates all sorts of problems with sizing and grid lines. It seems that Dundas type charts don’t do this easily, although the Nevron ones do (if you know where to look). However I’m not forking out for third-party charting controls just to get nice colours.

This problem first came to light when doing the QlikView vs SSRS development (QlikView charts are pretty by default). Since then I’ve looked in vain for a multicolored (sic) property similar to charts in Excel.

To illustrate my point, I’ve included a screenshot from Excel where I’ve put together a simple column chart with a category and value axis:

By default this renders all columns in a single colour. However if we right-click any of the columns and ‘Format Series Data’ then we can vary the colours by data point:

Anyway, after much gnashing of teeth and wailing I chanced upon this article which got me thinking about a simple VB.NET function that could be used in the fill property of the columns. Thereafter the solution was quite simple. Basically you need to add the following function to your report code (remembering to add a reference to the System.Drawing assembly):

Public Shared Function GenerateRandomColor(ByVal seed As Integer) As String 

Dim randNum As New Random(seed)
Return System.Drawing.ColorTranslator.ToHtml( _ System.Drawing.Color.FromArgb( _ randNum.Next(0, 255), _ randNum.Next(0, 255), _ randNum.Next(0, 255)))

End Function

Then right click the columns in design view to bring up the Series Properties dialog and go to the Fill section:

Enter the following expression for the Color formula (obviously remembering to replace the fieldname with the field being used on the category axis):

=Code.GenerateRandomColor(RunningValue(Fields!MyFieldName.Value,Count,Nothing))
 

That’s about it really. A bit convoluted just for random colours but it works. Despite the fact that I will kick myself if this is already a supported feature (probably a checkbox marked ‘multicolored’) I would be really grateful if anyone could enlighten me.

Sunday, 22 November 2009

QlikView and Reporting Services

I recently downloaded the Office 2010 and PowerPivot Betas so I could compare Microsoft’s current and near-future BI offerings against the competition – which in this case is QlikView. As it is, I haven’t actually had a chance to muck around with PowerPivot yet so I’ll just summarise what I found about QlikView vs SSRS.

Firstly, I should qualify this by pointing out that although it may not be comparing apples with oranges, it could be considered comparing Granny Smith with Cox’s. In other words, while QlikView and SSRS do pretty much the same thing, they do not have parallel feature sets or business contexts. However, there are a lot of BI implementations taking place where the people making the decision will see it as either/or.

As a long-time SSRS user it was going to take a lot to impress me, but QlikView has achieved this. More because of its reporting tools than its in-memory database. Anyone who claims it negates the need for a data warehouse should be dismissed with a withering glance as this simply isn’t true. Like any application, if you put crap in you will get crap out. The fact is that you need to put as much effort into preparing your data as you would when building as SSAS cube. However slightly less effort is required thereafter and it’s very quick with the data processing. But it looks pretty and makes configuration of most common charting features (e.g. median lines, multi-coloured bars) a simple matter of selecting a checkbox. Dashboards can be put together in minutes – good ones in less than an hour.

There are two killer features: chart scrollbars and parameter selection.

Now this may sound trivial, but if you’ve every tried to present a bar chart with 100 different data points then you’ll realise how clever the first of these is. Basically all charts that have data points extending beyond their defined area will put a scrollbar on the axis. Not the chart, the axis. So the legend stays fixed and you can scroll through all the data items within the small space allocated to the chart on the dashboard. There are some clever implementations of this in Excel but they are non-standard and you sure as heck can’t do it out of the box in SSRS. The SQL Server and Office teams need to sort this out pronto!

Parameter selection in QlikView is in a completely different league to SSRS. Basically the list of parameters have mutual filters and allow Ajax-style selection. So a list of 100,000 parameters doesn’t slow things to a snail’s pace or make it difficult to find items. It’s most impressive although it does highlight one of the major shortcomings of QlikView – namely that it doesn’t understand recursive hierarchies (although if anyone would care to dispute this I’d be most grateful). Feeding in a table with recursive keys leads to somewhat unexpected and unpredictable results. and even does recursive hierarchies (see comments).

The only other drawbacks I’ve found so far to QlikView are around security and customisation. Basically it isn’t too easy to do either, certainly nothing to match the logic-based security of SSAS or flexible security of SSRS. Also the drill through isn’t quite as intuitive, even if it is logical (charts drill through to more charts).

Overall I’d say QlikView is a very impressive product but it won’t preclude the need to get your data straight first. If Microsoft want to stay ahead then they need to get scrollbars on the chart axes and much better parameter selection than SSRS currently provides.

I haven’t covered QlikView as a server product (we were working with the desktop edition) so I suspect this will not be my last post on the topic.

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.