RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Create Reports from Any Data Source Using SQL Server Reporting Services Custom Data Extensions : Page 5

SQL Server Reporting Services makes it easy to build reports from database data, but what if the data isn't in a database? This article shows you how to take advantage of Reporting Services' unique extensibility model to build reports from ADO.NET datasets.

Report Authoring
After installing and registering the CDE, authoring a report that uses it as a data source is easy. The Reports project includes a sample TestDS report which you can use to test the CDE. For better design-time experience, I recommend you use serialize an ADO.NET dataset object to a file using the WriteXml method. This allows you to set the report parameter to the file path instead of the entire dataset XML payload.

To lay out the report, you need the dataset schema only. However, if you want to preview the report with data, make sure that the serialized dataset also contains sample data. The DatasetSalesOrder.xml sample file (included in the downloadable code) contains both the dataset schema and sample data.

Figure 4. Selecting a Data Source: Select the Dataset Extension as a data source type to use the dataset CDE.
Follow these steps to author a report that uses the CDE.

Create a Report Dataset
Start authoring the report by setting up the report dataset.

  1. Create a blank report and open it in the Report Designer. Switch to the Data tab.
  2. Create a new report dataset. Set its data source to use the Dataset Extension type, as shown in Figure 4.
Note: The Dataset Extension entry appears in the Data Source dialog only if the CDE is properly registered with the Report Designer.

  1. Leave the rest of the Data Source settings to their default values. The CDE doesn't use them. Click OK to return to the Dataset dialog.
  2. Enter SalesOrderHeader as a query string. This is the ADO.NET table name (the only table in the sample dataset) which will be used as a data source. Alternatively, enter Nothing to default to the first table in the dataset. Click OK to close the Dataset dialog.
Set Up Report Parameters
Figure 5. Creating Parameters: The client passes the application dataset to the hidden DataSource parameter.
As noted, the application will pass the ADO.NET dataset as a report parameter. Follow these steps to create a DataSource parameter.

Select the Report menu and then Report Parameters.

Create a DataSource parameter, as shown in Figure 5.

Check the "Hidden" checkbox to hide the parameter from the end user.

(Optional) To speed up report testing, enter the full path to the dataset file as a default parameter value. This allows you to switch to the Preview tab without having to set the DataSource parameter value each time. Click OK to return back to the Data tab.

The next step is very important. To pass the dataset to the CDE successfully, you need to link the report-level DataSource parameter to a query parameter. That's because only query parameters can be passed to the CDE.

Go to the report dataset properties (click the Ellipsis () button next to the Dataset dropdown) and flip to the Parameters tab.

Set up a new query-level parameter, as shown in Figure 6.

Figure 6. Linking Parameters: Link the report parameter to a query parameter to pass its value to the CDE.
When the report is processed, SSRS passes the value of the DataSource report-level parameter to the DataSource query-level parameter, which is passed subsequently to the CDE.

Retrieving the Dataset Schema
At this point, you have everything in place to test the CDE. Let's take the CDE for a ride.

Because the query syntax is proprietary, make sure to select the Generic Query Designer toolbar button.

Click the exclamation (!) toolbar button to execute the query.

In the Define Query Parameters dialog that follows enter the full path to the dataset file (should be the same as the default value of the @DataSource report-level parameter) and click OK.

Figure 7. Testing the CDE: The CDE query results are exposed as a regular report dataset.
If all is well, the Report Designer will pass the file path to the CDE, which loads the dataset and returns it to the Report Designer to display it in the grid.

Click the Refresh button to update the report dataset definition. Expand the DataSet1 node in the Datasets toolbox to see the dataset fields.

From here, switch to the Layout tab and create the report layout by dragging and dropping dataset fields as usual. Once you are satisfied with the results, deploy the report to the report catalog.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date