Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

SSRS – How to Create a Data Source?

, 3 Mar 2014
Rate this:
Please Sign up or sign in to vote.
CodeProject In the last article on SSRS, we have discussed about adding and deploying Reports using Business Intelligence Development Studio(BIDS). You can read that articles 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

In the last article on SSRS, we have discussed about adding and deploying Reports using Business Intelligence Development Studio(BIDS). You can read that articles 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 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 Reports. The advantage of using Embedded Data Source is that we can use dynamic connection strings. 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.

ssrs1

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.

ssrs2

ssrs3

Give a name to the data source. The name should not 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.

ssrs4

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.

ssrs5

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 dialogue box.

ssrs6

ssrs7

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

ssrs8

ssrs9

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

ssrs10

In 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.

ssrs11

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

ssrs12

Alternatively, we can hard code the user name and password.

ssrs13

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.

ssrs14

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

ssrs15

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.

ssrs16

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

ssrs17

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.

ssrs18

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 belongs to.

ssrs19

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.

ssrs20

ssrs21

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

ssrs22

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.

ssrs23

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.

ssrs24

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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Arun Ramachandran India
Software Developer
India India
Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India. He has written over 95 articles on the subject on his blog at http://BestTEchnologyBlog.com. Along with 3 years of hands on experience he holds a Master of Computer Applications degree from Cochin University of Science & Technology (CUSAT).

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 3 Mar 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid