Creating Data Sources for a Report

Article
07/13/2009

The processing mode of a ReportViewer control determines the kind of data you can use behind a report.

In remote processing mode where reports are processed on a SQL Server 2005 report server, data processing extensions on the report server determine the data source types you can use. Default data processing extensions are available for SQL Server, Analysis Services, SQL Server Integration Services, Oracle, SAP NetWeaver® Business Intelligence, XML, ODBC, and OLE DB. Connections to data sources and queries that retrieve data for a report are specified in a report definition at design time when you create the report in Business Intelligence Development Studio (this is the report design tool that is included in SQL Server 2005). Fields that are returned by the query can be used in the report layout when you drag them onto the report design surface. For more information about how to work with data in a server report, see Connecting to a Data Source in SQL Server Books Online.

In local processing mode where reports are processed by the control within your application, you can specify Visual Studio data sources and fields to define the data behind the report. Connections to the data source and queries that retrieve data for a report are defined in your application project at design time. A client report can use data from any source as long as it can be supplied as an ADO.NET DataTable or an enumerable collection of Business objects. The data table or Business object returns a list of fields that can be used by the report. Fields contain a pointer to a database field and a name property. You can drag fields from the Data Source window onto a report layout.

Binding Data in a Report

Report definitions use data regions to bind data to the report. You can choose from a variety of data regions to support different data structures in your report. Examples of data regions that you can use in a report definition include Table, Matrix, Chart, and List. To bind data in a report, you must do the following:

Define data sources and datasets that you want to use in the report (you can use the information and links provided in this topic to learn how to do this). The Data Source window shows the data sources and fields that you can use.
Drag the data regions that you want to use from the Report Toolbox onto the design surface.
Drag individual fields from the Data Source window onto the data region.

Each data region can use fields from a single dataset. Neither the ReportViewer control nor the report server can perform joins between different data sets. For example, if you are using business objects, the join must be performed within the business object. For more information about data regions, see Adding Data Regions to a Report (Visual Studio Report Designer).

How to Add Data Sources for ReportViewer Reports

The following approaches are recommended for setting up a data source so that it can be used in a client report definition.

Add a data source to the application project and configure the data connection to the underlying data. For an underlying data store, select the specific data that you want to use. The data that you want to use in the report must be available in the Data Sources window. For a data table, the data source shows column names returned from a query. For an enumerable collection of Business objects, the data source shows a list of public properties of simple data types exposed by the class object.
To create a data table, add a dataset to the project and use the TableAdapter Wizard to configure the data table. The TableAdapter Wizard provides a query builder and data preview feature so that you can confirm query results right away.
To create Business objects, use the Add New Item command on the Project menu and select the Class object. Provide implementations that expose public properties of simple data types for use at design time. Provide methods to return enumerable collections of the properties for use at run time.

Once a data source has been configured, the bindable data appears in a hierarchy in the Data Source window. To bind data to a report, drag the nodes of the expanded hierarchy to text boxes or data regions in a report layout. If you later modify the datasets after the report is defined, you must update the data bindings in the report and control. For more information, see Updating and Rebinding Data Source References (Visual Studio Report Designer).

You can have any number of data sources in your project. To view the list of data sources actually used by a report, choose Data Sources on the Report menu. To preview the report and its data in your application, you must build or deploy the application to verify the report contains the data you expect. For more information about configuring the control and defining report layout, see Configuring ReportViewer for Local Processing and Creating Client Report Definition (.rdlc) Files.

To get started immediately, use the following walkthroughs to learn how to add each type of data source to a project:

Using Business Objects

A business object is any application object that exposes public properties of simple data types. Examples of Business objects might include a collection of Customer objects from an underlying data store, an array of Book Title objects created by your application, or a list of Channel objects you create from an RSS XML source.

To be accessible as a data source, the collection must support IEnumerable. Bindable data appears in a hierarchical view in the Data Sources window. For Business objects, the data source shows a list of public properties of simple data types exposed by the Class object. You can then bind them to the data regions and text boxes in a report definition.

To provide a collection of Business objects, you can add a class or add a reference to the assembly for a class to your project.

Using Data Tables

A DataTable, as part of an ADO.NET DataSet, can easily be added to your project from the Project menu by selecting Add New Item. To configure the data connection, start the TableAdapter Configuration Wizard. From the Data menu, select Add, then select TableAdapter. You can also define a query to limit your result set. After the wizard finishes, the columns in the DataTable are accessible through the Data Sources window so you can bind them to data regions and text boxes in a client report definition.

Defining Data Sources for ReportViewer Web server controls

Data source controls provide support for abstracting the data access layer so that controls on a Web page bind to a data source, which in turn binds to the actual data. This allows you to efficiently change the data source without having to rebind every control to every piece of data.

When you are designing a Web page, the Toolbox Data section lists several data source controls like SqlDataSource and XmlDataSource. Some data source controls provide support for Page memory in Web pages and others use shared data. Reports do not use Page memory, so the best choice for a ReportViewer Web server control is to use the ObjectDataSource control. This control provides a bridge between in-memory cached objects (DataTables or Business object collections) and the report data regions. When you add a ReportViewer control to a Web page, a ObjectDataSource control is automatically added and configured.

To minimize the need for writing extra code, ASP.NET support for Web pages provides extra behind-the-scenes processing to detect collections that supply bindable Business objects. When you add a class to your project that returns a collection of objects that can be identified as Business objects, the collection is automatically added to the Website Data Sources window.

Creating Data Sources for a Report

Binding Data in a Report

How to Add Data Sources for ReportViewer Reports

Using Business Objects

Using Data Tables

Defining Data Sources for ReportViewer Web server controls

See Also

Reference

Concepts

Other Resources

Additional resources