Custom Programming to Enhance SSRS Reports

SQL Server Reporting Services (SSRS) is an enterprise-class reporting platform that sits on top of well-established services in the Windows server technologies, including SQL Server, ASP.NET, and the Microsoft .NET Framework. One of the most compelling capabilities in SSRS is the ability to extend report features with custom code and programming extensions. After I discuss the practicality of using custom code and extensions in daily report design, I'll introduce you to some built-in SSRS extensions and the concept of custom assemblies. I'll then provide step-by-step instructions on how to write a custom assembly with Visual Studio and use it to build a dynamic report dataset.

Choosing the Right Tool for the Job

Programmers are accustomed to using tools that support object-oriented design patterns (e.g., object instance management, interfaces, class inheritance) and event-driven programming models to build custom applications and application components. It's important to understand that for most practical purposes, SSRS doesn't support these programming patterns and models—and for good reason. SSRS is a reporting platform and not a programming tool.

When discussing advanced report design techniques on my SQL Server BI blog or other forums, occasionally a hard-core programmer will say something like, "I don't need to use a reporting tool like SSRS to design a report. I just write a custom web app, then build and manage everything myself using .NET programming objects." I respect that view and believe that it's the right approach when you need to have control over very specific UI features, behaviors, and interactions. Using event code and custom controls, you can add some nice adornments that SSRS just doesn't offer. But you need to carefully consider what would happen when a custom report is run with 100 times the anticipated data volume or run by several concurrent users. And what do you do when users want to view the report in a variety of formats? These are the kinds of considerations that SSRS addresses very well without additional design effort from you.

The essential components of a report—data connections, query executions, result sets, data caching, and the like—should be managed using built-in capabilities and not complex programming. It's important to know when you don't need to utilize advanced programming to do things that SSRS does well right out of the box.

Introducing Extensions

SSRS is built on an extensible architecture. I have to admit that when I started using SSRS back in 2003, I was enamored by its extensibility and the ability to add custom programming code to my report solutions. As a .NET programmer, I thought that reports would be a natural extension of my programming efforts. After building several complex reporting solutions and working alongside other professionals to architect large-scale reporting solutions for almost a decade, I've come to realize that the vast majority of SSRS reporting solutions don't require a lot of custom programming. The best application of a custom-coded library is to enhance the native capabilities of the SSRS architecture rather than replacing what it already does very well. Authoring custom extensions isn't trivial and is usually beyond the realm of practical applications for corporate reporting.

Internally, SSRS uses a set of .NET assemblies known as extensions to perform most of its core functions. The Standard, Business Intelligence, and Enterprise editions of SQL Server automatically install some of these extensions. For example, when you export a report to Microsoft Excel, the Excel rendering extension is employed. When you schedule a subscription, the email or file system delivery extension is used instead of the default interactive report delivery extension.

In the Standard, Business Intelligence, and Enterprise editions of SQL Server, SSRS supports extending its behavior through:

• Data processing extensions. These extensions provide access to different types of data using a consistent programming model. The provided data processing extensions include extensions for SQL Server, SQL Server Analysis Services (SSAS), SQL Server Parallel Data Warehouse, SQL Azure, Microsoft SharePoint lists, report models, ODBC, OLE DB, Hyperion Essbase, Oracle, SAP NetWeaver Business Intelligence, Teradata, and XML. Microsoft also offers data processing extensions for SAP Relational DB and DB2.
• Delivery extensions. These extensions interface with some kind of a delivery mechanism to send reports to users. The provided delivery extensions include extensions for interactive reports, email, the file system, and a "null" provider so that reports can be rendered without saving the output.
• Rendering extensions. These extensions control the type of document or media that's created when a report runs. Out of the box, Microsoft provides several rendering extensions that cover the most popular and useful document types, including HTML, web archive, PDF, Excel, Microsoft Word, images, XML, and comma-separated value (CSV).
• Security extensions. These extensions provide authentication and authorize users to run reports on a report server. The provided extensions support Windows Integrated Security and ASP.NET forms-based authentication. You can implement your own role-based security model with any authentication criteria.
• Report processing extensions. These extensions enable the creation of custom report items managed by the report processing engine. They enable you to extend the Report Definition Language (RDL) standard to include functionality not natively supported by RDL, such as custom MapPoint maps and horizontal lists. You can also extend current report items to provide alternative versions that better fit your needs.
• Report definition customization extensions. These extensions provide a hook into the preprocessing of the report definition. You can plug in custom code that modifies the report definition stream before it gets processed. This is handy if you need to modify the report's layout based on a culture, locale, or user identity that's specified in the report request.

