SQL Server 2005 | SQL Server 2008
In a previous post I showed an example of how to return a dynamic bitmap graphic from a SSAS stored procedure and we also displayed a Win/Loss chart using this technique. Now, I just want to add some other ideas for creating such sparklines.
Of course, the first idea is to plot some micro line charts. And it really isn’t difficult. The result looks somewhat like this:
The main code for the plot looks like this (I’ve removed some lines to increase readability):
-
// Create Bitmap
-
Bitmap bmp = new Bitmap(plot_width, plot_height, PixelFormat.Format32bppArgb);
-
Graphics gBmp = Graphics.FromImage(bmp);
-
// calculate min and max values
-
…
-
// do the output
-
for (i = 1; i <= count; i++)
-
{
-
float x1 = (float)(i * ((double)plot_width) / (double)count);
-
float y1 = (float)((q[i]–t_min) * scale);
-
gBmp.DrawLine(Pens.Black, x0, plot_height – y0, x1, plot_height – y1);
-
x0 = x1;
-
y0 = y1;
-
}
-
gBmp.Dispose();
-
// Serialize the output
-
MemoryStream IS = new MemoryStream();
-
bmp.Save(IS, ImageFormat.Png);
-
IS.Flush();
-
return Convert.ToBase64String(IS.ToArray());
The code is very similar to the code I posted about two weeks ago. First, we create the Bitmap and Graphics object (the canvas to paint on). Then we’re plotting the line and finally we serialize the output to a base 64 encoded string.
Another idea might be turn the output of the stored procedure for the quantile calculation from my last post into a nice graphic.
The result may be rendered to a histogram output like this:
Another to display this in a more granular way is shown in the example below. Like in the first plot, the red area is below the quantile.
The code for both outputs is a bit lengthy but the basic idea is still very simple. Just create a canvas with the code posted before and draw the graphic on this canvas. After that serialize the canvas to a base 64 encoded string. For the report it just requires an image component that takes the base 64 encoded string is its source. Therefor the string needs to be converted using the function Convert.FromBase64String(…).
So, rendering graphics in stored procedures can be a really flexible way of creating small data-aware graphics.
Such graphics can be really helpful in understanding the meaning of a report at a glance. But they can also be very confusing. So, in my opinion, it’s best practice to keep sparkline graphics (and all other small graphics that are repeated per line) as simple as possible.
In most cases I really don’t recommend creating sparklines or other kinds of micro graphics using the approach above. There are excellent toolsets out there, for example by BonaVista Systems
or Bissantz
that really simplify the process of drawing sparklines a lot and that even work with Excel, Reporting Services or other frontend clients. So in most cases you will want to use such tools. Only for the rare cases that you really need to create a special chart that is not supported by such toolsets, you might consider returning the graphic by creating your own stored procedure like shown above.
Kommentare (0)