Embedded Code In Reporting Services

When I first installed Reporting Services I had read something about embedded code, and my head had filled with visions of grandeur. I pictured hooking events for customization and deriving new classes to override virtual functions. I expected an ASP.NET experience complete with code behind and a reporting engine under the hood. So before we continue, let’s be clear – you won’t be doing any of the above with embedded code in Reporting Services for SQL Server 2000.

There are, however, lots of extensibility points in Reporting Services. You can write rendering, delivery, security, and data processing extensions to meet special needs, such as forms authentication – but the focus of this article will be on embedded code. The ability to embed code into a report will let you accomplish many tasks once you understand the purpose of the feature and the limitations you are working under. Let’s look at some of the details of code in Reporting Services.

Why Embed Code?

Reporting Services gives you a number of functions to use in a report. For instance, there are functions to sum and average numbers, and functions to count values. Visual Basic functions such as InStr and DateAdd are present, as are Iif and Switch. However, the product designers could not predict your every need, and embedded code allows you to implement functions with custom code.

Custom code comes in two forms. You can write a custom assembly and reference the assembly from a report. This is a good approach to use when you need to share code across a number of reports, or to decouple calculations and rules from a report into a class library and possibly share the class library with other applications. A custom assembly is also a good choice when you need additional security permissions, which we will revisit later.

The second form of custom code is code embedded directly into a report. We will address some of the difficulties in this approach, but first, a code example. Let’s say we want to sprinkle some color into a report. Depending on the value of a field, we will want to adjust the color from a plain black to a bright red. With a report in the designer we will go to the Report menu and select Report Properties. In the dialog box (shown below), we can enter code into the text area.

To use the code, we can select a textbox (or any control with a Color property that we want to set programmatically) and open the properties toolbox window. In this case, we will set the Font Color property for a Textbox control in a report table. W can enter the following expression, which assumes we have a query returning a Quantity column:

=Code.SetColor(Fields!Quantity.Value)

One of the nice features of Reporting Services is the ability to use an expression instead of a hard value on any property. When the report renders, this expression will change the colors of the font depending on the value in each row. Again, you can use expressions on any property to control layout, colors, and styles.

So where does the code live? Inside the RDL file. Right click the RDL file in Solution Explorer and take a look at the XML markup. Inside you'll see:

<Report >
...
  <Code>Public Shared Function SetColor(ByVal Value As Integer) As String

    SetColor = "Red"

    If Value &lt; 10 Then
        SetColor = "Black"

    ElseIf Value &lt; 15 Then
        SetColor = "Maroon"

    ElseIf Value &lt; 20 Then
        SetColor = "Orange"
    End If

  End Function 
  </Code>
. . .
</Report>

Take A Step Back

Now we have seen how and where we can use custom code, let’s talk about some restrictions (remember this article is written for Reporting Services 2000, future versions may change).

First, you must write the embedded code in the Visual Basic .NET language (although a custom assembly could use any .NET language). There is no intellisense in the code window, and you will not learn of any compilation errors until you do a report preview. You must write the code inside a public, shared function. You can embed more than a single function, but I would discourage anyone from overcrowding a report with code. A custom assembly, with all of the proper support from Visual Studio, should prove more manageable in the end.

You need to pass values from the report into your function to perform calculations. In other words, you cannot reach out from custom code and grab field values, nor can you obtain references to Dataset objects. There has been some discussion about future versions exposing the Dataset objects, which would allow the building of custom aggregators using custom code.

While shared methods are recommended, shared fields are definitely not. For instance, the following code will have problems.

Public Shared Function AddToCount(ByVal Value As Integer) As String
    Count = Count + value
End Function

Shared Count As Integer = 0

First, we have no control over the lifetime of the variable Count. Secondly, if multiple users are executing the report with this code at the same time, both reports will be changing the same Count field (that is why it is a shared field). You don’t want to debug these sorts of interactions – stick to shared functions using only local variables (variables passed ByVal or declared in the function body).

Finally, you can use types from the Microsoft.VisualBasic, System.Convert, and System.Math namespaces without namespace qualifiers. You can also use types from other namespaces, but you’ll need to specify the full namespace, for example:

Dim a as system.Collections.ArrayList

However, there are some types you will not be able to use for security reasons.

Embedded Code and Security

Code Access Security is in effect in Reporting Services. Since end-users can upload reports with embedded code, it makes perfect sense to protect the server and network from malicious report code. Embedded code executes with the built-in Execute permission by default, which does not allow for much beyond string and math operations on the Reporting Services object model. You would not, for instance, be able to use file or network IO.

If you need additional permissions for your code, you can change the default permission for embedded code but it is not a step to take without very careful thought. Modifying the permissions for embedded code will allow the code in any report to execute with the same permissions. This is another scenario where I would strongly advise using a custom assembly. For more information on code access security in Reporting Services, take a look at Understanding Code Access Security In Reporting Services.

Conclusion

Reporting Services is a platform to create, manage, and deliver reports. Having the ability to embed custom code inside of these reports allows flexibility in what you can achieve. Hopefully, future versions will allow access to more of the internal events and objects of the engine.

Additional Resources

Read more Reporting Services articles at OdeToCode.

-- by K. Scott Allen