In SQL Server Reporting Services, Dataset is an SQL Command used to retrieve the data from Data Source for Report creation. Dataset consists of SQL Command, parameters, filters etc. The 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 Embedded Dataset in SSRS 2014
Embedded Dataset: If you want to use the dataset in single reports then there is no point in creating shared dataset. In these situations, we create Embedded dataset in SSRS
Creating Embedded DataSet in SSRS 2014
After creating the New SQL Reporting Services project (SSRS project), the Report data tab looks like below. We already created Embedded Data Source in our previous article
To create Embedded dataset in SSRS, Right click on the Datasets folder present in the Report Data tab and select the Add Dataset option from the context menu.
Once you click on that option, it will open Dataset Properties window to configure the SSRS Dataset. Following are the properties inside the Data Source Properties window
- Name: Please specify 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 Embedded Data Source in SSRS
- Query Type: It allow 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.
TIP: If you want to use the already created Shared Dataset then, Please select the Use a Shared Dataset option and select the required dataset from the drop down list.
From the below screenshot you can observe that, We selected the Embedded Data Source as a data source (Created in Previous article) and we are selecting the Text type as Query type.
SQL Query we used in the above screenshot is:
FROM dbo.DimProduct as prod
dbo.DimProductSubcategory AS prodSubcat ON
prod.ProductSubcategoryKey = prodSubcat.ProductSubcategoryKey
dbo.DimProductCategory AS Prodcat ON
prodSubcat.ProductCategoryKey = Prodcat.ProductCategoryKey
dbo.FactInternetSales AS fact ON
fact.ProductKey = prod.ProductKey
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 allows you to change the column names as well.
Click ok to finish creating the Embedded Dataset in SSRS. Let us see in Datasets folder in the Report Data tab.
TIP: Now you can use the above shown columns in Report design
Thank You for Visiting our Blog