Chapter 52. Reporting Services tips and tricks

Bruce Loehle-Conger

This chapter covers performance and design tips and tricks that I’ve picked up over the years. When you’re through with the chapter, you’ll not only know how to design reports with the best possible performance, but you’ll also come away with new ways of solving problems. I guarantee that no matter how expert you are in Reporting Services (RS), you’ll find something new in this chapter.

Performance tips

The first three topics all approach optimizing performance in the same manner: limiting the amount of data that the rendering engine is working on. For good performance, limit the amount of data processed by RS. Let the database engine do what it does best and use it to retrieve the smallest set of data that the report needs to render to the final state.

Filters versus query parameters

Filters can be handy, but whenever possible, stay away from using them. Filters require RS to first retrieve all of the data, and then RS applies the filter. Query parameters follow the opposite processing path: query parameters let the database engine first apply the parameters to the data, then RS performs rendering tasks only on the subset of data specified by the parameters.

Filters are useful when developing snapshot reports. In this case, it still makes sense to limit the data as much as possible, but filters will be necessary to allow users to apply report parameters at runtime to focus the report on the data they want to see.

Linked servers

Linked servers allow you to treat data that resides on a different server as if it’s on the same server. But it’s easy to lose track of the fact that the data isn’t all on the same server. You may perform a query against a linked server in two ways. You can use four-part naming or you can use the OPENQUERY syntax.

The issue is how the database engine performs the processing. For the best performance, you want to build a pass-through query—the whole query should be passed on to the remote server for processing and have it return the result. Just like normal query processing, SQL Server has to make decisions on how to process the linked server data. The danger with the four-part nomenclature is that it’s easy to create a query that causes the entire remote server table to be brought over the wire to SQL Server. If the table is large, you’re in for a rude awakening. In SQL Server 2000, four-part naming was almost unusable. In SQL Server 2005, it improved dramatically. If in doubt, examine the query plan—it should show the processing being done remotely and should say “Remote Query” or “Remote Scan” and 100 percent. My testing has shown that even when the query plan shows 100 percent of the query being executed remotely, OPENQUERY still exhibits better performance. The code in listing 1 was executed against a table with 500K records. The OPENQUERY ran in .1 seconds and the four-part naming ran in 3 seconds.

Listing 1. Queries to compare performance of OPENQUERY and four-part naming

declare @MAXDATE datetime, @SQL nvarchar(255)

set @SQL = N'select @DATE = theDate from openquery(emerald,''select max(datestamp) as theDate from billing_recharge'')'

select getdate()as startdate

exec sp_executesql @SQL, N'@DATE datetime OUT',@MAXDATE out

select getdate()as afteropenquery

select @MAXDATE = max(datestamp) from emerald.iwts.dbo.billing_recharge

select getdate()as after4partnaming

Drillthrough instead of drill-down

This is a design choice that makes a big performance difference. Drill-down would be a wonderful UI choice if it weren’t for the problem of performance. The issue here is similar to filters. All the data is brought over from the database. Even though the user might never drill down into the data, all of the data is still retrieved. Imagine your query returns an entire year of sales data. You show the per-week totals that can be expanded to show by day and the by-day expanded to show the detail information. This type of report would mean that all the year’s sale data is being processed by the RS engine, which puts a large burden on your server.

A better way of handling this is to design a drillthrough report instead, because RS is optimized for this type of report. Plus, I’ve found that users find the interface quite easy and intuitive. Taking the same example as before, I’d create a report that summarizes the sales per week. Next, I’d add a column and put in a text box report item that says By Day, add an underline, and make it blue. Then I’d use Jump To Report to open up the By Day report.

Another advantage of drillthrough reports is that the reports you design are more likely to be useful when brought into Excel. In my experience, it’s best to always keep in mind the efficacy of exporting the data to Excel. Using drillthrough reports accomplishes both tasks.

Data export

