As part of SQL Server Pro eLearning series, I’ll be presenting three 75 minute sessions on March 26th on using Expressions to enhance report capabilities. The sessions are online with live chat from 11:00 to 4:30 Eastern Time.
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 & "’ "
sWhere &= " AND SalesTerritoryCountry = ‘" & Country & "’ "
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
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.
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.
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:
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:
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:
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 ****
Comments : 1 Comment »
Tags: Assemblies, Custom code
Categories : SSRS Administration, SSRS Design
I am proud to announce that our Professional SQL Server 2012 Reporting Services book is finally in print and generally available. This 865 page book took about 14 months to write and was completed about six weeks ago. Paperback and electronic editions are available from Wrox.com, Amazon & other resellers.
My esteemed co-authors were Robert Bruckner from the SSRS product team, Grant Paisley, Thiago Silva and Ken Withee; who all did fantastic work.
Thanks go to Joe Salvatore, Chris Albrektson and Nigel Sammy who did a great job as a technical reviewers.
In addition to covering every significant capability of the new professional reporting platform and the integrated SharePoint experience; chapters are dedicated to new BI capabilities introduced in SQL Server 2012 including BISM semantic tabular models, PowerPivot for Excel 2012 and SharePoint 2012, and Power View visualizations.
Table of Contents:
PART I: GETTING STARTED
CHAPTER 1: INTRODUCING REPORTING SERVICES 3
CHAPTER 2: REPORTING SERVICES INSTALLATION AND ARCHITECTURE 23
CHAPTER 3: CONFIGURING SHAREPOINT INTEGRATION 69
PART II: REPORT DESIGN
CHAPTER 4: BASIC REPORT DESIGN 95
CHAPTER 5: REPORT LAYOUT AND FORMATTING 123
CHAPTER 6: DESIGNING DATA ACCESS 143
CHAPTER 7: ADVANCED REPORT DESIGN 189
CHAPTER 8: CHART REPORTS 229
PART III: BUSINESS INTELLIGENCE REPORTING
CHAPTER 9: BI SEMANTIC MODELS 251
CHAPTER 10: REPORTING WITH ANALYSIS SERVICES 263
CHAPTER 11: OLAP REPORTING ADVANCED TECHNIQUES 295
PART IV: ENABLING USER REPORTING
CHAPTER 12: TABULAR MODELS 349
CHAPTER 13: VISUAL ANALYTICS WITH POWER VIEW 373
CHAPTER 14: REPORT BUILDER SOLUTION STRATEGIES 445
PART V: SOLUTION PATTERNS
CHAPTER 15: MANAGING REPORT PROJECTS 463
CHAPTER 16: REPORT SOLUTIONS, PATTERNS, AND RECIPES 483
PART VI: ADMINISTERING REPORTING SERVICES
CHAPTER 17: CONTENT MANAGEMENT 525
CHAPTER 18: INTEGRATING REPORTS WITH SHAREPOINT 559
CHAPTER 19: NATIVE MODE SERVER ADMINISTRATION 581
PART VII: REPORTING SERVICES CUSTOM PROGRAMMING
CHAPTER 20: INTEGRATING REPORTS INTO CUSTOM APPLICATIONS 619
CHAPTER 21: USING EMBEDDED AND REFERENCED CODE 681
CHAPTER 22: EXTENDING REPORTING SERVICES 697
PART VIII: APPENDIXES
APPENDIX A: T-SQL COMMAND SYNTAX REFERENCE 758
APPENDIX B: T-SQL SYSTEM VARIABLES AND FUNCTIONS 779
APPENDIX C: MDX REFERENCE 803
Comments : 2 Comments »
Categories : Microsoft BI Platform, SolidQ, SQL Server, SSRS Administration, SSRS Design
What has your experience been with using tool tips in Reporting Services reports? This feature gets some mixed reviews. I see it work and I see it, well, not work so well at times and under certain conditions. …but I’m interested in your unbiased feedback. If you’ve used this feature and you have something to say about it, please post a comment. I’d like to start a dialog. I have some thoughts and experience with this but I’d like to know what others think, first. Oh, and by the way, members of the Reporting Services product development team may be listening.
Comments : 7 Comments »
Categories : SSRS Design