SSRS Embedding .NET Code for Report Formatting and Error Handling

By: Scott Murray | 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:

code reuse
ease of code change
setting of a constant
and use of multiple methods.

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

Microsoft Visual Basic must be used for the coding (that may not be a terrible disadvantage except for the hard core C++ programmers )
you must include references to any namespaces used within the code except the System.Convert and System.Math namespaces,
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.

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.

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

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.

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.

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!

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.

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.

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

Now when we preview the report, shown below, we get a blank cell which is more graceful than displaying "#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.

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

SSRS Customer Assemblies - http://technet.microsoft.com/en-us/library/ms155974%28v=sql.100%29.aspx
Check out the SQL Server Business Intelligence tips

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

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