Embedded Dataset in SSRS

In SQL Server Reporting Services, Dataset is a command used to retrieve the data from Data Source for Report creation. SSRS Embedded Dataset consists of transact SQL query, parameters, filters, etc.

The SQL Server Reporting Services supports two types of datasets: Shared and Embedded Dataset. In this article, we will show you the steps involved in creating Embedded Dataset in SSRS with an example.

Embedded: If you want to use the Dataset in single reports, then there is no point in creating a shared. In these situations, we create Embedded Dataset in SSRS.

Creating Embedded DataSet in SSRS

After creating the New Reporting Services project, the Report data tab looks like the one below. We already created Embedded Data Source in our previous article.

Folders in Solution Explorer 1

To create an Embedded one, Right-click on the Datasets folder present in the Report Data tab and select the Add option from the context menu.

Add DataSet Option 2

Once you click on that option, it will open the Properties window to configure the SSRS Dataset. The following are the properties inside the SSRS Embedded Dataset 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 the New button to Create an Embedded Data Source.
  • Query Type: It allows us to choose from 3 options.
    • Text: You have to write the 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 Queries, 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: Specify the query execution time.
SSRS Embedded DataSet Properties 3

TIP: If you want to use the already created Shared Dataset, Please select the Use a Shared Dataset option and select the required one from the drop-down list.

From the below screenshot, we have selected the Embedded Data Source as a data source, which we created in the previous article. Next, we choose the Text type as the Query type.

SQL Query 5

SQL Query we used in the above screenshot is:

SELECT prod.EnglishProductName, 
       prod.Color, 
       Prodcat.EnglishProductCategoryName, 
       prodSubcat.EnglishProductSubcategoryName, 
       fact.SalesAmount, 
       fact.TaxAmt 
FROM dbo.DimProduct as prod 
  INNER JOIN 
dbo.DimProductSubcategory AS prodSubcat ON 
prod.ProductSubcategoryKey = prodSubcat.ProductSubcategoryKey 
  INNER JOIN  
dbo.DimProductCategory AS Prodcat ON 
prodSubcat.ProductCategoryKey = Prodcat.ProductCategoryKey 
  INNER JOIN  
dbo.FactInternetSales AS fact ON 
fact.ProductKey = prod.ProductKey

TIP: Please refer to the 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.

Available Columns 6

Click ok to finish creating the Embedded Dataset in SSRS. Let us see it in the folder in the Report Data tab.

SSRS Embedded DataSet 7

TIP: Now, you can use the above-shown columns in the Report design