Using Custom Assemblies in Reports to Generate Query Logic – Part 1

Sometimes you need to process parameter business rules that extend beyond the capabilities of a query language (like T-SQL or MDX) and it may just be easier to use a custom programming solution to manage a report dataset query and related report behavior.

In our recent book "Professional SQL Server 2012 Reporting Services", I approached this topic in Chapter 21, beginning in Page 692.  This post is an extension to that topic and here, I’ll explore the technique at greater depth.

T-SQL and MDX are great query languages and have some support for handling conditional logic but sometime you may just need to use a true programming tool to handle parameter exclusions and conditional logic.  To handle the real hard conditions, often the best approach is to programmatically assemble parts of the query as conditions dictate.  The example that I will use in this post is not all that complex and much of it could easily be handled using more conventional methods.  The problem with creating demonstrations is that it can be very difficult to simulate, in concise and simple form, some of the real vexing problems that we encounter on real live projects.  And I’ve got some nasty ones out there – it’s just tough to boil down some of the design challenges into simple examples.  So… trust me. The technique I’m demonstrating can be quite valuable, and in some cases; necessary.

The design pattern:  Use an external custom method to assemble a T-SQL statement based on several parameterized arguments. 

The assembly will be developed in Visual Studio, with the full suite of coding and debugging tools, 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 SSDT/BIDS report designer and on the report server.  I’ll use a Visual Basic.NET project but you could just as easily use C#.

Add a class library project to the solution

1. With the SSRS Student Labs project open in SSDT, drop-down the File menu.

2. Select Add > New Project…

3. In the Add New Project dialog, under the Installed Templates pane, select Visual Basic.

4. In the center pane, select Class Library and name the project Report_Class_Lib.

5. In the Solution Explorer, change the name of the default class file to ReportLib.cls.

In the following example code, the line continuation characters are optional and are provided only so this code fits on the printed page in these instructions. If you prefer, you can omit the underscore and the carriage return on reach line that ends with an underscore.

6. After the Public Class ReportLib declaration, enter the follow code on a new line.

Visual Studio’s Visual Basic code editor will assist you with code completion and debugging. Use these features to complete the code and save yourself some work. When a list of objects is presented, use the arrow keys to select the appropriate object and then press Tab to add it to your code. Press the Enter key to select an object and add a new line to the code.

Public Function SalesQuery(ByVal YearFrom As Integer, ByVal YearTo As
                  Integer, ByVal Country As String, ByVal MaxRows As 
                  Integer, ByVal IncludeFrieght As Boolean) As String
    Dim sSelect As String = "SELECT TOP " & MaxRows.ToString() & _
          " CalendarYear, CalendarQuarter, MonthNumberOfYear,
MonthName, " & "SalesTerritoryCountry, SUM(SalesAmt) AS SalesAmt, SUM(OrderQty) AS OrderQty"
    Dim sFrom As String = "FROM vSalesByTimeAndTerritory "
    If IncludeFrieght Then sSelect &= ", SUM(Freight) AS Freight "
    Dim sWhere As String = ""
    If YearFrom <> -1 Then sWhere = "(CalendarYear BETWEEN " & YearFrom.ToString() _
        & " AND " & YearTo.ToString() & ") "
    If Country <> "All" Then
        If sWhere = "" Then
            sWhere &= "SalesTerritoryCountry = ‘" & Country & "’ "
        Else
            sWhere &= " AND SalesTerritoryCountry = ‘" & Country & "’ "
        End If
    End If
    If sWhere <> "" Then sWhere = "WHERE " & sWhere
    Dim sGroupBy As String = "GROUP BY CalendarYear, CalendarQuarter, MonthNumberOfYear, " _
                             & "MonthName, SalesTerritoryCountry "
    Return sSelect & vbCrLf & sFrom & vbCrLf & sWhere & vbCrLf & sGroupBy
End Function

7. Check the code for errors and warnings that will have red or green squiggly lines under keywords.

You are encouraged to write this code yourself using the code completion and debugging tools in Visual Studio. If you need some help or would like to speed up this lab, you can open the SSRS Completed Labs solution and copy the code from the completed version of the Report_Class_Lib project.

8. In the Solution Explorer, right-click the Report_Class_Lib project and select Build from the menu.

