As i use SSRS more, i find the need to clean things up and make things easier to troubleshoot and work with. With that Custom code in SSRS is a real winner when it comes to how you want to format your Reports. and build up a Framework for the design standards.

In this example im going to change the background colour of my data cells to show the longer running CheckDBs, based off my earlier post:

https://sqlnotesfromtheunderground.wordpress.com/2013/11/06/knowing-your-checkdb-duration-times-using-ola-hallengren-maintenance-scripts/

Old me would format each cell with an expression like:

=Switch(Fields!ID1.Value > 0 and Fields!ID1.Value <= 25, "White" ,
Fields!ID1.Value >= 26 and Fields!ID1.Value <= 50, "YellowGreen",
Fields!ID1.Value >= 51 and Fields!ID1.Value <= 100, "DarkOliveGreen"
)

 

Now if i want to change the colours or alter the values that force a colour change. I need to update each cell.. Not so much fun, and from experiance pron to error!

 

with custom code we can create a .NET function and just point each cell to this function. Then any changes we make in the function roll down to every cell.. 1 change instead of 7!  We are on to a quick and easy winner!

Open a report and navigate on the top menu to Report -> Report Properties (opens a new Window)  -> Code

 

CustomCode1

 

Cut and passed the following code:

Function BkColour (Value As Decimal) As String
IF (Value > 1000) THEN Return "DarkOliveGreen"
IF (Value > 300) THEN Return "YellowGreen"
Return "White"
End Function

 

Click OK and the window closes. Now we need to edit the cells that we want to use the function!

Click on a cell in your report and in the properties bar click on BackGroundColor

CustomCode2

Click the Expression button and enter the code:

 

=Code.BkColour(Fields!ID7.Value)

 

This tells SSRS to run our custom code functon BkColour against the value of D7.Value  Repeat the process for every cell you want to be effected by the function (Remember to change the value that the function is evaluting against to a correct value for the cell!)

When you run the report you should will now have lovely code running through it!

 

DynamicDataSourceResults1

 

 

 

 

Advertisements