mssqltips logo

SQL Server Reporting Services Embedding .NET Code for Report Formatting and Error Handling

By:   |   Updated: 2014-04-07   |   Comments (6)   |   Related: > Reporting Services Formatting

Problem

I want to embed code into my SQL Server Reporting Servers (SSRS) to allow special formatting for report output. How do I implement and deploy this code and what functionality can use this embedded code?

Solution

SQL Server Reporting Servers (SSRS) fully supports embedding code into a SSRS report. By embedding code into your SSRS report, you are able to take advantage of several benefits including:

  1. code reuse
  2. ease of code change
  3. setting of a constant
  4. and use of multiple methods.

Of course, as with any functionality some caveats exist which include:

  1. Microsoft Visual Basic must be used for the coding (that may not be a terrible disadvantage except for the hard core C++ programmers )
  2. you must include references to any namespaces used within the code except the System.Convert and System.Math namespaces,
  3. if you pass a parameter collection to the code, the collection is read only and cannot be interrogated using a foreach construct.

Using Code Reference in SSRS for Formatting

The process of adding code to a report will be fully described below; we will use the AdventureWorks 2012 database as the basis for the SSRS report; the database is available on Codeplex. Once you download and install the database, you can also download a set of sample SSRS reports which use the AdventureWorks database as their data source. These sample reports are available for download. These reports provide a good basis for implementing the embedded code functionality.

For this tip, we will use the sample Sales Order Detail report which displays various details about a group of sales orders including unit price as shown below.

Sales Order Detail Report

Let's say that the report user would like any unit cost on the report over $150 to be highlighted as maybe those products have the highest profit margins. Also, maybe a similar highlight is needed for the line total column, the column farthest on the right in the above example. Of course we could go to the backgroundcolor property box and create an IIF statement to toggle the colors; however a more streamlined alternative would be to add some custom code to toggle the backgroundcolor. An example of such code is displayed below.

The function starts with 3 comment lines to describe what the VB code is being used for. Next we define the function name and declare any variables which will be passed to the function; in this example, an InputValue data point and a Thresholdvalue data point will be passed to the function. Next, a simple IF statement compares the InputValue to the Thresholdvalue, and if the InputValue is greater than the ThresholdValue, then "Yellow" is returned by the function, else "Nothing" is returned.  Although, this process may seem a bit more complicated than using an "IIF" statement directly in the backgroundcolor textbox properties box, we will display its power after a few more details as described below.

'*************************************************************************
' Set Background color based on input value and threshold
'*************************************************************************
Function BGColor(ByVal InputValue AS double, _
ByVal ThresholdValue As double) As String

If InputValue > ThresholdValue Then
Return "Yellow"
Else
Return Nothing
End If

End Function


The code snippet noted above must be placed in the code property of the report itself. To access report properties, as noted below, first the report must be in design mode in SQL Server Data Tools (SSDT), then click on Report from the Tool Bar Menu and then Report Properties. Next, click on the Code option and the "Write custom code for this report" window opens.

Report Properties

Code Sheet

The previously noted code is then input into the custom code box, as displayed below.

BG Color code

The last step to this process is to implement this code into the backgroundcolor property field for the two fields which require highlighting, unit price and line total. In order to call our custom code, we must first reference the "Code" object and then the function name, and finally any variables / parameters which are being passed to the code. Also, note that the code section, just like any other Visual Basic document can have many functions embedded in the report. For this example, we would input, =Code.BGColor(Fields!UnitPrice.Value,150), into the BackgroundColor property of the unit price field. Similarly, =Code.BGColor(Fields!LineTotal.Value,150) is used in the line total field. Both of these are illustrated below.

Unit Price BG Color

Line Total BG color

Now when we run the report, as illustrated below, the unit price and the line total cells are highlighted yellow when the value is greater than $150. 

Report Result 1

That's all nice and you can hopefully see some of the benefit of using code functionality, but what happens when the report consumer comes back and requests that the highlight color be green? Also, imagine that the highlight has actually been added to not just 2 cells, but maybe 20. In our example, using the code functionality now only requires us to change the color in just one place in the code snippet and not each of the backgroundcolor property boxes for each cell. Changing the color in just one place is definitely better than 20 places.

Finally, we could easily make a second change and also highlight any of the data points not meeting the threshold in Red, again by making just one small adjustment.

