Thursday, 4 June 2009

Reporting made easy

I recently found out at work that the MI analysts have a monthly report running up to seventy pages that they need to collate. Nothing unusual there, lots of places have big monthly reports. Then I was told that it takes five days to collate.

I initially though this was a joke, afterall this report had been publised each month for the last two years. Once I was told no, we really do do this, I needed to go and get myself a cup of sweet tea. After I had recovered, I started to speak to some of the analysts to try and find out the complete story behind this seemingly terrifying report, once I started to get some of the details behind this I came to the conclusion that the main reason for the length of time it takes to create this report was down to analysts doing the same thing every twenty-five days by hand. A prime opportunity to automate if ever there was one - and I had been after an excuse to create a .Net DAL for the team as well :).

There is nothing outstanding about this project at all, there isnt really any cutting edge tech being employed nor is there any new, fancy way of doing things being discussed. There is rarely anything taxing when it comes to generating reports. The most complicated thing you need to do is validate all the information that you are presenting, once the formula is there you are safe in the knowledge that your report will continue to churn out the required data for ever, the only time you will ever need to test it is when a new requirement is added to the schema of the report itself.

The uber report I am going to tackle just now is intended for publication via printed media. Because of this, I am confident in the knowledge that once the report has been created, no one will need to copy and past the data from it into another application - I know it gets distributed on paper. So, I know that I dont really need to provide a spreadsheet, csv, .doc or peice of XML initially. This is good news for me, as I only need to worry about one format, and I have decided to go for PDF. I had a look through the report a few days ago, when initially this task was given to me. Basically, its just a collection of graphs and tables, very little text, this just gets better as far as I am concerned. One format to worry about and very little wording to go into it - bliss!

So, what do I need for this to work? I need:
  • A graphing lib, I am going for ZedGraph. Its open source and does everything I need.

  • A PDF generation lob, for this I am going to use PFDJet. This is a commercial product, but there is also an open source version. As I dont need to do anything that fancy, it will do for me.

And thats pretty much it, all I will need to do is get the data I need from the db and use it to create some graphs and charts which go into a PDF report. Couldnt be simpler really, could it?

After a good planning meeting with the team lead and analyst I would be working with, it turns out that there is a similar system already in place for creating the basis for some of the data needed to create this report. After this session, I found that there is a monster Oracle DB somewhere in the depths of the core business that holds all the information we need. With the interesting bits to one side, I was then told a fairly comical story about Oracle, MySql and Access all doing a funny dance...

The only challenging thing really is the design of the report. Now that people have learned that I am automating some report generation, it seems like everyone has a preference as to how it should look. Right now, I am not concerned with fancy graphics or logos, right now the only important thing is getting the correct data into the report in a logical manner...

No comments:

Post a Comment