page static_html.xsl b2004_11_25

Highly flexible PowerPoint report generator

The article on Nov 3, 2004 added a web middle tier to the PowerPoint report generation framework. Another article described turning SQL queries into slides. In this article I will combine these two data access strategies and also add the ability to add static slides to a presentation. The resulting script is the latest iteration in my ever evolving PowerPoint report generator.

I am going to build a script which can consume an XML report manifest file such as the following:

<slideset>
	<slide type="static">
		<title>The title for this slide</title>
		<notes>The notes for this slide</notes>
		<url>http://localhost/thinolap/xmla2ppt-asp2/ppt/bullet2.ppt</url>
	</slide>

	<slide type="bullet">
		<title>This is a generated bullet-point slide</title>
		<notes>The notes for this slide</notes>
		<content>
			<p>This make a bullet</p>
			<p>And this does also</p>
			<p>One more here</p>
		</content>
	</slide>

	<slide type="bar" PlotBy="Column">
		<title>Number of Orders By Customer</title>
		<notes>Notes about report</notes>
		<query type="sqlxml">
		SELECT CustomerID, COUNT(*) AS NumberOfOrders
		FROM Orders
		GROUP BY CustomerID
		HAVING COUNT(*) > 15
		ORDER BY NumberOfOrders
		FOR XML RAW
		</query>
	</slide>
</slideset>

The manifest file is an XML interface to the report generation process. The examples included with this articles download are static files. But in a real application, this file would be generated by some other program - either desktop or web based. The PowerPoint generator could even be implemented as a web service, with the manifest as the input and the automation script as the output.

There are three meta-types of slides.

1. static
2. dynamic with data from manifest
3. dynamic with data from query

Static slides are used to copy into the presentation a slide from a slide repository. Static slides have a "url" element and optionally a "sid" element. The url must resolve to a PowerPoint presentation. The sid element identifies which slide in the presentation to copy. If no sid is present, then URL must reference a presentation with only one slide.

coverpage and bullet slide types are examples of the "dynamic from manifest" meta-type. For these, the data which populates dynamic elements in the slide are to be found in the manifest. In the above example, for instance, the coverpage slide has a title and a subtitle.

The "dynamic from query" meta-type has a query element child. This query is used to obtain the data which will populate elements within the slide - typically some kind of chart. The type of query specifies the data provider. Currently I support SQLXML and XMLA - others can be added.

The manifest processor (genpres.asp) iterates through the slides with a query element and executes the query. The query results are put back into the manifest DOM. A program generating the manifest could instead make the queries and put the results in the correct tabular format. In this case, genpres.asp will treat the slide as a "dynamic from manifest". The fifth slide in the manifest file unified.xml has an example of this.

I've also fixed in this version the insertion of the text from the notes element into the slide notes. This feature could be greatly expanded upon to insert html formatted notes, or whatever else the notes placeholders support.

One thing that continues to impress me is how well suited XSLT is for reusing snippets of automation script. While generating a presentation in a native XML format would still be preferable, my approach is proving to be robust and flexible.

downloads: b2004_11_25.zip (K)