SSRS Tutorial: Part 4 - Embedded Data Sources

This article explains

• What are Data Sources.
• What are Embedded Data Sources.
• Create Embedded Data Sources using SQL Query.
• Create Embedded Data Sources using Stored procedure.
• Add multiple Data Sources.

Data Source

• Data Source is a connection setting which is used to connect with underlying Databases or Data Warehouses.
  
• Data source is a combination of Provider name, Server instance Name, Database Name and Database credentials.

SSRS supports two types of data sources:

• Shared Data Sources
• Embedded Data Sources

Embedded Data Source

• The Embedded data source can only be used by the report in which it is embedded.
  
  
• If any change occurs in server name, database name, logon credentials, etc, then you would need to open each report and update the affected data source.

Steps involved in creating Embedded Data Source in SSRS 2012

The Embedded data sources can only be used by the report in which it is embedded.

Step 1: Create Project.

Go to Start > All Programs > SQL Server 2012 > Microsoft SQL Server Data Tools
Once SSDT (SQL Server Data Tools) is running, click New Project to create a new project.
Select Report Server Project under Reporting Services and specify the name and location.

Step 2: Add Report.

Go to Solution Explorer - Right Click On Report, click on Add, then New Item.



Select Report and give it a meaningful name.



Step 3: Add Data Source.

Go to Report Data (Ctrl + Alt + D) and right click on Data Sources and click on Add Data Source.



Step 4: Provide Data Source properties.

Provide name of your embedded data source.

Select Embedded Connection radio button.

Click on Edit.



Step 5: Connection Properties.

After clicking on Edit, we have to provide the following Connection Properties in the following wizard.

• Provide the Server name.
• Select the "Use SQL Server Authentication" radio button.
• Enter the User name and Password in the password text box.
• Check the "Save my password" checkbox.
• Select the "Select or enter a database name:" radio button.
• Select the database to which you want to set the connection.
• Click on the "Test Connection" button to ensure the connection can be established.

Click Ok and then you will see the connection as follows.



Click Ok. That’s it. Embedded data source created at this point. Go to Report Data (Ctrl + Alt + D), there you can see the created data source.



If you want to addone more data source then go to Report Data (Ctrl + Alt + D) and right click on Data Sources and click on Add Data Source and do the same as above.