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.

Design tips

Summary

About the author