I’ve seen people try to use RS as a data extraction tool like SSIS or BCP. This isn’t a good idea. But Excel is a powerful tool, and power users will always come up with new ways of looking at and analyzing the data. Invariably, users will want to pull large amounts of data into their spreadsheets for further analysis. What you want to do is make this as quick and easy as possible for them. This was a particular issue with SQL Server 2000, which was extremely slow at rendering in Excel format. Even with later versions of RS, if the data is large enough, you’ll want the fastest way possible to get the underlying data from the report into Excel. The absolute fastest way to do this is to export in CSV format.

The problem is that RS 2000 and RS 2005 export CSV in Unicode format. When Excel imports Unicode format, the data is all put into a single field instead of splitting it into individual cells. The solution is to configure RS to render CSV in ASCII format instead of Unicode by editing rsreportserver.config as shown in listing 2. You’ll need to stop and restart RS for the change to take effect. Note that this isn’t a problem in RS 2008, because it defaults to ASCII.

Listing 2. Editing rsreportserver.config to render CSV in ASCII

<!--
   <Extension Name="CSV"
     Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport, Microsoft.ReportingServices.CsvRendering"/>
-->
   <Extension Name="CSV"
     Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport, Microsoft.ReportingServices.CsvRendering">
       <Configuration>
           <DeviceInfo>
               <Encoding>ASCII</Encoding>
           </DeviceInfo>
       </Configuration>
   </Extension>

Connection pooling

Creating the connection to the database is a resource-intensive task. Connection pooling reuses connections instead of creating a new connection each time. In order for connection pooling to work its performance magic, the connection string must match exactly. This includes the login. In order to benefit from connection pooling with your reports, you need to do two things.

First, your data source must be configured to use a specified user and password. Although this could be a domain user (Integrated Security=SSPI), I prefer to configure SQL Server to run in mixed mode, which allows me to use a SQL login. When I configure the data sources to use the same login, connection pooling kicks in. I give the special login read-only access and execute rights to whatever stored procedures I use for the report. This allows me to eliminate the step of granting rights to the database for every new user.

Second, depending on the protocol, you might have to enable connection pooling. If you’re using ODBC, you’ll need to enable connection pooling. Open up the ODBC Administrator. Go to the Connection Pooling tab, find the driver, and enable it. The ADO.Net OLE DB and SqlClient .NET data providers automatically use connection pooling.

Design tips

In the second part of this chapter, let’s cover a number of design issues, beginning with a common one: the use of stored procedures and temp tables.

Stored procedures and temp tables

Using stored procedures and temp tables is the number one problem I address on the newsgroups. I use temp tables successfully and extensively with both SQL Server and Sybase. If you follow these guidelines, it’ll work for you:

• If your field list isn’t showing up, then in RS 2000 and RS 2005, go to the Data tab and click on the Refresh Fields button, which is to the right of the dataset drop-down box. If you’re in RS 2008, pull up the dataset properties by double-clicking on the dataset and click on the Refresh Fields command button.
• Don’t explicitly drop the temp tables. Let the temp tables fall out of scope. It’s not necessary to explicitly drop the tables, as SQL Server will properly dispose of them when they’re no longer needed. If you do explicitly drop them, the stored procedure won’t work with RS.
• Have your last statement be a SELECT.
• If none of these work, then add SET FMTONLY OFF. Simon Sabin, a SQL Server MVP, provided me with this tip a long time ago. Here’s his explanation: “The issue with RS is that the rowset of the SP is defined by calling the SP with SET FMTONLY ON because temp tables don’t get created if you select from the temp table—the metadata from the rowset can’t be returned. Turning FMTONLY OFF in the SP bypasses this issue.” I’ve found this to only be an issue when you create a temp table in your stored procedure that’s then filled with data from another stored procedure.

Excel merged cell solution

You create this beautiful report that your users export to Excel. They want to sort the data, but they bump up against the dreaded merged cell problem and they can’t sort the data. What happens is that when RS renders to Excel, it tries to make the Excel spreadsheet look as close as possible to the original report. In doing this, Reporting Services ends up creating merged cells, which prevents Excel from allowing the sorting of the data. Two design solutions are available. One has to do with lining up freestanding text boxes so that the Reporting Services renderer doesn’t create a spreadsheet with merged cells. I have a much easier solution: remove any freestanding text boxes such as those put in by the report wizard. I then add additional header lines to the table control, and put my report titles, parameter information, and so forth in these additional headers. You can merge cells together and this won’t cause a problem on export to Excel.

