|
Adding RDL-Resident Code to Reporting Services Reports
By William R. Vaughn
Go to page: 1 2 3 Next
When using the ReportViewer control in Visual Studio (2005 or 2008), you have ample opportunities to craft logic-driven SQL to build the rowset passed to the report or even munge the data values returned. Often, all you need to do is generate a string that's based on input parameters and pass these into a Report Definition Language (RDL) property. For example, in the following subroutine (see Listing 1), I generate an SQL string that can be passed to the CommandText based on a couple of input parameters. Ah, no. This code does not in of itself protect against SQL injection, but if you make sure the user does not provide the parameter values directly, your report will be safe to deploy.
Shared Function GetCustomerSQL(ByVal strInParm As String, _
ByVal strDebug As String, intMaxRows as Integer) As String
Dim strSELECTView As String ' Set to production report
If strDebug = "" Then
strSELECTView = "SELECT TOP " & CStr(intMaxRows) & _
" * FROM AddressesByLocation "
Else ' Production
strSELECTView = "SELECT * FROM AddressesByLocation "
End If
Dim strWHERE As String = "WHERE (CountryRegionCode = '" _
& strInParm & "') "
Dim strORDERBY As String = " ORDER BY CountryRegionCode," _
& " City, PostalCode"
If UCase(strInParm) = "ALL" Then
' Use View without WHERE clause
Return strSELECTView & strORDERBY
Else
' Use View with WHERE clause
Return strSELECTView & strWHERE & strORDERBY
End If
End Function
Listing 1: Generate an SQL string based on input parameters
This code can be used in lieu of a dataset reference in the RDL report definition by invoking it using the RDL Code= operator. That's what I'm going to discuss in this article—the mechanics of setting up the report and how to imbed a Visual Basic Function like this in the RDL.
Just to be clear, the purpose of the code shown in Listing 1 is to permit the developer to limit the number of rows during the "debug" phase of the report. Actually, there are other ways to solve this problem but the purpose of this article is to illustrate code-behind—and not in a Visual Basic (or C#) application subroutine but in a Reporting Services RDL report. That is, the actual code to be executed by the Report Processor at runtime. Yes, the code is re-interpreted each time the report is rendered.
RDL-based code-behind logic can be used for virtually any purpose and can be implemented in a variety of ways. In the following example, I'm going to illustrate how to place this code directly in the RDL (or RDLc) as a Report Property. In this case, the code returns a SQL string that's executed as a report data set. Yes, this code must be Visual Basic, not C#. And yes, it's possible to create compiled DLLs that contain other .NET executables in other languages, but getting them to work is a bit more complex. I'll leave that discussion for another article.
Preparing the Datasets
Now, it's time to get started. Don't even think about using Visual Studio 2008 for this project—not until SP1 as the Business Intelligence (BI) tools are not ready.
- Create a new BI Report Project in VS2005. No, don't choose the Report Project Wizard—not this time.
- Add a new Shared Data source by right-clicking the tab in the solution explorer. Fill in the Connection String or use the wizard to create one. In this case, I'll connect to the AdventureWorks example database that can be installed from the SQL Server 2005 setup disk.
Figure 1: Building a Connection String
- Add a report to the project. Sure, if you have a custom template, this is when you can add it to the project. I often make this recommendation to customers who have a specific report style in mind. You can launch the Add New Report wizard (again) at this point if you're unsure about how to build a report from scratch. Most of the time, you won't—you'll steal (er, "leverage") an existing report as a starting point.
Figure 2: Adding a report to the project.
- Because the purpose of this article is not authoring reports, I'm going to assume you can set up a basic report against the AdventureWorks Products table. For the initial query, you're going to create a parameter query to limit rows to products that returns selected columns based on a chosen Product Style and Class columns. Boring, but easy to set up and illustrate a few points. Listing 2 shows the SQL generated by the Query Builder as shown in Figure 3.
Note: It's okay to cheat when creating the actual query—I suggest using SQL Server Management Studio's query designer to code and debug the query. That's because Visual Studio (even 2008) still does not return informative SQL syntax exception information. I just pasted the result into the Query Designer dialog when I was satisfied it was returning the right rowset.
SELECT Name, Color, StandardCost, ListPrice, Style, Class
FROM Production.Product
WHERE (Style = @prmStyleWanted) AND _
(Class IN (@prmClassWanted))
Listing 2: SQL generated by the Query Builder.
Click here for a larger image.
Figure 3: Building the Dataset query with the Graphical Query Designer.
Tip: Click the Refresh button to populate the Dataset explorer window with the columns returned by the query.
Go to page: 1 2 3 Next
|
IE 7
Firefox 2.0
