Home > Reporting Services > Custom Code in Reports – Part 1

Custom Code in Reports – Part 1


One of the nicer , although I suppose under-utilised, features of Reporting Services is the ability to enhance your reports with a bit of custom code. Why do this ? Well you could implement some sort of difficult business logic using .NET code and your report fields, parameters and filters could interact with it to take advantage of that functionality. Now there are 2 ways to implement custom code :

  1. Use embedded code in your report. This is simple to implement and the code is deployed in your RDL.

  2. Create a custom assembly which is called by the report. A bit more work I suppose, but this is very powerful.

Lets start off with the embedded code. A simple example – I have here a sales report off adventure works. This uses the same query that I’ve shown previously on this blog and is a matrix with a product category grouping on rows and the year on the columns.


Right , so lets say that we would like to calculate the mean and standard deviation of these figures and stick them at the end of each row. One of the nice features of the tablix is that you can click on the cell of a column and select Insert Column – Outside Group – Right. That will create a parallel column at the end of the tablix .

To work out the mean value , the formula is simply ∑x / n ( The sum of x divided by the number of values ). A simple function that could return that for us would be :

Public Function Mean (ByVal Sum as Decimal, ByVal N as Integer) as Decimal
    Return Sum / N
End Function

To implement the function in your report , right click anywhere outside the design area , select Report Properties and go to the Code tab. Paste the above function into the code area – you can have more than 1 function here.

Now to call the function from a cell we use the [Code] command, so in this example it would be :

=Code.Mean(Sum(Fields!SalesAmount.Value),CountDistinct(Fields!OrderYear.Value,"DataSet1"))

As you can see, I’m supplying the Sum of my sales value and a Count Distinct of the Year values ( across the entire dataset ) as the 2 arguments into the function. This is simple enough and will result in the following output :


Now I know what you’re thinking , you’re thinking we didn’t need a function for that simple calculation. Well the real value in this comes into play for something a bit more complex, something that you don’t want to write from scratch in that little cell. And what about if you need the same calculation for many cells in the same report ? You create one function and call it from many places in the report. It’s quite useful, although just be careful about writing too many of these functions in your reports – maintanence can quickly become a nightmare, especially if these functions are hidden in there and not documented. In part 2 , we’ll do something alot more interesting , that is , call a custom component from a report.

Advertisements
Categories: Reporting Services
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: