Returning an image from an SSAS stored procedure
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:
- public static String SampleImage()
- {
- Bitmap bmp = new Bitmap( 80, 20, PixelFormat.Format32bppArgb);
- Graphics gBmp = Graphics.FromImage(bmp);
- System.Random RndObject = new System.Random();
- gBmp.CompositingMode = CompositingMode.SourceCopy;
- int y=10;
- int y1 = 0;
- Pen p = new Pen(Color.Black);
- gBmp.DrawLine(p, 0, 10, 80, 10);
- int y=10;
- int y1 = 0;
- Pen p = new Pen(Color.Black);
- gBmp.DrawLine(p, 0, 10, 80, 10);
- p.Color = Color.Blue;
- p.Width = 1;
- for (int i=10;i<=80;i+=10) {
- y1 = RndObject.Next(1, 19);
- gBmp.DrawLine(p, i - 10, y, i, y1);
- y = y1;
- }
- MemoryStream IS = new MemoryStream();
- bmp.Save(IS, ImageFormat.Png);
- IS.Flush();
- return Convert.ToBase64String(IS.ToArray());
- }
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
- with member bmp as ASStatistics.SampleImage()
- select {bmp} on columns,
- [Product].[Product].[Product] on 1
- 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
- =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.
Neuen Kommentar schreiben