Export (0) Print
Expand All

Data Connections, Data Sources, and Connection Strings in Report Builder 3.0

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 3.0 and SSRS) and Data Connections, Data Sources, and Connection Strings (SSRS).

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 Adding Data to a Report (Report Builder 3.0 and SSRS).

NoteNote

You can create and modify report definitions (.rdl) in Report Builder 3.0 and in Report Designer in Business Intelligence Development Studio. 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 3.0 (SSRS) on the Web at microsoft.com.

Getting Data from External Data Sources

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=AdventureWorks

Set data source type to SQL Server.

SQL Server instance database

Data Source=localhost\MSSQL10_50.InstanceName; Initial Catalog= AdventureWorks

Set data source type to SQL Server.

SQL Server Express database

Data Source=localhost\MSSQL10_50.SQLEXPRESS; Initial Catalog= AdventureWorks

Set data source type to SQL Server.

Analysis Services database on the local server

data source=localhost;initial catalog=Adventure Works DW

Set data source type to SQL Server Analysis Services.

SharePoint List

data source=http://MySharePointWeb/MySharePointSite/

Set data source type to SharePoint List.

SQL Server 2000 Analysis Services server

provider=MSOLAP.2;data source=<remote server name>;initial catalog=FoodMart 2000

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

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=http://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

http://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 Adding Data from External Data Sources (SSRS) and Data Sources Supported by Reporting Services (SSRS).

Arrow icon used with Back to Top linkBack to Top

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 How to: Add and Verify a Data Connection or Data Source (Report Builder 3.0 and SSRS).

NoteNote

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 Specifying Credentials in Report Builder 3.0.

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 Business Intelligence Development Studio. For more information, see How to: Create an Embedded or Shared Data Source (SSRS).

Arrow icon used with Back to Top linkBack to Top

Community Additions

ADD
Show:
© 2014 Microsoft