Search This Blog

Sunday, April 29, 2012

Some more Reporting Services charts

SQL Server 2008-2012

To finish my current Reporting Services mini series in this blog, here are some more charts I recently created using SSRS (this is just a list, I did not include titles and they make no sense in this combination). Most charts here make use of the range chart type which allows very flexible positioning of the bars.

imageimage

imageimage

image image

image

imageimage

And I also like to refer to my older blog post about using the map functionality of SSRS to display all kind of stuff. The example below is taken from this post. It shows a level in a park house together with the utilization of each single parking slot (based on data from a data base). The layout and all elements like the barriers, the arrows and the text have been created using Microsoft Visio.

image

Monday, April 16, 2012

SSRS Custom Drawing (Code)

SQL Server 2005 - 2012

After my last blog post, I got a lot of requests for the code I used for the graphics. So this post just shows the source code for the graphics of my last post.

Both examples require that you include a reference to System.Drawing to your report as shown below:

image

 

1. The deviation chart element

image

Here is the code used for the last column’s image:

Function PaintAbw(ByVal width As Integer, ByVal height As Integer, ByVal min As Single, ByVal max As Single, ByVal middle As Single, ByVal cur As Single) As System.Drawing.Bitmap
    ' Draws a vertical deviation chart
    ' parameters
    '   width/height   width and height of the resulting image in pixel
    '   min/max        minimum and maximum value of the data being passed to the chart
    '   middle         position of the vertical reference line (usually zero or avg of the data)
    '                  data points below this value are plotted in red, above this value in blue
    '   cur            actual value to display

    Dim objBitmap As System.Drawing.Bitmap
    Dim objGraphic As System.Drawing.Graphics
    Dim myBrush As System.Drawing.Brush
    Dim x0 As Integer, x As Integer

    ' Initialize the graphic
    objBitmap = New System.Drawing.Bitmap(width, height)
    objGraphic = System.Drawing.Graphics.FromImage(objBitmap)
    objGraphic.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias
    objGraphic.FillRectangle(System.Drawing.Brushes.White, 0, 0, width, height)

    ' Draw the vertical line (the "middle" position)
    x0 = 8 + ((width - 18) * (middle - min)) \ (max - min)
    objGraphic.DrawLine(System.Drawing.Pens.Gray, x0, 0, x0, height)
    If cur < min Then cur = min
    If cur > max Then cur = max

    ' Draw the deviation (line and circle)
    x = 8 + ((width - 18) * (cur - min)) \ (max - min)
    objGraphic.DrawLine(System.Drawing.Pens.Gray, x0, height \ 2, x, height \ 2)
    If cur < middle Then
        myBrush = New System.Drawing.SolidBrush(System.Drawing.Color.FromArgb(215, 100, 100))
    Else
        myBrush = New System.Drawing.SolidBrush(System.Drawing.Color.FromArgb(100, 100, 215))
    End If
    objGraphic.FillPie(myBrush, x - 4, height \ 2 - 4, 8, 8, 0, 360)

    ' Return the image as type bitmap
    Return objBitmap
End Function

Function PaintAbwBmp(ByVal width As Integer, ByVal height As Integer, ByVal min As Single, ByVal max As Single, ByVal middle As Single, ByVal cur As Single) As Byte()
    ' Wrapper function for PaintAbw. This function is to be called from reporting services as
    ' for parameters see PaintAbw

    Dim bmpImage As System.Drawing.Bitmap

    ' Get bitmap from PaintAbw
    bmpImage = PaintAbw(width, height, min, max, middle, cur)

    ' Convert this bitmap to a byte array of type BMP
    Dim stream As System.IO.MemoryStream = New IO.MemoryStream
    Dim bitmapBytes As Byte()
    bmpImage.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)
    bitmapBytes = stream.ToArray
    stream.Close()
    bmpImage.Dispose()
    Return bitmapBytes
End Function

Examples

