Defining and Managing Report Data
New: 14 April 2006
SQL Server Reporting Services (SSRS) lets you create reports using data from many types of data sources, including relational databases, multidimensional databases, Web services, and XML and other document types. Report data can be as simple as data from a document or as complex as multidimensional data from business intelligence warehouses. You can present data from multiple data sources in a single report. After you define a report data source, you can use it for a single report or share it among many reports. How you work with report data depends on your organizational requirements.
Reporting Services provides four tools for creating and managing report data and data sources.
- Report Designer enables you to define data sources and report datasets. Each dataset defines a specific query to retrieve a collection of fields from a data source. You can define multiple datasets for the same data source. Report Designer includes a set of query designers that enable you to interactively explore the underlying data for each data source and create the query for each dataset. As soon as the report datasets are defined, Report Designer provides a report layout feature with preview. When you are satisfied with the report design, you can publish the report and data sources to the report server. Report Designer also lets you edit reports created by using the Report Builder tool.
- Report Manager is a Web-based application used to manage data sources and report processing after they are published to the report server. Use Report Application to edit most report properties, report model properties, and data source properties.
- SQL Server Management Studio is an integrated application for managing SQL Server features. After connecting to a Reporting Services server type, use this application to manage all aspects of report properties, report data source properties, report model properties, and report processing after reports are published to the report server.
- Model Designer is used to create models of the underlying data sources, which can then be accessed by the separate Report Builder tool that enables users to create ad hoc reports. Reports created by Report Builder can also be modified with the Report Designer tool.
Reports
When defining the datasets for your report, you have several decisions to make about the report data that you need. Some decisions revolve around identifying the sources of data. Where does the data come from? How do you access it? Do you need all data from the underlying data source or can you specify a subset of the data? Topics in this section cover these issues.
Other decisions revolve around when and how report data is processed. Do you want end users who view your report to see the most up-to-date data available or to see data from a specific point in time? If the underlying source of data is from a database, do you have to retrieve all your report data in one transaction? For more information about these issues, see Managing Report Processing.
A Reporting Services report definition includes data sources, datasets, and parameters.
- A data source definition includes the location of the source of data, the connection information, and credentials to use when connecting to the data source. Data sources can be shared among multiple reports or report-specific.
- A dataset includes the data source, the query command that specifies which to data to retrieve, the collection of fields to retrieve, data options about how to interpret the data to retrieve, parameters to use in the query, and filters to use to limit the data retrieved.
- Report parameters are automatically created from dataset query parameters.
When a report is published to a report server, the report is separated into the report definition, its data sources, and its parameters as independently managed report items in the report server. You can set data source properties, control access to data sources, and change report data sources (for example, from a test data source to a production data source) if the underlying form of data is the same for two data sources. You can also create parameterized reports that use an expression evaluated at run-time to control a report data source.
Report Models
When a report model is published to the report server, the model is separated into model definition and shared data sources as independently managed report server items. You can create a report from a report model using Report Builder, save the report to your computer, and use Report Designer to edit it. You can create new datasets from a report model in Report Designer by using a Report Server Model data source. When using a report model for a data source, you work with metadata created by the model designer that represents the underlying data structures from the model data source.
Note
Some additions you make to a report in Report Designer cannot be supported in Report Builder. Report Builder uses predefined templates and limited design elements. If you are editing a report built with Report Builder and plan to continue to use Report Builder, be aware that not all report elements that you can create with Report Designer can be supported by Report Builder.
In This Section
- Defining Report Data Sources
Explains how to define and manage a report data source and provides examples of connecting to different types of data sources.
- Defining Report Datasets
Describes the various data source types and query designers, and provides examples for creating datasets for supported data sources.
See Also
Concepts
Working with Report Builder (Ad Hoc Reports)
Report Data How-to Topics (Report Designer)
Working with Report Designer
Working with Model Designer