RS 2008 renders reports to Excel more completely than previous versions. On one hand, this is good. Previously, if you had a subreport in a cell of a table object when you exported it, RS would put an error message in the cell of the spreadsheet. RS 2008 now renders the subreport correctly by putting the subreport into the appropriate spreadsheet cell. Unfortunately, this can cause the merged cell problem discussed previously. I haven’t found a workaround for this.

Excel web queries and reports

Exporting to Excel is one of RS’s strengths. It allows users to build on the data in the report by performing additional analysis or building their own charts. Eventually, a lot of work gets put into these spreadsheets and users want to share them with others on a wider scale. You have several options at this point. One possibility is to use the spreadsheet as the specification for designing a report that does the same thing. Another option is to pull the report data into Excel. Excel has a couple of ways to do this.

One way is to create a connection to the database and execute the query or stored procedure from within Excel. This can be done by either using Excel’s support for creating the query or writing VBA code to pull in the data. Neither was what I was looking for. Creating the query required creating a database source name (DSN) on each computer. Writing VBA code required either embedding a username and password or using integrated security. As I said earlier, I try to touch the database as little as possible where it comes to security. I have only a single read-only user that’s used by RS. That brings us to the possibility of using web queries.

Web queries are an Excel feature that allows you to enter a URL during the design process of the web query; then Excel presents the web page and you pick the part you want to bring in. I put in a URL in the same format as used for the Jump to URL field and pick the table of data I’m interested in. The first problem is that it only brings in the first page of data. This is resolved by setting the interactive height for the report to 0, which includes all the data returned with no paging. It works great. If all you want to do is perform a one-off data load when the spreadsheet is open, you have a URL that will work for your purposes. But if you want to allow refreshing the data, this solution won’t work. What happens is the link being used by Excel is tied to the session being maintained by RS. Once enough time has passed that the session has timed out, Excel can no longer refresh the data.

The final piece of the puzzle falls into place when you add the Format=MHTML switch to the URL, telling it to render in MHTML format. Now everything works auto-magically, using no VBA code and without having to touch each desktop. Deploy the spreadsheet to Reporting Services and you now have a spreadsheet that all your users can benefit from.

Let’s go over the steps one by one. First, when using web queries, you should create the URL with all your parameters hard-coded:

http://Servername/ReportServer?/Folder/
ReportName&rs:Format=MHTML&Parameter 1=ParameterValue

You can take some additional configuration steps in Excel. When creating the web query, you have the option to have it run automatically at startup. Instead of doing that, I prefer to create a macro with the name Auto_Open. This macro automatically runs at startup. You can also tie the macro to a shortcut. When data is brought into Excel, you have the option of how you want Excel to bring in the data. It can come in as straight text or full HTML. Right-click anywhere on the data in the spreadsheet and select Edit Web Query. Click on Options. Other configuration changes are available by right-clicking again and selecting Data Range Properties.

If your report can take a static parameter or no parameters, you’re done. But if you want to have a parameter tied to a cell in the spreadsheet, this is also possible. Edit the web query and change the URL to

http://Servername/ReportServer?/Folder/
ReportName&rs:Format=MHTML&Parameter 1= ["SomeParamName"]

At this point, you get an error about a missing parameter. This is because of how Excel tries to refresh the data during the design process. Just ignore the error.

Next, right-click the spreadsheet again and you’ll see a new menu option for Parameters. Click to configure the parameters. You can configure the parameter to prompt the user, put in a constant value, or tie the parameter to a cell. You can also have the data refresh automatically when the cell value changes. You can now get as fancy as you want with the spreadsheet—if the value of the cell is either a string or a number. Unfortunately, if it’s a date, you have a problem.

