Reporting Services provides the ability to add references to embededed VB.NET code from within the report. You can use this feature to create reusable functions that are called more than once in a report. This post walks you though the steps needed to add embedded code to your reports.
Note: Custom code can include new custom constants, variables, functions, or subroutines. You can include read-only references to built-in collections such as the Parameters collection. However, you cannot pass sets of report data values to custom functions; specifically, custom aggregates are not supported.
The article assumes you have the following:
- Experience in designing reports in Reporting Services
- Both SQL Server and Reporting Services installed
- Business Intelligence Development Studio (BIDS)
Adding Embedded Code
1) Open and existing report project from BIDS.
2) From the Solution Explorer, double click on an existing report, or simply create a new one.
3) Select Design tab, and click your mouse anywhere in the design pane.
4) From the Toolbox pane, drag and drop a TextBox object to the report designer.
5) From the top menu bar select Report >> Report Properties
Note: if you don't see the Report menu item, try clicking your mouse anywhere in the design view; you should then see the menu item.
6) The Report Properties window opens. From within the left pane, select Code.
7) Enter the following VB.NET code in the Custom Code field.
Public Function Sec(ByVal angle As Double) As Double
' Calculate the secant of angle, in radians.
Return 1.0 / Math.Cos(angle)
End Function
Public Function Sinh(ByVal angle As Double) As Double
' Calculate hyperbolic sine of an angle, in radians.
Return (Math.Exp(angle) - Math.Exp(-angle)) / 2.0
End Function
Public Function GetPi() As Double
' Calculate the value of pi.
Return 4.0 * Math.Atan(1.0)
End Function
8) Click OK to save and close the Report Properties window.
9) Right click on the Text Box you added previously and select Expression...
10) Enter the following code in the text box:
=Code.GetPi()
11) Click OK to save and close the Expression window.
12) Click the Preview tab to run the report. You should see a similar result.
That is it! Try calling the remaining functions to test your code.
Cheers!
function reference: http://msdn.microsoft.com/en-us/library/thc0a116(VS.80).aspx