5 SQL Server Reporting Services Hacks That Everyone Should Know

SQL Server Reporting Services (SSRS) is a powerful set of tools for distributing data within an organization. Users can pair SSRS with the report editor in Visual Studio to develop a variety of reports. But with such a range of reports, simple tasks, such as repeating a header or adding a default parameter value, are more complex than expected.

When I was first learning to use SSRS, I spent hours scouring online resources for ways to complete small (yet critical) changes to my reports. This article is an attempt to distill what I learned from my development struggles into a short and informative guide to solve five common problems in SSRS.

Problem 1: I’ve updated the stored procedure for my dataset, but the previewer is still displaying the old data.

The datasets for my reports generated fields through stored procedures. When I made changes to a stored procedure and refreshed the fields in Visual Studio, the previewer was still displaying data from the previous version of the query.

This problem stems from how Visual Studio caches the data for reports. When testing, users will often run the same report over and over with minor formatting changes. To avoid long execution times from rerunning the same query, Visual Studio stores the data in a .data file. While this feature can be helpful, it does not automatically update the file when the stored procedure is updated. Deleting the .data file from the project file in Windows Explorer will clear the cache and run the latest version of your query (just be careful not to delete your report .rdl files!).

Problem 2: I’ve selected “repeat column headers on every page,” but the headers are not repeating.

This is one of the more frustrating issues with the reports editor in Visual Studio. After selecting “Repeat column headers” in Tablix properties and previewing the report, the headers would not actually repeat on each page. For the most recent version of the SSRS report editor (Visual Studio 15.3 and SSDT 15.1), changing this particular setting does not actually affect the code-behind for the report.

The solution to this issue is in the grouping properties and not the Tablix properties. Select advanced mode in the grouping window, choose the static group for the column header, and hit F4 to open the properties window. Finally, set the Keep With Group property to “After” and the Repeat On New Page property to “True.” Once you open your report in the previewer, the column headings will display on every page.

Problem 3: My default parameter values aren’t correct.

When I was adding default and available values to my parameters, I learned that there is a subtle difference between using the field label and field value. For my report, I was displaying a customer’s name as the label while passing their unique ID number in to the query as the value. However, when I tried to set a customer’s name as the default, the parameter would show up blank in the previewer.

To set a default value for a parameter, the value must exist in the dataset for the available values. I was typing in the field label as the default (the customer name), which obviously would not exist in the list of customer ID numbers. Be sure to check that values you are defaulting to are what you want to pass to your query, not what you want to display to the user.

Problem 4: My dataset query runs quickly in SSMS, but the report is very slow.

As in the example in Problem 1, I developed stored procedures for datasets in SQL Server Management Studio (SSMS). In some cases, queries that ran in one or two seconds in SSMS would take several minutes to render in SSRS. I later learned that my stored procedures were slowing down because of a process called parameter sniffing.

SSRS uses parameter sniffing to create and cache an optimal query execution plan. Performance problems arise when SSRS reuses a cached execution plan for new parameters (SSMS will usually avoid this trap). While the plan might have been optimal for the old parameters, it could be inefficient and sluggish for the new parameters.

There are several ways to “trick” the optimizer into recompiling the execution plan (for example, using the RECOMPILE and OPTIMIZE FOR keywords). The easiest (and often most effective) way is to set a newly declared variable equal to the parameter value passed into the query. Declaring a variable forces the optimizer to recompile to account for the potentially “new” value, creating an optimized execution plan.

Problem 5: My PDF export has extra blank pages.

Once I completed my report, I wanted to ensure that it would print correctly for the business users. However, when I exported my final report to a PDF from the preview window, I found blank pages in between every page of data.

This problem can be solved with another hard-to-find setting. In the design window, click outside of the report body (the black or grey area surrounding your report) and click F4 to access the report properties. Select the Consume Container White Space property and set it to “True.” You can now export the report without any blank pages.

Conclusion

While it’s a useful tool, SSRS has many quirks that can frustrate beginning and experienced developers alike. Hopefully, these tips will shorten your development time and give your business users the data they need as quickly as possible.

Have any other questions about SQL Server, SSRS, database infrastructure, or report development? We would love to discuss potential solutions with you—contact us at findoutmore@credera.com.

• SSRS
• SQL Server