Reporting on the cheap

[Warning - heavy technical post. Non-technical summary: it's hard to do good reports in Excel.]

I'm embarking on a project to produce some reports that need to be sent out to various people around the world. One of the key requirements is Excel format. In the past I've done some research for this and I've not found a good, cheap solution.

I'm amazed at this given the popularity of Excel. What I mean by reporting is the ability to extra data from databases, produce reports that are well laid out, having groups within groups. Another requirement is the ability to change the layout without having to dive into some underlying programming.

My research turned up some products that cost a few hundred pounds, but I would have thought this is such a common requirement that some sort of open source solution was available.

One solution we used for our management reports is Excel macros. There the layout was pretty fixed, and so the customisation was limited to how many levels of detail you went to, what font and size each had.

What we do currently for the report that is being revamped at the moment is use Crystal Reports, to output to RTF. Its Excel output is hopeless, which is not surprising given that Crystal is heavily orientated towards the printed page. New versions keep on coming out, but they don't change the underlying approach. (Even its RTF output produces RTF that only works on Word for Windows, though you could blame all the other RTF handling programs for not implementing the full RTF spec.)

There are two possible solutions I'm evaluating for this problem. One is XML, XSLT XML-FO and other leading-edge acronym-heavy things. The downside to that is that layout isn't as WYSIWYG as option two: OpenOffice for layout and Python to fill in the data. Fortunately I've got some colleagues who know more about the former than me to help evaluate those two options.

tags: reporting
Excel
XML
Crystal Reports