Overview
A Dataset contains a query or stored procedure that retrieves the data to be rendered on your report. This topic will explore the two types of datasets and walk through creating a dataset that will be used to build our sample reports.
Explanation
A report contains one or more datasets that define the queries to retrieve the data for your report. When you add a dataset to a report, you can select a shared dataset or you can create a new one that will be stored inside of your report (an embedded dataset). As the name implies, a shared dataset can be defined once and used in multiple reports. Using shared datasets is a best practice.
Shared datasets must use a shared data source. When you add a shared dataset to your report, the shared data source that it uses is also added to your report. The advantage of the shared dataset is that the user doesn’t have to know the data source; he just needs to know how to navigate to the folder that contains the shared datasets and what data they provide. There is no need to understand SQL queries, MDX queries, and so on. Your IT department should create shared datasets to make your job easier; they will benefit from not having to respond to questions about how to get certain data.
To create a shared dataset, select New Dataset from either the Getting Started dialog or the New Report or Dataset dialog. Select the shared data source to use:
The above dialog will display shared data sources that you have used previously. You can click the Browse other data sources hyperlink to navigate to a data source that isn’t shown in the list.
Click the Create button to continue; expand the tables, views or stored procedures in the Database view then drag and drop the columns you want from their respective tables to the Selected fields area:
Click the Auto Detect button to the right of Relationships so the designer takes care of joining the tables for you. If you are comfortable with SQL you can click the Edit as Text button on the ribbon to just type in your query.
Click the Edit as Text button to see the generated query:
Click the exclamation point icon to run the query and see a sample of the results:
Save the dataset by clicking the diskette icon or selecting Save from the Report menu. You will be prompted with the familiar File..Save dialog; navigate to a folder in the Report Manager or a SharePoint document library to save the dataset:
Note that you can create a dataset that includes data at the lowest level of detail; i.e. without aggregations such as SUM or COUNT. This allows you to more easily reuse the dataset. Report Builder can easily calculate the counts and sums that your report may need. In addition your report will often need the details as well as the aggregations. If you create a dataset that only returns the aggregations, you will have limited opportunities for reusing the dataset.
When you create an embedded dataset, you select a data source that is already included in your report or you can create a new embedded data source. You then select your tables and columns in the query designer. There isn’t much difference between creating a shared dataset and an embedded one. However, there is a lot of benefit in creating shared datasets versus embedded ones.
Additional Information
- Take a look at Creating and Adding Datasets for additional details.
- Refer to the tip Getting Started with Report Builder 3.0 Shared Datasets.
