05.07.2008

Returning an image from an SSAS stored procedure

Technical Value

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:

image

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.

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