Repurposing XML statistics for InDesign automation

22 03 2010

Well! I’ve been working on a lovely statistics prototype. Using the latest jQuery, theme rolled to match my employer’s corp ID, and the awesome free trial of Fusion Charts (along with a bit of php and xsl mastery), I’ve made a pretty cool stats prototype website.

All the graphs OR table views are rendered from XML files containing the institution’s statistics, both views support clickable drill downs and the whole interface is slick as, but it stops at on-screen graph/table viewing.. Even though the UI is rich and fun to use (accordion, nice jQuery forms and tabs), I’d like to make it a little more fancy than that. In comes InDesign.

Screenshot of the online statistics prototype I was working on

I don’t have a copy of InDesign server, and I’d love some training in scripting and InDesign server apps, but for the moment I’m working on a scenario with the InDesign CS4 client – here’s what I want to do.

I want a button on the prototype site that says “send me these stats”.. from the user’s perspective, they click it and next thing, an email shows up in their inbox with an attached PDF of beautifully formatted statistics, with the institution’s logo and so on. Sound nice?

From my perspective that means: I’d need the XSL for InDesign to transform the XML used by Fusion Charts and the prototypes XSL (in table view) – which would allow InDesign to import the data as an actual table, already mapped with table and cell styles. Obviously to automate all this and generate/email a final PDF I’d need the InDesign server, but hey, I’m just trying to prove a concept here that it’s doable.

It’s quite easy to import XML in to InDesign and have it all mapped with styles and automatic formatting. Take my XML for example:

<chart caption='Student Load (EFTSL) by Gender' subcaption='2006 Full Year' xaxisname='Student Modes' yaxisname='Student Load' palette='1'>
		<category label='Commencing' />
		<category label='Continuing' />
		<category label='All' />
	<dataset SeriesName='Male'>
		<set value='4275' />
		<set value='6245' />
		<set value='10520' />
	<dataset SeriesName='Female'>
		<set value='3401' />
		<set value='4532' />
		<set value='7933' />
	<dataset SeriesName='Both'>
		<set value='7676' />
		<set value='10777' />
		<set value='18453' />

And look at how I’ve done the XSL for InDesign, note the name space defs, in particular the aid5 one which is for the latest version of InDesign only ; )

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="" xmlns:aid="" xmlns:aid5="">
<xsl:template match="/">
<xsl:for-each select="chart">
<h3 aid:pstyle="h3"><xsl:value-of select="@caption"></xsl:value-of></h3>
<h4 aid:pstyle="h4"><xsl:value-of select="@subcaption"></xsl:value-of></h4>

<Table aid:table="table" aid:trows="4" aid:tcols="4" aid5:tablestyle="DataTable">

	<ColHeader aid5:cellstyle="BlankCell" aid:table="cell" aid:theader="" aid:crows="1" aid:ccols="1" > </ColHeader>

	<xsl:for-each select="categories/category">
		<ColHeader aid5:cellstyle="ColHeader" aid:table="cell" aid:theader="" aid:crows="1" aid:ccols="1" aid:pstyle="TableHeading"><xsl:value-of select="@label"></xsl:value-of></ColHeader>

	<xsl:for-each select="dataset">

	<RowHeader aid5:cellstyle="RowHeader" aid:table="cell" aid:crows="1" aid:ccols="1" aid:pstyle="RowHeading">
		<xsl:value-of select="@SeriesName"></xsl:value-of>

	<xsl:for-each select="set">    	
		<Cell aid5:cellstyle="TableBody" aid:table="cell" aid:crows="1" aid:ccols="1" aid:pstyle="TableText">
			<xsl:value-of select="@value"></xsl:value-of>



To get it in to InDesign, it’s quick as, you just import the XML using the above XSL file, easy peasy, and because the XSL transforms the XML in to a format InDesign ‘likes’, the table will be created properly.

Problems are always involved though. So far I haven’t solved these:

  • I had to set the table’s cols and rows manually in the XSL – this is totally not good. You can do all sorts of tricky things with XSL 2.0, so, I’m sure I could put the count of the desired nodes in to a variable and base the column and row count on that.
  • Look at how it comes in to InDesign – the table width!!! Or should I say LACK of table width. I want it to full width the table but I can’t set any static dimensions because the number of columns in tables will always be variable… what to do I’m not quite sure. I could probably create an InDesign script which makes the table the width of the page (within the margins) then distributes the columns evenly? Then again, I could also do some trickery and math in the XSL – count the columns, divide the page width by the number of columns and use the standard cell width in XSL?

Still, it does look promising nonetheless. I’ve seen lots of posts on the net with people having problems getting table data in to InDesign at all, so hopefully the above code snippets and stuff will help some people.

