Shared Dataset in SSRS

In SSRS, Dataset is an SQL Command used to retrieve the data from Data Sources for Report creation. The dataset consists of SQL commands, parameters, filters, etc. SSRS supports two types of datasets: Shared Dataset and Embedded Dataset. In this article, we will show you the steps involved in creating a Shared Dataset.

SSRS Shared Dataset: If you want to share the same Dataset with multiple reports, then we have to create a shared Dataset. The following are a few uses of the shared Dataset:

  • It can be created once and used in multiple reports.
  • If you want to change the Dataset, you can do it in one place, and it will reflect in all the supporting reports.

Create a Shared DataSet in SSRS

After creating the New SQL Reporting Services project (SSRS project), the solution explorer looks like the one below. We already created a Shared Data Source in our previous article.

To create a shared dataset in SSRS, Right-click on the Shared Datasets folder present in the solution explorer. Next, select the Add New Dataset option from the context menu. Or you can choose the Add option and then select the New Item option from the context menu.

It will open a new window, and from that window, you can select the New Dataset option

Add New Shared DataSet in SSRS 2

In this example, We selected the Add New Dataset option from the context menu. Once you click on that option, it will open the Shared Dataset Properties window to configure the SSRS Dataset. Following are the properties inside the Shared Data Source Properties window

  • Name: Please specify a valid unique name for the Dataset you want to create.
  • Data Source: If you have already created it, Please select the data source from the drop-down list. If not, please click on the New button to Create a Shared Data Source.
  • Query Type: It allows us to choose from 3 options.
    • Text: You have to write the Query or use Query Designer
    • Table: Select the table you want to use for this Shared Dataset.
    • Stored Procedure: You have to select the stored procedure you want to use.
  • Query Designer: If you don’t know, How to write the Queries, then you can select this option. It will allow you to design using a graphical user interface. Please refer to the Query Designer article to understand the steps involved in it.
  • Time out: It allows you to specify the query execution time.

NOTE: To create a shared Dataset in SSRS, we require a shared data source only.

From the below screenshot, you can observe that We are selecting the Text type

Query 4

SQL Query we used in the above screenshot is:

SELECT DimCustomer.FirstName, 
       DimCustomer.LastName, 
       DimCustomer.EmailAddress, 
       FactInternetSales.SalesAmount, 
       FactInternetSales.TaxAmt 
FROM DimCustomer 
   INNER JOIN 
     FactInternetSales ON 
DimCustomer.CustomerKey = FactInternetSales.CustomerKey

TIP: Please refer Inner Join article to understand the Inner Join.

You can check the available fields by selecting the Fields option. It will also allow you to change the column names as well.

SSRS Shared DataSet Columns 5

Click ok to finish creating the Shared Dataset. Let us see in the solution explorer.

SSRS Shared DataSet 6