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

New: 5 December 2005

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 https://localhost/reportserver/reportservice2005.asmx?wsdl to obtain the namespace information for the Report Server Web service. Later, you will specify the namespace in the query.

   Note

   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.

   Note

   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 https://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:

   https://localhost/reportserver/reportservice2005.asmx
   

   The dialog box should look similar to the following illustration:

   

   Note

   In SQL Server 2005 Reporting Services (SSRS) Service Pack 2 (SP2) and later versions, use the following connection string for a report server that is running in SharePoint integrated mode: 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:

    <Query>
    <SoapAction>https://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices/ListChildren</SoapAction>
    </Query>
    

    The dialog box should look similar to the following illustration:

    

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:

    Item

    Recursive

    Note

    In SP2 and later versions, only the Item parameter is available.

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

    Note

    In SP2 and later versions, 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:

    

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.

    Note

    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 () 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:

    

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

Concepts

Reporting Services Tutorials

Other Resources

Defining Report Datasets for XML Data
Connecting to a Data Source
Defining Report Datasets
How to: Create or Edit a Report-Specific Data Source (Report Designer)
How to: Create a Dataset (Report Designer)
Working With Fields in a Report Dataset
How to: Add, Edit, or Delete a Field in the Datasets Window (Report Designer)
Report Datasets (Report Designer)

Help and Information

Getting SQL Server 2005 Assistance