Now that you have created a Share Data Source, it’s time to add some data to your project. In this post I will explain Shared Datasets and demonstrate how to add a one to your project.

As with a data source, you can create an embedded or shared dataset. The primary advantage to creating a shared dataset is that it can be used by all reports in a given Reporting Services project. In addition, you can deploy a shared dataset so that it is exposed to end-users via SharePoint or the Report Manager. I will explain this in later posts.

Regardless of the type of dataset, Shared or Embedded, the concept is still the same. A dataset specifies the data that will be used from a Data Source. The dataset includes a query that provides a list of fields that can be used in your report. If you plan on creating a shared dataset you must create a shared data source first. Visit the following link for more details:

http://msdn.microsoft.com/en-us/library/dd239331.aspx

Before you walk through the following steps I have prepared several stored procedures that will be used throughout this series. The most recent backup of the ContosoSchoolsDW includes them, however if you obtained an earlier version you will need to download and execute this query.

Watch the video if you prefer:

Creating a Shared Dataset

1. Open SQL Server Data Tools (SSDT).

2. Open the project that you created in the Creating a SQL Server Reporting Services Project blog. You can also download the project from here.

3. If the Solution Explorer is not open, go to View in the menu and select Solution Explorer.

4. Right-click the folder labeled Shared Datasets.

   

5. Select Add New Dataset from the menu that appears.

6. The Shared Dataset Properties window will open.

7. There are several choices available in the left navigation section. For now we will focus on Query and Parameters.

8. In the textbox labeled Name enter Get_SchoolsAbsencesAndTardies_aggr.

9. Ensure that SQL_ContosoSchoolDW is selected in the Data source drop down list.

10. Select the radio button labeled Stored Procedure from the Query type list.

11. Select Get_SchoolsAbsencesAndTardies_aggr from the Select or enter stored procedure name drop down list.

    

12. Select Parameters in the left navigation section. Notice that the DistrictAreaCode parameter has been added to the dataset. This is because the specified stored procedure includes the parameter in its definition.

13. Click OK and the Shared Dataset will be created.

14. Repeat steps 4 – 13, changing the Name specified in step 8 to Get_Districts and the Stored procedure selected in step 11 to Get_Districts.

    

In the next post, SSRS 101 – Creating your first Report and Adding Data Sources and Datasets, I will explain how to create a report and add a data source and data set.

Talk to you soon, Patrick LeBlanc, Microsoft, Technical Solutions Professional SQL Server and Business Intelligence

Founder www.sqllunch.com