When tying a parameter to a cell that contains a date, Excel passes the internal representation of the date, not a date format that RS will recognize. To resolve this issue, I’ve come up with two Excel macros that significantly help with adding parameters to the web query. The first is a macro I use to correct the URL. After the hard-coded URL string is working, I run the fix-up script shown in listing 3 to change the URL to include the parameters.

Listing 3. Script to change URL to include parameters

Sub FixupURL()
Dim strURL As String
Dim strT As String
Dim oQuery As QueryTable
    Set oQuery = Sheets("Sheetname").QueryTables(1)
    strURL =     "URL;http://Servername/ReportServer?/Folder/
ReportName&rs:Format=MHTML&Parameter1= [""SomeParamName""]"
    oQuery.Connection = strURL
    strT = oQuery.Connection
    MsgBox (strT)
End Sub

Next, use the macro shown in listing 4 to put in the date parameters and refresh the query.

Listing 4. Macro to add date parameters and refresh the query

Sub RefreshData()
Dim strQueryTableName As String
Dim strParam1Name As String
Dim strParam2Name As String
Dim oQuery As QueryTable
Dim strFromDate As String
Dim strToDate As String

    strFromDate = Sheets("Sheetname").Range("FromDateCellName")
    strToDate = Sheets("Sheetname").Range("ToDateCellName")
    Set oQuery = Sheets("Sheetname").QueryTables.Item(1)
    oQuery.Parameters.Item(1).SetParam xlConstant, strFromDate
    oQuery.Parameters.Item(2).SetParam xlConstant, strToDate
    oQuery.Refresh

End Sub

If you don’t want to use the web query wizards, you can use CSV format, which is the fastest way to get data into your spreadsheet. As before, first get your URL string perfected. Next, use the macro shown in listing 5.

Listing 5. CSV alternative to web query wizards

Sub CreateWebQuery()
Dim strURL As String
Dim strFromDate As String
Dim strToDate As String
Dim strT As String
Dim oQuery As QueryTable
Dim i As Integer
Dim strSheetname As String

strSheetname = "Test"
Sheets(strSheetname).Select

For i = 1 To ActiveSheet.QueryTables.Count
    ActiveSheet.QueryTables(1).Delete
Next i


strURL = "URL;http://Servername/ReportServer/Pages/ReportViewer.aspx?/Foldername/Reportname&FROMDATE=[""FROMDATE""]&TODATE=[""TODATE""]&rs:Format=CSV"
'This code assumes a sheet called DateRange that has the two necessary dates
strFromDate = Sheets("DateRange").Range("FromDate")
strToDate = Sheets("DateRange").Range("ToDate")

Set oQuery = ActiveSheet.QueryTables.Add(Destination:=Range("A1"), Connection:="URL;")
With oQuery
    .Connection = strURL
    .Name = strSheetname 'Naming webquery the same as sheet
    .EnableRefresh = False
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = True
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .SaveData = False
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .Refresh BackgroundQuery:=False
    .Parameters.Item(1).SetParam xlConstant, strFromDate
    .Parameters.Item(2).SetParam xlConstant, strToDate
    .EnableRefresh = True
    .Refresh
End With
ActiveSheet.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False
ActiveSheet.Columns("A:A").EntireColumn.AutoFit

End Sub

HTML or Word documents

You might find it handy to have RS host file types other than reports. I’ve used Reporting Services to host both Word and HTML documents. To add a document to your project, right-click on Reports; choose Add, Existing Item; and point to an existing document file. Deploy the file just like you’d deploy a report. One little quirk has to do with deploying changes. In versions of Reporting Services prior to RS 2008, I found that I had to delete the document from Report Manager and then redeploy for any changes to make it to the server.

Server portability

At some point, you might need to roll out a new server. When that happens, you’ll want to thoroughly test the new server and go live as easily as possible. Two steps are available that will greatly ease this chore. First, you need to design your reports for portability. The main culprit here has to be using Jump to URL. You don’t want to embed the name of the server in the URL. Instead, use the global variable Globals!ReportServerUrl:

