Lesson 1: Defining a Report Dataset for a Reporting Services Web Service

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

Use the following steps to learn how to specify a data source connection and return XML data from a Web service. In this lesson, you will create a dataset by calling the Report Server Web service ListChildren method that returns a list of all items from the root folder in the Report Server database. You define the parameters required by the ListChildren method and set default values to iterate through the hierarchy starting with the root folder. Item properties defined by the Web service appear as fields for the dataset in the Datasets window. Finally, you drag the dataset fields to the report layout to design your report. When you preview the report, you see items and item properties from your report server database, such as reports, folders, and data sources.

To define an XML data source, dataset, and query

  1. Open a browser window and type http://localhost/reportserver/reportservice2005.asmx?wsdl to get the namespace information for the Report Server Web service. Later, you will specify the namespace in the query.


    If you do not get an XML WSDL definition response in your browser when you try this, check the requirements section for Tutorial: Using XML Data in a Report.


    Namespace version information can change from one release to the next. To verify the namespace information for the report server that you are using, you can generate the WSDL to verify that you have the correct Web service reference. The format of the namespace is http://schemas.microsoft.com/sqlserver/<year>/<month>/<day>/reporting/reportingservices.

  2. Start Report Designer and create a new report. If you do not know how to create a report, see Tutorial: Creating a Basic Report.

  3. In Data view, select New Dataset. Type a name for the dataset (for example, XMLDataSet).

  4. In the Dataset dialog box, in Data source, select New Data Source. The Data Source dialog box appears.

  5. Type a name for the data source (for example, XMLDataSource).

  6. In Type, select XML.

  7. In Connection string, type the following URL to the Report Server Web service:


    The dialog box should look similar to the following illustration:

    Screenshot of an XML data source type


    For a report server running in SharePoint integrated mode, use a connection string similar to the following example: http://<servername>/_vti_bin/ReportServer/ReportService2006.asmx

  8. In the Credentials tab, select Use Windows Authentication (Integrated Security).

  9. Click OK to save your changes and close the Data Source dialog box.

  10. In the Dataset dialog box, type the following query using the namespace version information that you verified in step 1:


    The dialog box should look similar to the following illustration:

    Screenshot showing an XML dataset

  11. In Parameters tab of the Dataset dialog box, type two parameters. These are the parameters on the ListChildren method that specify where to start in the Report Server folder hierarchy and whether to include all nested folders:



  12. Set Item to /. Remove the "=" that Report Designer adds. The / symbol specifies the root node of the report server folder namespace.


    If you are running Reporting Services in SharePoint integrated mode, the parameter must be a URL to the SharePoint top-level site, folder, or document library. For example, http://<servername>/<top level site>, http://<servername>/<top level site>/<folder>, or http://<servername>/<top level site>/<document library>.

  13. Set Recursive to 1. Remove the "=" that Report Designer adds.

    The dialog box should look similar to the following illustration:

    screenshot showing an XML dataset with parameters

  14. Click OK. The dataset is added to the Datasets window.

  15. Click Run (!) to view the result set. If the report server database contains reports and other items, you should see a row of data for each item.


    If you get the error "An error occurred while executing the query", check that you have the set the parameters as described in step 11. The ListChildren method requires the parameters Item and Recursive.

  16. Click the Refresh Fields (Refresh dataset fields) button on the toolbar. This saves the report definition and updates the view of fields in the Report Datasets window to show all the fields you can use.

    The dialog box should look similar to the following illustration:

    screenshot showing dataset fields and query result

Next Steps

You have successfully defined the metadata for a report dataset for Report Server database items using the Report Server Web service. When you process the report, the data represented by the dataset metadata will be retrieved from the Report Server database. Next, you can create a report dataset from a Web service that returns an XML System.Data.DataSet object. See Lesson 2: Defining a Report Dataset for an ADO.NET DataSet from a Web Service.

See Also


How to: Create or Edit a Report-Specific Data Source (Report Designer)
How to: Create a Dataset (Report Designer)
How to: Add, Edit, or Delete a Field in the Datasets Window (Report Designer)


Defining Report Datasets for XML Data
Connecting to a Data Source
Defining Report Datasets
Working with Fields in a Report Dataset (Reporting Services)

Other Resources

How Do I Find Tutorials (Reporting Services)
Report Datasets Dialog Box (Report Designer)

Help and Information

Getting SQL Server 2008 Assistance