email article printer friendly get the code

Integrate XML Into Your Reporting Environment
You'll find several areas of interest regarding XML usage in SQL Server Reporting Services.
by Bill Wolff

May 1, 2006

SQL Server Reporting Services is fast becoming the mainstay for custom reporting in Microsoft-based shops. Connecting to relational tables, views, and procedures is easy and productive, and with version 2005 you have new data source options for multidimensional queries (Analysis Services cubes and data mining models) and XML data structures. Similarly, XML is fast becoming the protocol of choice for data transfer between trading partners and across the enterprise using service-oriented architectures (SOAs). How well does Reporting Services play in this arena?

You'll find four areas of interest regarding XML usage in Reporting Services: report object storage, data source queries, output formatting, and XSLT transformations. I'll look at each of these and make some recommendations for integrating XML into your reporting environment.

All the samples I'll discuss are packaged in a zip file available for download. Extract the zip to a directory where you keep Visual Studio projects. There is one solution with a reporting project. The project has sample reports, two XML files, and one XSLT.

XML-Based Report Objects
First and foremost, XML is used to store all pertinent report objects. Report Definition Language (RDL) files have an XML syntax defined by a known schema. This format is well-documented and third parties can use this fact to create utilities that produce Reporting Service–compatible reports. To easily view an RDL file, right-click a sample report in the reporting project and select View Code. You can see the element hierarchy and required sections. If you copy this text into a new XML file in Visual Studio, the desired color-coding and syntax-checking work.

Shared Data Sources (Report Data Source files, or RDS files) are also stored in simple XML files. You can't use Visual Studio to view code for these. Instead, go to the file system and open with Notepad or Internet Explorer.

Report models, another interesting feature, define data semantics in business-user language. They are stored in a Semantic Model Definition Language (SMDL) file. The Visual Studio view-code technique works for these.

Finally, you'll find several configuration files (.CONFIG) in the Reporting Services installation folder. Both virtual directories, one for Report Manager and one for the Web service engine, have standard web.config files. Two policy files control permissions and trusts: The ReportingServicesService.exe.config has settings that control tracing and logging, while the rsreportserver.config file configures the reporting environment. You can add your own custom extensions and remove built-in features such as PDF generation. Refer to the product documentation for details before editing this critical file.

XML Data Sources
The new XML data source option provides tabular dataset results for use in report layout and processing. There are three variants: XML file access from a Web address, embedded XML files, and SOAP Web service method calls. You can combine the XML data sources with relational and multidimensional data in a report. You can also add them as shared data sources, as in Shared XML File.rds.

The XML file approach requires a URL address pointing to a file with an XML extension. In these samples, the Session.XML file is included in the Reporting Services XML project. This is deployed to the Report Server and stored there in a folder (see Figure 1).

Look at the "Session from XML File" report. In the Data tab, click the ellipsis next to the dataset name to see the dataset properties. Click the ellipsis button next to the data source name to see the data source properties. The URL goes in the connection string. Note that this field can be an expression based on some report global, parameter, or field value.

The Query string for the XML data source contains a <Query> block. An <ElementPath> uses modified XPath syntax to specify the element level in the XML hierarchy. You can use phrases to get elements, attributes, or their children. Once you get the syntax straight, the Run button (!) returns the tabular dataset from your XML. The field names, which come from the element and attribute names, appear in the dataset explorer for use in layout tasks.

Embedded XML works like the file approach. Use the "Session from Embedded XML" sample report. The data source is configured as XML with a blank connection string. The Query string has an <XMLData> block where the target XML nodes are stored (see Figure 2).