The whole idea of this blog was to push out interesting news here first. Instead once again I'm getting it here last. For those that are not on any of our mailing lists, we now allow designing reports in Excel. We did a couple of interesting things with this.'
First, we support some (not all) Excel functions in our engine. So if in generating a report a line in the template becomes 10 lines of data from a database, and the row under it is a sum(B3), that sum is now applied to all 10 rows as sum(B3:B12). If the output is an XLS file, then Excel will re-calculate the sum - but this is still incredibly useful because the range for the sum was expanded to include all inserted rows. And if the output is Word, PDF, HTML, etc, it is even more valuable because the engine calculates the sum and displays the total as none of those programs can do so.
Second, we support the excel number formatting. This is the capability to say a positive value of 1234.56 is displayed as $1,234.56 while -1234.56 is displayed as ($1,234.56). Again, this means you get the desired output for Word, PDF, HTML, etc., and again, this is something that cannot be done in a Word template.
The beauty of the above (and some other items) is threefold. First, if your output is a spreadsheet, even if it is as a PDF file, then the logical way to create it is in Excel. Second, for people used to Excel for setting up their data, this makes for a much easier design tool. And third, it allows for functionality that does not exist in any other reporting package - but provides that capability in a manner that people already know how to use.
Why we did this now
Ok, so we have this new functionality that turns out to be really useful (and we are getting a strong response to). All because we studied the market, determined what was needed next, and executed - right? Well no.
Actually we were discussing it and had moved it near the bottom of the feature list when one developer got upset claiming that he could implement the whole thing in 2 weeks. The developer in question is very good and we had nothing critical on his plate so I let him run with it.
As anyone who works with developers could predict, it was not done in 2 weeks. But it was a lot further along than anyone had predicted. The engine was accomplishing the basics. So we continued. The formula evaluator, number formatting, and image handling (that one surprised me) all took quite a bit of additional time. And then porting AutoTag over to Excel took almost as long as all the other work put together.
But we're mostly there. We've put it out to get feedback on the feature set and there may be some final tweaking based on that feedback. And the test group still finds bugs daily - but the bug count is rapidly declining.
The key to the effort
The key to being able to do this so quickly was we support the new Excel 2007 XLSX file format only for templates (we do output to XLS and XML(SpreadsheetML)). Because Microsoft shipped a file filter for Excel 2000/XP/2003 to support reading and writing XLSX we were able to not bother with XLS and that saved us a lot of time - but older versions of Excel can still be used because of the MS file filter.
So there you have it, a terrific effort by everyone on the team. If you're interested in more detail on the new Excel support, please click here for the datasheet or click here to get the demo.
thanks - dave