Chris21 Reporting and Excel Macros
I want to share an example of a Chris21 client success story that I was involved in. The client asked me to look at a current Chris21 reporting process and to help them to improve this process.
The answer was to create an Excel macro. While I don’t want to go into detail about the requirements, I will describe the outcome and the benefits the client has achieved.
The Problem
The source Chris21 report is run over the Account History (EMPIT) file. The raw data is published to Excel and then requires significant formatting to produce the required reports for managers and finance.
The problem was that this formatting takes between one and two days to complete and this process is undertaken each fortnight after the payrun is completed.
To enable this process there was some customisation to Chris21 and this has meant there is some duplication of data entered into Chris21.
Obviously all of this is at significant cost to the organisation.
The Solution
An Excel macro was developed to take care of all the manual processing without using any customised forms in Chris21. The solution is a macro that does the following:
- Sorts the data from the Chris21 report (PIT) based on the Account Number string.
- Adds report breaks and totals.
- Emails segments of the report to the appropriate recipients.
- Password protection is enabled on reports that are emailed.
The macro formats and emails more than 80 reports. The process requires no user intervention, except to get the macro started. It takes approximately five minutes for the macro to do the work.
The Benefits to the Client
The client has benefited greatly from this solution. The major benefits are:
- Significant cost and time savings.
- Improved reporting accuracy.
- Reduced maintenance – no customised Chris21 forms to maintain.
- Any employee can run the process – it no longer requires specialised knowledge.
This macro has been running successfully for the last four years.
This example shows how taking a different approach can deliver greater efficiencies and cost savings. Sometimes it’s worth going through the exercise of analysing current processes and looking for a better way. For more information about using Excel macros see my blog: Chris21 Reporting – How Excel Macros Can Reduce Processing From Days to Minutes.
Leave a comment or email me at tony.danher@systemlogic.com.au if you currently have a similar situation that you think may be able to be improved. I’ll make some suggestions if I think it can be done better.
Excel Macros definitely benefit a business with Chris21 report outputs. The flexibility Excel Macros can provide with the right coding is unreal. From garnishee listings to union fee listings through to superannuation code breakdowns, leave reports etc: its amazing.
A few examples; I’ve managed to reduce 2 weeks of manual listing creation and sending to one button and 20 minutes of running the actual macro with built in flexibility. I can also create, import, modify and export xml files generated by the system for last second changes for uploading to a clearing house and generate pdf breakdowns of the xml contents.
Another macro I just worked on was an ABA File generator for Bank File uploads to NAB/CBA using direct entry (all to ABA and legal specifications – trial found). The possibilities are endless.
If you’re a C21 reporting analyst or a C21 systems admin I highly recommend learning excel macros. Its power and accessibility to other Microsoft Office products and services is extremely useful.