Create a Shared Dataset or Embedded Dataset (Report Builder and SSRS)
Updated: October 17, 2016
Applies To: SQL Server 2016
Embedded datasets are for use in a single Reporting Services report. Shared datasets on a report server can be used by multiple reports, both mobile and paginated. To create a dataset, you need an embedded or shared data source.
Use Report Builder for the following tasks:
Create a shared dataset in Dataset Design View. Shared datasets must use published shared data sources.
Create an embedded dataset in Report Design View.
Save the dataset directly to the report server or SharePoint site.
Use Report Designer in SQL Server Data Tools (SSDT) for the following tasks:
Create a shared dataset in Solution Explorer. Shared datasets must use data sources from the Shared Data Sources folder in Solution Explorer.
Create an embedded dataset in the Report Data pane.
Optionally deploy the shared datasets and shared data source with the report. For each type of item, use Project Properties to specify paths to folders on the report server or SharePoint site.
For more information, see Report Embedded Datasets and Shared Datasets (Report Builder and SSRS).
Open Report Builder. The New report or dataset pane opens, as shown in the following figure:
If the New report or dataset pane does not appear, from the Report Builder button, click New.
In the left pane, under Create a dataset, click Shared Dataset.
In the right pane, click Browse to select a shared data source from the report server, and then click Create. The query designer associated with the shared data source opens.
In the query designer, specify the fields to include in the dataset.
Click Run (!) to run the query.
On the Report Builder button, click Save or Save As to save the shared dataset to the report server.
To exit Report Builder, click Report Builder, and then click Exit Report Builder. To work with reports, click Report Builder, and then click New or Open.
Open Report Builder.
Browse to the report server, and select the folder for the shared data source.
In Items of type, click Datasets (*.rsd) in the drop-down list.
Select the shared dataset, and then click Open. The associated query designer opens.
On the Ribbon, click Dataset Properties.
Click Parameters. On this page, set a default value to a constant or an expression, mark the parameter as read-only, nullable, or Omit From Query. For more information, see Report Parameters.
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.
The dataset name is used internally within the report. For clarity, we recommend that the name of the dataset describe the data that the query returns.
In Data source, browse to and 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 depend 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.
The dataset and its field collection appear in the Report Data pane under the data source node.
Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)
Dataset Fields Collection (Report Builder and SSRS)
Report Datasets (SSRS)
Data Connections, Data Sources, and Connection Strings (Report Builder and SSRS)
Embedded and Shared Datasets (Report Builder and SSRS)