Automator Excel report row height issue / Datawatch praise / Tip
Altair EmployeeSynopsis:
Issue - Automator was creating Excel files with a short row height of 9.5 while the Classic was producing great looking reports with a height of 15
Fix - Server (for the Automator) needed to be restarted
Service - Datawatch provides patient, diligent and skilled help. I've worked with John, Mark, Zhanna and Nick, most recently,… all of whom are excellent.
Product- Datawatch has a series of product which provide great results; currently using v15 of Monarch Classic, the Automator and starting to test DataPrep.
As Paul Harvey used to say when I was a child, here is the rest of the story:
The Problem
As a controller, I produce reports for various departments/facilities to summarize their activities. One morning I realized the Excel report that the Automator distributed had rows that were squished too short that it forced the user to modify the row height to be usable. When I ran the same projects from Classic, the Excel files looked great! I first searched the Datawatch Community, but didn't see a similar issue posted. Hoping it would be an easy fix for a stupid error on my part, I sent an e-mail to Datawatch support. It became apparent this was not a common error, as the techs were diligent in asking for the setup and log files to try to duplicate the error. In analyzing the problem, I knew the inputs for these processes, that had been working great for months, had not changed. The issue wasn't happening with one project, but rather to every project that created an Excel file. The problem was not occurring when run from Classic... only with the Automator. We had been using the same version of Monarch/Automator for six weeks. My thought was that the problem was with the environment in which it was running, but when I reached out to our own IT manager, unlike the cooperative Datawatch group, he insisted that only the changes were the occasional Windows updates and that I needed to work with Datawatch. After the techs at Datawatch couldn't duplicate the issue I experienced, the settings had been checked and the server file versions had grudgingly been obtained from the IT manager, our attention again turned to the environment - the server. I escalated the issue to the IT director, who started to check out our theories. The last Windows update had been put into place the prior week, but the process had continued to work afterward. I had narrowed down the start of the error to a 24 hour window. I suddenly realized there had been the odd occurrence of five simultaneous lightning strikes in the area that afternoon which produced earthquake type of quakes for 30 seconds, the timing of which was readily documented on Facebook. The IT director diligently checked, but nothing looked strange during that time period and the server was on a backup and surge protector. Finally I requested for the server to be restarted. That fixed it! The lightning must have disrupted some connection enough to produce the formatting changes.
Moral of the story is to think through the problem, check resources like the Datawatch Community site and have your IT group make sure your hardware is performing correctly before bothering the good people at Datawatch!
Datawatch Support
The service that I've gotten over the years from Datawatch has been excellent. They've always been polite, knowledgeable and patient. I have never worked with a better service group.
Datawatch uses
For years we only used Datawatch Classic as a data capture tool to summarize data into a small enough table to paste into Excel for VLookup report templates. Two years ago we got smart and started expanding our use to create actual reports and added the Automator. Currently, we use them in a variety of ways. The IT department presents two huge files every day for which the Automator is scheduled to run various checks to compare file A to file B and vise-versa. Our receipts group provides it with a messy text file every day that the Automator monitors and immediately produces the necessary exception summary, e-mailing the resulting report to them. We've also replaced another software that our IT dept had been using to distribute pdf reports, which we couldn't even modify in-house. Now, with Monarch/Automator, we have the flexibility to make changes and provide the reports in a more friendly Excel version. The report keys help to create separate reports and distribute them automatically by email to our related entities. For most reports, I like to have one step create the file with a summary or complete listing and then have a second step append to the same file to add tables for each distinct key, such as a facility. For our litigation group we create over 150 facility files, using keys, and then use a macro to distribute them (I'll discuss more in depth in a moment). For our own department, we summarize/differentiate activities and inventory (over a million lines of data) using keys and filters which greatly speeds our process. In most every case, after the job has been completed, the Automator moves the input file to an archive folder, using built-in macros to uniquely name them by date. Unlike Excel or queries, Datawatch products can combine and use so many various types of files. Unlike Access, Monarch is more user friendly and the Automator has a built-in way to e-mail the product. However, the best part is that once most of these projects are set up, you don't have worry about them... they'll continue to do their job at either a specific time, or when presented with a data file, without having to be involved.
Tip
I love the way Datawatch allows keys to separate separate reports and distribute them to specific recipients. However, instead of setting up 150 distributions for our litigation group, knowing they'd constantly change, I decided to just allow the Automator to create the 150 detail files and update a tab within an Excel macro file. I had found and modified a macro (https://www.mrexcel.com/forum/excel-questions/970834-macro-create-email-excel.html) from the internet, to distribute the detail files with Outlook based on the summary list it was given. I let the litigation manager take ownership of the Excel macro file so she could keep the distribution list updated and verify the data before pushing the macro button to start the process. The Automator simply sends her an e-mail to let her know when the files are ready.
------------------------------
Brian Johnson
Controller
Community Health Systems, Inc.
BRENTWOOD TN
(615) 465-8512
------------------------------