SSRS Tutorial: Part 4 - Embedded Data Sources

Introduction

Before reading this article, I highly recommend reading the previous part of the series:

• SSRS Tutorial: Part 1 - Create A Simple Report
• SSRS Tutorial: Part 2 - Shared Data Sources
• SSRS Tutorial: Part 3 - Shared Datasets

This article explains

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

Data Source in SSRS

• Data Source is a connection setting with underlying Databases or Data Warehouses.
• Data source combines the 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 the server name, database name, login credentials, etc., you must 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 a Project

Go to Start > All Programs > SQL Server 2012 > Microsoft SQL Server Data Tools.

Once SSDT (SQL Server Data Tools) runs, 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), right-click on Data Sources, and Add Data Source.

Step 4. Provide Data Source properties

Provide the name of your embedded data source.

Select the Embedded Connection radio button.

Click on Edit.

Step 5. Connection Properties

After clicking Edit, we must 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.

Go to Report Data (Ctrl + Alt + D) to see the built data source. Click Ok. That's it—embedded data source created at this point.

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

Conclusion

In this article, we learned about  Embedded Data Sources and how to create an embedded data source using Stored Procedure in SQL Server.