Data Connections, Data Sources, and Connection Strings (Report Builder and SSRS)

 

Updated: August 26, 2016

Applies To: SQL Server 2016

To include data in Report Builder and Reporting Services paginated reports, you must first create data sources and datasets. This topic explains the type of data sources, how to create data sources, and important information related to data source credentials. A data source includes the data source type, connection information, and the type of credentials to use. There are two types of data sources: embedded and shared. An embedded data source is defined in the report and used only by that report. A shared data source is defined independently from a report and can be used by multiple reports. For more information, see Embedded and Shared Data Connections or Data Sources (Report Builder and SSRS) and Embedded and Shared Datasets (Report Builder and SSRS).

Applies to:  Report Builder | Reporting Services Native mode | Reporting Services SharePoint mode
System_CAPS_ICON_note.jpg Note


You can create and modify paginated report definition (.rdl) files in Report Builder and in Report Designer in SQL Server Data Tools. Each authoring environment provides different ways to create, open, and save reports and related items.

Shared data sources are useful when you have data sources that you use often. It is recommended that you use shared data sources as much as possible. They make reports and report access easier to manage, and help to keep reports and the data sources they access more secure. If you need a shared data source, ask your system administrator to create one for you.

An embedded data source is a data connection that is saved in the report definition. Embedded data source connection information can be used only by the report in which it is embedded. To define and manage embedded data sources, use the Data Source Properties dialog box.

The difference between the embedded and shared data sources is in how they are created, stored, and managed.

  • In Report Designer, create embedded or shared data sources as part of a SQL Server Data Tools (SSDT) project. You can control whether to use them locally for preview or to deploy them as part of the project to a report server or SharePoint site. You can use custom data extensions that have been installed on your computer and on the report server or SharePoint site where you deploy your reports.

    System administrators can install and configure additional data processing extensions and .NET Framework data providers. For more information, see Data Processing Extensions and .NET Framework Data Providers (SSRS).

    Developers can use the Microsoft.ReportingServices.DataProcessing API to create data processing extensions to support additional types of data sources.

  • In Report Builder, browse to a report server or SharePoint site and select shared data sources or create embedded data sources in the report. You cannot create a shared data source in Report Builder. You cannot use custom data extensions in Report Builder.

The following table summarizes the differences between embedded and shared data sources.

DescriptionEmbedded

Data Source
Shared

Data Source
Data connection is embedded in the report definition.Available
Pointer to the data connection on the report server is embedded in the report definition.Available
Managed on the report serverAvailableAvailable
Required for shared datasetsAvailable
Required for componentsAvailable

Default data extensions in Reporting Services include the following types of data connections:

  • Microsoft SQL Server

  • Microsoft SQL Server Analysis Services

  • Microsoft SharePoint List

  • Azure SQL Database

  • Microsoft SQL Server Parallel Data Warehouse

  • OLE DB

  • Oracle

  • SAP NetWeaver BI

  • Hyperion Essbase

  • Teradata

  • XML

  • ODBC

  • Microsoft BI Semantic Model for Power View: On a SharePoint site that has been configured for a Power Pivot gallery and Power View, this data source type is available. This data source type is used only for Power View presentations. For more information, see Building the Perfect BI Semantic Tabular Models for Power View.

For a full list of data sources and versions Reporting Services supports, see Data Sources Supported by Reporting Services (SSRS).

rs_DataSourcesStory

  1. Data sources in the Report Data pane A data source appears in the Report Data pane after you create an embedded data source or add a shared data source.

  2. Connection Dialog Box Use the Connection Dialog Box to build a connection string or to paste a connection string.

  3. Data connection information The connection string is passed to the data extension.

  4. Credentials Credentials are managed separately from the connection string.

  5. Data Extension/Data Provider Connecting to the data can be through multiple data access layers.

  6. External data sources Retrieve data from relational databases, multidimensional data bases, SharePoint lists, Web services, or report models.

Connection strings are the text representation of connection properties for a data provider. The following table lists examples of connections strings for various data connection types.

System_CAPS_ICON_note.jpg Note


Connectionstrings.com is another resource to get examples for connection strings.

Data sourceExampleDescription
SQL Server database on the local serverdata source="(local)";initial catalog=AdventureWorksSet data source type to Microsoft SQL Server. For more information, see SQL Server Connection Type (SSRS).
SQL Server database on the local serverdata source="(local)";initial catalog=AdventureWorksSet data source type to Microsoft SQL Server.
SQL Server Instance

database
Data Source=localhost\MSSQL13.<InstanceName>; Initial Catalog=AdventureWorksSet data source type to Microsoft SQL Server.
SQL Server Express databaseData Source=localhost\MSSQL13.SQLEXPRESS; Initial Catalog=AdventureWorksSet data source type to Microsoft SQL Server.
SQL Database in the cloudData Source=<host>;Initial Catalog=AdventureWorks; Encrypt=TrueSet data source type to Windows Azure SQL Database. For more information, see SQL Azure Connection Type (SSRS).
SQL Server Parallel Data WarehouseHOST=<IP address>;database= AdventureWorks; port=<port>Set data source type to Microsoft SQL Server Parallel Data Warehouse. For more information, see SQL Server Parallel Data Warehouse Connection Type (SSRS).
Analysis Services database on the local serverdata source=localhost;initial catalog=Adventure Works DWSet data source type to Microsoft SQL Server Analysis Services. For more information, see Analysis Services Connection Type for MDX (SSRS) or Analysis Services Connection Type for DMX (SSRS).
Analysis Services tabular model database with Sales perspectiveData source=<servername>;initial catalog= Adventure Works DW;cube='Sales’Set data source type to Microsoft SQL Server Analysis Services. Specify perspective name in cube= setting. For more information, see Perspectives (SSAS Tabular).
Report model data source on a report server configured in native modeServer=http://myreportservername/reportserver; datasource=/models/Adventure WorksSpecify the report server or document library URL and the path to the published model in the report server folder or document library folder namespace. For more information, see Report Model Connection (SSRS).
Report model data source on a report server configured in SharePoint integrated modeServer=http://server; datasource=http://server/site/documents/models/Adventure Works.smdlSpecify the report server or document library URL and the path to the published model in the report server folder or document library folder namespace.
SQL Server 2000 Analysis Services serverprovider=MSOLAP.2;data source=<remote server name>;initial catalog=FoodMart 2000Set the data source type to OLE DB Provider for OLAP Services 8.0.