When it comes to creating custom extensions, you can learn from the experience of others. I've worked with companies that use SSRS as an integrated part of their service offering and some of these companies have created their own custom extensions. One company built a custom security extension so that its application could handle user logins and authentication rather than requiring users to log in using Windows. Other companies with complex data and application programming layers in their solutions built custom data processing extensions to use instead of the standard data providers in SSRS. In one instance, a company built a data processing extension to handle complex industry-specific business rules and filtering logic. In each case, it took teams of programmers months to create, debug, test, and put these custom extensions into production.

Introducing Custom Assemblies

An assembly is a reusable code module, typically written in a .NET programming language with Visual Studio and often compiled into a DLL file that contains reusable functions and other program logic. A custom assembly can be used to extend report functionality for many reports on the same server. For example, if you need to process parameter business rules that extend beyond the capabilities of a query language such as T-SQL or MDX, you can use a custom assembly to manage a report dataset query and related report behavior. SQL is a powerful language that can be used to process a fair amount of business logic. Where possible, you should leverage the query language—and especially persistent database objects like stored procedures—to encapsulate complex rules. The best examples of where this technique is the most beneficial are much more complex than the simple example I'm about to provide here. However, the technique I'll show you is quite valuable and, in some cases, necessary.

Demonstrating How to Create and Use a Custom Assembly

In the following demonstration, you'll see how to build a custom assembly that assembles a T-SQL statement based on several parameterized arguments. The assembly will be developed in Visual Studio 2010 in a class library project that can optionally be part of the same solution as the report project. After testing, the assembly will be deployed to trusted folders, where it can be used in the SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS) report designer and on the report server. The project is written in Visual Basic .NET but you could just as easily use C#. You can download a working copy of this report and the sample code project by clicking the Download button.

Creating the View and Class Library Project

The first task is to create the vSalesByTimeAndTerritory view in the AdventureWorksDW2012 database. After making sure that you have permission to create objects in this database, open a SQL Server Management Studio (SSMS) query window and execute the script in Listing 1 to create the view.

Next, you need to create the class library project in Visual Studio by following these steps:

1. From the File menu in Visual Studio, select New and choose Project.
2. In the Add New Project dialog box, select Visual Basic in the Installed Templates pane.
3. In the center pane, select Class Library and name the project Report_Class_Lib.
4. In Solution Explorer, change the name of the default class file to ReportLib.cls.
5. Open ReportLib.cls in the Visual Basic code editor. When working in this editor, I recommend that you use the code completion and debugging features to save yourself some work. For example, when a list of objects is presented, you can use the arrow keys to select the appropriate object and then press Tab to add it to your code. You can also press the Enter key to select an object and add a new line to the code.
6. After the Public Class ReportLib declaration, enter the code in Listing 2. This code creates the SalesQuery function, which builds the T-SQL query based on the conditional logic in the code. Note that the line continuation characters are optional and are provided only so the code fits on the page. If you prefer, you can omit the underscore (_) and the carriage return on each line that ends with an underscore.
7. Check the code for errors and warnings, which will have red or green squiggly lines under keywords.
8. In Solution Explorer, right-click the Report_Class_Lib project and select Build.

Deploying the Class Library

There are at least two important pieces of information that you'll need before you continue. You need to find the folders for the assemblies trusted by Visual Studio and by the SSRS report server. These paths are similar for different product versions, but they'll vary depending on the options you chose during their installation and setup. After you find the paths, copy and paste them in Notepad.

In this example, I'm using the path C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies for the Visual Studio 2012 assemblies folder. For the SSRS report server, I'm using the default report path for SSRS 2012, which is C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin. Note that if you're using SSRS 2012 in SharePoint, the default path is C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting\bin.

After your paths are in Notepad, follow these steps:

1.  Open the project properties. Under the Compile options, choose Build Events.
2.  In the Post-build event command line text box, enter the following commands using the paths you copied in Notepad:

COPY /Y "C:\Users\Administrator\Documents\
Visual Studio 2010\Projects\Report_Class_Lib\bin\Debug\
Report_Class_Lib.dll" "C:\Program Files (x86)\
Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies"

COPY /Y "C:\Users\Administrator\Documents\
Visual Studio 2010\Projects\Report_Class_Lib\bin\Debug\
Report_Class_Lib.dll" "C:\Program Files\
Microsoft SQL Server\MSRS11.MSSQLSERVER\
Reporting Services\ReportServer\bin"

