Information drives operations at most enterprises. The faster and easier end users can extract the information they need from host databases, the faster their company can respond to customer needs and market opportunities.

Frequently, having access to a product that facilitates information movement and report writing can save time and effort that's better directed to other activities.

This was the experience of Homac, a manufacturer and supplier of electrical connectors and cable accessories, based in Ormond Beach, Florida. Homac has 420 employees split between its Florida headquarters and a manufacturing facility in Corcoran, California. The Florida site is home to 160 employees who use PC clients connected to a single iSeries 9406-520 running V5R3. Homac's IT staff consists of a single network administrator and the MIS Director, Mary Anne Howell.

The Old Way

It used to be that end users relied heavily on Howell for accessing data and generating reports from the company's enterprise resource planning system, BPCS.

"We don't allow use of Query/400 by end users other than queries I'd already set up. [The end users] don't understand the file structure or the field names," Howell explains. So the standard procedure was for end users to ask Howell to generate any queries or reports that weren't already established. Over time, this consumed a larger and larger part of Howell's work day.

"We used to send a lot of reports from the iSeries, but then eventually everybody wanted something different," Howell recalls. This included different ways of sorting data, downloading data to Microsoft Excel, and other requirements. "Sometimes we couldn't pull the data directly, so we had to create an RPG program that creates a file with the data and then map queries to that file," Howell remembers. An added time-consumer was the need to use Client Access to transfer data, a program only Howell understood well. "It was a two-step process. You had to run a query on the host, create the file, and then create a transfer. It was just too complicated for the end users."

Before StarQuery, Homac collected quotes for parts that were entered into BPCS on the iSeries, after which the quotes were compared with a cost file. Then, using Client Access, the data was transferred and brought down into an Excel spreadsheet.

"One problem is that our cost file doesn't always contain a cost," says Howell. "We used Query400, but you can't do things like outer joins, which is a problem, as there might be an item on a quote with no cost, and not being able to do an outer join meant the item was missed completely."

A Better Way

"I didn't realize there was an easier way of doing things," Howell admits, "but I'm always looking for ways users can pull data from BPCS without having to ask me for it." Homac had earlier begun using Symtrax Corporation's Compleo iSeries spool-file management solution, so when in the fall of 2003, Howell's Symtrax sales rep recommended she try Symtrax's StarQuery product as a possible answer for her query backlog, Howell was willing to give it a try.

StarQuery lets users create reports from almost any database and pass data directly into an Excel spreadsheet. Without needing to know SQL programming language, end users can build complex queries and intelligent, customized reports on their own. Because of StarQuery's interface with Excel, users can display their queries within a familiar environment and use advanced formatting functions such as automatically updating charts and pivot tables.

"When I saw how easily StarQuery installs, I knew I'd found a winner. It's the easiest installation I've ever done, and I've installed a lot of software," Howell enthused. She liked it even better once she started using it. "I started making maps and queries to files and realized this was something I could turn over to the end users because they wouldn't have to understand the fields or files." A map lets an administrator select fields and tables from a database, apply constraints to the data if necessary, and pass it on to an end user. The end user can further customize the map, run the query, and send the results to an Excel spreadsheet without administrator intervention.

Performing outer joins is not a problem for StarQuery. By choosing the option when joining tables, Howell was able to compare the cost file to the quotes with just a few clicks of a button. "The first thing I saw was how easy it is to do," she notes. "I can now bring in files that I had problems with in the past."

Once Howell was finished with her query, she was able to link it to a button. Linking the query to a button lets her end users run the query without needing to even use StarQuery. "I created an Excel template and linked the query with a button to make it as idiot-proof as possible. Now all the end users need to do is key in their quote number and hit the update button and the information appears in Excel!"

"We haven't had to do any training of users for StarQuery," Howell relates. "When I create a query, I put an update button on the spreadsheet, and they use the first field to key in the variable-like date. You don't need training for that. If you asked most of our users to change the constraint and the date, they'd never get it. So I just put the button in Excel, give them a cell to put in the date they want, and they get the results."

Simplifying Order Entry

StarQuery resolved some frequent problems for the Homac Order Entry (OE) department. "The department takes in a lot of data, some of which includes the date we're going to ship the product and the date the order was received," Howell explains. "These two dates are never supposed to be equal unless you're shipping the same day, which is not usually the case." However, OE would sometimes forget to differentiate the shipping date from the order date, so the shipping department would take the blame every time an item didn't ship. To solve this problem, Howell created a query with conditional formatting that brought both the order date and the shipping date into Excel.

"Toward the end of the day, OE runs the query and it brings in all the information that they've entered that day. I placed some conditional formatting on the query that said if the shipping date is equal to the order date, the result in Excel would turn red. OE users can easily look at the spreadsheet and see any mistakes. This has cut down the error rate, which is something we track very closely."

Longer Term Benefits

StarQuery is now fully implemented at Homac. "We still haven't completed the rollout phase, really," Howell confesses. "We just keep adding more licenses of the Runtime version for more users. We keep finding more and more reasons to use it."

Homac has never done a cost analysis of how much money StarQuery has saved it. "The time saved, though, is huge," Howell notes.

"My view of StarQuery's benefits are purely selfish," Howell continues. "When you're the only iSeries person in a $50 million company, you can't afford to be running reports for people all the time. StarQuery has been a huge lifesaver. It lets end users get to their own data. Once I've done the map for them, I never hear from them again."

John Ghrist is senior products editor for iSeries NEWS.