Data extracts and formatting on IBMi

The Challenge

One of our customers had a process set up for generating CSV documents on the IBMi and emailing them to the requesting user. They wanted to improve upon this by allowing conditional formatting on a summary row.

To save time and development they wanted to continue using the existing process to generate the data, the focus was on a new process for formatting.

The Background

The csv was initially designed to aide with product costing, to check the data setup against the actual production. Any production issue reported would then have its data collated in a spreadsheet and forwarded on to the production managers.

Later, its use was extended to take into daily production meetings to talk through any differences. This required that the data supplied be expanded and the report sent directly to the production distribution group.

As the report became more widely used, specifically by sales and planning staff, to give an accurate picture of what is currently achievable another rewrite was required.

The Solution

The customer had been using RouteOne from Utilities400 for some time. Previously they had used it to generate formatted PDFs of their invoice/purchase order documents.

The software can also handle generating XLS documents, so it was a natural choice to investigate for this new process. It also has the advantage of working from the IBMi physical file, meaning the system did not have to generate the CSV file beforehand.

RouteOne has an option to retain the stylesheet within an XLS template, meaning that the formatting could be applied to the spreadsheet and would remain there even when overwriting with new data.

We used conditional formatting, if the first column was empty (indicating it is a summary line) then:

• Condition columns to be currency, rather than plain number
• Negative values highlighted in red

An added bonus, we were able to set the header formatting to word-wrap to save the user resizing the columns when the spreadsheet came out.

Another challenge RouteOne helped with was avoiding a new version of the report being created. The customer wanted the new style spreadsheet to be emailed out on a trial basis, without impacting the existing run. As the new version was removing several columns from the spreadsheet, RouteOne would select only the columns required from the physical file. Record selection criteria also meant that the summary line could be left off the “old” version, by ensuring when the first column was empty it would not be output.

This also benefited the customer as no programming changes had to take place, meaning that non-IBMi trained personnel could make the amendments and create the XLS format download.

The Benefits

With this simple change to their existing routines the customer didn't have to worry about manually formatting each time the report was generated. This had a knock-on benefit that reporting to external parties, which before had to be a staff driven process, could now be automated when the software emailed out the report.

A further benefit of RouteOne is the simplicity of use. If the customer needed a new version of the report, or a new workfile, then program changes would be developed and tested. With RouteOne you take the existing output, amend the layout and formatting, and you're done. This means you don't have to be a programmer to make use of the software, RouteOne will handle that for you. If at first you don't succeed, within minutes you can tweak the layout and try again until you're happy.