Adding RDL-Resident Code to Reporting Services Reports
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.
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
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.
Page 3 of 3