Self-Service Reports System (Excel and MS Outlook Macro)

Hi All,

This post is probably only useful for techies familiar to VBA.

I thought I'd pass on an idea that we've implemented and is working great with good feedback.

To give some context, our SME company only uses Jet Reports in our Finance Team and we often get asked questions from our Ops team like: can you please provide me a statement of account for customer X, how much do we hold in deposit for customer Y, how much does customer Z owe us? For each query, we run the appropriate jet report for the required options and send it in an e-mail attachment.

We automated this process into a self-service system so that the Ops team can run Jet Reports themselves. Put simply, we have a computer setup with Jet Design license and MS Outlook running all the time. A user will e-mail the self-service computer a specific instruction with options via the subject header. On recieving a new e-mail the self service system will run the corresponding Jet report for the provide option(s) and reply with the attachment (or flag invalid instructions). For example, "STATEMENT [CUSTOMERA]" will run the statement report for CUSTOMERA and "DEPOSITS [CUSTOMERB]" will run the deposits report for CUSTOMERB (as at today's date). We have 20 instructions all together and it only works for internal recipients/senders.

Through trial and error, there have been a lot of complex updates to make the system run quicker and without user interaction. For example, each .xls report now has a macro in it which is .ontime called to refresh the Jet Report and auto-saves the finished report to a 'Completed' folder - which allows macro control to be passed back to MS Outlook instead of waiting for the report to finish (i.e. allowing multiple requests to be processed simultaneously). Using a timer, MS Outlook checks the 'Completed' folder for finished reports every 30 seconds and removes the macros before replying with the attachment. I've also added an instruction called REMINDER (e.g. REMINDER [DEBTORS [BUSINESSUNITX],12/07/2013 18:00,MONTHLY]) which replicates the Jet Scheduler to schedule requests on a ONCE, DAILY, WEEKLY or MONTHLY basis. The REMINDER works by adding a reoccuring calendar item, and reminders trigger a macro to send itself an e-mail with the valid instruction and lists the recipients - which in turn triggers the original macro to run the corresponding report.

It's been of great value to the ops teams, especially as the system works out of hours. I just thought it might be useful to share the idea of what's possible so that other teams may benefit, if needed.

Kind regards,
SD