Stay connected with us!

stay connected with us !

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

Utilities400 Logo

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.

software development and IBM i

Software Development & IBM i

MNI Services specialise in IBM i for Power Systems (whether you know it as AS400, iSeries, or System i), bespoke software development and support.

We can recommend, supply, install, integrate, and maintain a wide range of software packages to increase business efficiency and profitability. MNIS continually invest in staff training on new packages and achieve partnership accreditations, allowing our customers to benefit from creative and innovative solutions.

tell me more...

facilities management and outsourcing

Facilities Management & Outsourcing

MNI Services bring expertise across the entire spectrum of information technology to deliver fully customizable IT services to the small business community.

By offering the knowledge and resources of an experienced team of specialists for a fraction of the price to hire a single employee, MNIS gives our clients access to the power of a big business IT department while providing the responsive, consultative support that small businesses need.

tell me more...

infrastructure, servers and computers

Infrastructure, Servers & Computers

MNI Services can supply, install and maintain the hardware and software your business needs for optimum efficiency within your IT budget.

We have vast experience with, for example, IBM i (formerly iSeries / AS400), HP, Dell and IBM servers. We offer a variety of computers, WI-FI, back-up, storage, archiving, disaster recovery systems and all the networking infrastructure you need to make your systems readily available to local or remote users.

tell me more...

disaster recovery and realtime monitoring

Disaster Recovery & Realtime Monitoring

Any IT system disaster can seriously affect your company’s ability to do business normally. They are costly, damaging, and, like the Spanish Inquisition, no one expects them.

The smart companies, however, are those who prepare for the unexpected, so that when disaster strikes, recovery is swift and seamless.

tell me more...

Recent Tweets

MNIServices Note to self, if David ever asks for a new laptop bag remind him there's plenty to choose from in the cupboard.....… https://t.co/vjj7o4yi4t
MNIServices Hidden beneath all the V7R5 excitement. One of our analysts, @DavidShears11 , writing in todays #IBMi @PowerWire ne… https://t.co/pXs48MRYg3
MNIServices Are your #windows users having trouble suddenly with #VPN connections? It could be related to an update released la… https://t.co/D1lFFE65lx
MNIServices A new video in our Exit Point Integrator series. Today looking at enrollment in Google Authenticator to enable #MFAhttps://t.co/PO3RGDmoVv
MNIServices If you find it easier to visualise, rather than read about, solutions then we've got you covered. Our @YouTube chan… https://t.co/yWVZ427Uq5