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.

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.

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

 

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