February 2012

Whether the need is financial, human resources, distribution, or manufacturing, HarrisData software helps companies reduce costs and improve the essential processes that run their business. Add the data access, analysis, and reporting options that SEQUEL Software brings to the table, and you have a powerful combo for any business or industry.

Heath Kath, Technical Solutions Consultant for SEQUEL Software, Steve Spieler, Training and Services Consultant for SEQUEL Software, and Jim Doyle, IS Manager for Amigo Mobility demonstrated that power in a recent HarrisData Lunch and Learn webinar, HarrisData Discovers SEQUEL Software.

Q: Before we listen to the webinar Heath, can you give us a little background about this Harris Data Lunch and Learn and how you met Jim Doyle of Amigo Mobility. What led you to the idea for this webinar?

Heath: Well, Stacy Jensen, our SEQUEL Software Sales Manager, had the idea of showing HarrisData customers the many ways they could go "beyond the green screen" by using SEQUEL. Jim got involved because he's a HarrisData customer who was new to SEQUEL. About a year ago I did a SEQUEL overview for him, followed by a more extensive demo. His goal was to use SEQUEL to automate the process of building an invoice report that gathered data from multiple sources. At the time, that process was manual and cumbersome. He's really done some great work with SEQUEL that he's proud to share.

Q: OK, so which particular markets—finance, retail, health care, or others— and types of users do you think would find the information presented in this webinar useful and why?

Heath: As Jim's example at the beginning of the presentation reveals, almost every business or industry could benefit from using SEQUEL to streamline and automate data collection and analysis processes. This demo was designed for HarrisData clients, but across the board—finance, retail, health care, and more—all industries and businesses need to access, analyze, understand, and manage data efficiently.

Basically, the presentation helps IT type information designers see how easy it is to get actionable information into the hands of their end users efficiently. But, management and other end users also get the benefit of seeing the ways that SEQUEL can deliver data to them and the options they have to work with it.

Q: I noticed that you and Steve demonstrated many of SEQUEL's capabilities. Which parts of the demo do you think are particularly useful for viewers?

Heath: Well, let me say first that the demo was quick! Because we had less than an hour to cover everything, our primary focus was the dynamic data access that SEQUEL offers. SEQUEL lets you access live [current] database data quickly for display in dashboards, to convert to various PC formats, to incorporate in tables, and to distribute in reports. And, all of these options are available "on the spot," or scheduled on a regular basis.

As a result, end users can access information at their desktops (dashboards); in e-mailed reports; or from web browsers, at or away from the office—whatever best meets their needs.

Q: Thanks Heath. For those of you interested in a quick demonstration of SEQUEL, check out Harris Data Discovers SEQUEL Software. The session starts with Jim describing how he used SEQUEL to automate and streamline an invoice report at Amigo Mobility. Heath and Steve follow with a short, high-level summary of SEQUEL and a quick demo. Enjoy!

Take Your Reports to the Next Level

By Steven Smith, Technical Consultant

Introduction

When you need to take your reports to the next level, SEQUEL ViewPoint's optional Client Report Option (CRO) is the way to go. CRO lets you design reports for PC printer output using IBM i data. You use its graphical design interface to specify fonts, colors, pictures, shading, borders, conditional highlighting, and more.

Benefits

CRO offers the following advantages:

• A faster, direct connection to IBM i databases because no ODBC connection is required—SEQUEL views and tables provide the underlying data access capabilities. That means you can summarize or reformat data before it's delivered to the client workstation.
• For users designing reports, CRO presents the full field attributes and descriptive text, plus field names.
• All Client Report objects are managed and saved on the IBM i system. This means that it's easy for multiple users to share common reports and to include report objects in regular backups.
• Because CRO uses standard IBM i security, plus additional SEQUEL record-level security, you can restrict access to just the people with proper authority.

Powerful Formatting

CRO offers a number of powerful report formatting features.

Dynamic Image Locations

Dynamic image locations let you include a unique picture for each item on a report, such as an employee or student roster, a product list, or any report where it's useful to show supporting images. The report can contain a reference to a server/file location where the image is stored.

The image can be on a local drive, network drive, or referenced by a URL address. And, because the location is formula-based, you can use built-in functions to derive the location string—it's not necessary to store the image in the report object.

Intelligent Charting

