Win/Loss chart as dynamic image created in SSAS stored procedure

Sascha Lorenz posted the source code for creating win/loss charts in his blog. I really like this kind of charts as it is especially useful for sparklines. Sascha created the bitmap for the chart dynamically on demand using ASP.NET. I thought this could be a great idea for our interactive graphics from the previous posts so that we can easily define them inside the cube and use them in a SSRS report.

First, let’s look at the result: 

The underlying data is just an example. It shows the days on which the order amount is higher (green) or lower (red) compared to the previous day covering the last 32 days.

The MDX query for our report looks like this:

WITH
  MEMBER [WLPlot] AS
    ASStatistics.WinLossChart(
    tail(
      nonempty(
        [Date].[Date].[Date],
        [Measures].[Sales Amount]),32),
    [Measures].[Sales Amount], (
    [Measures].[Sales Amount],
    [Date].[Date].currentmember.prevmember))
SELECT
  {
    [WLPlot],
    [Gross Profit Margin]
  } ON 0,
  [Product].[Category].[Category] ON 1
FROM [Adventure Works]

I turned Sascha’s function into a stored procedure named WinLossChart. WinLossChart takes three arguments:

Argument

Description

inputset

The set over which the expressions are to be evaluated

valueExpression

The expression for the value

thresholdExpression

The expression for the threshold

If the value is higher than the threshold we paint a green bar, if it is lower, we paint a red bar. For the simple example above we made Sales Amount the value expression and the Sales Amount of the previous day the threshold expression. Of course, the threshold could also be a simple constant.  

So, here’s the code for the stored procedure. The first function returns the bitmap and is very close to the code Sascha posted in his blog entry:  

        public static Bitmap CreateWinLossBitmap(double[] q, double[] threshold)
        {
            int i;
            int l = q.Length;
            
            Bitmap objBitmap = new Bitmap(5 * l + 20, 22);
            Graphics objGraphic = Graphics.FromImage(objBitmap);

            objGraphic.FillRectangle(Brushes.White, 0, 0, 5 * l + 20, 30);
            for (i = 0; i < l; ++i)
            {
                if (q[i] == Double.MinValue)
                    objGraphic.FillRectangle(Brushes.Gray, 10 + (i * 5), 9, 4, 4);
                else
                    if (q[i] > threshold[i])
                        objGraphic.FillRectangle(Brushes.Green, 10 + (i * 5), 2, 4, 7);
                    else
                        objGraphic.FillRectangle(Brushes.Red, 10 + (i * 5), 10, 4, 7);
            }           

            return objBitmap;
        }

And here’s the function wrapping the above code into a SSAS stored procedure. This is the function we’re calling from MDX:  

public static string WinLossChart(Set inputset, Expression valueExpression, Expression thresholdExpression)
        {
            double[] q = new double[inputset.Tuples.Count];
            double[] thr = new double[inputset.Tuples.Count];
            int idx = 0;


            foreach (Tuple tuple in inputset)
            {
                q[idx] = valueExpression.Calculate(tuple).ToDouble();
                thr[idx] = thresholdExpression.Calculate(tuple).ToDouble();
                idx++;
            }

            Bitmap bmp = CreateWinLossBitmap(q, thr);
            MemoryStream IS = new MemoryStream();
            bmp.Save(IS, ImageFormat.Png);
            IS.Flush();

            return Convert.ToBase64String(IS.ToArray());
        }

For building the library you need references to

  • Microsoft.AnalysisServices (Microsoft.AnalysisServices.dll)
  • Microsoft.AnalysisServices.AdomdClient (Microsoft.AnalysisServices.AdomdClient.dll)
  • Microsoft.AnalysisServices.XMLA (Microsoft.AnalysisServices.XMLA.dll)
  • msmgdsrv (msmgdsrv.dll) 
If you don't have the above assemblies installed on your machine you can take them from a SQL Server 
installation and install them to the global assembly cache (GAC) using
Gacutil.exe /i assembly_name.dll

Of course the code above is just to show the basic idea and should be rewritten for a productive environment.

Thanks to Sascha for the visualization. Again, I recommend reading his blog entry for the original code.

Advertisements
This entry was posted in Allgemein. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s