Creating a Report Dataset
When you create reports in Reporting Services, you first define one or more data sources and then you create one or more datasets for each data source. Each dataset specifies the fields from the data source that you plan to use in the report, and calculated fields that you can create. In addition to the query and field definitions, a dataset contains a pointer to the data source, query parameters, and data options that include character sensitivities including case, kana type, width, accent, and collation information.
You can also add a reference to a shared dataset that is published on a report server or SharePoint site. A shared dataset enables you to use a query that someone else provided. For more information about shared datasets, see Managing Shared Datasets.
After you create a dataset, you can view the name of the dataset and its field collection in the Report Data pane. From the Report Data pane, you can create additional fields or edit existing fields. For more information, see Working with Fields in a Report Dataset.
To display data in your report, link a dataset to a data region, such as a table or chart, by dragging fields from the Report Data pane to a data region on the design surface. For more information about each type of data region and the concepts of preparing, filtering, and grouping your data, see Working with Data Regions, Filtering Data in a Report, and Grouping and Sorting Report Data.
Reporting Services provides the following features to help you create datasets that specify the data that you want in your report:
Data source types. A list of predefined types of relational, multidimensional, and XML data sources, for example, SQL Server and Microsoft SQL Server Analysis Services. For a complete list, see Connecting to a Data Source (Reporting Services).
Data processing extensions and data providers. Preconfigured components that connect to a data source type and retrieve the report data for the dataset. You can also register third-party data providers as data sources. For more information, see Data Processing Extensions and .NET Framework Data Providers.
Graphical and text-based query designers. Query designers help you write a query to specify the data to retrieve from the data source. Query designers display example data for your report and help you define query parameters. For more information, see Query Design Tools in Reporting Services. The default sort order for the dataset is defined in the query.
Automatically generated report parameters for query parameters. When your query includes query parameters, Reporting Services automatically creates report parameters so that the user can vary the parameters before they run the report. When your query includes multiple related parameters, you can create cascading parameters to help control the number of values from which to choose. For more information, see Adding Parameters to Your Report and Using Query Parameters with Specific Data Sources (Reporting Services).
Dataset filters. Filters that you can define to include or exclude specific values after the data is retrieved from the data source. For more information, see Dataset Properties Dialog Box, Filters.
Using Report Models
Report models enable you to select data for a report without having a detailed knowledge of the data structure on the underlying data source. Someone with expertise in model design must first create a report model and publish it before you can use it as a data source.
Report models contain predefined fields and data relationships, called entities and roles, that appear in the Data pane. You can use these items in the same way that you use dataset fields: by dragging them to the design surface as you design your report. For more information about how to build report models, see Working with Model Designer. For more information about creating ad hoc reports, see Designing and Implementing Reports Using Report Builder 1.0 on msdn.microsoft.com.
To learn more about how to connect to and retrieve data from the data source types supported by Reporting Services, see the topics in this section.
In This Section
Understanding Report Datasets
Describes the characteristics of data retrieved from a data source.
Creating Report Datasets from a SQL Server Relational Database
Discusses how to define a dataset from a SQL Server relational database.
Creating Report Datasets from SQL Server Analysis Services
Discusses how to define a dataset PowerPivot workbook published in a PowerPivot Gallery.
Creating Report Datasets from Microsoft PowerPivot Data
Discusses how to define a dataset from an Analysis Services multidimensional database.
Creating Report Datasets from a Microsoft SharePoint List
Discusses how to define a dataset from a Microsoft SharePoint list.
Creating Report Datasets from a SQL Azure Database
Discusses how to define a dataset from a SQL Azure database.
Creating Report Datasets from a SQL Server Parallel Data Warehouse
Discusses how to define a dataset from a Microsoft parallel data warehouse.
Creating Report Datasets from XML Data
Discusses how to define a dataset from XML data in an XML document, Web service, or Web application.
Creating Report Datasets from an Oracle Database
Discusses how to define a dataset from an Oracle database.
Creating Report Datasets from an SAP NetWeaver BI Data Source
Discusses how to define a dataset from an SAP NetWeaver Business Intelligence data source.
Creating Report Datasets from a Hyperion Essbase Data Source
Discusses how to define a dataset from a Hyperion Essbase data source.
Defining Report Datasets from a Teradata Database (Reporting Services)
Discusses how to define a dataset from a Teradata data source.
Creating Report Datasets for Other Data Sources
Discusses how to define a dataset using a data source from an OLE DB, ODBC, or other data provider.
Using Report Models as Data Sources
Discusses how to access a report model as a predefined dataset.
Working with Fields in a Report Dataset
Discusses general dataset field collection issues.