Sometimes, it's just easier to use your Visual Basic skills to incorporate complex logic in a report expression.
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 "
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
' Use View with WHERE clause
Return strSELECTView & strWHERE & strORDERBY
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
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.
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.
Click here for a larger image.
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:
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.
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))"
Listing 3: Code to Return selected Style values.
- 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.
Click here for a larger image.
Figure 6: Setting the Code in the Report Properties.
- 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.
Click here for a larger image.
Figure 7: Configuring the Report Parameters.
Reviewing the Report Layout
As I said, this is not a "how to lay out a report" tutorial. There are quite a few of those in the book, but I thought you might want to see how this example report is configured. I expect the project including the RDL source code is included with the article. The layout is shown in Figure 8.
Basically, the report illustrates a number of uses for expressions—any of which can call Visual Basic code-behind to be executed by the RDL interpreter. The report itself performs some runtime calculations. For example, the Profit is computed by subtracting the Price from the Cost and the Profit Percent is computed by dividing the Cost by profit. Notice that the dollar amounts are not formatted as Currency (c) but the Language property of the cells is set to English (United States) to ensure that the report always renders the amounts as US Dollars. Again, the nuances of this issue and other internationalization issues are discussed in the Reporting Services book.
Click here for a larger image.
Figure 8: The report layout.
Setting Property Expressions
So far, you've used the code-behind logic to return a block of customized SQL to be passed as the CommandText in a query. Well, it's also possible to create code-behind routines to set properties as well. For example, in this report you want to set the background color of the "Profit %" cell. Each profit level is to be shown in a different color varying from red to shades of green. This is accomplished by setting the BackgroundColor property of the Table element's cell to an expression that invokes the custom code-behind class. This code is shown in Figure 6 and is incorporated into the report RDL in the same way.
Testing the Report
It's time to test the report. This is really simple with the Business Intelligence tools; all you need to do is click the "Preview" tab that invokes the Report Processor RDL interpreter. This engine opens the connection and runs each of the queries as needed. It then displays a set of dialogs to capture any parameters and subsequently renders the report with these parameter settings as shown in Figure 9. In the process, several blocks of Visual Basic code are interpreted and executed by the Report Processor. The Background color property of the Table element detail row is driven by another code-behind Visual Basic expression that shows which units are more (or less) profitable—in living color.
Ah, no. I have not deployed this report to the SQL Server Reporting Services server. As when using the ReportViewer control, this is entirely unnecessary during development. The local (client-side) development report processor emulates (approximates) what the HTML generating server-side report processor will do once the report is executed post deployment.
Click here for a larger image.
Figure 9: The completed report as rendered in Visual Studio Business Intelligence tools.
So, as you can see, the RDL report definition files generated by the Business Intelligence tools (or by any other means) can contain Visual Basic code segments to execute simple to highly complex code that's interpreted at runtime to suit virtually any purpose. A disadvantage of this approach is that if the code contains business logic that changes from time to time (like the coloring of the profit levels), you'll have to revisit the project to recode the logic, replace it in the RDL, recompile and test the report, and redeploy the change. A better technique would be to incorporate business logic that's external to the RDL and called by the Report Processor as needed. In another article, I'll show you how to build a .NET DLL to do just that. Using the DLL approach the code being accessed can be changed independently of the report. However, this requires a bit more work and a few tricks as well.
Download the Files
You can download the code ZIP file and a sample PDF file here.
About the Author
William (Bill) Vaughn is an industry-recognized author, mentor, and subject-matter expert on Visual Studio, SQL Server, Reporting Services, and data access interfaces. He's worked in the computer industry for over thirty-five years—working with mainframe, minicomputer, and personal computer systems as a developer, manager, architect, trainer, marketer, support specialist, writer, and publisher. In 2000, after 14 years at Microsoft, Bill stepped away to work on his books, mentoring, and independent training seminars. He's written seven editions of the Hitchhiker's Guide to Visual Basic and SQL Server, and three editions of ADO.NET and ADO Examples and Best Practices for Visual Basic (and C#) Programmers. He and Peter Blackburn also wrote the critically acclaimed Hitchhiker's Guide to SQL Server 2000 Reporting Services.
Bill is a top-rated speaker and frequents conferences all over the world, including TechEd, Visual Studio/SQL Connections, DevTeach, and many others. He's also written a wealth of articles for magazines such as MSDN, SQL Server Magazine, Visual Basic Programmer's Journal, .NET Magazine, and many others as well as a regular editorial for Processor magazine. Bill spends considerable time answering questions on the public newsgroups and speaking at INETA user group meetings all over the country and at other speaking venues all over the world. He's available for consulting, mentoring, or custom training. See www.betav.com or www.betav.com/blog/billva for his current schedule and course catalog.