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. For more information, see Understanding Report Datasets.

To create a dataset, you must have defined an embedded or shared data source. For more information, see Creating a Report Data Source and Shared Data Sources and Embedded Data Sources. The data source type you choose determines some of the options available for a dataset, for example, the query type and the query designer. For more information about creating a dataset for a specific data source type, see the following topics:

To create a dataset

  1. 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.

  2. 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.

  3. In Data source, select the name of an existing shared data source, or click New to create a new embedded data source.

  4. 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.

  5. 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.

  6. 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.

  7. Click OK.

    The dataset and its field collection appear in the Report Data pane under the data source node.