SSRS Shared Data Source

In SSRS, Data Source (DS) is a connection setting that is used to connect with underlying Databases or Data Warehouses. The SSRS Shared data source is a combination of Provider name, Server instance Name, Database Name, and Database credentials.

The SQL Server Reporting Services supports two types of data sources: Shared Data Source and Embedded. In this article, we will show you, Steps involved in creating SSRS Shared Data Source.

SSRS Shared Data Source: If you want to share the same data source for multiple reports, report models, and subscriptions, then we have to create a shared data source. Following are the few uses of SSRS shared data source:

  • It can be created once and used in multiple reports.
  • If you want to change the data source, do it at once place, and it will reflect in all the supporting reports.

Creating SSRS Shared Data Source

After creating the New SQL Reporting Services project, the solution explorer looks like below.

TIP: Please refer to Creating Project article to understand the steps involved in creating a new project.

Solution Explorer 1

To create an SSRS shared data source, Right-click on the Shared Data Sources folder present in the solution explorer and select the Add New option from the context menu.

Add New Shared Data Source 2

Or you can choose the Add option and select the New Item option from the context menu. It opens a New window to select the New DS option.

Shared Data Source 3

In this example, We selected the Add New Data Source option from the context menu. Once you click on that option, it will open the Shared Data Source Properties window to configure the SSRS Shared Data Source. The following are the properties inside the Shared Data Source Properties window.

  • Name: Please specify a valid unique name for the DS you want to create.
  • Type: Please select the connection type from the drop-down list
  • Connection Strings: If you know, How to write the connection string, then write it down here. If not, click on the Edit button to specify the Provider, Server Instance Name, Login credentials, and Database Name.
Shared Data Source Properties 4

The following screenshot will show you the supporting types. For instance, if it is SQL Server, then select Microsoft SQL Server or OLE DB. If it is SQL Server Analysis Service, then select Microsoft SQL Server Analysis Services.

Choose the Connection Type 5

Click on the Edit button from the above screenshot will open another window to configure the Connection Properties. Here we have to specify the Data Source (Provider), Server Name (or Instance Name), Login credentials, and Database Name.

Connection Properties 6

In this SSRS Shared data source example,

  • Data Source: We are retrieving data from SQL Server, we selected the Microsoft SQL Server (SqlClient). You can change this option as per your requirement.
  • Server name: Please specify the SQL Server name. For now, we are using our local hostname.
  • Log on to the Server: Please specify, How you want to log into SQL Server. For now, we are using windows authentication.
Select the Database 7

From the above screenshot, you can observe that we are using selecting the [AdventureWorksDW2014] database from the list.

NOTE: Here, we work on the local database, so we used windows credentials. In real-time, you have to select the SQL Server authentication and provide the credentials given by the database admin people or your team leader.

Click on the Test Connection button provided below to check whether the connection is successful or not.

Test Shared Data Source Connection 8

Click Ok to finish configuring the connection properties.

SSRS Shared Data Source Connection String 9

Credentials Tab of the SSRS Shared Data Source:

  • Use Windows Authentication: It will use the current user’s windows credentials to connect. For now, We are using this option.
  • Use this User name and Password: Please specify the username and password to log into the server. The report will use this username and password to connect.
  • Prompt for credentials: When the report is a connection to the data source, it will prompt the given text (asking for credentials).
  • Do not use credentials: Report will not use any credentials to connect.
Shared Data Source Credentials 10

Click ok to finish creating the SSRS shared Data Source. Let us see in the solution explorer.

View Shared Data Source 11

Comments are closed.