Automatic addition of values from user-defined reports from HELIOS iNuvio to an Excel table

For our client using the HELIOS information system, we solved an interesting and rather complex problem: How to use aggregated data from a HELIOS report for an automatically generated report in Excel, even though the HELIOS report and its structure change frequently.

The task was to develop a program to automatically add values from user-defined reports in HELIOS to an Excel spreadsheet. This is done on a daily, weekly and monthly basis, where some values are only added currently (e.g. weekly), some values are also added retrospectively, replacing values already stored in the Excel spreadsheet.

Why have reporting in Excel when you can look at the required values directly in HELIOS? Displaying in Excel is easier for many users. Many of them do not even have access to HELIOS. And in Excel, the data can then be further analysed and historical data for the last few years are available, to which the data from the current period from the HELIOS report is always added. Also, links from one report to another are conveniently used in Excel spreadsheets, which is not possible in HELIOS reports.

How did we approach the solution to the assignment?

The report in HELIOS, which is basically a " clicked" SQL query, consisted not only of various joined tables, but also a condition where unit numbers were added. However, we needed this report for our program, which processed its data and automatically filled in a different aggregated excel report every day (just running the SQL query of the report was not enough).

There were several possible solutions. One of the easy-to-implement ones was to pull the SQL query from the report into a text file from HELIOS, and when changing it in HELIOS, the user would have to change it simultaneously in the text file. However, it was clear to us that forcing a "standard" user to take this action was virtually impossible. We had to think put. How to modify the program to recognize the change in the HELIOS report and at least alert the user to the change? All these modifications were impractical and would require the interaction of the user and the "administrator" of the program that automatically generates the reports. We suspected that there must be another solution - complicated to program, but simple for the user.

And we came up with one.

We used an existing SQL free tokenizer and used it to create our own simple parser-tree SQL query from a retrieved report from HELIOS. Once we had the SQL query processed in the parser-tree, we could modify it as needed, i.e. remove unnecessary columns, modify conditions, group differently, just model the SQL query according to our needs. It was no longer a textual SQL query, but it could be manipulated as an object structure.

The beauty of this solution lies in the fact that the user has a free hand to modify the report in HELIOS (of course, keeping the selection of the necessary columns, etc.) and our program can handle these changes.

We have succeeded in developing a way to automatically populate Excel tables with calculated values from user reports in HELIOS, with the result that the program can handle changes to user reports in HELIOS and minor modifications to the Excel table without manual intervention.

Currently, the client uses the solution to retrieve two dozen records on a daily, weekly and monthly basis.