(Although these commands wrap here, you'd enter each command on one line.) If you're running in SharePoint Integrated mode, you must also copy the assembly to the shared folder for the web server extensions. To do so, add this line:

COPY /Y "C:\Users\Administrator\Documents\
Visual Studio 2010\Projects\Report_Class_Lib\bin\Debug\
Report_Class_Lib.dll" "C:\Program Files\Common Files\
Microsoft Shared\Web Server Extensions\14\
WebServices\Reporting\bin"

3.  Set the post-build eventto run upon a successful build, as shown in Figure 1.
4.  Click OK to save the changes.
5.  Build the project again.
6.  Use Windows Explorer to verify that the Report_Class_Lib.dll file has been copied to each location and has a recent modification date.

Creating the Report

At this point, you can create the report. You'll be adding an assembly reference, parameters, and an expression to it. Follow these steps:

 1.  Create a new report named Generated Query.
 2.  Choose Report Properties from the Report menu.
 3.  In the Report Properties dialog box, select the References page.
 4.  In the Add or remove assemblies section, click the Add button.
 5.  Click the ellipsis (...) button on the right side of the new line.
 6.  In the Add Reference dialog box, choose the Browse tab and locate the Report_ClassLib.dll file in the Visual Studio trusted assemblies folder.
 7.  Select the Report_ClassLib.dll file and click OK to add the reference.
 8.  In the Add or remove classes section of the References page, click the Add button.
 9.  In the box under the Class Name column, enter Report_Class_Lib.ReportLib.
10. In the box under the Instance Name column, enter m_ReportLib. This is the alias class name you'll use in the report.
11. After making sure that your References page looks like that in Figure 2, click OK to save the changes.

12. In the Report Data window on the left side of the report designer, right-click the Parameters node to add a new parameter.
13. Use the Report Parameters dialog box to add each parameter. Use the information in Table 1 to set the name, prompt, type, and default value for each one.

Table 1: Report Parameters

Parameter	Prompt	Data Type	Default Value
YearFrom	Year From	Integer	2007
YearTo	Year To	Integer	2008
Country	Country	Text	All
MaxRows	Max Rows	Integer	100
IncludeFreight	Include Freight	Boolean	True
SalesQuery	SalesQuery	Text

14. For the SalesQuery parameter, set the parameter visibility to Hidden on the General page of the Report Parameters dialog box, then go to the Default Value page.
15. Choose Specific values to add a default.
16. Click the Expression button (fx) to the right of the drop-down list box.
17. Enter the following text, then place the cursor between the parentheses:

=Code.m_ReportLib.SalesQuery( )

18. Select Parameters from the Category list and double-click the YearFrom parameter to add this reference to the expression. Type a comma followed by a space after this text.
19. Use the same procedure to add the additional parameters needed to build the following expression (with no carriage returns):

=Code.m_ReportLib.SalesQuery(Parameters!YearFrom.Value
, Parameters!YearTo.Value, Parameters!Country.Value
, Parameters!MaxRows.Value, Parameters!IncludeFreight.Value)

20. Click OK to save the expression.
21. Add a textbox to the report.
22. Right-click the textbox and choose Expression.
23. Use the Expression Builder to set the textbox expression to:

=Parameters!SalesQuery.Value

24. Preview the generated query. It will be displayed in a textbox, as Figure 3 shows.

25. Verify that the generated query is syntactically correct. You should see the following:

• The TOP value should be set by the MaxRows parameter.
• The Freight column should be included in the column list because the IncludeFreight parameter is set to True.
• The YearFrom and YearTo values in the WHERE clause should match the YearFrom and YearTo parameter values, respectively.
• The WHERE clause shouldn't include the SalesTerritoryCountry column because the Country parameter is set to All.

26. On the Preview tab, change the parameter values and rerun the report to see the effect on the generated query.

It's important to note that the parameter values in this example aren't being validated and could be subject to errors or code injection. Safeguards should be added to the solution before it's used in production. I'll address this later.

Using the Generated Query to Create a Report Dataset

Once the report is generating queries as expected, the next task is to copy the SQL script and use it to create the initial dataset query. Follow these steps:

1. Deploy the report to the report server and run the report.
2. With the report running in the browser, highlight the entire contents of the SalesQuery parameter text displayed at the top of the report and copy the SQL script to the clipboard.
3. In the report designer, switch to the Design view.
4. Add a data source to connect to the AdventureWorksDW2012 database. You can use an embedded or shared data source.
5. Add a new embedded dataset to the report and name the dataset ReportData.
6. In the dataset's Properties dialog box, paste the copied SQL script into the Query box.
7. Click OK. Note that the report designer runs the query to generate the dataset field list.
8. Expand the dataset field list in the Report Data pane.
9. Add a table to the report body.
10. Drag the CalendarYear and MonthNumberOfYear fields into the Row Groups pane at the bottom of the designer.
11. Change the MonthNumberOfYear row header cell in the table to display the MonthName field.
12. Drag the SalesTerritoryCountry field into the first open detail cell in the table.
13. Edit the Details group in the Row Groups pane. Add a group using the SalesTerritoryCountry field.
14. Drag and drop the SalesAmt and Freight fields into the detail cells of the table.
15. For debugging purposes, change the SalesQuery parameter to Visible, but make sure you hide it again after you're done debugging. Otherwise, the report won't run correctly.

Functionally, the report is finished, but you have introduced some security concerns. SSRS has a number of built-in security constraints, but it's always best not to push these limits. You don't need to make any changes in this example, but in production you'll want to address the concerns discussed in the "Putting Security First" section to make sure there's no possibility for a SQL injection attack.

Dynamically Hiding the Freight Column

The ability to selectively show or hide different columns is the first step toward making one report take on the job of many different reports. To see how it's done, let's dynamically hide the Freight column. In the report designer, perform these steps:

1.  Click a cell in the table to show the column and row selection handles.
2.  Right-click the gray column header for the Freight column and choose Column Visibility.
3.  In the Column Visibility dialog box, choose the radio button labeled Show or hide based on an expression.
4.  Open the Expression Builder dialog box and enter the following expression:

=(Fields!Freight.IsMissing)

5.  Click the OK buttons to save and close the Expression Builder and Column Visibility dialog boxes.

Modifying the Dataset to Use the Generated Code

Now, you're about to shake things up a little by changing the way you write and modify a query.Follow these steps:

1.  In the Report Data pane, double-click the ReportData dataset.
2.  In the Dataset Properties dialog box, click the Expression button (fx) next to the Query box.
3.  In the Expression Builder dialog box, replace the command text with this reference to the SalesQuery parameter:

=Parameters!SalesQuery.Value

4.  Click the OK button to save and close the Expression Builder and Dataset Properties dialog boxes.
5.  Make sure the SalesQuery parameter is set to Hidden.
6.  Change the Hidden property to hide the textbox showing the SalesQuery parameter value.
7.  Preview the report and experiment with different parameter values. Figure 4 shows a sample report.

Debugging the Query

When developing a solution like this, you'll probably need to debug it. (It's easy to miss a step.) It helps to make a short debug checklist and work through it every time you need to make a design or code change.