Figure 1

Deploy the class library

1. Open Windows Explorer and navigate to the project folder, which should be the following path (if it’s different, that’s OK. Just be mindful of your changes):

C:\Users\Administrator\Documents\Visual Studio 2010\Projects\Report_Class_Lib

2. Click in the address box and copy this path to the clipboard.

If you are an experienced Visual Studio developer, you can replace steps 3 through 11 with another approach to simplify deployment… Create a build event in the class library project properties to copy the assembly file at the end of the build process. Define a post-build event (on successful build).  Instead of creating a CMD file, enter the two COPY commands below into the post build event script.

3. Create a new text file in this folder and name it Deploy_Library.cmd.

4. Right-click the file and open it in Notepad.

5. Enter the following command line text into the file. You can start by pasting the path you copied from Windows Explorer in place of the first path in each of the two statements.

Note that there are no carriage returns in the two COPY commands. Enter all of the text for each COPY command into one line with to file paths in double quotes.

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"

If you are running in SharePoint Integrated mode, you must also copy the assembly to the shared folder for web server extensions.  If 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"

6. Close Notepad and save changes to the command file.

7. Add this file to the class library project using Add > Existing Item… from the Solution Explorer project right-click menu.

8. In the Solution Explorer, right-click the Deploy_Library.cmd file and choose Open With…

9. Click the Add… button and in the Add Program dialog, use the ellipsis button (…) to browse to the Deploy_Library.cmd file (you’ll need to change the Files of type drop-down option to All Files *.*).

10. Accept the default Friendly Name and click OK to accept the file addition.

11. Click the Set as Default button on the Open With dialog. Click the OK button to save this setting. A command window should open momentarily and then close.

You can double-click the Deploy_Library.cmd file in the Solution Explorer any time to ensure that the latest library is deployed.

12. Use Windows Explorer to verify that the Report_Class_Lib.dll file has been copied to the following locations and has a recent modification date:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin

These folders are trusted locations for libraries referenced and run by Reporting Services. The first path supports the report designer development environment.

The second path is trusted by the report server. As updates are made to a library, be sure to copy it to both of these folders.

Create a report and add an assembly reference

1. Create a new report named Generated Query.

2. Choose Report Properties… from the Report menu.

3. In the Report Properties dialog, 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, choose the Browse tab and locate the Report_ClassLib.dll in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies.

7. Select the Report_ClassLib.dll file and click OK to add the reference.

8. In the Add or remove classes section of the Report Properties | References page, click the Add button.

9. In the box under the Class Name column, enter the namespace and class reference in the assembly: Report_Class_Lib.ReportLib.

10. In the box under the Instance Name column, enter m_ReportLib. This is the class alias name to use in the report.

11. Click the OK button to save these changes.

Figure 2

Setup the report to call a class method

1. Add the following parameters to the report. Use the table to set the parameter name, prompt, type and default value:

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

2. For the SalesQuery parameter, use the Default Value page. Choose specific values and add a default.

3. Click the Expression button (fx) to the right of the drop-down list box.

4. Enter the following text and then place the cursor between the parentheses:

=Code.m_ReportLib.SalesQuery( )

5. 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.

6. Repeat these steps to build the following expression with additional parameters (with no carriage returns):

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

7. Click the OK button to save the expression.

8. Add a textbox to the report.

9. Right-click on the textbox and choose Expression…

10. Use the Expression Builder to set the textbox expression to:

=Parameters!SalesQuery.Value

11. Preview the report.

12. Read and verify that the generated SQL is syntactically correct.

Some notes about the generated query:

• The TOP value is set by the MaxRows parameter.
• The Freight column is included in the column list because the IncludeFreight parameter is set to True.
• The YearFrom and YearTo values in the WHERE clause match the parameters.
• The WHERE clause doesn’t include the SalesTerritoryCountry column because the Country parameter is set to All.
• Parameter values in this example are not being validated and could be subject to errors or code injection.  Safeguards should be added before this technique is used in production.

 

In a later post, I’ll cover:

• Ensuring Safe Threat-Free Computing practices.
• Secure and safe deployments
• Preparing for the steps necessary to deploy safe, signed packages

 

**** As always, give me your thoughts an your comments ****