Reporting Services 2008 is a very powerful tool for creating reports. It contains many different tools and even allows you to place complex expressions on reports in every field. Even with all of these tools there are some instances where you need more functionality. This is where custom coding comes into play. You can write your own custom VB code on a report and use it anywhere on the report.

Before we start writing custom code let’s talk about some of the limitation of the custom code option in SSRS. The custom code allows you to reference most of the .NET framework. You are limited to working on the report environment though. You cannot use the custom code to write files and delete directories. This is done by design for security. You can reference items like System.Drawing and Microsoft.Office.Tools.Common. These tools allow you to extend the abilities of Reporting Services 2008 beyond the limitation of expressions.

The expression language built in to reporting services 2008 allow for a lot of customization of your reports. Custom code not only allows you to extend the reporting ability, it also makes it easy to reuse code.

We will now walk through building a report in Reporting Services 2008. In this example we will use the adventure works data base which is freely available on codeplex. This walkthrough will assume you already have some knowledge of reporting services and understand how to create data sources and datasets.

1. Create a blank report (do not use the report wizard).

2. Create a data source connecting to AdventureWorks2008.

3. Create a dataset with the following query.

SELECT Name, ProductID, Color, ListPrice, StandardCost

FROM Production.Product

WHERE (ListPrice > 0) AND (Color IS NOT NULL) and StandardCost > 0

ORDER BY StandardCost,ListPrice

4. Drag in a table and drop each column from the query on the table in the order of the query.

5. Set the format property of Standard Cost and the List Price to “C” (Currency).

6. Set the background color of the header row to a light blue color.

7. Set the header row to bold font.

8. Preview the report. It should look similar to the figure below.

A very common request is to format the currency or other measures on a report to be red if they are under or over a certain value. This can be done with expressions and we will do that first. We are going to format the currencies to red for a certain value, then a darker red for the next set of values, then black for everything else.

9. Go back to design view

10. Set the color property of the list price column to:

=iif(Fields!ListPrice.Value <20,”Red”,

iif(Fields!ListPrice.Value >=20 and Fields!ListPrice.Value < 30,”Maroon”,

“Black”))

11. Set the color property of the list price column to:

=iif(Fields!StandardCost.Value <20,”Red”,

iif(Fields!StandardCost.Value >=20 and Fields!StandardCost.Value < 30,”Maroon”,

“Black”))

12. Preview the report. It should look like the figure below.

Notice the three different colors in the list price and the standard cost columns. The “nested if” statements allow you to set the color to multiple colors. The red indicate the lowest numbers, the darker red indicate the next level and the rest of the values are black. This works great on a financial report in showing which values are lower and higher at a glance.

The downfall of this approach is the fact that we have to write duplicate code. In the future when management decides to change the value you will have to update two different expressions on this report. We can write all of this code in the report custom code. We can use the custom code anywhere on the report also. This gives us just one place the update the report.

13. Return to design view

14. On the text tool bar across the top click on Report>Report Properties >Code

15. Type in the following code:

Function GetColor(ByVal amt As integer)

If amt < 20 then

Return “Red”

ElseIf amt >= 20 and amt < 30 then

Return “Maroon”

Else

Return “Black”

End If

End Function

16. Change the expression on the color property of the list price column to:

=code.getColor(Fields!ListPrice.Value)

17. Change the expression on the color property of the standard cost column to:

=code.getColor(Fields!StandardCost.Value)

18. Preview the report. It should not have changed from the last figure.

Notice the custom code is in VB. This is a function you create that is now called using the code function in the expression editor. You type the word code followed by a period and the name of the function. You have one value being passed into the function. It is the value of list price or standard cost. Now if the value of one of the levels change you can update the value in the custom code and the both columns will be updated. Now the report can be updated very easily. This is a small example. Imagine a report with dozens of columns that need to be updated. This method can significantly reduce maintenance time in the future.