=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value

I tend to use Jump to URL when I want the jump to render in a different format than the default HTML, or I want the report to be rendered to a different window. If the action you desire is the same as Jump to Report, and you want to reference a report that’s not in the same project, you don’t have to use Jump to URL. Instead, you can use Jump to Report. Normally, you enter the name of the report. Instead, enter the report folder and report name. This has two issues. You have to map the parameters manually, and you can’t test it in the IDE. You have to deploy the report to test the link.

The other suggestion for making your site portable has nothing to do with report design. Instead, it’s a systems suggestion. Register a virtual name for your server in the DNS. This name should have a different fixed IP address than your server. When you provide users with the link to Report Manager on your server or wherever you integrate RS into your solution, use this virtual server name.

Next, take advantage of the fact that you can configure TCP/IP for your network card to handle two IP addresses. To either add or remove IP addresses, first go to the properties of the TCP/IP stack. The General tab should have the fixed IP address of your server. Click on Advanced and then the Add button. Now, when you’re ready to go live, you don’t have to coordinate anything with any other group. Remove the address of your virtual server and add it to the new server. As an added benefit, this approach allows you to keep the old server around—just in case.

Embedding T-SQL in a report

Although it’s possible to embed T-SQL in a report, this can be a source of trouble. The first step is to switch to the Generic Query Designer. In RS 2000 and RS 2005, you click on the Generic Query Designer button in the Data tab. In RS 2008, click on the Edit as Text button. If you want to write queries that can go against multiple databases, you have four choices:

• Use stored procedures
• Use an expression-based dataset
• Use an expression-based data source (RS 2008)
• Embed T-SQL in the report

I tend not to like to litter up the database with stored procedures when I’m doing a simple SELECT. Expression-based datasets are awkward. They don’t return a list of fields, which makes developing a report a multistep process. Before I found out about embedding T-SQL, I created some expression-based reports. I’d first develop a report using static SQL and once the report was finished, I’d change it to an expression.

RS 2008 introduced expression-based data sources, which are a step forward. But I don’t like to use report-based data sources—I prefer to use shared data sources, which brings me to embedded T-SQL. I used embedded T-SQL with RS 2005 because I could develop and test the T-SQL outside of the report. Embedded T-SQL has come into its own with RS 2008—where it now works with the Report Wizard, and where the report parameters are automatically created and all fields are recognized. Try the following T-SQL with the Report Wizard:

declare @SQL varchar(255)

select @SQL = 'select table_name from ' + @Database + '.information_schema.tables order by table_name'

exec (@SQL)

User!UserID

Sometimes you might want to develop a report where you limit the data shown depending on the specific user requesting the report. Normally, I prefer to use security to determine who can run a report; therefore, anyone with the rights for the report can see all the data, but this isn’t always what you want. I have some reports that anyone in our company can use as far as security is concerned. First, I create a table for the reports that lists the user, what reports they can run, and the parameter values for that report. The report dataset joins to this table. A key point here is that a query parameter doesn’t have to be mapped to a report parameter. Instead, the query parameter is mapped to the User!UserID global parameter. This global parameter contains the user running the report. This means that you can’t use this for subscriptions. Also, report snapshots can use the wrong UserID when accessed via web services or URL access. Report Snapshots work correctly with UserID only when the report is accessed via Report Manager. My advice is to only use UserID for on-demand reports. User!UserID includes the domain. If you don’t want the domain, use the following expression when mapping the query parameter:

select RIGHT(@UserID, LEN(@UserID) - CHARINDEX('', @UserID)) as UserName

Summary

Reporting Services is both a flexible and powerful tool, but there are a number of things we need to be aware of to derive maximum performance from our reports. Hopefully I’ve provided you with several ideas that you can use when building your next report.

About the author

Bruce Loehle-Conger works for a Fortune 100 company and works with data ranging from real-time control databases to SQL Server to Sybase. He has been working with Reporting Services since it was first in beta form and was one of the first two MVPs to be awarded for Reporting Services.