Returning an image from an SSAS stored procedure

05.07.2008 Hilmar Buchta

SQL Server 2005

I just played with a simple way to return an image from a stored procedure by returning a base 64 encoded string of the image in a certain image format (e.g. PNG or JPEG).

Our sample stored procedure looks like this:

  1. public static String SampleImage()
  2.         {
  3.             Bitmap bmp = new Bitmap( 80, 20, PixelFormat.Format32bppArgb);
  4.             Graphics gBmp = Graphics.FromImage(bmp);
  5.             System.Random RndObject = new System.Random();
  6.             gBmp.CompositingMode = CompositingMode.SourceCopy;
  7.             int y=10;
  8.             int y1 = 0;
  9.             Pen p = new Pen(Color.Black);
  10.             gBmp.DrawLine(p, 0, 10, 80, 10);
  11.             int y=10;
  12.             int y1 = 0;
  13.             Pen p = new Pen(Color.Black);
  14.             gBmp.DrawLine(p, 0, 10, 80, 10);
  15.             p.Color = Color.Blue;
  16.             p.Width = 1;
  17.             for (int i=10;i<=80;i+=10) {
  18.                 y1 = RndObject.Next(1, 19);
  19.                 gBmp.DrawLine(p, i 10, y, i, y1);
  20.                 y = y1;
  21.             }
  22.             MemoryStream IS = new MemoryStream();
  23.             bmp.Save(IS, ImageFormat.Png);
  24.             IS.Flush();
  25.             return Convert.ToBase64String(IS.ToArray());
  26.         }

The function draws a very simple random line chart (we provided no real data but in some later posts I will provide some samples) and returns the resulting in-memory bitmap as base 64 encoded string.

Therefore the MDX code

  1. with member bmp as ASStatistics.SampleImage()
  2. select {bmp} on columns,
  3. [Product].[Product].[Product] on 1
  4. from [Adventure Works]

returns a list of products together with the base 64 encoded image string. We use this MDX query for the report data source. In order to show the image on a report, we have to use the .Net function Convert.FromBase64(…). So, first we place an image object on our report, then we set the value property of the image to

  1. =Convert.FromBase64String(Fields!bmp.Value)

That’s all folks. The result looks like this:


Ok, this isn’t exactly nice, but it still gives us some freedom in designing custom data aware graphics, like sparklines or other micro charts, and put them on a report.

Your email address will not be published. Required fields are marked *

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten