How to: Create a Dataset (Report Designer)
In Reporting Services, a dataset specifies a data source, a query that is the command to run against the data source to retrieve data, query parameters, filters, and the collection of fields that represents the result set. You can also specify data options, such as ,case, collation, kanatype, width, and accent, for the data retrieved from the data source. All reports require at least one dataset. You can create multiple datasets for use within a single report. However, each data region that you add to a report can accept data from just one dataset.
The syntax for specifying connection strings and queries varies considerably among the supported data sources. To create a dataset, you must know how to connect to the data source you want to use and how to build queries in the query language of the data source. For more information about supported data sources in Reporting Services, see Defining Report Data Sources and Connecting to a Data Source. For more information about the user interface, see SQL Server Query Designer User Interface.
The steps for creating a dataset vary depending on whether there are shared data sources defined in the project. If a shared data source is available, Report Designer specifies it in the New Dataset dialog box. If you want to use a different data source, provide values in the Data Source dialog box. For more information, see How to: Create or Edit a Report-Specific Data Source (Report Designer). If no shared data sources are available, Report Designer opens the Data Source dialog box so that you can specify a data source type and connection. Once the data source is selected, you can create a dataset.
To create a dataset
In Data view, from Dataset, select New Dataset. The Dataset dialog box opens.
Note
If the Data Source dialog box opens, you must specify a data source first. For more information, see Data Source (General Tab, Report Designer).
On the Query tab, in Name, type a name for the dataset.
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.
From Data Source, select a data source.
From Command Type, select the form that the query will take:
- Select Text to write a query using the query language of the data source.
- Select StoredProcedure to execute a stored procedure by name.
- Select Table to return all the fields in a relational database table.
In Query String, type the query, stored procedure, or table name. (You can leave Query String blank and build the query in Data view.)
In Timeout, type the number of seconds that the report server waits for a response from the database. The default value is 30 seconds. Timeout must contain a value greater than zero or be left empty. If it is empty, the query does not time out.
Click OK.
Note The Dataset dialog box includes several tabs that you can use to configure the dataset. The Data Options tab contains case sensitivity, collation, kanatype sensitivity, width sensitivity, and accent sensitivity options. By default, the data options indicate that the report server retrieves these settings from the data provider when the report runs. The Fields tab contains a list of fields in the dataset. It is automatically populated with fields from the query, but you can add additional database or calculated fields The Parameters tab is automatically populated with parameters from the query, and you can use this tab to add additional parameters. The Filters tab lists the filters that you can apply to the dataset.
See Also
Concepts
Defining Report Datasets for a SQL Server Relational Database
Defining Report Datasets for Analysis Services Multidimensional and Data Mining Prediction Data
Defining Report Datasets for Other Data Sources
Defining Report Datasets for Relational Data from an SAP Database
Defining Report Datasets for Relational Data from an Oracle Database