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).
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:
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)
-
Also, the code above requires a reference to the System.Drawing assembly:
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:
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.
Kommentare (0)