SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Randomized Colors in Report

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.

zip

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:

Random Background 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

Report Menu

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

3 report properties

4 add reference

3) Click OK all the way back to the report

4) Right click the report and click Report Properties

5 report menu

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

6 report properties

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 chart menu

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

8 series properties

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.

9 random background color

 

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.

Avi Niv

Avi Niv is a senior consultant at Madeira. He is an expert in SQL Server administration and development. He has over 8 years of experience with SQL Server as well as with system infrastructures and with development projects.

Comments

Leave a comment on the original post [www.madeiradata.com, opens in a new window]

Loading comments...