Defining Report Data Sources

Data can be supplied to ReportViewer either as objects or as DataTables. In either case there are two steps to adding data to a report:

1. Add a datasource to your project.
2. Add the project datasource to the report.

The procedure for adding a datasource to your project is different for Windows Application Projects vs ASP.NET Web Sites. See below for details.

To add a project datasource to the report use the Report Data Sources dialog. To open this dialog, from the menu choose Report > Data Sources. Note that the Report menu is only available when you are editing a .RDLC file.

The second step is not always necessary because it happens automatically when you drag and drop a field from the Data Sources window to the report.

Report Designer cannot detect if your report is no longer using a datasource that you previously added to the report. Even if you delete all report items that use fields from a particular datasource, the reference to the datasource remains in the .rdlc file. Then when you run the report ReportViewer displays an error message such as this: A data source instance has not been supplied for the data source 'AdventureWorks_Sales'. To fix the problem the datasource reference must be manually removed from the .rdlc file using the Remove button of the Report Data Sources dialog.

There are two other common reasons to open the Report Data Sources dialog:

If a new field has been added to a datasource since it was added to the report, you have to press the Refresh All button before the report can "see" the new field.

You will also open this dialog to find out the names of report data sources. To supply data for subreports or drillthrough reports (in the corresponding event handlers) you'll need to know the names of the report datasources. (For the main report, you got away because Visual Studio automatically generated code to supply data.)

Note about the Rename button: Renaming a report datasource does not automatically rename all references to it. Because of this, this button is typically only useful immediately after adding a project data source to the report. If you rename a report datasource any time after that you have to manually rename any references to the old name in your report.

Windows Application Projects

Objects:

See this document.

DataSets:

Add a .xsd file describing the dataset to your project. If you already have an .xsd file you can manually add it to the project. Otherwise use the Data Source Configuration Wizard (from the menu choose Data > Add New Data Source) and choose the Database option.

Another option is to use the TableAdapter Configuration Wizard. Here's how: First add an empty .xsd file to your project: From the menu choose Project > Add New Item > DataSet. An empty .xsd file is added to your project, and opened in Dataset Designer. Right-click anywhere in the Dataset Designer window and choose Add > TableAdapter. This starts TableAdapter Configuration Wizard. This wizard lets you enter SQL queries.

Tip: At runtime you can set the database to query by setting the Connection property of TableAdapter.

ASP.NET Web Sites

In ASP.NET datasources can be defined on a page, or at the site level. Since .rdlc files are at the site level, Report Designer can only see datasources defined at the site level. At runtime ReportViewer can accept datasources defined on the page, however.

Note: Site-level datasources are the ones that live in the App_Code folder. The .vb or .cs source code of business objects you want to use in a report should be in the App_Code folder. The .xsd definitions of datasets should also be in the App_Code folder.

Objects:

See this document.

DataSets:

To add a new dataset, from the menu choose Website > Add New Item > DataSet. You are asked if you want to place the .xsd in the App_Code directory. Answer yes. A new .xsd file is added to the App_Code folder, the .xsd is opened in Dataset Designer, and the TableAdapter Configuration Wizard is automatically launched. This wizard lets you enter SQL queries.

After designing the report, drag and drop the ReportViewer control into the web page. Using the smart tags panel select the report you designed. As soon as the report is selected, code is generated and you are ready to run the website.

But here's the weird part: Even though you designed the report based on a SQL query, the generated code uses an ObjectDataSource control as opposed to a SqlDataSource control! What's going on? If you examine the ObjectDataSource closely you can see that it simply calls the GetData method of your TableAdapter. So your SQL is being executed to get data. Can you use a SqlDataSource control here instead? Sure, but in this case the actual SQL query will exist in two places: in the .xsd file and in your .aspx file. This is harder to maintain.

Tip: At runtime you can set the database to query by handling the ObjectDataSource_ObjectCreated event, and then setting the Connection property of TableAdapter.

Related links