By: Scott Murray
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.
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.
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.
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.
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
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.
The process to setup an embedded data source is exactly the same as for a shared data source and is illustrated below.
Instead of using the embedded option, a shared data source could easily be selected from the project list
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:
If using the deploy option, you will want to be sure to set the TargetDataSourceFolder and OverWrite options in the project properties.
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.)
Within the properties window, the data source name, type, connection string, and credentials can all be maintained.
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.
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.
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.
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.
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
- Implementing a Data Source Processing Extension - Implementing a Data Processing Extension - https://docs.microsoft.com/en-us/sql/reporting-services/extensions/data-processing/data-processing-extensions-overview
