Search This Blog

Saturday, July 5, 2008

Returning an image from an SSAS stored procedure

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);

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.





Therefor 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:





image_thumb3





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.

2 comments:

  1. What do I need to do to be able to see the chart in an Excel Pivot table?

    Convert.FromBase64String is not available within MDX.

    Great stuff!
    Dirk

    ReplyDelete
  2. As far as I know you would need at least some extensions in Excel in order to render the bitmapped graphics in an Excel pivot table.

    You might still be able to show the graphics in a single image object with some Visual Basic behind the scenes. In some later post I described how to query MDX from Excel 2003 via VB. From there it might be possible to convert it and display the result in an image object.

    For sparklines in Excel you might want to take a look at Bonavista (www.bonavistasystems.com/) or Bissantz (www.bissantz.de/sparklines/)

    ReplyDelete