In SSRS, Dataset is an SQL Command used to retrieve the data from Data Source for Report creation. Dataset consists of SQL Command, parameters, filters etc. SQL Server Reporting Services supports two types of datasets: Shared Dataset and Embedded Dataset. In this article, we will show you, Steps involved in creating Shared Dataset in SSRS
Shared Dataset: If you want to share the same dataset to multiple reports then we have to create shared dataset. Following are the few uses of shared dataset:
- It can be created once and used in multiple reports.
- If you want to change the dataset, you can do it at once place and it will be reflected 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 below. We already created 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 select Add option and then select New Item option from the context menu.
This will open New window and from that window, you can select the New Dataset option
In this example, We selected the Add New Dataset option from the context menu. Once you click on that option, it will open 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 already created, Please select the data source from the drop-down list. If not, Please click on the New button to Create SSRS Shared Data Source
- Query Type: It allows us to choose from 3 options.
- Text: You have to write the SQL Query or use Query Designer
- Table: You have to select the table you want to use.
- Stored Procedure: You have to select the stored procedure you want to use.
- Query Designer: If you don’t know, How to write the SQL Queries then you can select this option. It will allow you to design using graphical user Interface. Please refer SSRS Query Designer article to understand the steps involved in it.
- Time out: It will allow you to specify the query execution time.
NOTE: To create shared dataset we require shared data source only.
From the below screenshot you can observe that We are selecting the Text type
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 SQL Inner Join article to understand the Inner Join.
You can check the available field by selecting the Fields option. It will also allow you to change the column names as well.
Click ok to finish creating the Shared Dataset in SSRS. Let us see in the solution explorer
Thank You for Visiting our Blog