SSRS Tutorial Part 2 – Data Source Configuration in SSRS – Reporting services

Last modified date:

We took a look at creation of project and empty report in the BI tool SSDT (SQL Server Data tools) in previous part of the miniseries (Part 1 -SSRS project and report creation). This set up the ground for this part in which we will configure the report for data tools (SQL Server database in our case). We will find out how to configure data source in SSRS.

How to Configure Data Source in SSRS

Previous part ended by having correctly created project and empty report, as seen in the screenshot:

This is how your project should look like now. Since the report extracts data from a database, it is about time to configure connection of the report to the Report data source database. You can see it in the higher left part of the picture.

Configuration of shared (project) Data source in SSRS

We create lasting connection of report to the data source by creating one. Data source will be the SQL database. There are 2 options how to create a source:

  1. Individual Data source for a report – There is an option to set any number of data source above each report. If you create it above the report, you will not be able to use it for another one.
  2. Shared data source – In this case, we create data source shared for the whole project. This is the more sensible option in most of the cases. If you expect to have more reports in a project that will use the same database, it would be really annoying to define individual data source for each and every report. It is much more comfortable to set the source just once. Then only create reference in the report.

SSRS data source

[1] Individual and shared data source explanation

Let’s be sensible and create shared project data source to which we will set reference afterwards. When in the solution explorer, click on Shared Data sources – Add new data source. In the next screen, name your data source and click on Edit.

SSRS sdílený data source

[2] Shared data source creation

[3] Naming the data source

 

 

 

 

 

 

 

 

 

Here the actual connection configuration to the database takes place. We firstly select the SQL instance. My instances are named “MOJEINSTANCE”. Then we select database from which we will extract the data [4]. After the configuration it is suitable to test whether the connection to the database works. You can do that by clicking on Test connection [5]. If everything is OK, save the data source and it is done. You should see it in the solution explorer [6].

[4] Creating connection – SQL Instance a database

[5] Test connection

[6] Final Data source

 

 

 

 

 

 

 

 

 

 

Setting Reference from Shared to Report Data Source

We have set up shared dataset to which we can create a reference in any report inside the project. Last formality to go through is adding the reference in our report to shared Data source. Above report, click on Data source – Add new data source [7]. Name the data source (caution – no spaces) and use option “Use shared data source reference” [8]. The result should be a view on BI project identical to the one in the picture [9].

[7] Creating data source for the report

[8] Reference to a Shared Dataset in our project

[9] Final reference to shared (project) data source

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Finance math in Excel SSRS - Reporting Services

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.