Data Connections, Data Sources, and Connection Strings in Report Builder
To include data in a report, you create data connections and datasets. A data connection includes information about how to access an external source of data. A dataset includes a query command that specifies which data to include by using the data connection.
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.
Connection Dialog Box Use the Connection Dialog Box to build a connection string or to paste a connection string.
Data connection information The connection string is passed to the data extension.
Credentials Credentials are managed separately from the connection string.
Data Extension/Data Provider Connecting to the data can be through multiple data access layers.
External data sources Retrieve data from relational databases, multidimensional data bases, SharePoint lists, Web services, or report models.
For more information, see Embedded and Shared Data Connections or Data Sources (Report Builder and SSRS) and Data Connections, Data Sources, and Connection Strings in Reporting Services.
Data can also be included in a report by using predefined shared data sources, shared datasets, and report parts. These items already have the data connection information that you need. For more information, see Add Data to a Report (Report Builder and SSRS).
Note
You can create and modify report definitions (.rdl) 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. For more information, see Designing Reports in Report Designer and Report Builder (SSRS) on the Web at microsoft.com.
Connection String Examples
A data connection includes a connection string that is typically provided by the owner of the external data source. The following table lists examples of connections strings for different types of external data sources.
| Data source | Example | Description |
|---|---|---|
| SQL Server database on the local server | data source="(local)";initial catalog=AdventureWorks2012 |
Set data source type to SQL Server. |
| SQL Server instance database | Data Source=localhost\MSSQL12.InstanceName; Initial Catalog= AdventureWorks2012 |
Set data source type to SQL Server. |
| SQL Server Express database | Data Source=localhost\MSSQL12.SQLEXPRESS; Initial Catalog= AdventureWorks2012 |
Set data source type to SQL Server. |
| Analysis Services database on the local server | data source=localhost;initial catalog=Adventure Works DW 2012 |
Set data source type to SQL Server Analysis Services. |
| SharePoint List | data source=http://MySharePointWeb/MySharePointSite/ |
Set data source type to SharePoint List. |
| Report Models | Not applicable. | You do not need a connection string for a report model. In Report Builder, browse to the report server and select the .smdl file that is the report model. |
| Oracle server | data source=myserver |
Set the data source type to Oracle. The Oracle client tools must be installed on the Report Builder computer and on the report server. |
| SAP NetWeaver BI data source | DataSource=http://mySAPNetWeaverBIServer:8000/sap/bw/xml/soap/xmla |
Set the data source type to SAP NetWeaver BI. |
| Hyperion Essbase data source | Data Source=https://localhost:13080/aps/XMLA; Initial Catalog=Sample |
Set the data source type to Hyperion Essbase. |
| Teradata data source | data source= <NN>.<NNN>.<NNN>.<N> ; |
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. |
| Teradata data source | Database= <database name> ; data source= <NNN*>.<NNN>.<NNN>.<NNN>*;Use X Views=False;Restrict to Default Database=True |
Set 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 service | data source=http://adventure-works.com/results.aspx |
Set the data source type to XML. The connection string is a URL for a web service that supports Web Services Definition Language (WSDL). |
| XML data source, XML document | https://localhost/XML/Customers.xml |
Set the data source type to XML. The connection string is a URL to the XML document. |
| XML data source, embedded XML document | Empty | Set the data source type to XML. The XML data is embedded in the report definition. |
For more information about each connection type, see Add Data from External Data Sources (SSRS) and Data Sources Supported by Reporting Services (SSRS).
Creating Data Sources
To create an embedded data source, you must have a connection string and the credentials that you need to access the data. This information usually comes from the owner of the data source. The data connection is saved in the report definition as part of the data source. Credentials are managed independently from the connection. For step-by-step instructions, see Add and Verify a Data Connection or Data Source (Report Builder and SSRS).
Note
Some types of credentials might not support all the scenarios that Report Builder uses: to run a query in the query designer, preview a report from your computer when you are not connected to a report server, and run the report from the report server. We recommend that you use shared data sources whenever possible. You can store credentials for a shared data source on the report server. For more information, see Specify Credentials in Report Builder.
To create a shared data source, you must use Report Manager to create the data source directly on the report server, or use an authoring environment such as Report Designer in SQL Server SQL Server Data Tools (SSDT). For more information, see Create an Embedded or Shared Data Source (SSRS).
See Also
Add Data to a Report (Report Builder and SSRS)
Report Parts (Report Builder and SSRS)