Adding RDL-Resident Code to Reporting Services Reports
The Graphical Query Designer is not the default method to create or enter SQL. As a matter of fact, this (all too familiar) graphical designer can handle only the simplest of parameter queries—but often that's all you need for a report. I used the Graphical Query Designer (GQD) instead of the Generic Query Designer to illustrate the first of two ways to pass SQL to the RDL report definition. At this point (as evidenced by the Report Datasets dialog on the left of Figure 3), the RDL has the Dataset SQL and selected fields—that's because I ran the query in the GQD.
The default query designer dialog is basically a blank slate—the Generic Query Designer. It offers little assistance building queries other than letting you select either Text (the default) or Stored Procedure. This permits you to simply enter the name of stored procedure to execute in the query window—something the Graphical Query Designer won't let you do. If you need to run a more complex or multi-part query, you'll be forced to use the Generic Query Designer.
If you click on the designer toggle button (to the left of the exclamation point icon), the query you've built with the Graphical Query Designer (listing 2) appears in the Generic Query Designer dialog, as shown in Figure 4.
Figure 4: SQL query as presented in the Generic Query Designer.
Notice that I didn't include an Order By clause in the SQL. I've found that it's often easier (and faster) to let the Report Processor do the sorting and some of the filtering—or let the user choose another sort order or change the filters without having to re-query. Peter Blackburn and I discuss these performance issues in our book "Hitchhiker's Guide to SQL Server 2000 Reporting Services"—nope, these issues have not changed since then.
At this point, I want to perform this same query, but using code behind. Again, this is a bit contrived, but bear with me because this approach can lead to a far more sophisticated block of generated SQL. The trick here is to create an SQL query that returns the same named columns in the rowset. That way, you can trick the Visual Studio tools and use the same drag-and-drop techniques to build the report layout. You've already done that and Visual Studio has listed the column names in the Report Datasets dialog (as shown in Figure 3). Again, the key is that the columns in the RDL match those being supplied by the Dataset—no matter how it's generated.
See how this is done.
- Go to the Data tab of the Business Intelligence reporting tools (as you are in Figures 3 and 4). Click on the dropdown list and choose "<New Dataset>..."
- In the Query String, enter:
=Code.GetProductsByStyleSQL(Parameters!prmStyleWanted.Value, JOIN (Parameters!prmClassWanted.Value,",") )
This expression tells the report processor interpreting the RDL that it needs to look for a block of code called "GetProductsByStyleSQL" and pass in the report parameters as defined for this report—in this case, the Style value and a comma-delimited string containing all of the Class values wanted. The effect here is that when the Dataset is to be executed, the CommandText generated and sent to the Data Source (SQL Server in this case) is not hard-coded but generated on the fly. Click OK when you've filled in this query.
Figure 5: Configuring the new Dataset
When you return to the Data tab, you'll notice that the exclamation point (run the query) icon is disabled. No, you should not expect Visual Studio to execute the code-behind query and re-populate the Report Dataset columns. You don't want these to change.
Adding the Code-Behind Logic
Okay, now you're ready to add the Visual Basic code to the report RDL—this is the code that builds and returns the SQL to execute the SELECT query. I find it easier to add another project to the Visual Studio solution to create and test the code—I'm not that good.
- Add another project to the solution (File | Add | New Project). For the example, I added a new Visual Basic Class Library project to the solution. Remember, this project must be coded in Visual Basic because the Report Processor only recognizes Visual Basic code when it comes time to render the report. The new class is shown in Listing 3. You'll need to verify the report parameters are in the RDL before testing this.
- Copy the code from the Visual Basic Class to the Clipboard.
- Next, switch to the Business Intelligence Report Designer "Layout Tab." This is where the report elements are positioned and programmed.
- Select the report body. This is an important step. You know you've selected the report when the Report menu item appears. Note that this menu item is not enabled unless you have the Layout tab and the report selected.
- Because you want to access the Report's properties (not any specific element), you need to choose the Visual Studio menus and Report | Properties.
- Navigate to the Code tab and insert the code from the Clipboard (from the class shown in Listing 3). Click OK to close the dialog. At this point, the code is now imbedded in the report's RDL. Sure, you can add as many Functions here as your report needs. They're all imbedded in the RDL. Notice that this report has another code-behind function that decides which color to return based on the profitability of the item. I'll show you where that gets hooked up later.
- Return to the Layout tab and choose Report | Report Parameters. Notice that the prmStyleWanted and prmClassWanted parameters have already been created by Visual Studio and imbedded in the RDL. Remember that the initial SELECT query for Product data expects these query parameters. I've already tuned these parameters to be correctly populated. That is, the prmStyleWanted has its drop-down pick-list generated from a query (dsValidStyles) and the prmClassWanted is configured to accept multiple values but these are hard-coded in the RDL as shown in Figure 7.
Note: Sure, it makes sense to build a test application to invoke this method and verify this code actually works—especially if it's more complex. This will save time in the long run because Reporting Services won't let you step through the code at runtime.
Public Class clsGetProductsByStyleSQL Function GetProductsByStyleSQL(ByVal strStyleWanted _ As String, _ ByVal dsClassWanted As String) As String Dim strSQL As String = "SELECT Name, Color, _ StandardCost, " _ & " ListPrice, Style, Class" _ & " FROM Production.Product " _ & " WHERE (Style = @prmStyleWanted) AND _ (Class IN (@prmClassWanted))" Return strSQL End Function End Class
Listing 3: Code to Return selected Style values.
Figure 6: Setting the Code in the Report Properties.
Figure 7: Configuring the Report Parameters.
Page 2 of 3