PaintAbw(100, 20, -100.0, 100.0, 0.0, 50.0) image
PaintAbw(100, 20, 0.0, 100.0, 0.0, 80.0) image
PaintAbw(100, 20, -100.0, 100.0, 0.0, -80.0) image

Usage within the report as the source for a bitmap

image

Sample Expression:

=Code.PaintAbwBmp(100,20,Min(Fields!Value.Value, "DataSet1"),Max(Fields!Value.Value, "DataSet1"), Avg(Fields!Value.Value, "DataSet1"), Fields!Value.Value)

 

2. The KPI slider

image

Here is the code used for the last column’s image:

Function PaintBox(ByVal width As Integer, ByVal height As Integer, ByVal level As Single) As System.Drawing.Bitmap
    ' Draws a range chart (blending from red to green)
    ' parameters
    '   width/height   width and height of the resulting image in pixel
    '   min/max        minimum and maximum value of the data being passed to the chart
    '   level          position of slide
    '                  0: left, 1: right, 0.5 middle
    '                  make sure you scale this value to your needs

    Dim objBitmap As System.Drawing.Bitmap
    Dim objGraphic As System.Drawing.Graphics

    ' Initialize the graphic
    objBitmap = New System.Drawing.Bitmap(width, height)
    objGraphic = System.Drawing.Graphics.FromImage(objBitmap)
    objGraphic.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias
    objGraphic.FillRectangle(System.Drawing.Brushes.White, 0, 0, width, height)

    ' Create color gradient for the background
    Dim BrushRedYellow As New System.Drawing.Drawing2D.LinearGradientBrush(New System.Drawing.Rectangle(8, 0, (width - 16) \ 2, height), System.Drawing.Color.Red, System.Drawing.Color.Yellow, System.Drawing.Drawing2D.LinearGradientMode.Horizontal)
    Dim BrushYellowGreen As New System.Drawing.Drawing2D.LinearGradientBrush(New System.Drawing.Rectangle(7 + (width - 16) \ 2, 0, (width - 16) \ 2, height), System.Drawing.Color.Yellow, System.Drawing.Color.Green, System.Drawing.Drawing2D.LinearGradientMode.Horizontal)
    objGraphic.FillRectangle(BrushRedYellow, 8, 3, (width - 16) \ 2, height - 10)
    objGraphic.FillRectangle(BrushYellowGreen, 8 + (width - 16) \ 2, 3, (width - 16) \ 2, height - 10)

    ' Scale level properly
    If level > 1 Then level = 1
    If level < 0 Then level = 0

    ' Draw the arrow
    Dim Arrow(2) As System.Drawing.PointF
    Arrow(0) = New System.Drawing.PointF(8 + (width - 16) * level, height - 10)
    Arrow(1) = New System.Drawing.PointF(16 + (width - 16) * level, height - 2)
    Arrow(2) = New System.Drawing.PointF(0 + (width - 16) * level, height - 2)
    objGraphic.FillPolygon(System.Drawing.Brushes.Black, Arrow)

    ' Return the image as type bitmap
    Return objBitmap
End Function

Function PaintBoxBmp(ByVal width As Integer, ByVal height As Integer, ByVal level As Single) As Byte()
    ' Wrapper function for PaintBox. This function is to be called from reporting services as
    ' for parameters see PaintBox
    Dim bmpImage As System.Drawing.Bitmap

    ' Get bitmap from PaintBox
    bmpImage = PaintBox(width, height, level)

    ' Convert this bitmap to a byte array of type BMP
    Dim stream As System.IO.MemoryStream = New IO.MemoryStream
    Dim bitmapBytes As Byte()
    bmpImage.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)
    bitmapBytes = stream.ToArray
    stream.Close()
    bmpImage.Dispose()
    Return bitmapBytes
End Function

 

Examples

PaintBox(100, 20, 0.0) image
PaintBox(100, 20, 1.0) image
PaintBox(100, 20, 0.5) image

 

