Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Integrating .NET Code and SQL Server Reporting Services : Page 2

No product can satisfy all developers out of the box. SQL Server Reporting Services is no exception. But you can extend the SSRS expression system with .NET code.


advertisement
Embedding VB.NET Code
SSRS provides the capability of embedding Visual Basic .NET code directly within report layouts. Embed code into reports by selecting Report | Report Properties… from the Visual Studio shell, and then select the Code tab in the provided dialog box. Enter the following code into the "Custom Code" field and click OK:

Function CoDeDemo(ByVal cField As String) As String Return cField.ToUpper() End Function

 
Figure 3: XML source code showing <Code> section.
You can call your new function from your report by using the Code function provided via the SSRS expression syntax. To use your new function, switch to the Layout tab in the Report Designer. In the Company Name field, change the Value property from =Fields!CompanyName.Value to =Code.CoDeDemo(Fields!CompanyName.Value). Now select the Preview tab. The output for the CompanyName value displays in upper case.

If you don't like editing code using small dialog boxes, you have another mechanism for editing embedded report code. In the Solution Explorer, right-click the report and select "View Code" from the provided pop-up menu. Search for the <Code> element in the provided XML source code (see Figure 3). You can type or paste code directly into the XML file.

By default the SSRS custom code mechanism is limited to the most basic functions of the .NET Framework. To add more advanced capabilities (data access for instance) you must add references to the desired assemblies. The code below demonstrates accessing data from SQL Server and returning it to the report.

Function GetCustomerOrderCount( _ ByVal CustomerID As String) As Integer Dim oConn As New System.Data.SqlClient.SqlConnection oConn.ConnectionString = "Data Source=(local);" & _ "Initial Catalog=Northwind; IntegratedSecurity=SSPI" oConn.Open() Dim oCmd As New System.Data.SqlClient.SqlCommand oCmd.Connection = oConn oCmd.CommandText = "Select count(*) From Orders " & _ "Where CustomerID = @CustomerID" oCmd.Parameters.AddWithValue("@CustomerID", CustomerID) Dim nRetVal As Integer = oCmd.ExecuteScalar() oConn.Close() Return nRetVal End Function

 
Figure 4: Missing reference error.
After adding the preceding code to the report you can test it by adding a new column to the report. From the Layout tab add a new column and set its Value property to =Code.GetCustomerOrderCount(Fields!CustomerID.Value). When you select the Preview tab you see a compilation error in the Error List tab of the Visual Studio designer (see Figure 4). This signifies that a reference to the assembly containing the System.Data.SqlClient.SqlConnection class is missing. To fix this issue you need to add references to the System.Data and System.XML assemblies.

To add a reference to your report, open the Report Properties dialog box from the Visual Studio Designer and select the References tab. Click the ellipsis button. This opens an Add Reference dialog box. Select the System.Data and the System.Xml assemblies from the provided dialog box. Previewing your report should now show the number of orders for each customer record.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap