Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services SSRS 2017 Data Sources



By:
Overview

After installing SQL Server Reporting Services (SSRS), one of the first tasks that will need to be completed after the install is to establish the data sources that will be used in your report. Selecting the proper data source is then supplemented by establishing the correct setting for the data source connection including whether the data source will be shared among reports.

Explanation

Reporting Services contains an extensive list of available sources that can be used as the "data basis" for a report. This list contains everything from SQL Server to Oracle to Teradata to SharePoint. Generally, you would need a driver for the specific source you are attempting to connect to, many of which are ODBC or OLEDB based. The importance of all the fuss about data sources centers on the fact that the wider the data source list, the more likely that you can use SSRS as your central reporting tool no matter the source. In this section of our SSRS tutorial series, we will cover both the available data sources and the implementation of their use.

Available Data Sources

The list of available data sources for SSRS includes the following:

  • Microsoft SQL Server
  • Microsoft SQL Server Analysis Services – multi-dimensional and tabular
  • Microsoft Azure SQL Database
  • Oracle
  • SAP BW
  • Hyperion Essbase
  • Microsoft SharePoint List
  • Teradata
  • OLE DB
  • ODBC
  • XML

Additionally, custom data processing extensions can also be developed and used to attach to a variety of sources not available otherwise. The process of using a data source requires several keys pieces of information to make the connection to the data for the report after deciding on the source of the data. You will also need to determine the security needed to access the source and how the security will be implemented not just for the report development but also once implemented on the Report Server website. We will cover all these options later in the tip.

Creating a data source-Shared

The first step in the data source process is deciding on whether a shared or embedded data source will be used. The basic difference between the types is an embedded data source is used and only available to that report. A shared data source can be used by not only this report, but also any report that points to that same data source. These data sources must be published to the report server website; such a data source must be available for the reports to connect to.

Furthermore, consideration needs to be given to the use of 32 vs 64 data source connection. Furthermore, if specific data sources require a certain adapter installed, then that adapter must be installed on the machine where development is taking place AND on the web server which is housing the SSRS site. For instance, Teradata requires a specific set of Teradata Utilities to be installed including the Teradata ODBC driver.

To start our development process, you would open Visual Studio (VS 2017 SSDT BI in the below examples) and create a new SSRS project. Next, you would need to create a new report. To get started with defining a shared data source, you first just right click on the new data source folder in your project.

New Data Source - Description: New Data Source

This data source is connected to a particular project within Visual Studio, but once deployed to the SSRS web server, it can be shared among any report that has access to the data sources.

Next, we need to name the data source and then select the data source type.

Name and Type - Description: Name and Type

Subsequently, we define the connection specifics using the Edit button to the right of the Connection String box. These settings include the “address” of the server, any connection properties, and also the security considerations, as shown below.

Connection String - Description: Connection String
Authentication - Description: Authentication

Once you have completed the selection of the data source type, authentication and database, it is a good idea to test the connection (Test Connection Button) to validate the entered details.

test connection - Description: test connectioin.

You should remember the security that is setup on a shared data source will get conveyed to the SSRS site when the data source is deployed. You should note that the authentication method setup in the connection string setup is different than the credential properties available on the Credentials tab on the data source setup screen.  In its most basic form, the authentication method tells the data source what type of User ID and password to expect.  The credentials windows convey what user information should be passed to the authentication method.  You can select to use Windows Authentication, no Credentials, Prompt for Credentials, or use a set user ID and password. Thus, if you select the no security credentials option, illustrated next, then the current account running the SSRS service will be the account used to connect to the data source. Note, though, that these options can be changed at a later time and even when the data source has been deployed to the SSRS web site.

Some other important properties that you need to consider as you create your data source include:

  • the timeout parameters for your connection
  • packet sizes
  • connection retry properties
  • whether to keep the connection alive or connected ongoing
  • multiple active result sets
  • integrated security
data source properties - Description: data source properties

Creating a report data source-Embedded or Use Shared

The above data source that was created at the project level of the solution can be referenced within a report or as an alternative, you can embed a data source within the report itself. Of course, each method has advantages and disadvantages; shared project level data sources means there is one place to change a property whereas an embedded source allows for customized security and connection details. To get started, we first will right click on the data source folder in the Report Data pane within the report itself, as displayed below.

New Report data source - Description: New report data source.

The process to setup an embedded data source is exactly the same as for a shared data source and is illustrated below.

Embed Data Source - Description: Embed Data source

Instead of using the embedded option, a shared data source could easily be selected from the project list

Use Shared Data Source - Description: Use Shared Data Source

In either case, you can again adjust the credentials which get passed to either a shared or embedded data source by using the Credentials tab.

Deploying and Using on SSRS Website

When each shared data source is complete, it must be uploaded to the SSRS website either via the deploy option or they can be manually uploaded. Both options are illustrated below:

deploy data source - Description: deploy data source
manual upload - Description: manual upload

If using the deploy option, you will want to be sure to set the TargetDataSourceFolder and OverWrite options in the project properties.

project properties - Description: project properties
project properties detail - Description: project properties detail

Once the shared data sources are on the SSRS site, they can be edited using the Manage option (move and delete options are also available if needed.)

manage data source - Description: manage data source

Within the properties window, the data source name, type, connection string, and credentials can all be maintained.

online data source properties 1 - Description: online data source properties 1
online data source properties 2 - Description: online data source properties 2

Additionally, any subscriptions dependent on that data source can be reviewed as can any reports or shared data sets that use that data source and are dependent on it.

dependent subscriptions - Description: dependent subscriptions
dependent reports - Description: dependent reports

Finally, data source access can be restricted to only certain users or groups.

At the report level, the data source for a particular report can also be maintained by using the Manage option.

Manage Report - Description: Manage Report

Next, you would want to click on the Data Source option. As shown below, a shared data source is being used; the location noted is the location on the SSRS site.

shared data source - Description: shared data source

If an embedded data source is used (or if you would like to switch to an embedded data source), then the custom data source option can be selected. The custom option allows you to select the data source type, credentials to be used, and the connection string.  You will notice, however, that the connection string is a “free form” text box. Using this site: https://docs.microsoft.com/en-us/sql/reporting-services/report-data/data-connections-data-sources-and-connection-strings-report-builder-and-ssrs , you can get examples of possible connection strings to use or you can set up the data source in VS-SSDTBI, and then just copy the connection string generated into the text box.

Custom Data Source - Description: Custom Data Source

Now that we have created the connection to our data, we can use that data source to create data sets and parameters which will be the next part in this tutorial of tips.

Additional Information

Last Update: 12/29/2017




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools