SSRS – How to Create a Data Source?

In the last article on SSRS, we discussed about adding and deploying Reports using Business Intelligence Development Studio(BIDS). You can read that article here. In this article, we will go over Creating Data Sources in SSRS.

To display data in our Report, we need one or more data sources. A data source is actually a connection string. The content of the string depends on the data provider we use.

Connection String

• Syntax of the connection string is specific to data provider.
• It describes the location of the data for Reports.
• It contains optional keywords for authentication.

In Reporting services, the data source file also specifies the authentication method to use when establishing a connection. We need to specify whether the Report uses Windows Authentication or others.

Authentication Properties

There are 4 types of authentication properties available in SSRS. They are the following:

• Windows integrated security
• Specific user credentials
• Prompted credentials
• No credentials

There are different types of data sources the Reporting Services support. Probably the most common type of data we use in Reports are Relational Data. Reporting Services support a wide range of Relational Data Sources which are given below:

• SQL Server
• SQL Azure
• Parallel Data Warehouse
• Oracle
• TERADATA
• OLE DB
• ODBC

We can also use Multidimensional Data in Reports using any of the below data providers.

• Analysis Services
• SAP Netweaver BI
• Hyperion Essbase

We can also use XML Data and SharePoint Lists as data sources for Reports.

There are majorly 2 types of Data Sources:

1. Shared Data Sources

Typically, we use a Shared Data Source to define the connection information if the project contains many Reports. If connection information changes later such as move a source database to a different server, the administrator can update the definition once and all the associated Reports get the data from the correct location.

2. Embedded Data Sources

In this case, we don’t create a separate data source file, but create a data source definition inside of each Report. The advantage of using Embedded Data Source is that we can use dynamic connection strings. The disadvantage is that if we need to make change to the connection string, we need to open every Report and edit the connection string which will be a very tedious process if there are a lot of Reports.

Now let’s have a quick look at how to create Data Sources in SQL Server Reporting Services(SSRS).

First of all, create a new Report Project and a new Report, Report1 which we have already discussed in the previous article.

Now we are going to add a Shared Data Source for MyDataSource. In Solution Explorer, we can see the Shared Data Sources folder. Right click on that and select Add New Data Source.

Give a name to the data source. The name should not be embedded with a space and it has to start with a letter. It is always a good practice to give the same name as that of the database to make it easy to understand.

Now, we need to give Microsoft SQL Server data provider for this data source which is default. But we can see all the available data providers in the drop down list.

If we know the connection string syntax, click on the Connection string box and type out the string or we can use the Edit button to use the Connection Properties dialog box.

Give a server name and in the drop down list below, select a database name and click OK.

Now, we can see the .rds file in the Solution Explorer.

At a later point of time, if we need to edit the data source, we can just double click on the file and edit the connection string.

Now let’s take a quick look at the Credentials page. Here, we can see the default as Windows Integrated Security.

Alternatively, we can hardcode the user name and password.

Another option is Prompt for credentials. If we select this option, we can enter some text which will be displayed to the users to let them know that the credentials are needed.

If the data source doesn’t require any credentials, we can select the last option, Do not use credentials.

So at this point, we have a Shared Data Source, but it is not yet associated with Reports in the project. Let’s try to associate this Shared Data Source with the existing Report, Report1.

If the Report is in the opened stage, we can access the Report Data Pane that appears over left, next to the Toolbox.

If it is not seen, we can use View menu to get the Report Data option at the bottom.

Report Data Pane has a Data Sources folder in it. Here we can add a reference to the Shared Data Source or an Embedded Data Source. So right click on the folder and select Add Data Source.

Now we need to provide a name to the data source reference. Here, we are writing the same name as that of the data source to avoid confusions.

We can create an Embedded connection here if needed. But remember that an Embedded Data Source can only be changed by opening the Report that it belongs to.

So instead, we use the best practice method of reference the Shared Data Source. If we don’t already have a Shared Data Source in the project, a New button is there which allows to create a Shared Data Source. Instead, we use the drop down list which shows all of the available data sources in the project.

Before clicking OK button, notice in the Report Data Pane. As of now, there is nothing in the Data Sources folder.

When we click OK button, a reference is added to the Data Sources folder. But remember that this is only a reference to the Shared Data Source.

Notice the arrow icon which indicates that this is a Shared Data Source. In Embedded Data Source reference, there will not be any arrow icon like this.

In the next article, we will discuss about Creating Data Sets in SSRS.

Reference

• Arun Ramachandran (http://BestTEchnologyBlog.Com)