I’ll keep working on this though, and at some point, I’d LOVE to get my mittens on a copy of InDesign server to see what it can do to help me.

The stats of success

23 02 2010

Some time ago I posted about the eStudyGuide website I set up to match our InDesign study guide process. To explain it again, briefly, the XML from our InDesign TOCs get’s uploaded to a webserver along with the PDFs – the webpage reads the XML files and renders a download page for the course you’re looking for.

Well!  Anyway!  Term has started finally and this is the first semester that I’ve had the pages also hooked in to Google Analytics. I had this weird moment of doubt at some point, that the eStudyGuides weren’t really being used, and that we’d not really contributed anything of use to the University.  Not sure why on earth I thought that.  The stats for the first couple of weeks of Term prove how useful it is.

  • Over 4000 page views already
  • Over 1300 hits already
  • Over 5.5 minutes average on each page

eStudyGuide stats

I love Google Analytics, it’s damn awesome.  I’m looking forward to seeing the trends from now on, how each term differs from the next.

Overall, the InDesign XML features have really proved essential in all of this.  There are some glitches though that I believe I’ve never mentioned.  Some times carriage returns get tagged in the TOC when you map styles to tags – which means you get an empty XML tag in your XML document.

You’ll need error checking to make sure you’re not rendering that empty tag as anything on your website, especially if the count of XML tags matches a document count like the eStudyGuide set up.

I wonder though, would it be worth doing an XML Schema and having InDesign validate it’s output with the schema when we export our TOC XML?

The whole process, exporting the XML and PDFs, and then uploading the files is a bit labourious too, and mistakes happen.  With a bit of perl/php mastery, one of the more technical sauvy guys in our team has done some brilliant reporting around the setup, as well as a script that handles that uploading – a must have.  It checks document counts with the XML tags to make sure they match, checks for typos even, missing files, and more.  Many thanks to Damo for his help.

Onwards and upwards, I wonder what the next lot of improvements to the eSGs could be 🙂

Putting statistics online

4 02 2010

We have a statistics website at work with an enormous amount of statistics available for PDF download.  I like PDF downloads.  But.  Just to get a table of statistics, a user has to click through 5 layers of navigation!  The menu, sub menu, sub sub menu, sub sub sub menu…. you see?  It’s not really ideal – all that just to get a downloadable PDF, which usually opens up in an annoying new window as well.

The sub menu navigation on the site is basically like choosing a statistics category, and graph metrics.  So, I figure it’s got to be cleaner if we move to some sort of online graphing tool, right?  We could have a single page even, where a form (or cascading forms) refresh the graph and it’s metrics as you click.  All in one spot, tidy and neat, and fun.  It’d also be easy to have a tab for table-view for people who don’t like graphs.

There’s a lot of problems to overcome before I can start though.  I wrote a report for my boss, just to itemise what we’d need to consider, to move to online graphing.  The stuff below is a part of the report.  If anyone out there has any wonderful ideas about the best way to go, please let me know.  I’m mostly interested in how to get the data in to XML (from excel) in an automated way; and how to conceptualise heavily layered statistics in to graphical break downs.  Is all this a good idea?

As a start I actually did a tiny prototype using the free tool SWF/XML Charts.  It’s a good tool, easy to use, looks reasonable to the eyes – and most of all it was easy to flex in to something more powerful, with a bit of php, ajax and jquery.  I had it set up to work really well – the forms refreshed the graph on the fly; the table view read the same XML file as the Flash graphing tool, and transformed it in to a HTML table (in the table tab).  It was so dynamic and I loved it as a basic prototype – but I’m just not sure of the best next step yet.

Example graphing prototype

It worked really well - there was one tricky part though, the tabs loaded content with jquery ajax and if you changed the forms whilst in table view (for example) the graph view didn't refresh because it didn't exist. Luckily it was fairly straight forward to fix with some callback methods in the ajax call.

Oh, and another tip – the tool hated having symbols parsed in the URL for the xml data source. So my ajax table loading might be like this:  (all these variables are actually values from the form, and the events are triggered by changing the form)
$("#Table").load("tableContent.php?data=TrendData_MainIntake" + "&graph=" + graph + "&category=" + category);

But the url variable in the script that loaded the flash graphing tool had to be like this, using hex values in the path:
url = "graphContent.php%3Fdata%3DTrendData_MainIntake" + "%26graph%3D" + graph + "%26category%3D" + category + "%26type%3D" + type;

It’s fun getting all this stuff to work. I hope it turns in to an excellent project, a small part of my report is as follows if you’re interested.
Read the rest of this entry »