You can use drag-and-drop charting, cross-tabs, and chart types (such as gauge, Gantt, and funnel) to create intelligent charts. Variables are approximated when a chart is dropped into a section, and chart design is fast and easy because charts are updated automatically when you add new variables.

Formula Workshop

The Formula Workshop allows you to work with formulas, custom functions, and SQL expressions. It consists of a toolbar, a tree listing the types of formulas you can create or modify, and an area for defining the formula.

Display String Formatting

A conditional formatting button lets you enter a formula to display number, currency, date, time, date and time, Boolean values, or even string fields as a custom string. This conditional formatting is not limited to database fields—you can use it for running totals, formulas, parameter fields, summaries, and so on.

Parameterized Sorting

You can use parameters to control sort order and the N value of a top or bottom N-report. The benefit is a more flexible report that can meet the needs of multiple users, reducing the number of reports you need to create and support.

Export to Excel

CRO exports to Excel and supports images. The Excel file looks the same as the original report. You can export the fully formatted report or just the data.

RTF Export Format

This feature makes it easy to edit RTF files. You can select two different RTF export format options, based on whether you require accuracy or editing. Powerful ActiveX Viewer

• The Export dialog box supports editable RTF.
• The visual quality of output and printing is outstanding.
• You can highlight objects displayed in the ActiveX viewer individually or in groups, and you can copy or paste them into applications such as Word, Excel, and Outlook.
• You can use the page-up and page-down keys to navigate.

Sharper Images for Presentation-Quality Reports

Because CRO supports vector-based images, you can zoom in on charts, maps, and other images without affecting their clarity.

Sample Output

The following section contains samples of reports you can create with CRO.

Barcodes

Barcode generation is easy. You just format your data to the correct barcode-type specification and use a Barcode User Function Library and font (available from third-party vendors), and you can create almost any barcode.

Figure 1. Sample Barcode

Cross-Tabs

A report listing detail information plus a summary table is a powerful way to present data. Many users find that this increases their confidence in the integrity of the data. It also makes it easy to analyze summary data alongside the details.

Using CRO, you can insert a cross-tab anywhere in a report. After selecting the three elements needed to generate a cross-tab, CRO creates the calculations dynamically. And, as the data changes, the table changes. So, after it's designed, you should never have to modify the report.

Here are two samples of cross-tab report tables you can create using CRO:

Figure 2. Cross-Tab Reports

Conditional Row Shading

Line shading is a good way to add visual impact to printed output, and disseminate and analyze information. In the sample output below, we created our own 'green bar' report by shading alternate rows.

Figure 3. Alternate Row Shading

You also can add shading to rows based on a data value. In this example, only CS-type customers are shaded. This is a way to conditionally highlight a row, not just a field.

Figure 4. Conditional Row Shading

Use an Underlay to Combine Graphic Elements with Data

The following report prints data on the same page as a chart or graphic. It shows numeric totals by state. We added a chart of the same data and a logo.

Figure 5. Using Underlays in Reports

Create Professional Looking Reports with Graphical Formatting

Anyone—from administrator to end-user—can create great looking reports easily by using the SEQUEL Client Report wizard. You create most reports in four simple steps. And, with the drag-and-drop design interface, you can design the report the way you like it. Plus, it’s easy to add a logo, or cross-tabulated table, to a report.

Figure 6. Graphical Elements and Formatting

Try the SEQUEL ViewPoint Client Report Option and enhance the visual impact of your reports.

Q & A

Q1:What is the difference between the TABLE command and the EXECUTE command?

A1: You can use both commands, TABLE and EXECUTE, to create a file from the results of a SEQUEL table. The TABLE command offers more flexibility because you can specify a different view for the table to use for record selection (or override the existing specification).

Q2: In a SEQUEL script, what is the difference between the EXECUTE and BCHEXECUTE commands?

A2: The BCHEXECUTE command submits the request as a separate job; the EXECUTE command submits the request inline with the script job.

IBM i Solution Edition for Help/Systems

Purchase SEQUEL (or other software solutions from Help/Systems—Robot Automated Operations Solution; PowerTech IBM i security solutions; and Bytware antivirus and monitoring solutions for IBM i) and enjoy big discounts on training, services, and IBM POWER7 systems.

For details, contact your local IBM Business Partner, or Doug Fulmer at doug.fulmer@helpsystems.com, or visit our IBM i Solution Edition web page.