Randomized color report? So help my CODE!

Writing a Reporting Services report can be challenging, making you use the most of its features so that even the simplest tasks may require wider range of capabilities. One of those is using .NET code (VB.NET) in the report. This post outlines the basics of implementing .NET code in your reports. The following .rdl file is the final version of the report for your reference.

One of our customers created a multi-bar graph in one of his reports. However, the bars’ color pattern repeated, which meant a few bars with the same color:

I’ve been asked to change the report to generate random background colors for the bars. Luckily, I found the post Multicoloured column charts in SSRS 2008 by Ben E. about providing this functionality to the report using VB.NET code. So here are step-by-step instructions on adding a code to a report.

The sample report is simple, running the two-column query below and using a stacked column graph. As you can see, the output will vary due to the randomizing of the order by clause.

SELECT LEFT([name], 3) AS [PartialName], COUNT(*) AS [counter]
FROM sys.objects
GROUP BY LEFT([name], 3)
ORDER BY NEWID(); -- Randomize the output

Once the graph is added, enter the VB.NET code:

1) Right click the report and click Report Properties

2) Select References in the left pane, click Add to add an assembly and browse for the System.Drawing assembly

3) Click OK all the way back to the report

4) Right click the report and click Report Properties

5) Select Code in the left pane and copy-paste the code below into the text box. Click OK.

Please note, I changed the code to assure a randomized output.

Public Shared Function GenerateRandomColor() As String
Randomize()
Dim R As Integer = CInt(Int((255 * Rnd()) + 1))
Dim G As Integer = CInt(Int((255 * Rnd()) + 1))
Dim B As Integer = CInt(Int((255 * Rnd()) + 1))
Return System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(R, G, B))
End Function

6) Change the background of the series by right-clicking the series and selecting Series Properties

7) Select Fill in the left pane and click the fx (expression) button.

Add the following expression (note the “.Code” before the function). Click OK to return to the report.

=Code.GenerateRandomColor()

The report is ready. As you can see in the preview tab, the graph is colorful with no repeating pattern.

Generating reports can be more complex than one may think. Adding code to the report can make a big difference. This code can be modified and used differently. Furthermore, you can add classes and more VB.NET code to enhance your report.

The post Randomized Colors in Report appeared first on Madeira Data Solutions.