You can achieve a faster connection to SQL Server 2000 Analysis Services data sources if you set the ConnectTo property to 8.0. To set this property, use the Connection Properties dialog box, Advanced Properties tab.
Oracle serverdata source=myserverSet the data source type to Oracle. The Oracle client tools must be installed on the Report Designer computer and on the report server. For more information, see Oracle Connection Type (SSRS).
SAP NetWeaver BI data sourceDataSource=http://mySAPNetWeaverBIServer:8000/sap/bw/xml/soap/xmlaSet the data source type to SAP NetWeaver BI. For more information, see SAP NetWeaver BI Connection Type (SSRS).
Hyperion Essbase data sourceData Source=http://localhost:13080/aps/XMLA; Initial Catalog=SampleSet the data source type to Hyperion Essbase. For more information, see Hyperion Essbase Connection Type (SSRS).
Teradata data sourcedata source=<NNN>.<NNN>.<NNN>.<NNN>;Set the data source type to Teradata. The connection string is an Internet Protocol (IP) address in the form of four fields, where each field can be from one to three digits. For more information, see Teradata Connection Type (SSRS).
Teradata data sourceDatabase= <database name> ; data source= <NNN>.<NNN>.<NNN>.<NNN>;Use X Views=False;Restrict to Default Database=TrueSet the data source type to Teradata, similar to the previous example. Only use the default database that is specified in the Database tag, and do not automatically discover data relationships.
XML data source, Web servicedata source=http://adventure-works.com/results.aspxSet the data source type to XML. The connection string is a URL for a web service that supports Web Services Definition Language (WSDL). For more information, see XML Connection Type (SSRS).
XML data source, XML documenthttp://localhost/XML/Customers.xmlSet the data source type to XML. The connection string is a URL to the XML document.
XML data source, embedded XML documentEmptySet the data source type to XML. The XML data is embedded in the report definition.
SharePoint Listdata source=http://MySharePointWeb/MySharePointSite/Set data source type to SharePoint List.

If you fail to connect to a report server using localhost, check that the network protocol for TCP/IP protocol is enabled. For more information, see Configure Client Protocols.

For more information about the configurations needed to connect to these data source types, see the specific data connection topic under Add Data from External Data Sources (SSRS) or Data Sources Supported by Reporting Services (SSRS).

If you configure your ODBC or SQL data source to prompt for a password or to include the password in the connection string, and a user enters the password with special characters like punctuation marks, some underlying data source drivers cannot validate the special characters. When you process the report, the message "Not a valid password" may indicate this problem. If changing the password is impractical, you can work with your database administrator to store the appropriate credentials on the server as part of a system ODBC data source name (DSN). For more information, see "OdbcConnection.ConnectionString" in the .NET Framework SDK documentation.

Expression-based connection strings are evaluated at run time. For example, you can specify the data source as a parameter, include the parameter reference in the connection string, and allow the user to choose a data source for the report. For example, suppose a multinational firm has data servers in several countries. With an expression-based connection string, a user who is running a sales report can select a data source for a particular country before running the report.

The following example illustrates the use of a data source expression in a SQL Server connection string. The example assumes you have created a report parameter named ServerName:

="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks"  

Data source expressions are processed at run time or when a report is previewed. The expression must be written in Visual Basic. Use the following guidelines when defining a data source expression:

  • Design the report using a static connection string. A static connection string refers to a connection string that is not set through an expression (for example, when you follow the steps for creating a report-specific or shared data source, you are defining a static connection string). Using a static connection string allows you to connect to the data source in Report Designer so that you can get the query results you need to create the report.

  • When defining the data source connection, do not use a shared data source. You cannot use a data source expression in a shared data source. You must define an embedded data source for the report.

  • Specify credentials separately from the connection string. You can use stored credentials, prompted credentials, or integrated security.

  • Add a report parameter to specify a data source. For parameter values, you can either provide a static list of available values (in this case, the available values should be data sources you can use with the report) or define a query that retrieves a list of data sources at run time.

  • Be sure that the list of data sources shares the same database schema. All report design begins with schema information. If there is a mismatch between the schema used to define the report and the actual schema used by the report at run time, the report might not run.

  • Before publishing the report, replace the static connection string with an expression. Wait until you are finished designing the report before you replace the static connection string with an expression. Once you use an expression, you cannot execute the query in Report Designer. Furthermore, the field list in the Report Data pane and the Parameters list will not update automatically.

Create, Modify, and Delete Shared Data Sources (SSRS)
Create and Modify Embedded Data Sources
Set Deployment Properties (Reporting Services)
Specify Credential and Connection Information for Report Data Sources

Community Additions

ADD
Show: