page static_html.xsl b2003_4_11

Generating Excel Workbooks from XML/A

In this article I will demonstrate the creation of Excel analytic report workbooks. The script which generates Excel is called xmla2xls.wsf. The operation occurs in several discrete steps which are explained below. A link to the source code can be found at the end of the article.

XML/A report list XML

Most interesting reports will have more than one table or chart. For my Excel reports, I want to create a workbook with several worksheets. Each worksheet will have the results from a single MDX query. In pursuit of my goal of creating declarative analytics, I am going to switch the method of specifying queries from an MDX expression given as a command line argument to a report configuration approach using XML.

A sample report configuration file can be seen in mdxqueries.xml. The root XML tag is <slideset>, which comes from the fact that my ultimate goal is the creation of PowerPoint slides. For our present purposes, consider the slideset to be synonymous with a report definition. The slideset element has <slide> children (again the PowerPoint connection). Each slide element has a title element and a query element. The title gives a plain English explanation of the corresponding MDX query.

In order to create the collection of worksheets (one per "slide"), it is clear that I will need to iterate through this list of slides and make an XML/A Execute call for each query. The processing logic is as follows. First, the report list XML is loaded into an XML document. Then the XML document method "selectNodes()" is called, passing in an XPath expression for the nodes I desire - in this case the slide nodes. In the for loop I a) get the query from the current slide node, b) create an XML/A Execute SOAP request, c) send this request, d) load the query result into an XML document, and e) add the root node of the result back into the current slide nodes <result> element.

After each slide node is processed, the XML document - which now has all of the query results in addition to the queries - is saved to a new file named mdxout.xml. This document is now ready for use in whatever report generation phase comes next.

The results from an Execute call are quite verbose because it represents a sub-cube and contains everything you would need in order to present these results. Since I know that my downstream processing of these results is going to want a simpler representation, the next step of processing is the transformation of the XML/A result into a tabular form. This transformation is done within the for loop iterating the slides. The transformed results are stored back to the original XML. This transformation uses the file xamd2.xsl. As a comment in the code explains, I couldn't get the transformation to directly feed back into the query XML - I had to use an intermediate document.

An XSLT to generate XML Spreadsheet

In Article 1 of this series, I demonstrated an WSH script which loaded a XML Spreadsheet (XMLSS) file into Excel XP. To complete the current report generation task, I need to generate such a file from the list of query results. While this may appear at first to be a daunting task, this is in fact a simple task for XSLT. In fact, a great testimonial to the flexibility of XML and XSL is that this is possible.

The best approach for generating XMLSS is to take advantage of the fact that Excel can save documents in this format. So I start by creating in Excel a report which has the look (fonts, colors, layout, etc.) that I desire, and then save this as XML Spreadsheet (file -> save as -> type: XML Spreadsheet). Now I have a "template" for my file generation. I will typically chop this into XSLT templates for the task at hand. The result in this case in the XSLT in ssxml.xsl. This XSLT will be invoked on the XML file which contains the XML results from XML/A. The last step of this script is to open in Excel the report file generated. If you run this program, you will find open on your desktop an Excel workbook with seven worksheets.

In this sample, I have punted on the issue of output formatting. You will need to format the columns as desired in Excel to display the data with the correct style and number of decimal places. In a future article, I will extend this sample to automatically format the results in Excel. Also, this sample doesn't accept optional credentials as did the xmla.wsf script - adding these would make it more generic.

downloads: xmla2xls.zip (8K)