Automating Client Reports

We just finished an interesting project with an Elite 3E customer. It was building on work we had done with their Keystone database but interesting nevertheless.

The business requirement is that the firm has a number of clients that regularly want information about their matters in electronic format, most typically Excel. The information varies from client to client, either in content, layout or sort sequence. What they did previous to our involvement was highly manual, employing the equivalent of a person just to manually create the reports.

To address this we developed a program that allows them to register a report for a client. The first step is to give it a report name, some grouping and filtering information, column sequencing, report headings, the file format to be created (Word, PDF, Excel) and then the email address of who it is to be delivered to.  The next step is to pick the columns of information to be included on the report by ticking check boxes.

Once the report is registered, it can be set up to run every day, week, month, quarter etc… for a particular range or period of time. The user runs the report production program, selecting which schedule to run. The program then automatically requests SQLServer Reporting Services to generate generic content and Excel macros to format and customise the layout for each client..

As it turns out, they typically email to the partner/secretary responsible for the client first to check the data quality rather than email the client direct. The document is reviewed first and then forwarded to the customer. One manual step but at least people aren’t involved in the monotonous task of extracting and formatting the data each month now!

The net result from the firm’s view point is that the process is significantly less labour intensive and setting up a new report is simply adding a line in a program we have created. The number of reports produced like this now for clients is well over 50 and, because it is all automated, the costs involved have diminished considerably.