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.

  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.

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

System_CAPS_ICON_note.jpg 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.

rs_DataSourcesStory

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 sourceExampleDescription
SQL Server database on the local serverdata source="(local)";initial catalog=AdventureWorks2012Set data source type to SQL Server.
SQL Server instance databaseData Source=localhost\MSSQL12.InstanceName; Initial Catalog= AdventureWorks2012Set data source type to SQL Server.
SQL Server Express databaseData Source=localhost\MSSQL12.SQLEXPRESS; Initial Catalog= AdventureWorks2012Set data source type to SQL Server.
Analysis Services database on the local serverdata source=localhost;initial catalog=Adventure Works DW 2012Set data source type to SQL Server Analysis Services.
SharePoint Listdata source=http://MySharePointWeb/MySharePointSite/Set data source type to SharePoint List.
Report ModelsNot 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 serverdata source=myserverSet 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 sourceDataSource=http://mySAPNetWeaverBIServer:8000/sap/bw/xml/soap/xmlaSet the data source type to SAP NetWeaver BI.
Hyperion Essbase data sourceData Source=http://localhost:13080/aps/XMLA; Initial Catalog=SampleSet the data source type to Hyperion Essbase.
Teradata data sourcedata 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 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).
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.

For more information about each connection type, see Add Data from External Data Sources (SSRS) and Data Sources Supported by Reporting Services (SSRS).

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

System_CAPS_ICON_note.jpg 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).

Add Data to a Report (Report Builder and SSRS)
Report Parts (Report Builder and SSRS)

Show: