SSRS - How to Create Shared Data Source


Shared Data Source :


Shared Data Source is data source connection that can be used by multiple reports. If our reports are pointing to the same database we should use Shared data Source that is connection string to database instead of creating separate data sources for each of the report. This can be a lot of work as we have to deploy each of them to the report server and also have to go to each of them and point to different servers or database depending upon the Environment( DEV, SIT, UAT, Production).

If we will create Shared Data Source , our all reports can use the same connection and we have to make change only for one data source on report server if we want to point to different Server.

Let's create our first Shared Data Source step by step

Step 1:
Go to Programs and then go to
Microsoft SQL Server 2008 --> Business Intelligence Development Studio
OR
Microsoft SQL Server 2012--> SQL Server Data Tools

Once BIDS or SSDT is open, Create new project as shown below

Step 2:
Right Click on Shared Data Source under project and choose Add New Data Source


Step 3:
Provide meaningful name to Data Source, Choose the type from the given list. In our case we have provided the Data Source Name =SDS_TestDB and we selected SQL Server as type. SSRS let you make connection to any database given in the list or you can use OBDC in case driver is not provider for the database.





The connection string is created to our database



Our Shared Data Source is ready to use. We will be using this Shared Data Source for all the reports those we will create in HOW TO SERIES.

1 comment: