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

Technical Value

SQL Server 2005

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:

  1. WITH
  2.   MEMBER [WLPlot] AS
  3.     ASStatistics.WinLossChart(
  4.     tail(
  5.       nonempty(
  6.         [Date].[Date].[Date],
  7.         [Measures].[Sales Amount]),32),
  8.     [Measures].[Sales Amount], (
  9.     [Measures].[Sales Amount],
  10.     [Date].[Date].currentmember.prevmember))
  11. SELECT
  12.   {
  13.     [WLPlot],
  14.     [Gross Profit Margin]
  15.   } ON 0,
  16.   [Product].[Category].[Category] ON 1
  17. 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:

  1. public static Bitmap CreateWinLossBitmap(double[] q, double[] threshold)
  2. {
  3.     int i;
  4.     int l = q.Length;
  5.     Bitmap objBitmap = new Bitmap(5 * l + 20, 22);
  6.     Graphics objGraphic = Graphics.FromImage(objBitmap);
  7.     objGraphic.FillRectangle(Brushes.White, 0, 0, 5 * l + 20, 30);
  8.     for (i = 0; i < l; ++i)
  9.     {
  10.         if (q[i] == Double.MinValue)
  11.             objGraphic.FillRectangle(Brushes.Gray, 10 + (i * 5), 9, 4, 4);
  12.         else
  13.             if (q[i] > threshold[i])
  14.                 objGraphic.FillRectangle(Brushes.Green, 10 + (i * 5), 2, 4, 7);
  15.             else
  16.                 objGraphic.FillRectangle(Brushes.Red, 10 + (i * 5), 10, 4, 7);
  17.     }
  18.     return objBitmap;
  19. }

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

  1. public static string WinLossChart(Set inputset, Expression valueExpression, Expression thresholdExpression)
  2.         {
  3.             double[] q = new double[inputset.Tuples.Count];
  4.             double[] thr = new double[inputset.Tuples.Count];
  5.             int idx = 0;
  6.             foreach (Tuple tuple in inputset)
  7.             {
  8.                 q[idx] = valueExpression.Calculate(tuple).ToDouble();
  9.                 thr[idx] = thresholdExpression.Calculate(tuple).ToDouble();
  10.                 idx++;
  11.             }
  12.             Bitmap bmp = CreateWinLossBitmap(q, thr);
  13.             MemoryStream IS = new MemoryStream();
  14.             bmp.Save(IS, ImageFormat.Png);
  15.             IS.Flush();
  16.             return Convert.ToBase64String(IS.ToArray());
  17.         }

For building the library you need references to

  1. Microsoft.AnalysisServices (Microsoft.AnalysisServices.dll)
  2. Microsoft.AnalysisServices.AdomdClient (Microsoft.AnalysisServices.AdomdClient.dll)
  3. Microsoft.AnalysisServices.XMLA (Microsoft.AnalysisServices.XMLA.dll)
  4. 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

  1. Gacutil.exe /i assembly_name.dll

You also have to add references on these libraries to your C# project like this:

  1. <ItemGroup>   
  2.     <Reference Include="Microsoft.AnalysisServices, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
  3.       <SpecificVersion>False</SpecificVersion>
  4.     </Reference>
  5.     <Reference Include="Microsoft.AnalysisServices.AdomdClient, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
  6.       <SpecificVersion>False</SpecificVersion>
  7.     </Reference>
  8.     <Reference Include="Microsoft.AnalysisServices.XMLA, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
  9.       <SpecificVersion>False</SpecificVersion>
  10.     </Reference>   
  11.     <Reference Include="msmgdsrv, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86">
  12.       <SpecificVersion>False</SpecificVersion>     
  13.       <Private>False</Private>
  14.     </Reference>
  15.      ...   
  16.  </ItemGroup>

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.

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden

Unsere Website benutzt Cookies, um bestmögliche Funktionalitäten bieten zu können. Durch die Nutzung unserer Website, erklären Sie sich mit der Verwendung von Cookies einverstanden. In unserer Datenschutzerklärung finden Sie unter §5 Informationen und Links, wie Sie diesen Maßnahmen jederzeit widersprechen können.