Apress - Pro SQL Server 2008 Reporting Services (2008)02 | CHAPTER 2 Report Authoring Designing Efficient Queries SSRS provides a platform for developing and managing reports in an environment that includes multiple data sources of information. These data sources can include both relational data for example SQL Server Oracle MySQL and so on and nonrelational data for example Active Directory LDAP stores and Exchange Server . Standards such as ODBC OLE DB and .NET facilitate the retrieval of data from these disparate data stores so as long as your system has the relevant drivers SSRS can access the data. In the SSRS report design environment configuring a dataset that drives the report content is the first step of the design process. However before we introduce the many elements of the report design environment it s important to begin with the heart of any data-driven report whether it s Business Objects Reports SSRS or Microsoft Access and that is the query. With any report design application developing a query that returns the desired data efficiently is the key to a successful report. In this chapter we will describe the following The health-care database that is the target of the reporting queries in this book you cannot design efficient queries unless you understand the design of the data. We ll also describe an easy way to familiarize yourself with your data when the full schema details are not available. How to design basic but effective SQL queries for reporting purposes we ll create queries based on real-world applications the kind that report writers and database administrators create every day. How to use SSMS to gauge query performance the initial query defines the performance and value of the report so it s important to understand the tools required to create and test the query to ensure that it s both accurate and tuned for high performance. How to transform the optimized query into a parameterized stored procedure. This gives you the benefit of precompilation for faster performance and the benefit of the .