If you're not getting the correct results, you should first click the refresh button on the report toolbar to flush the report execution cache and rerun the query. By design, SSRS has a habit of caching prior results. I can't tell you how many times this has challenged my perception of reality and the laws of time, space, and physics!

If you still don't get the correct results or if the parameters aren't working, it most likely means that the dataset isn't using the generated code. To debug, unhide the SalesQuery parameter and show the textbox. When you run the report, check the values to make sure you're getting the correct query syntax. If not, you know that the problem is in your code. If the parameter has correctly generated the query syntax in the textbox but it doesn't change the report output, the only thing left to check is the expression used to map the dataset to the parameter.

Putting Security First

Whenever a query is generated from user input, security should be a concern. SQL injection is a technique used by hackers to gain unauthorized access to data and database server features. Although SSRS is less susceptible to injection attacks because of a number of inherent security features, it's important to be cautious when using parameters exposed to a generated query like this.

You need to make sure that the user account used by the data source has read-only access and access to only the data needed for the report. You also need to be careful with parameters with Text data types. (The numeric, date, and Boolean data types don't generally pose a threat because a script can't be substituted for their values.) When possible, hide any parameters with Text data types. For those parameters that must be visible and that require text values, use a custom function to validate the text values. Allow only certain values and truncate strings to a maximum length.

Using Security Exception Policies

Any custom assembly code that performs actions such as manipulating strings, formatting, or generating a dynamic query like you've done here will run just fine and won't require any special configurations. If that's all you need to do in the assembly, you're good to go.

However, when an assembly contains references to namespaces that require extra permissions to access external resources such as a file system, the assembly will be untrusted by default. Thus, you need to configure the report server to explicitly trust an assembly. It's not hard to do, but you must be meticulous about following directions.

Implementing a security exception policy is a management task that needs to be justified and well-understood before being put into production. Before asking your corporate server administrator to make a security exception policy for your component, you should be able to explain exactly what the component can and can't be used to do and why such an exception is a necessary part of the solution. The MSDN articles "Code Access Security in Reporting Services" and "Using Reporting Services Security Policy Files" explain the criteria in detail and demonstrate how to create the necessary code groups and named permission sets.

A Useful Technique

Custom assembly code can be used to perform all kinds of useful reporting features. For example, you can use a centrally managed component to universally apply styling features to all your reports. With a single database value change or a parameter selection, you can change the company logo or apply different border styles and fonts to every company report.

I've shown you one of many possible techniques. I'm hopeful that you can see the potential behind this capability and find the right way to make it work for you, for your report development teams, and most of all for your business users and stakeholders that rely on you to make all this magic happen for their benefit.