An embedded dataset is defined in and used by only the report it is embedded in. It can be used when you want to get data from an external data source to be used only in one report.
Embedded datasets are useful when you want to create a query that has no other dependencies and that you do not need to use for multiple reports.
Lets see the how to create an Embedded data set in SSRS.
As you can see, here we have sample report and that have an embedded data source.
If you do not know how to create an embedded data source then you can refer post Create an Embedded data source in SSRS.
Create an Embedded Data Set
Lets right click on Datasets folder, as show
Once you click on Add Dataset.., a Dataset Properties window opens.
[Also Read.. Create a Shared Data Set in SSRS]
Now you need to provide following details:
- Name: specify name for the Dataset you want to create.
- Data Source: If you have already created a Data source, then you can select an existing data source from the drop down list. If not then you can create new by clicking on New button, refer post Create an Embedded Data Source in SSRS.
- Query Type: It gives you three option as follows.
- Text: this option allows you to write the T-SQL query.
- Table: this option allows allows you to select the table that you want to use for data set.
- Stored Procedure: this option allows you to select or enter the Stored Procedure that you want to use.
- Time out: When you select either Table or Stored Procedure option, you will see the Time Out option gets enabled for them, here you can specify the query execution time.
Now click on Fields tab, here you can see available fields.
You can also add new fields in data set by clicking on Add button.
You can also delete an existing fields by selecting field then click on delete button. Also can Move up and Move down selected Item.
After that click on Ok button.
Now you can see Embedded data set is created inside DataSets folder. You can see data set DataSet_Demo and data fields.