How to: Create a Dataset (Reporting Services)
In Reporting Services, a dataset is based on an existing a data source. A dataset specifies a query, query parameters, filters, and a field collection. You can also specify data options, such as case, collation, kanatype, width, and accent, for the data retrieved from the data source. An embedded dataset is defined in and used in a single report. A shared dataset is defined on a report server or SharePoint site and can be used by multiple reports.
For more information, see Creating a Report Dataset.
To create an embedded dataset
In the Report Data pane, right-click the name of the data source, and then click Add Dataset. The Query page of the Dataset Properties dialog box opens.
In Name, type a name for the dataset or accept the default name.
Note
The dataset name is used internally within the report. For clarity, it is recommended that the name of the dataset describe the data that the query returns.
In Data source, select the name of an existing shared data source, or click New to create a new embedded data source.
Select a Query type option. Options vary depending on the data source type.
Select Text to write a query using the query language of the data source.
Select Table to return all the fields in a relational database table.
Select StoredProcedure to run a stored procedure by name.
In Query, type the query, stored procedure, or table name. Alternatively, click Query Designer to open the graphical or text-based query designer tool, or Import to import the query from an existing report.
In a few cases, the field collection specified by the query can only be determined by running the query on the data source. For example, a stored procedure may return a variable set of fields in the result set. Click Refresh Fields to run the query on the data source and retrieve the field names that are needed to populate the dataset field collection in the Report Data pane. The field collection appears under the dataset node after you close the Dataset Properties dialog box.
In Timeout, type the number of seconds that the report server waits for a response from the database. The default value is 0 seconds. When the time out value is 0 seconds, the query does not time out.
Click OK.
The dataset and its field collection appear in the Report Data pane under the data source node.
To create a shared dataset
In Solution Explorer, right-click the Shared Datasets folder, and then click Add New Dataset.
The Query page of the Dataset Properties dialog box opens.
In Name, type a name for the dataset or accept the default name.
Note
The dataset name is used internally within the report. For clarity, it is recommended that the name of the dataset describe the data that the query returns.
In Data source, select the name of an existing shared data source. A shared dataset must be based on a shared data source.
Click Query Designer.
The query designer that is associated with the data source type opens.
Alternatively, you can click Import to import a query from an existing report.
Click Build a query, and then Click OK.
For more information, see Reporting Services Query Designers.
In Timeout, type the number of seconds that the report server waits for a response from the database. The default value is 0 seconds. When the time out value is 0 seconds, the query does not time out.
Click OK.
The dataset and its field collection appear in the Report Data pane under the data source node.
See Also
Concepts
Working with Fields in a Report Dataset
Using Report Models as Data Sources