=Code.PaintBoxBmp(100,20,Fields!Sales_Amount.Value/Fields!Sales_Amount_Quota.Value)

Sample Expression:

=Code.PaintAbwBmp(100,20,Min(Fields!Value.Value, "DataSet1"),Max(Fields!Value.Value, "DataSet1"), Avg(Fields!Value.Value, "DataSet1"), Fields!Value.Value)

Tuesday, April 10, 2012

SSRS Custom Drawing

SQL Server 2005 -  SQL Server 2012

In my last post I showed some tricks with reporting services charts. As I mentioned, the possibilities are almost endless.For example, Jason Thomas created an excellent step-by-step instruction of how to create overlapping bar charts. So, I can just encourage everybody to be creative with these chart properties. It’s really amazing what kind of charts can be created this way.

But with the ability to use code behind a report, SSRS is even more powerful. In an early post, Jason Thomas reported about a method of rotating text in SSRS 2005. As SSRS 2005 has no property to rotate text, the solution was to write custom code that actually returns a bitmap as a byte array. This bitmap can then be placed on the report. Just imagine what this means for you: You can create a .Net drawing function, pass parameters from the report (maybe from a data set) to the function and place the resulting imagine on the report! Being able to use the powerful System.Drawing functions gives us almost unlimited power to create interesting graphical representations. This post shows just two examples.

In the following screenshot of a Reporting Services report I used this technique to create a custom KPI representation (the “sliders” in the last column).

image

In order to create this graphical element, I used two functions. The first function returns a System.Drawing.Bitmap, the second function converts this bitmap to a byte array. I removed the simple drawing code itself here to focus on the idea. This is how the two functions look like:

Function PaintBox(ByVal width As Integer, ByVal height As Integer, ByVal level As Single) As System.Drawing.Bitmap
    Dim objBitmap As System.Drawing.Bitmap
    Dim objGraphic As System.Drawing.Graphics

    objBitmap = New System.Drawing.Bitmap(width, height)
    objGraphic = System.Drawing.Graphics.FromImage(objBitmap)

    ' … do the drawing here, for example objGraphic.DrawLine(System.Drawing.Pens.Gray, 0, 0, width, height)

    Return objBitmap
End Function

Function PaintBoxBmp(ByVal width As Integer, ByVal height As Integer, ByVal level As Single) As Byte()
    Dim bmpImage As System.Drawing.Bitmap

    bmpImage = PaintBox(width, height, level)
    Dim stream As System.IO.MemoryStream = New IO.MemoryStream
    Dim bitmapBytes As Byte()
    bmpImage.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)
    bitmapBytes = stream.ToArray
    stream.Close()
    bmpImage.Dispose()
    Return bitmapBytes
End Function

The report uses a table with an image in the last column:

image

In the image properties, the following settings have to be made:

  • Image Source: Database
  • MIME Type: image/bmp (you may use one of the other mime types instead, as long if the mime type matches the output format for the function PaintBoxBmp above).
  • Use this field: Expression
    =Code.PaintBoxBmp(100,20,Fields!Sales_Amount.Value/Fields!Sales_Amount_Quota.Value)

image

Also, the code above requires a reference to the System.Drawing assembly:

image

When writing such functions, it’s a good practice to wrap them in a custom .Net assembly.

If you use a custom .Net assembly…

  • … it’s easier to write the code. Nobody wants to write larger code blocks using the SSRS code editor which gives no hints or syntax highlighting
  • … you may use C# to code the custom assembly
  • … you can use import statements to reference assemblies (like System.Drawing). With code behind a report, this doesn’t work yet, so you always have to use the fully qualified class name
  • … it’s easier to test and debug your code for example by sending the output to a Windows Forms test application

Here is another example of a graphical element (last column) created using the approach from above:

image

These kind of charts show which rows are above or below the average. There is one image (chart) per line composing the effect of a single chart with a straight vertical line.

So with just a little bit of code behind the report it’s possible to create interesting graphical representations of data that are not provided by Reporting Services out of the box.