Developer Product Briefs

Integrate XML Into Your Reporting Environment

You'll find several areas of interest regarding XML usage in SQL Server Reporting Services.

• By Bill Wolff
• 05/01/2006

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).

In Report Designer, you have a limit on the amount of text that fits in this box, so the embedded XML can't be very large. Use a text editor instead to build report files with large embedded <XMLData> blocks.

The third data source candidate is a SOAP Web service. Many commercial and public services such as Google and Amazon offer search results using SOAP method calls. It is common to use Web services in the enterprise for emerging SOA frameworks. Other Microsoft products such as Microsoft Dynamics and Dynamics CRM have rich Web service catalogs. Even SQL Server 2005 has a new feature called HTTP EndPoints that makes high-performance Web services. No matter what your source might be, Reporting Services can accommodate your needs.

This simple example connects directly to the Reporting Services Web service virtual directory. Open the Report Web Service sample and go to the Data tab. Open the dataset properties and then the data source properties. Notice that the connection string points straight to an ASMX file. The credentials in this case are set to Windows authentication. The Query tab for the dataset uses a <SoapAction> block inside the <Query> definition. One Web service might have many methods or SOAP actions available. In this case, you are traversing the Report Server folder hierarchy and listing all children.

To make this work, two parameters are configured in the Dataset parameters tab. Once you review this, go back to the Data tab and run the query. A list of folder objects appears and the field names are listed in the Dataset Explorer. Use these in the report Layout tab as needed.

XML Output Formatting
XML is one of several output formatting options in Reporting Services. When a report is run in Report Manager or the ReportViewer control, the optional toolbar includes a dropdown list of registered output formats. HTML 4.0 is the default, but HTML 3.2 and MHTML (Web Archive) are also available. PDF and Excel are popular e-mail targets; CSV and XML aren't used as much.

Selecting XML file with report data opens a new browser window with the XML color-coded by Internet Explorer. Each group, section, and detail level in a report turns into nested XML elements with textboxes configured as XML attributes. Unless the report developer makes adjustments to default behavior, the element names match the group names defined in Report Designer.

This behavior is easily modified by setting a few properties. For each object, ensure that the Name property is meaningful. This is used as the default element name. Then look at the Data Output tab, which you use to tailor your XML output. You can enable or disable output per group or object, set the element name, and define the parent collection name. This tab appears for each container (table, matrix, list, chart) and each display object (textbox, table group, table cell). Selecting properties for the entire report provides control over the XML root element name and the detail row rendering style (see Figure 3).

You can set this rendering style to element or attribute. The sample report "Session with Enhanced Data Output" has these settings configured. Inspect the Data Output tabs and compare the XML output from this report to that of the "Session from XML File" sample.

Another way to expose the XML output involves URL addressing. Instead of using the Report Manager toolbar, enter a precise phrase into the URL of a browser to go directly to the XML. Use the rs:Format switch to specify XML like this:

http://localhost/reportserver?/Reporting+Services+XML/reports/Session
+with+Enhanced+Data+Output&rs:Command=Render&rs:Format=XML&
rc:OmitSchema=True&rc:FileExtension=htm&rc:XSLT=Transform.xsl

This URL can be used in programming projects. Have some code send an HTTP request and return a byte stream. The XML can be parsed into an XMLDocument or similar object. This object can then be queried with XPath or combined with XSLT to transform the output into any text format including HTML.

XML Transformations
XML can be transformed using XML Stylesheet Transformations, or XSLT. If the exact XML you need cannot be created easily using the Data Output technique that I mentioned, use XSLT to refine the output. A seasoned XSLT programmer can select any combination of XML elements or attributes, rearrange them, apply conditional logic, format values, and even create text files or HTML Web pages. This method performs well, and you should consider it before writing a custom output extension using the Reporting Services API.

XSLT is a well-documented but not well-known language. It is a W3C standard with plenty of books and articles defining usage patterns, and Visual Studio 2005 does a good job of editing it. The reporting project contains a sample of an XSLT file that uses the XML output format from "Session with Enhanced Data Output." This source file is called Data Ouput.XML, and the transform is called Transform.XSL. Load the latter and select the XML menu option "Show XSLT output," which shows sample output in the designer. This is a simple example that makes Category headers with a table that has a row for each session title. The idea is to make something that is not obvious in Reporting Services.

To get this working directly in a report, go to the Report Properties Data Output tab and set the Data transform to the working URL of the XSLT file. Once you set this, the XSLT file will transform every time the user selects XML output. In this example, the XSLT file is published to the Report Server so that it can be accessed from any reporting URL address. Note that your XML or XSLT files could be stored on any Web site.

When working from Report Manager, you must save the file to view because the default extension is XML. The XSLT file can also be assigned directly using the URL address syntax—notice the mime type and extension:

http://localhost/reportserver?/Reporting+Services+XML/
reports/Session+with+Transformed+Data+Output&rs:Command=Render

This provides the developer with an optional transform. As mentioned earlier, programmers can also request the HTTP URL in code and apply the XSLT with a compiled transform object. This has better performance in high-volume applications.

• Review each of the key Reporting Services file types (RDL, RDS, SMDL, CONFIG) to see how XML is used to configure your reporting environment.
• Use XML files from a Web address or embed XML directly into the report data source.
• Link reports to your SOAP Web services using an XML data source. Specify a method in the query section and define parameters as needed.
• Make sure that all Report Designer objects have meaningful and descriptive names to address XML element naming. Also consider using the Data Layout tab in each object property box to shape XML output.
• Use URL addressing to expose Reporting Services XML output directly to code projects.
• Consider learning and using XSLT as an extension to XML output formatting. This is easier than writing a custom report output extension. Use the Report Properties page or a URL address parameter to trigger the transform.
• Keep experimenting and trying different XML tricks. You can do many more things with SQL Server Reporting Services.

About the Author
Bill Wolff is an independent consultant, trainer, and architect specializing in Microsoft development technologies. His company, Agility Systems, is based in the Philadelphia area. Bill is founder and president of the philly.net user group, a board member for INETA (Vice President, Speaker Bureau), and a VSLive! speaker. Bill was a contributing author to several books and articles. His certifications include trainer, systems engineer, developer, and Microsoft MVP for VB.NET.