Dela via


Adding Data from External Data Sources (SSRS)

To retrieve data from an external data source, you use a data connection. Data connection information is usually provided by the owner of the external data source, who is responsible for granting permissions and specifying which types of credentials to use. Data connection information is saved as a report data source. The data source type specifies which data extension to use to retrieve the data.

For more information about data source types, see In This Section.

In This Article

Understanding Data Access Technology

Understanding Report Data

Working with Data Types

How-To Topics

In This Section

Related Sections

Understanding Data Access Technology

To retrieve data for a report dataset requires multiple layers of data access software. The following list provides a simple description of how reports work with data access technologies:

  • Application and user interface   The Report Builder 3.0 application that you use to create a data source, add a reference to a shared data source, add a shared dataset, or add a report part that includes the data sources and datasets that it depends on..

  • **Report definition elements   **Data sources and datasets are part of the report definition. After a report is published to a report server, shared data sources and shared datasets are managed independently from the report definition.

    • Data source and Shared data source   Part of a report definition that includes the information about the type of data processing extension, the connection information, and the authentication.

    • Dataset and field collection   Part of a report definition that includes the query, the field collection, and the field data types.

  • Reporting Services data extensions   Built-in data extensions that are installed with Report Builder. A data extension provides functionality that handles authentication, server aggregates, and multi-value parameters.

  • Data provider   The software that manages the connection and retrieval of data from the external data source. The data provider defines the connection string syntax. Most data extensions are built on top of a data provider layer.

  • External data source   Where to retrieve report data from, for example, a database, a file, a cube, or a Web service.

Note

When you are not connected to a report server, you can choose from data extensions that are installed with Report Builder 3.0. You access the data as a single user using credentials from your computer. When you are connected to a report server, you can choose from data extensions that are installed on the report server. You access the data as one of multiple users who run the report and you are using credentials on the report server. For more information, see Report Servers and SharePoint Report Servers (Report Builder 3.0 and SSRS) and Specifying Credentials in Report Builder 3.0.

Understanding Report Data

In its simplest form, a report displays data from a report dataset in a data region on the report page, that is, in a single table, chart, matrix, or other type of report data region. The data in a report dataset comes from the first result set that is returned from a single query command that runs from read-only access to an external data source. Each data region expands as needed to display all the data from the dataset.

Data in a dataset are essentially tabular. Columns are the fields from the dataset query. Rows are from the rows in the result set. You can use the following generalized types of data in a report:

  • Rectangular data. Data from a result set that has the same number of columns in every row.

  • Hierarchical data is supported as a flattened rowset.

    • Ragged hierarchies, where there is a different number of columns for each row of data, is not supported. For some data extensions, this has some implications.

    • Data extensions that work with multidimensional data sources use XML for Analysis protocol and retrieve data as a flattened row set and not as a cell set.

    • The XML data extension automatically flattens XML data to use it in a report. If the first instance of an XML element does not include all attributes or subelements, the data might not be available as report data.

  • Recursive data is supported. A result set that contains a recursive data hierarchy includes all the information about the hierarchy structure in a rectangular result set. For example, the report-to structure in a company can be represented by a table that includes two columns: an employee and a manager. Each manager also is an employee with a manager. The top manager usually contains a null or some other identifier that indicates that this employee has no manager.

Arrow icon used with Back to Top linkBack to Top

Working with Data Types

When you create a dataset, the data types of the fields are mapped to a subset of common language runtime (CLR) data types from the .NET Framework. Data types that cannot be clearly mapped are returned as strings. For more information about working with field data types, see Working with Fields in a Report Dataset (Report Builder 3.0 and SSRS). When you create a parameter, the data type must be a supported report definition data type. For more information about mapping data types from the data provider to a report parameter, see Working with Data Types in Expressions (Report Builder 3.0 and SSRS).

Arrow icon used with Back to Top linkBack to Top

In This Section

The following topics provide information about each built-in data extension.

Topic

Data Source Type

SQL Server Connection Type (SSRS)

Microsoft SQL Server

Analysis Services Connection Type for MDX (SSRS)

Microsoft SQL Server Analysis Services

PowerPivot Connection Type (SSRS)

Microsoft SQL Server Analysis Services

SharePoint List Connection Type (SSRS)

Microsoft SharePoint List

SQL Azure Connection Type (SSRS)

Microsoft SQL Azure

SQL Server Parallel Data Warehouse Connection Type (SSRS)

Microsoft SQL Server 2008 R2 Parallel Data Warehouse

SAP NetWeaver BI Connection Type (SSRS)

SAP NetWeaver BI

Hyperion Essbase Connection Type (SSRS)

Hyperion Essbase

OLE DB Connection Type (SSRS)

OLE DB

ODBC Connection Type (SSRS)

ODBC

XML Connection Type (SSRS)

XML

Report Model Connection (SSRS)

.smdl model

Arrow icon used with Back to Top linkBack to Top

These sections of the documentation provide in-depth conceptual information about report data, as well as procedural information about how to define, customize, and use parts of a report that are related to data.

Topic

Description

Adding Data to a Report (Report Builder 3.0 and SSRS)

Provides an overview of accessing data for your report.

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

Provides information about data connections and data sources.

Creating and Adding Datasets (Report Builder 3.0 and SSRS)

Provides information about embedded and shared datasets.

Working with Fields in a Report Dataset (Report Builder 3.0 and SSRS)

Provides information about the dataset field collection generated by the query.

Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.

Provides in-depth information about platform and version support for each data extension.

Data Processing Extensions Overview in the Reporting Services documentation in SQL Server Books Online.

Provides in-depth information for advanced users about data extensions.

Arrow icon used with Back to Top linkBack to Top