CHAPTER 3 INTRODUCTION TO REPORTING SERVICES DESIGN WITH SQL SERVER DATA TOOLSprovided another folder in the Report Data pane called Data Sources. For now, just keep in mind that
this new IDE has been designed to centralize your local and shared data sources. Again, coming from VS
2005, you will notice the new Row Groups and Column Groups areas, designed for easier management
of Tablix-style groupings. As with any report that contains data from a data source, the first step is to
create a link between the report and the data source as well as one or more datasets. We will do both in
the next section.
Figure 3-6. BIDS IDE with Report1.rdl loaded
Setting Up Data Sources and Datasets
Each report created in SSRS contains both a data source and a dataset. A data source not only defines the
type of connection that is established to retrieve the data, whether it’s SQL Server, Analysis Services, or
Oracle, but also defines the specific connection properties, such as the server, database name and
security credentials. A dataset, on the other hand, is the data, rows and columns, or fields that are
returned from the data source. The dataset is created by building a query that retrieves information from
the data source. This query, as in the case of a SQL Server data source, can be a text-based query
embedded directly in the report, or a stored procedure.
In Chapter 2, you created a stored procedure called Emp_Svc_Cost, containing employee and patient
visit information. You’ll use that procedure as your dataset as you work with most of the report objects in
this chapter. For other report objects, such as the image report object, you will use direct queries instead
of the Emp_Svc_Cost stored procedure.
Creating a Data Source
Each report can use one or more data sources. Reports that use the same data source—for example, one
that connects to a specific SQL Server database—can use what is referred to in SSRS as a shared data
source. Shared data sources are published along with the report and can be modified on the report server