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.

Neuen Kommentar schreiben

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

Klartext

  • 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.