Retirement Remittance with ActiveReports Web Service

Business Situation

When TSA Consulting Group needed a powerful and easy-to-use financial application that streamlined the processing and compliance analysis of thousands of individual retirement transactions, they called Bit-Wizards, a managed Microsoft Gold Partner headquartered in Fort Walton Beach, Florida. The proposed system had to be able to manage millions of dollars in financial transactions, be adaptable for ever-changing IRS tax policies, integrate with existing accounting applications, and of course be simple to administer. The system that Bit-Wizards created became Electronic Process for Automated Remittance Service, or EPARS.

EPARS, an automated business solution for public school districts, allows employers to electronically remit retirement contributions to multiple vendors through one simple transaction, thus relieving the school district from the administrative challenges of parsing the thousands of individual contributions and distributing those contributions to any number of different retirement plan vendors.

Additionally, TSA provides a compliance system to ensure that the financial transactions are within IRS regulation limits and also warns the administrators when individual contributions are approaching the prescribed limits.

Business Challenges

There are currently thousands of school districts using the EPARS service, and each month TSA Consulting continues to add even more school districts to this rapidly growing system. Early on, as the number of users grew, Bit-Wizards recognized that the existing reporting solution was not going to be able support the future demand for reports due to TSA’s exponential business growth. Therefore, they began searching for a solution that was cost efficient to migrate to, and that would provide the necessary performance, functional features, and throughput to handle the expected future growth of the company.

Why ActiveReports

Kevin Grohoske, Director of Software Engineering, states, “We have been a long-time believer and a long-time customer of ActiveReports, so it was an ideal fit for this project. We have well over fifty customers for whom we have developed ActiveReports solutions over the years, and plan to continue using ActiveReports in the future.”

Solution

“ActiveReports was an ideal solution because we were already using it within the web site, but in a manner that was not conducive to supporting the future expected growth. Our existing ActiveReports solution, due to the size and complexity of the reports, was actually maxing out the memory limitations of the ASP.NET framework. We found an article on the GrapeCity website that described how to create a reporting service with ActiveReports. Matt Parry-Hill, Bit-Wizards Lead Developer, used that article as the baseline of our custom reporting solution. Some critical capabilities that we added were separating the report generation from the actual web server, queuing report requests, and creating a multi-threaded backend service. The resulting solution has since processed over half a million reports without a single outage.”

Bit-Wizard’s reporting solution is comprised of a front-end EPARS web site that generates the report request and uses AJAX to provide the end user with a simple, user-friendly experience.

EPARS Payroll Run Report

The front-end web site’s request is passed to a web service that manages a queue of report requests from among the hundreds of simultaneous users. The report queue is processed by one of the currently running 10 background threads. It can be run from a separate reporting server and it uses ActiveReports to acquire the data, generate the visual report, and save the completed report image to the database for auditing and future requests.

Finally, the service notifies the EPARS web site that the report is available and then EPARS simply retrieves the report image and displays it using ActiveReport’s WebViewer component. This approach allows users the flexibility to save the resulting report in one of the many supported file types such as RTF, HTML, Adobe PDF, Microsoft Excel XLS, TXT, and CSV.

The resulting system averages over 100 users a day and generates over 4000 reports a day with build times that average just milliseconds per report. It has created over half a million reports since its inception, and the only downtime it has had is during updates and server reboots.

The lifecycle of a report in the Windows Service in EPARS is described in the following steps and flowchart.

  1. Report request data is submitted to the reports web service. (Request contains all necessary information for building the report, including database connection string, full path to the reports assembly, etc.).
  2. The reports web service receives the request and stores it into a queue. Information regarding the request is also stored in a SQL Server database. (In this implementation, the report request data is serialized and written out as a file to a queue folder.)
  3. The reports Windows service uses a file watcher to pick up the report request and adds it to an in-memory queue.
  4. A timer fires periodically inside a Windows service to check the queue for reports to process.
  5. Each pending report request is removed from the queue and assigned to a worker thread from a thread pool for processing.
  6. Each report request is de-serialized and the status of the report build is periodically written to the reports database.
  7. The report is processed by a worker thread.
  8. When the report is completed, the report image is saved to the database in the format requested by the caller, for example, RDF, PDF, or HTML.
  9. The request is completed and the worker thread is returned to the thread pool.
Reports Server Design

Benefits

  • Improved Productivity
  • Increased Accuracy
  • Improved Communication
  • Increased Website Performance
  • Cost Savings through:
  • Reduced Complexity
  • Reduced Support and Maintenance Costs
  • Reusing Existing Report-Building Knowledge

Looking Ahead

Since its implementation, EPARS has handled over half a million reports, with no performance problems. Use of EPARS has grown from 20 to over 1,000 school districts, and it generates an average of over 4,000 reports a day.

This solution continues to create a huge return on investment (ROI) for the client and can be leveraged for other non-website report needs in the future. Matt Parry-Hill, Lead Developer at Bit-Wizards, summarizes, “We have not yet approached the technical limits of this solution and expect the reporting solution will continue to extend and support the continued success of TSA Consulting Group’s financial products.”

Bit-Wizards Bit-Wizards

Kevin Grohoske, Director of Software Engineering

Download

Published 8/21/2012