'*************************************************************************
' Set Background color based on input value and threshold
'*************************************************************************
Function BGColor(ByVal InputValue AS double, _
ByVal ThresholdValue As double) As String

If InputValue > ThresholdValue Then
Return "Green"
Else
Return "Red"
End If

End Function


Now look at the report results displayed below. That change was very simple!

Report Results after color change

Using Code Reference in SSRS for Divide By Zero

Certainly, you could follow similar logic for font size, weight, and color and many other formatting options. Even so, the embedded code function can do more than just formatting. One common use for me is to alleviate divide by zero errors which commonly display in report server as illustrated below in the Error Column cell. This column divides the Unit Price by the Discount; as you can see the discount field is zero which in turn results in "#Error" being displayed. Most report consumers hate seeing the word error in their reports, so we can use embedded code to more "gracefully" display the results.

Divide By Zero Error

The CalcDivision function, included below, accepts three variables for inputs: the Numerator, the Denominator, and the DivideByZeroDefault. Hopefully, the first two variables are self-explanatory, whereas the third argument is what will be returned if the denominator is 0. This last argument allows the report designer to customize what will be displayed when a divide by zero issue occurs. Thus, if the denominator is 0, we display the default value; otherwise the division calculation is completed.

'*************************************************************************
' Handle divide by zero without an error
' Author: Scott Murray
'*************************************************************************
Public Function CalcDivision(ByVal Numerator As Object, _
ByVal Denominator As object, ByVal DivideByZeroDefault As Object) As Object
  If Denominator <> 0 Then
    Return Numerator/Denominator
  Else
    Return DivideByZeroDefault
  End If
End Function

Thus, our Report Properties custom code now includes a second function for the divide by zero handling as seen below.

Divide by Zero Error

To implement this function we would add the following expression to the appropriate cell =Code.CalcDivision(Fields!UnitPrice.Value,Fields!UnitPriceDiscount.Value,Nothing).

Divide by Zero Expression

Now when we preview the report, shown below, we get a blank cell which is more graceful than displaying "#Error".

Graceful error

Alternately, we could adjust the last parameter we are passing to the function to display a well spoken error; the new expression could be: =Code.CalcDivision(Fields!UnitPrice.Value,Fields!UnitPriceDiscount.Value,"Div by 0"). Now the report displays "Div by 0" if a zero denominator is encountered. 

graceful error message 2

Conclusion

SSRS allows us to add embedded customized code to a report in order to perform functions that may not be available or easily implemented in SSRS. The code which is embedded must be Visual Basic and allows for code reuse which in turn may simplify report updates. Furthermore the code function can accept and return various variables / strings, however when passing a parameter collection it is read only. In addition to embedded code, custom assemblies and group variables can be added to a report. I am hoping to cover those two items in future tips.

Next Steps


Last Updated: 2014-04-07


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, April 23, 2015 - 10:04:58 AM - Gisela Paredes Back To Top

Thanks, very nice article for special data format
idem conditional formatting in  Excel


Thursday, September 18, 2014 - 10:30:07 AM - Scott Back To Top

You can not go from BIDS 2012 to SSRS 2008.


Thursday, September 18, 2014 - 9:12:05 AM - Tushar Back To Top

Good article,

I have used IF operator as short circuit, like IF(expression, arg1, arg2) and this works in SSRS report when I do preview the report in visual studio 2012 however when I try to upload it on Report Server 2008 it gives me below error 

 

"There is an error on line 109 of custom code: [BC30201] Expression expected. (rsCompilerErrorInCode)"

 

Thanks,

Tushar.


Wednesday, August 13, 2014 - 8:02:19 AM - Ronald Back To Top

Thanks, I was looking for a way to implement a default of NOTHING, this was the solution.


Monday, April 14, 2014 - 8:54:36 AM - scott murray Back To Top

Juerg... you are absolultely correct about the bug when attempting to copy a cell.  It has been around for way too long and needs to be fixed.


Monday, April 14, 2014 - 1:50:48 AM - Juerg Back To Top

Be aware that with newer versions of Report Builder cells containing code refernces can not be copied. You can copy them by replacing the code references temporarily (e.g. Code..BGColor( ) and fix the reference afterwards - but that is a process you will learn to hate.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools