Working in Shared Dataset Design View (Report Builder 3.0)
The Shared Dataset Design window helps you create a dataset query that you can share with others. The window lets you select a shared data source, specify properties for the shared dataset, and create a query in the query designer.
.gif "Report Builder Shared Dataset Design View")
For more information about working with data in a report, see Adding Data to a Report (Report Builder 3.0 and SSRS).
In This Article
The Ribbon
The Query Designer Surface
Viewing Query Results
Saving the Shared Dataset
The Ribbon
The Ribbon helps you quickly find the commands that you need to complete a task. Commands are organized into the following logical groups: Connection, Dataset, and Query Designer.
Connection
Use the Select button in the Connection group to select a shared data source in your report, or browse to a shared data source on the report server.
Note
A shared dataset must be based on a shared data source. If the data source that you need is not available, you must create one on the report server. For more information, see How to: Create, Delete, or Modify a Shared Data Source (Report Manager) in the Reporting Services documentation in SQL Server Books Online.
For more information, see Data Connections, Data Sources, and Connection Strings in Report Builder 3.0.
Dataset
Use the Set Options button to set shared dataset properties. These include the following:
Fields. You can add a field or edit a field in the field collection.
Data options. You can set options that affect match criteria and sort order, such as case sensitivity and collation.
Filters. You can define filters that limit the data in a report after it is retrieved from the data connection.
Parameters. You can add a parameter or edit parameter options. For example, you can specify a default value for each parameter so that you can create a cache refresh plan for this shared dataset on the report server.
The values that you set become part of the shared dataset definition on the report server. When a report author includes this shared dataset in a report, the options that you specify apply to that dataset instance.
After a shared dataset is added to a report, a report author can override the following options: collation, case sensitivity, accent sensitivity, kanatype sensitivity, width sensitivity, subtotals. They can also create additional dataset filters to limit the data in the report.
For more information, see Creating and Adding Datasets (Report Builder 3.0 and SSRS).
For more information about cache refresh plans, see Caching Shared Datasets (SSRS) in the Reporting Services documentation in SQL Server Books Online.
Query Designer
Use the query designer toolbar to help build a query that specifies which data to retrieve from the data connection. The toolbar that you see depends on the query designer that is associated with the data source type from the data connection.
For more information, see the topic that corresponds to the data source type in Adding Data from External Data Sources (SSRS) and Query Designers (Report Builder 3.0).
.gif "Arrow icon used with Back to Top link")
Back to Top
The Query Designer Surface
A query designer helps you to build a query in the syntax that is required by the external data source.
Some data source types provide a graphical query designer that you can use to explore the metadata on an external data source. You can interactively drag names from the metadata pane to the query design surface, or interactively select the names to use.
Some data source types support a text-based query designer that you can use to paste in queries that you have created in other tools, such as SQL Server Management Studio.
Each data source type has specific requirements for the query that will work against the external data source. For more information, see the topic that corresponds to the data source type in Adding Data from External Data Sources (SSRS) and Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.
Back to Top
Viewing Query Results
In shared dataset design view, you are building a query that will retrieve data from the data connection when the report is processed.
Run the query to see example data from the data connection to verify that the query returns the type of data that you expect. The columns in the result set come from the metadata for data schemas from the data connection. The column names become the dataset field collection. The values of the data that you see in the query result set is design time data. After you save the shared dataset as a shared dataset definition on the report server, only the query text is saved. The data in the query result set is not saved.
When a report author adds this shared dataset to a report, a pointer to the dataset definition on the report server is added. In the report, the dataset field collection appears in the Report Data pane. The query text is not available.
The credentials that you use to run a query are separate from the credentials that are used to preview a report or to run a report from the report server. For more information, see Specifying Credentials in Report Builder 3.0.
Running a Report with Parameters
When your query includes query variables, dataset parameters are created automatically for you. In turn, when you finish building the dataset query, report parameters that are set to dataset parameters are created automatically.
If a report contains parameters, all the parameters must have default values before the report can run automatically. If a parameter does not have a default value, when you run the report you must choose a value for the parameter, and then click View Report on the Run tab.
For more information, see Parameters (Report Builder 3.0 and SSRS).
Back to Top
Saving the Shared Dataset
To save the query that you built, on the Report Builder button, click Save or Save As. Navigate to the appropriate folder on the report server and save the shared dataset definition. The shared dataset is not available to others until you save it to the report server.
Back to Top