viewpointuserguide

SEQUEL

ViewPointClient/Server Data Access for the System i

User Guide

CopyrightsCopyright 2000 - 2007 Help/SystemsThe software described in this document is furnished under a license agreement or nondisclosure agreement. The software may be used or copied only in accordance with the terms of those agree-ments. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or any means electronic or mechanical, including photocopying and recording for any purpose other than the purchaser's personal use without the written permission of Help/Systems.This publication contains examples of data and reports used in daily business operations. To illus-trate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental.

Help/Systems6533 Flying Cloud Drive, Suite200Eden Prairie, MN 55344

Phone 952/933-0609Fax 952/933-8153

[email protected]

If you find any errors or problems with this documentation, please notify Help/Systems.Help/Systems does not guarantee that this document is without error. The information in this docu-ment is subject to change without notice.

TrademarksAS/400, iSeries, System i, DB2, Operations Navigator, Client Access Express and Client Access/400 are trademarks of International Business Machines Corporation. Windows and Microsoft SQL Server are trademarks of Microsoft Corporation. Oracle is a trademark of Oracle Corporation. MySQL is a trademark of MySQL AB.Any individuals or corporations who violate these copyrights and trademarks will be prosecuted under both criminal and civil laws and any resulting products will be required to be withdrawn from the marketplace.

3/17/2007

Table of ContentsWelcome to ViewPoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

Sequel ViewPoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1Contacting Help/Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2ViewPoint Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Install ViewPoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3Get the Latest Version of ViewPoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

ViewPoint Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5ViewPoint Explorer Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5ViewPoint Explorer Menu-Bar Reference . . . . . . . . . . . . . . . . . . . . . . . . 8ViewPoint Explorer Toolbar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . 9Controlling the Object List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10Working with Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14Creating a Shortcut . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16Shortcut Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Edit a Shortcut . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Using Command Line Switches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29Visual Basic Code Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33ViewPoint Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33ViewPoint Defaults . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

ViewPoint Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Directing ViewPoint Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Displaying View or Table Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42Dynamic Regrouping / Show Detail . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44Custom Display Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Create a Custom Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51Custom Layout Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Graph View and Table Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61Printing Results Locally . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64System i Print . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65Displaying Report Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66System i Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67Create a PDF Image Overlay . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68Saving Results as a PC File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 iii

Saving Results as an System i File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

Saving Results as an System i Document . . . . . . . . . . . . . . . . . . . . . . . . 76Sending Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77Schedule a ViewPoint Job . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78Prompted Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82Display Results Menu Bar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . 86Display Results Toolbar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87Changing the Option File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

ViewPoint View Designer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89New View Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89Actions Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94Template Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95Design View Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96Design View Menu Bar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99Design View Toolbar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101Open an Existing View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102Create a New ViewPoint View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102Adding Files to the FROM Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103Adding Fields to the SELECT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . 112ViewPoint Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114Choosing Records with the WHERE Clause . . . . . . . . . . . . . . . . . . . . . 137Adding Fields to the GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . 137Choosing Grouped Records - HAVING Clause . . . . . . . . . . . . . . . . . . 138Sorting Records with the ORDER BY Clause . . . . . . . . . . . . . . . . . . . 138Merge Records with UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139Adding Variables to a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142Import Query Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153Create a Separate View from Embedded SQL . . . . . . . . . . . . . . . . . . . 159View Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162Saving View Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170Recover View Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

ViewPoint Report Designer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173Report Designer Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173Report Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175Report Designer Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176Design a Report with the New Report Wizard . . . . . . . . . . . . . . . . . . . 177iv

Select an Existing View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182

Report Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183Report Designer Menu Bar Reference . . . . . . . . . . . . . . . . . . . . . . . . . 185Report Designer Toolbar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Adding, Copying, and Deleting Lines . . . . . . . . . . . . . . . . . . . . . . . . . . 188Adding Fields and Literals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192Creating Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199Report Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209Saving Report Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212

ViewPoint Table Designer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213Table Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213Table Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215Table Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220Design a Table with the New Table Wizard . . . . . . . . . . . . . . . . . . . . . 224Table Designer Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229Table Designer Menu Bar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . 230Table Designer Toolbar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232Defining Table Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233Defining Table Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238Defining Table Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242Table Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247Saving Table Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248

ViewPoint Dashboard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249Create a New Dashboard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250Dashboard Menu Bar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260Dashboard Right-Click Menu Reference . . . . . . . . . . . . . . . . . . . . . . . 261

ViewPoint Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263ViewPoint Scripting Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263ViewPoint Scripting Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264Definitions and Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265Design a New Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266Design Script Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267Design Script Menu Bar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268Design Script Tool Bar Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270Adding Commands and Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 v

Working with Commands and Comments . . . . . . . . . . . . . . . . . . . . . . 273

Prompting Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276Command Prompter Menu / Button Options . . . . . . . . . . . . . . . . . . . . . 277Inserting ViewPoint Objects into a Script . . . . . . . . . . . . . . . . . . . . . . . 278Working with Script Lines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279Import a CL Source Member . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280Using Runtime Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282Placing Variables in a Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283Defining Script Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286Script Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287Printing Script Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289Saving Script Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290Working with Existing Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290Running a Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291Submit a Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292Trouble Shoot with Step Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293Working with Bookmarks and Breakpoints . . . . . . . . . . . . . . . . . . . . . 294Script Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

Drill Down Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299Drill Down Application Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299Drill Down Sample . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300New Drill Down Application Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . 301Elements of the Drill Down Application Designer . . . . . . . . . . . . . . . . 307Drill Down Application Menu Bar Reference . . . . . . . . . . . . . . . . . . . . 308Add Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309Add Option File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310Select the Start Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310Edit Application Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311Create an Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313Drill To Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314Drill Down to Other Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316VPSHELL Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318Drill Down Application Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319Drill Down Preferences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320Saving Drill Down Application Definitions . . . . . . . . . . . . . . . . . . . . . 322Run a Drill Down Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323vi

Client Table Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325

Client Table Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326Client Table Results Menu Bar Reference . . . . . . . . . . . . . . . . . . . . . . 327Filter Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328Data Pivots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330Add Row and Column Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331Drill to Detail Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333Show and Hide Detail Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334Export Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335Print and Print Preview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337New Client Table Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339Design Screen Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344Calculated Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345Data Markers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346Ranking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348Percents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351Client Table Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360Saving Client Table Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361

Appendix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363Connection Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363Remote Access through a Firewall . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364Product Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365Create a ViewPoint Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365Viewing Joblogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367Active ViewPoint Job Monitor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368Using Remote Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370Performance of Remote Database Access . . . . . . . . . . . . . . . . . . . . . . . 375Characteristics of Remote Database Access . . . . . . . . . . . . . . . . . . . . . 376Remote Database Problem Determination . . . . . . . . . . . . . . . . . . . . . . 379

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383 vii

Welcome to ViewPoint Sequel ViewPoint

SEQUEL ViewPoint provides a powerful and easy to use Windows interface to most SEQUEL functions. With it, you use an 'Explorer' like window to manage your view, report, table and script objects. Run views (static, prompted, and tabling) and display results in a PC window, store them as local files or System i files, or send as an E-mail attachment. You can also submit report and execution requests to the System i for batch execution. The View creation module provides a graphical interface that makes view design and modification a snap. Search for files and fields anywhere in the database, visually link tables, click and drag fields for display, and so much more.ViewPoint also provides drill-down and graph drawing facilities for advanced EIS or data warehousing/mining applications.Using the ViewPoint Report Designer you can create virtually any type of report. All the power and functionality of our renowned SEQUEL Report Writer, along with added features you would expect from a PC based product, is available in View-Point.Create dynamic spreadsheets quickly and easily with the Table Designer. The Table Designer helps you capture the "big picture" stored in your database. Summarize and tabulate large amounts of data into a few rows and columns of useful information. Display, print or download to numerous PC formats including XLS, WKS, and HTML.Create a SEQUEL Script to automate multi-step processes without writing or com-piling a program.If you need to insert graphics, control fonts, add sub reports and embed dynamic summary tables, ViewPoint Client Report Option (CRO) can do all this and more. See the ViewPoint CRO User Guide for more information.Monitor the use of ViewPoint, add an extra layer of security and manage users SEQUEL defaults with the ViewPoint Administrator. See the ViewPoint Administrator User Guide for more information. Welcome to ViewPoint 1

Contacting Help/SystemsPlease contact Help/Systems for questions or to receive information about SEQUEL ViewPoint. You can contact Help/Systems to receive technical bulletins, updates, program fixes, and other information via electronic mail, Internet, or fax.

For general Help/Systems Technology Information.Help/Systems can be reached by calling 952-933-0609.

For technical support or information.Call our general number 952-933-0609, and ask for technical support.-or-Send an E-mail to [email protected]

For information on Help/Systems products, services, and partner programs.Go to the Help/Systems home page:http://www.helpsystems.com

To download documentation, software, or the latest pro-gram fixes.Go to the Help/Systems home page: http//www.helpsystems.com

ViewPoint Requirements

System iYour System i operating system should be V5R1 or higher.

Personal ComputerYour Personal Computer should have: 20Mb of available disk space. 128Mb of memory. Pentium processor.2 ViewPoint User Guide

Graphics resolution of 800 x 600 or higher.

Windows 9x / ME / 2000 / XP / NT. iSeries Client Access Express version V5R1 (service pack SI05361) or higher

with the following installed components: - Client Access Express Required Programs- iSeries Navigator Base Support (an option under iSeries Navigator)- Commands Component (an option under iSeries Navigator - for prompting

commands in the script designer)- OLE DB Provider (an option under Data Access)

Note: When using TCP/IP, a connection error will occur if any of the required host servers are not started on the System i. Refer to page 363 regarding connection errors.

Install ViewPointThe ViewPoint CD1 contains everything needed to install the software. 1. Place the CD in your CD-ROM drive. If autorun is enabled on your system, the

installation starts automatically and you can skip to step 4.2. From the Start menu, select Run.3. Type D:\install (substitute the appropriate letter of your CD-ROM drive for D).4. Select the Viewpoint Tab, and follow the instructions on the screen.The installation process prompts you to select a type of setup: Typical - In most cases this option is the best choice. Program files, online help

and documentation as well as the web update function are installed. ViewPoint CRO and Administrator are also installed.

Compact - This option installs only the ViewPoint program and online help. Custom - This option allows you to choose which components to install. By

default, all items listed in the Typical install are selected.ViewPoint is installed in C:\Program Files\Sequel Viewpoint.

1. The CD should not be used for upgrading ViewPoint. ViewPoint updates are delivered only through our web update serviceWelcome to ViewPoint 3

Get the Latest Version of ViewPointAfter the initial installation of ViewPoint (usually from the CD), updates are easy to acquire through the Internet.From the desktop navigate to Start=>Programs=>Sequel ViewPoint=>ViewPoint Update. This will download and install the latest version.-or-Navigate to the address below. (this is the same as the above item)http://www.asc-iseries.com/VPMaint.htm -or-Download a self-extracting executable from our FTP site.ftp://ftp.asc-iseries.com/ASCftp/vpt/VPMaint.exe -or-In ViewPoint Options, configure ViewPoint to update automatically. See ViewPoint Options on page 33.Check your current version by selecting Help=>About ViewPoint from the ViewPoint Explorer menu.

versions2.bmp4 ViewPoint User Guide

ViewPoint ExplorerViewPoint Explorer Display

The ViewPoint Explorer is displayed when ViewPoint is first started. From this win-dow you can select a System i to connect to, make changes to your library list, and obtain a list of SEQUEL objects. From this display SEQUEL objects can be run, edited, or new objects can be created. The ViewPoint Explorer consists of a Menu and Toolbar section, an Object List sec-tion, and an Object Filter section.The Object List section has three tabs for Existing or available SEQUEL objects, Recently accessed SEQUEL objects, and a New tab to create new SEQUEL objects.

main.bmp

The Menu and Tool bars provide access to various functions and actions. See the Menu Bar Reference (page 8) and the Tool Bar Reference (page 9) for a complete list of these functions.The Object List panel displays SEQUEL objects for a given library on a given sys-tem. This list can be filtered to show specific SEQUEL object types, or all types.The Object Filter section provides for the selection of specific libraries, objects, or types of objects to display. ViewPoint Explorer 5

Existing Objects

The Existing Objects pane displays Sequel Objects for a given library and system.The list can be sorted by Name, Type, or Description by the clicking the correspond-ing column heading.Use the Type drop down menu to 'filter' the type of objects to display.

main_exist.tif

Customize the Columns in the Object PaneColumns of information can be added or removed in the Object Pane. Simply right click on any existing column and check a new column to display or uncheck a col-umn to remove.

existing_tab.bmp6 ViewPoint User Guide

Recent Objects

The Recent Objects pane displays recently created or modified objects - the most recent being first in the list. The number of objects displayed is controlled by the ViewPoint Options Recent Files (page 33) parameter.

main_recent.bmp

New ObjectsThe New Object pane displays seven icons that when double clicked, will start a design process for the selected object.

main_new.bmpViewPoint Explorer 7

ViewPoint Explorer Menu-Bar Reference

File

Options

Display Results Displays the results of the selected View, Table, Report or Script.

Open Edit the selected item.New Create a new View, Table, CRO Report, Viewpoint

Report, Script, or Sequel Application.Import Import a Query Definition, QM Query, or QM Form.Save Results As Save the results of the selected item as a PC file, a Sys-

tem i physical file or a System i document.Send Results To Send the results of the selected item via E-mail or FTP.Print Print the results of the selected item locally or on the

System i.Cut Add selected object to the clipboard. When pasted else-

where, the object will disappear.Copy Copy selected object to the clipboard.Paste Paste object from the clipboard.Create Shortcut Create a Desktop shortcut to the selected item.Delete Delete the selected items.Rename Rename the selected items.Properties View the Object Properties Exit Exit ViewPoint.

Logging Display the System i joblog for the current ViewPoint session, start/open a Viewpoint trace log, or start/open Client Access trace and error logs.

Edit Library List Add or remove libraries to/from your library list.Font Change the Font used for the Object List.Show Toolbar Show or hide the toolbar (toggle).ViewPoint Options

Set ViewPoint Options.

SEQUEL Defaults

Display or modify User defaults.8 ViewPoint User Guide

Help

ViewPoint Explorer Toolbar Reference

btn_VE_refresh.bmp

ViewPoint Administrator

Launch ViewPoint Administrator.

Refresh List Refresh the displayed objects.

Contents Open the Viewpoint Help System.About ViewPoint Display the current version of ViewPoint.

Display results for the selected item.

Print results to a local printer.

Open the selected object for editing.

Print results to a System i printer.

Create a new view, table or report. Create a Shortcut.

Save the results to the PC. Edit the Library List.

Create a System i Outfile (physical file).

Change the font used in the Object Display.

Create a shared folder document or IFS stream file on the System i.

Refresh the list of dis-played objects.

Send results to email or FTP recipient.ViewPoint Explorer 9

Controlling the Object List

This display relates to SEQUEL objects within a given library for a given system. Elsewhere in ViewPoint, a similar Object Display is used for selecting data files in a library, or option files.The objects that are displayed can be controlled with the following methods: Choosing a system from the list of systems on the left side of the Object Display. Choosing a specific library. Choosing one of the five library lists.

open.bmp

10 ViewPoint User Guide

Choosing a System

Using the navigation panel on the left, choose a system to work with by expanding the library structure. Press the [+] control to expand, or [-] to collapse.

systems.bmp

Choosing a Specific LibraryUse the Object Filter section to choose a specific object, object type, or library.

specific_lib2.bmpViewPoint Explorer 11

Choosing a Library List

Right-click any of the systems to select from the menu one of the five library lists.

liblst_cust_menu.bmp

Library List Types

*LIBL - Libraries on the library list containing the type of objects indicated by the OBJTYPE parameter are shown.

*USRLIBL - Libraries on the user portion of your library list containing views or reports are shown.

*ALLUSR - All user libraries (those not beginning with the letter "Q") on the sys-tem that contain views or reports are shown.

*ALL - All libraries on the system containing views or reports are shown.

*CUSTOM - A private list of libraries created by the ViewPoint user containing views and reports.12 ViewPoint User Guide

Create a Custom Library List

The Custom Library List dialog will allow you to create a personal list of libraries that contain SEQUEL objects.From the ViewPoint Explorer menu bar, select Options=>ViewPoint Options=>General Tab. Press the Edit Custom Library List button to access the display below.Select libraries from the list on the left. Use the Add button to place them on the cus-tom list.

liblst_cust.bmp

Changing the Library ListThe Edit Library dialog is displayed whenever the Library List option is chosen from the ViewPoint Explorer, or any of the design screens.

liblst.bmpViewPoint Explorer 13

With the Edit Library screen you can choose a library from the System Libraries sec-

tion and add it to the System or User portion of the Library list with the Add buttons. You can also remove a library with the Remove buttons.Press the OK button to apply your changes or the Cancel button to close the Edit Library window without making changes.Note: If your Sequel Default, 'Allow Command Line Functions' is set to no, you will not be allowed to edit your library list. See the SEQUEL Users Guide for more infor-mation on Interface Defaults.

Working with Objects

Drag and Drop'Windows Explorer' like drag and drop functionality as well as multiple object selec-tion makes it easy to manage (cut, copy, paste, move) SEQUEL objects.1. In the ViewPoint Explorer, locate the SEQUEL object(s) you want to work with.2. Make sure the library you want to drag the object to is visible.3. Right-click and drag the object(s) to the destination library.

objdsp_drag_drop1.bmp

4. A menu will appear with the available options.

objdsp_drag_drop2.bmp14 ViewPoint User Guide

Tips

You can hold down a key while dragging for the following:

To move a file, use SHIFT.To copy a file, use CTRL.

Multiple Object SelectionIn the object display, hold down the CTRL key, and then click each object you want to select.

objdsp_ctrl_select.bmp

To select a group of objects that are next to each other, hold down the mouse button and drag a rectangle around the items you want to select.

objdsp_drag_select.bmpViewPoint Explorer 15

Creating a Shortcut

To create a shortcut for frequently run views, tables and reports, highlight an item in the Object Display (Existing or Recent Tab) and choose Create Shortcut from the File drop-down menu.-or-Right-click any object and choose the Create Shortcut option.

shcut_create.bmp

A 'Shortcut Wizard ' will display and allow you specify how the view is to be run.-or-Click with the mouse and drag the selected View to the desktop and release the mouse button.

The above actions will place an icon on your desktop that when double clicked will display results for the View.

A VPT file can be edited using Notepad. See Edit a Shortcut on page 25 for details.For more powerful shortcuts, see Using Command Line Switches on page 29 for details.16 ViewPoint User Guide

Shortcut Wizard

The Shortcut Options screen (Shortcut Wizard) will allow you to select different options for the execution of any SEQUEL object. Once the options are selected, a 'Save As' screen will prompt you for the location of the new shortcut (.vpt exten-sion). Typically these are saved to the Desktop.

shcut_dsp.bmp

Each output option has specific parameters, which are described further on in this section.ViewPoint Explorer 17

Shortcut Wizard - Display

Run the view directly and prompt for any run-time variables. Type in a new window title caption if desired.

shcut_dspx.bmp

A VPT shortcut file will be created like the following:

vpt_display

The VPT shortcut file can be edited using a text editor (like Notepad). See Edit a Shortcut on page 25 for details.The [Command] section shows the complete command, with command line switches, that could be run from a dos prompt or the Windows Start=>Run option to produce the same results as the shortcut. See Using Command Line Switches on page 29 for details.18 ViewPoint User Guide

Shortcut Wizard - System i Print

Print the view results to the specified output queue.If output queue is 'Default', then the outq specified in the user default is used.

shcut_prt400x.bmp


vpt_print 400

The VPT shortcut file can be edited using a text editor (like Notepad). See Edit a Shortcut on page 25 for details.The [Command] section shows the complete command, with command line switches, that could be run from a dos prompt or the Windows Start=>Run option to produce the same results as the shortcut. See Using Command Line Switches on page 29 for details.ViewPoint Explorer 19

Shortcut Wizard - PC Print

Choose the windows printer you would like to use.You may also choose the page orientation. 'Size view results to fit the page' will automatically scale the font size to make sure the entire view fits the page orienta-tion you chose.

shcut_prtpcx.bmp


vpt_print pc


Shortcut Wizard - System i File

Create a physical file on the System i.Specify the file name, library, member and number of records. To create a file with all records set the 'Max # of records' to 0 (zero).

shcut_file400x.bmp


vpt_400 file


Shortcut Wizard - PC File

Create a PC file.Specify path, file name, and type. If file is left blank, a prompt is displayed when you run the shortcut. To create a file with all records set the 'Max # of records' to 0 (zero).Use the radio buttons to create a file on the System i or the PC.

shcut_filepcx.bmp


vpt_file pc


Shortcut Wizard - Email Results

Email the results of the view as a PC file attachment. Specify recipient(s), subject, message, attachment name, and type. Email addresses must be separated by a semi-colon. The DISTRIBS member in the System i source file *LIBL/MAILGROUPS will be opened to the specified addresses (if any).

shcut_emailx.bmp


vpt_email


Shortcut Wizard - Design Mode

Open the view in design mode. No other options are required.A VPT shortcut file will be created like the following:

vpt_design


Edit a Shortcut

To edit a VPT shortcut created with the Shortcut wizard, simply right-click the new Viewpoint shortcut icon and choose the Open option from the drop down menu.

shcut_edit.bmp

In Notepad you can edit the entries and values of the shortcut, however the Shortcut Wizard (page 17) is easier to use and less prone to error.Each Section of the VPT shortcut starts with a Section Heading enclosed in brackets ( [ ] ) and contains one or more Section Entries. A Section Entry follows the format of Keyname = Value.

vpt layout.bmp

The different shortcut sections are explained in detail in the pages that follow.ViewPoint Explorer 25

Data

The vpt file always starts with the [Data] section. This section specifies the name of the SEQUEL/ViewPoint object (view, report, table, script) to process.[Data]

FunctionThe [Function] section is the second part of the vpt file. It specifies the Option (more precisely the command) that will be performed against the object named in the [Data] section. Each Option (Display, PC_File, AS/400_Print, etc.) has its own set of required and optional entries (keynames). Each Option and it's required and optional keynames are explained below.[Function]

View = [[system/]library/]object Sets SEQUEL object to process.Title = Results window title. Sets the name to appear in the title window

for a view - only valid when used with DIS-PLAY or VIEW options.

Hidden = Y or N Hide the view results grid - useful if you only want to see graphs.

Option = DISPLAY or VIEW Display SEQUEL object results.No other entries required.

Option = PC_FILE Creates a PC file on the PC or on the System i.Target = AS400 or PC Specify host or client for the file loca-

tionFile = Prompt or directory/file-name

Prompt will prompt the user for a file name when the VPT is run.PC Syntax - c:\temp\filename.extension.400 syntax - /ifs path/filename.extension.

Multiple files are separated by a semico-lon. If graphs are defined via [GRAPH] paragraphs, the file names listed here will contain the results of GRAPH01, GRAPH02, etc. in sequence. If the number of filena-mes exceeds the number graph definitions, the final file name will contain the data results.

FileType = A valid file type e.g. *XLS, *HTM, *CSVMaxRecords = # Maximum number of records to add to the

file up to 999. Zero value indicates all records should be processed.26 ViewPoint User Guide

Creates a PC file on the PC or on the Optional Values If file results are being created in the IFS on the 400, then those files can be emailed.

EmailAddress = Recipient Email address or recipients separated by semicolons.

EmailSubject = Subject value Subject lineEmailMessage = Message value Message lineEmailFileName = /ifs path/file-name.ext

Filename(s) separated by semicolons of files located in the IFS.

Option = AS400_PRINT Sends the results to an AS400 printer.OutQ = Name, Prompt, Default Name is a valid AS400 output queue.

Prompt will prompt the user for the output queue when the vpt is run.Default will use the default output queue for the user.

Option = PC_PRINT Prints the results on a PC printer.Printer = Name, Prompt, Default Name is the name of a PC printer.

Prompt will prompt the user for the PC printer name when the vpt is run. Default will use the PC's default printer.

Optional ValuesSizeToFit = Y or N Automatically adjust the font size to fit

on the paper.Orientation = P or L Print in portrait or landscape mode.

Option = AS400_FILE Creates a physical file on the System i.Library = System i library nameFile = Name, Prompt Name is a valid physical file name for the

results. Prompt will prompt the user for a file name when the vpt is run.

Member = File member nameOptional ValuesMaxRecords = # Maximum number of records to add to the

file.

Option = EMAIL Email the results from the System i.EmailAddress = Recipient Email address of recipients separated by

semicolons.

Option = PC_FILE System i.ViewPoint Explorer 27

Email the results from the System i.The following entries can be used with any of the Options above.

VariablesIn the [Variables] section values can be specified that should be passed to run-time prompted requests (views).[Variables]

GraphThe [Graph] section is usually created by saving window definitions after displaying data and creating a graph. This section contains the settings for the graph control to recreate the graph results. These three settings below control whether the Title, Labels, and/or Legend for the graph will be drawn from the view data or whether they will be the values saved into the .VPT file.

EmaiFileName = Filename.ext Filename of results to be created and emailed.

EmailFileType = File type value *XLS, *HTM, *CSV, etc.Optional ValuesEmailSubject = Subject value Subject lineEmailMessage = Message value Message line

Option = DESIGN Open the object in it's designer.No other entries required

Option = JOBD Prompt to submit the request.Jobd = Prompt, *NONE, Default Prompt will prompt the user for a valid job

description. *NONE will not submit - run immediate.Default will use the jobd specified for the users Sequel defaults.

QuietMode = 0 or N = 1 = 2 = 3 or Y

Show all messages as the vpt runs. Show warning and error messages only. Show error messages only. Show no messages.

Logging = Y or N Specify Y to create the VPLOG.txt execution log. N is the default.

Variable Name = Value Variable Name is the variable defined in the view or script. Value is a number or character value to pass to the variable.

Option = EMAIL28 ViewPoint User Guide

[Graph]CommandThe [Command] section is generated by the Shortcut Wizard and is for information only. This is the complete command, with command line switches, that could be run from a DOS command line or the Windows Start=>Run option to produce the same results as the vpt shortcut.[Command]

Using Command Line Switches

Greater functionality and control can be gained by creating a shortcut using com-mand line switches. These shortcuts can be used to direct output to different file types (locally or on the System i), to an email recipient, to a different printer, etc.The shortcut consists of a Windows shortcut to the Viewpoint.exe program and any combination of the command line switches.To create this shortcut perform the following:1. Use Windows Explorer and navigate to C:\Program Files\SEQUEL View-

point.2. Using the right mouse button drag Viewpoint.exe to your desktop. When you

release the mouse, choose Create Shortcut from the menu.3. Right-click the new Viewpoint shortcut icon and choose the Properties option

from the drop down menu.

shcut_edit.bmp

UseSavedTitle = Y or NUseSavedLabels = Y or NUseSavedLegends = Y or N

Specify N to indicate current values should be retrieved from the view data; Y to indi-cate that the saved values in the .VPT should be used.

Switches = "C:\PROGRAM FILES\SEQUEL VIEWPOINT\VIEW-PNT.exe" /V:ASC404/SEQUELEX/CUS-TLIST /Q:N

This sample is for a vpt that displays a view.ViewPoint Explorer 29

4. On the Shortcut tab you can add command line switches to the Target line.shortcut_vpexe_switches.bmp

5. Press OK to save changes.

Command Line SwitchesThese switches can be used with the Viewpoint executable (viewpoint.exe) from the MSDOS prompt, called in a Visual Basic Script, or in a DOS batch file.

Switch SyntaxDescription

/A /A:(Option) Prints the view to a System i printer. If Option is blank, then print prompt is displayed. If Option is 'Default', then use printer specified in user defaults.

/B /B:(Option) Creates a System i Outfile. If Option is blank, the prompt is displayed. If Option starts with a forward slash "/", then create a PC Document. IFS document - Option = "[/ifs path]/doc name" Physical File -Option = "[lib/]file [(mbr)]"

/B /B:[/ifs path]/docname Creates a document in the IFS.

/D /D:dddddd Creates a file on the PC.If used the a /G(vpt), graphs and view results can be saved.Separate multiple files with semicolons.

/E /E:Email Address, [Subject], [Message] Email the view results.30 ViewPoint User Guide

/F /F:ffffff Sets the file type of an outfile.

/G /G:path\file Runs a .vpt file.

/I /I:(Printer Name) Prints the view to a PC printer. If Printer Name is blank, then print prompt is displayed. If Printer Name is 'Default', then print to default printer. If Printer Name is something else, then print to specified printer.

/J /J:(Option)If Option is Blank then show submit panel and prompt for jobd.If Option is Default then use SEQUEL defaults.If Option is *NONE then dont submit - run immediate.

/L /L:UserID, PasswordSet user id and password.

/N /N: Opens View (specified by /V:) in design mode.

/O /O:[yes/no] Overwrite files on 400.

/Q /Q:[Blank, Y, N, 0,1,2,3]Quiet Mode - Choose how messages will be shown.0 or N - show all messages.1 - show warning and error messages.2 - show error messages only.Blank, 3, or Y - show no messages.

/R /R:nnnn Sets the maximum number of records to process.

/S /S:ssssss Sets the system name.

/Setvar /SETVAR((variable value)) Pass variable values to View.

/T /T:tttttt Set the name to appear in the title window.

/U /U: Update: Check for a new version of ViewPoint

Switch SyntaxDescriptionViewPoint Explorer 31

Switch Parameters

Command Line ExamplesSend the results of a view (custlist) to an email recipient as a file(test.htm)./v:asc999/sequelex/custlist /e:[email protected] /d:test.htm

Create a file (test.xls) on the windows desktop from the results of a view (custlist)./v:asc999/sequelex/custlist /d: c:\windows\desktop\test.xls

Display the view CUSTLISTP and pass the value 101100 to the variable (defined in the view) &cusno./v:asc999/sequelex/custlistp /setvar((&cusno 101100))

/V /V:[[ssssss/]llllll/]oooooo Set the view to run.

/Z /ZGenerate the log called VPLOG.TXT in the C:\Documents and Settings\userid\Application Data\Sequel Viewpoint folder.

/? /? Show list of command line switches.

dddddd Document Name to create (outfile option)

ffffff PC document format (if not inferred from the document suffix)

llllll System i Library Name (or *LIBL, etc.)

nnnn Max records to process from view.

oooooo View Name

ssssss System Name

tttttt "Title" view name to use

Switch SyntaxDescription32 ViewPoint User Guide

Visual Basic Code Example

Display the view DDCUSNO and pass the value of the variable myRegion to the SEQUEL variable (defined in the view) &REGON.Dim myRegion as IntegermyRegion = 10Shell ("C:\Program files\SEQUEL ViewPoint\ViewPoint.exe _/v:asc999/sequelex/ddcusno /setvar((&regon "& myRegion &"))")

ViewPoint Options

The Options display provides access to ViewPoint package options - for the product in general and for view creation / design. Access this screen from the Options menu on the ViewPoint Explorer.

ViewPoint Options - General

opt_general_new.bmp

Options

Default System - Choose or change the system for the current session.

Email Completion Messages - Check the box and specify an email address to receive completion messages for ViewPoint jobs. ViewPoint Explorer 33

Recent Files - Specify the number of recent Views to retain. Clear all recent Views

with the Clear Recent button.

Auto Update - Enable or disable automatic updates and specify how often to update. Use to Update Now button to update immediately.

Check Now - Press to perform a ViewPoint Update.

Edit Custom Library List - Press to modify your custom lib list for the Explorer display.

ViewPoint Options - DesignThe Design tab controls the type of information that will be displayed in the Files and Fields tab of the Design View screen.

opt_design_new.bmp

Options

Show in Field List - Use the check boxes to specify the amount of field information to display when working with files in the Design View module.

Sort Field List By - Use the check boxes to specify how field information is to be sorted when working with files in the Design View module.

When Adding Database Files - When adding files to a view, select this option to limit the list to physical files only.34 ViewPoint User Guide

Show Field Properties - Check or uncheck the field attributes to show or hide in the

Files and Fields tab of the Design View screen.

File Qualifier - Specify how to qualify files in ViewPoint.

Edit Favorite Files List - Create or edit your list of Favorite Files. This list can be used when adding files to a view. (see page 106)

File Join Options - ViewPoint includes an AutoJoin facility that automates the pro-cess of joining files in ViewPoint. AutoJoin is a user selectable option that draws on information stored in the SQLJOINDB file in the SEQUEL library. When enabled, this facility automatically searches SQLJOINDB for all file pairs that can be derived from the files in the FROM clause. ViewPoint then generates the join clause by including all relevant join information available.

sqljoindb2.bmp

The join relationships recorded in SQLJOINDB are generated by running the LOADJOINDB command. This process analyzes the join clauses in existing views, keeping track of file pairs so as to avoid inserting redundant join relations. Estab-lished users should be able to run this command over their views and capture join relations without needing to do any further work. New users may find it more effec-tive to simply create a special view containing all the relevant files and join relations they will need and then run LOADJOINDB over that single view to populate SQLJOINDB.ViewPoint Explorer 35

ViewPoint Options - View Layout

The View Layout options allow you to create a user default format that will be applied if you restore your Custom Layout defaults for a given view, or for views that have not had any formatting defined.The Format Layout button will open the Format Layout screen (page 55) so you can set the defaults for the different elements of the custom layout.The View Layout tab presents sample data with default settings applied.

opt_layout_new.bmp

The formatting options defined here are stored in a file (C:\Documents and Settings\userid\Application Data\Sequel Viewpoint\DftFormatLayout.xml), and used to override the global settings shipped with the software.36 ViewPoint User Guide

ViewPoint Defaults

Interface defaults are displayed and changed with the default screens below. A user specific data area containing preferred default values will be created for each SEQUEL user. The first time a user accesses ViewPoint, the SQ#DFT "global default" data area will be used as the source for their default values.

ViewPoint Defaults - General

dft_general.bmp

Options

Output Queue - Specify the preferred output queue for PRINT and REPORT out-put.

Job Queue and Description - Specify a preferred job queue and description to be used when a request is submitted for batch execution.

Option File - Specify the preferred option file, member, and library to be used.

Execute - When creating a physical file, this controls whether records are added to the file, or the file is replaced. ViewPoint Explorer 37

ViewPoint Defaults - Print / Report Defaultsdft_print_rpt.bmp

Options

Page Setup - Specify preferred settings for page length, width, overflow, LPI, and CPI.

Forms Type - Choose a pre-defined forms type from the drop-down list.

Copies - Specify the number of copies to print.

SQL Summary - Check to print the SQL statement at the end of a view printout.

Hold and Save - Specify whether PRINT and REPORT output should be Held or Saved.

Save after Print - Check to save spool file after print. 38 ViewPoint User Guide

ViewPoint Defaults - Design View Defaultsdft_design.bmp

Options

Public Authority - Specify the value you want ViewPoint to supply as the public authority (AUT) parameter when Views and Reports are created.

From-file Prompt Library - Identify the default library to be used in displaying the initial list of files or libraries when the FROM prompt is selected.

View and Outfile Library - Specify the default target library to be used when views and output files are created.

Template Library - Specify the library containing templates. If the value is set to *NONE, the New View Wizard will not be invoked.

Join Attributes - Specify default join values.

Date and Time Values - Specify the format and separator that you want to use for date and time values in the View or Report.

Database - Select a database for use in ViewPoint View Designer:

*SEQUEL - Use native SEQUEL SQL processing for requests on the selected System i.ViewPoint Explorer 39

*LOCAL - Use SQL/400 processing for requests on the selected System i.Sequel Host List Entry - Select a Database Server name to connect to a remote database. These entries are listed in the Sequel Host Server file and define the remote database to use. The SQL native to the database will be used to process the request. See Using Remote Databases on page 370 for more information.40 ViewPoint User Guide

ViewPoint ResultsDirecting ViewPoint Output

In addition to printing and displaying a view, table, or report the output can be directed to a file, a document in a folder, sent to an e-mail address or transferred using File Transfer protocol (FTP)1.Following is a chart of the different output options for the different ViewPoint objects. They are discussed in detail further on in this section.

Note: Client Table results are explained in the Client Table Option chapter starting on page 326.

1. There are several required components including, but not limited to: User space SEQUEL version 6.8 or higher, a mini-mum OS/400 version of V3R7 and a TCP/IP connection from the System i to your LAN.

Display PCPrintSystem i

PrintPCFile

System iFile

System iPCDoc

EmailAs

View &Table Y Y Y

.xls, .wks,

.dbf, .htm,

.xml, .csv,.txt

Y.xls, .wks,.dbf, .htm,.xml, .csv,

.txt

.xls, .wks,

.dbf, .htm,

.xml, .csv,.txt, .pdf,

.rtf

Report Y Y Y .rtf, .txt N .rtf, .txt,.pdf, .htm.rtf, .txt,

.pdf, .htm

Client Report Y Y N

.htm, .pdf,.rtf, .txt,

.xlsN N .htm, .pdf,.rtf, .txt,

Client Table Y Y N .htm, .xls N .htm, .xls NViewPoint Results 41

Displaying View or Table Results

The Results screen (shown below) will be displayed whenever the Display Results option is chosen from the ViewPoint Explorer, Design View, or Table Designer screens.Once the data is displayed it can be further manipulated using any of the following: Reposition (page 43) Re-sort (page 43) View Single Record (page 44) Regrouping/Show Detail (page 44) Custom Display Layout (page 47) Graphing Results (page 61)The menu bar and toolbar provide access to the various functions.

new reulsts.bmp

By default the left column is 'frozen' (grayed out). To unfreeze, set the Number of Frozen Columns on the General tab of the Custom Display Layout settings. (page 55)42 ViewPoint User Guide

Reposition the Display ResultsThe Reposition dialog is displayed after pressing the Reposition button from the Results display. If the view does not contain an Order By statement, this button will be grayed out.Enter a new value and press OK to reposition or Cancel to exit back to the Results display.

reposition.bmp

Re-sort the Display Results

First select a column of data by clicking the column heading with the mouse. Then use the Sort Ascending or Sort Descending buttons to re-sort the displayed data.

new results.bmp

The remaining result output options (print, send, file) are explained later in this sec-tion.ViewPoint Results 43

View a Single Record

After a view or table's results are displayed, double click any record or press the View Single Record button to see the single record screen below. Use the Next and Previous buttons (up and down arrows) to move through the records.

new_results_single_record.bmp

Dynamic Regrouping / Show Detail

Any grouping view, once it is displayed, can be regrouped using any of the fields from the file(s) in the view plus any derived fields. Also, the supporting detail can be drilled into for any grouped record.1

Regroup and Show Details are the two options available either by right-clicking on a summary (grouped) record, or from the menu bar - Options=>Additional Options.

1. This feature requires fields to be added to the Dynamic Drill Down Field list. This list is stored in a file (SEQUEL/VPTDYNFLTR) which can be easily maintained using ViewPoint Administrator, or at a green screen com-mand line with UPDATE, INSERT, or DFU.44 ViewPoint User Guide

Regroup

First simply display a grouping view. The display on the left is order detail summed and grouped by product number. Right click on a record (BMXCARTON) and select Regroup from the menu.

regroup how to1.bmp

The Regroup Data window will list all fields from all files used in the view that are also defined in the Dynamic Drill Down Field List. If no fields from the starting view exist in the Dynamic Drill Down List, then a dialog will appear so you can list all fields for the view.Place a check next to all the fields for which you want to regroup the data. Our example will regroup using the state field. Press the OK button and a second window will open with the new results.

regroup how to2.bmpViewPoint Results 45

Show Detail

This option will show the supporting detail for the chosen grouped record; in effect allowing you to drill-into the data that created the summary information.Right click on a record (Illinois) and select Show Details from the menu.In the Regroup Data window, select the fields you want to display. As with the Regroup feature, the view fields must be defined in the Dynamic Drill Down Fields List.The original summary columns (quantity ordered and extended price in our exam-ple) will automatically be added to the display.

regroup how to3.bmp

The original data set is filtered based on the record selected above - customers in IL.

detail_cname.bmp46 ViewPoint User Guide

Custom Display Layout

The display for View and Table results can be customized using colors (fonts and backgrounds) and by creating levels of grouping and summary information. You can create a custom display (page 51) for each of your views and tables, or apply a user default format (page 50).The before and after example below illustrates the difference between the standard results (before) and the same results with custom formatting (after).

before after small.bmp

Note: Because the custom formatting is saved with the view object, you need to be in design mode for a given view in order to create a custom display.ViewPoint Results 47

Elements of the Custom Display

The elements noted below are either turned on or off, formatted with colors and fonts, or created and added to the display. The following is a cross reference of the noted items below and the Format tab where they are located:

style formats.bmp

Group By Cell BoxColor and Font - Style Editor (page 57)

Group FooterColor and Font - Style Editor (page 57) Summary Calc - Grouping (page 56)Text Value - Grouping (page 56)

HeadingColor and Font - Style Editor (page 57)

Group By BoxColor and Font - Style Editor (page 57)Hide or Display - Grouping (page 56)

Group HeaderColor and Font - Style Editor (page 57)

Grid BackgroundColor and Font - Style Editor (page 57)48 ViewPoint User Guide

style formats2.bmp

Merged ColumnColor and Font - Style Editor (page 57)Hide or Display - General (page 55)

Odd/EvenColor - General (page 55)

Frozen ColumnColor and Font - Style Editor (page 57)Select number - General (page 55)

Conditional FormatColor and Font- Column Editor (page 58)ViewPoint Results 49

Apply a User Default Format

The fastest way to create a Custom Layout is to apply your default layout.First you have to set the View Layout defaults (page 36) in ViewPoint Options.Then, open an existing view (page 102), or create a new view (page 89) and display the results.On the results window, select Format=>Layout.

how to_ menu format layout.bmp

This will open the Layout Format Display.Press the Restore Default button and press OK.

layout_general tab.bmp

Your View Layout defaults will be applied to the display.50 ViewPoint User Guide

Create a Custom Layout

1. You must be in the View Designer to create a custom layout. Open an existing view (page 102), or create a new view (page 89), and display the view results.

2. Drag a column to the Group By Box to create a group level. In this example a group is created for each state.

drag group column2.bmp

3. From the Results Menu, select Format=>Layout.

how to_ menu format layout.bmp

In this simple example, formatting will be added to a basic result set.

The final result will look like the follow-ing:

how 2 b4.bmp how to after.bmpViewPoint Results 51

This will open the Layout Format screen below. This display uses tabs to access the

different elements (page 48) that can be configured when creating a custom display.4. On the General Tab: Check the box to Apply Odd/Even Colors and select these colors using the

drop down palettes. Set the Number of Frozen Columns to zero. Press the Apply button.

how to general.bmp

See page 55 to review all the parameters on the General Tab.

5. On the Grouping Tab: Uncheck the Group By Box Visible option. Check the box to Hide Grouped Columns. Select a Field to Calculate using the drop-down list, and a function (SUM) in

the Calculation column. Enter a value for the Group Footer Text.52 ViewPoint User Guide

Press the Apply button.how to grouping.bmp

See page 56 to review all the parameters on the Grouping Tab.

6. On the Style Editor Tab: Use the Choose Style to Update dropdown list to select the Style Section to

change. In the final result above the Heading, Group Header, and Group Footer were set to different shades of orange.

Once the Style Section is selected, use the color palettes to choose Background Color (usually the 'fill' color for a given section), and the Foreground Color (usually the Font color).

Press the Apply button before selecting a different style.

how to style.bmp

See page 57 to review all the parameters on the Style Editor Tab.ViewPoint Results 53

No changes were made to the Column Editor Tab.layout_column editor tab.bmp

See page 58 to review all the parameters on the Column Editor Tab.

No changes were made to the Printing Options Tab.

layout_printing options tab.bmp

See page 59 to review all the parameters on the Printing Options Tab.

7. Press OK to close the Format Layout display and see the changes.54 ViewPoint User Guide

Custom Layout Settings

General LayoutThe General Tab has settings to control the overall look of the results display.

layout_general tab.bmp

Note: If setting View Layout options (to restore defaults - page 36), the Merged and Frozen cell options are not available.

Options

Apply Odd/Even Colors - Check this box to apply the selected odd and even colors. Use the palettes to select the odd and even color. Hint: If you want a solid color for all records, use the same color for both odd and even.

Gridline Type - Use the drop-down menu to select from the different gridline types.

Gridline Color - Use the color palette to select a gridline color. This color is only used for the 'flat' gridline types.

Fit Last Column to Grid - Check the option to automatically size the results dis-play to the columns displayed.

Merge Cells in first N Columns - Merge works like print suppression. Repeating values are suppressed until a value in that column changes. Check the box and select the number of columns the merge function is applied to. ViewPoint Results 55

Number of Frozen Columns - Starting with the leftmost column, select the number

of frozen columns to display. A frozen column is a column that you want to differentiate from the rest of the body of data.

OK Button - Press OK to close the tab and apply changes.

Cancel Button - Press Cancel to close the tab and not apply any changes (since last Apply).

Apply Button - Press Apply to add changes to the results display. The Format Lay-out display will remain open.

Restore Default Button - Press to apply user-defined default to the current results display.

Grouping LayoutThe Grouping Tab contains the settings for group level formatting and functions.

layout_grouping tab.bmp

Note: If setting View Layout options (to restore defaults - page 36), the Field to Cal-culate and Group Footer Text options are not available.

Options

Group By Box Visible - Check this box to leave the Group by Box visible.

Group By Text - Change the default text that displays in the Group by Box.56 ViewPoint User Guide

Hide Grouped Columns - Check this box to hide the column of data for the field

that is added to the Group By Box.

Field to Calculate - Select a field from the drop down list to use in a calculation (below). The final calculation will appear in the Group Footer.

Calculation - Select a function to apply to the field chosen above.

Display Format - Choose one of the format templates to apply to the calculation.

Group Footer Text - Enter a text string to appear at the Group Footer.





Style Editor LayoutThe Style Editor Tab is used to change the colors and fonts for the different elements of the results display.

layout_style editor tab.bmpViewPoint Results 57

Options

Choose Style to Update - Use the drop-down menu to select a style (element) to change.Background Color - Use the color palette to set the background color for the selected style (above).Foreground Color - Use the color palette to set the foreground color. For most styles, this sets the font color.Font - Use the font dialog to choose font type, size and style.





Column Editor LayoutThe Column Editor Tab is used to apply conditional formatting to a column. For instance, if a customer's outstanding balance is above a defined limit, display that number as red in a yellow background.

layout_column editor tab.bmp58 ViewPoint User Guide

Note: If setting View Layout options (to restore defaults - page 36), the Column Edi-

tor options are not available.

Options

IF: 'Choose a Field' - Select a field from the list to conditionally format.

Test - Select the type of test for the chosen field.

Value - Enter a value for the test.

Upper Value (between) - If using the 'Between' test, specify an upper value.

Background Color - Use the color palette to select the background color if the con-dition above is 'true'.

Foreground Color - Use the color palette to select the foreground color if the condi-tion above is 'true'.

Font - Set the font for the conditional column.

Alignment - Change the alignment for the conditional column.





Printing Options LayoutThe Printing Options Tab is used to set print defaults for the results display.ViewPoint Results 59

layout_printing options tab.bmp

Options

Header - Enter a value to appear at the top of each page.

Height - This value determines where above the data the header value will print.

Footer - Enter a value to appear at the bottom of each page.

Height - This value determines where above the bottom page margin the footer value will print.

Translate Colors for Black/White Printing - Check this box to translate colors to grayscale values for printing.

Margins - Use to change default page margin values.

Orientation - Select Portrait or Landscape page orientation.




Restore Default Button - Press to apply user-defined default to the current results display.60 ViewPoint User Guide

Graph View and Table Results

It's a simple matter to graph view and table results. First use your mouse to highlight the data you want to graph. (The Total Order Value column is selected in this example.)

grapf2.bmp

Next, select the Create Graph option from the menu or toolbar.

graph3.bmp

ViewPoint automatically graphs your data in a default graph layout.

graph4.bmpViewPoint Results 61

You can select another type of graph or edit the graph attributes any way you want.

Select the ChToolbar option from the pull down menu.This will display the edit toolbar at the top of the graph.

graph5.bmp

When you click on one of the toolbar buttons the Graph Control window is dis-played. The contents of the window will vary depending on the toolbar option you selected. From here, you can select from 6 types of 3-dimensional graphs or 12 types of 2-dimensional graphs. You can change background or foreground colors, add descriptions, change axes, create overlay graphs, or modify any other attributes to graph your data just the way you want.

graph6.bmp62 ViewPoint User Guide

After you customize the graph to your specifications, you can save it as a window

definition. This lets you display the same graph again in the future, but with new data. Select File/Create Shortcut from the View Results window if you want to save the graph definition.You can also create multiple graphs over a single view. In our example, we could graph both Total Order Value and Total Orders for each customer. If you save the window definition with multiple graphs open, each will be displayed when you run the view in the future. (You can set up an icon to run and display the graphs, too.)

Print PreviewPrint Preview is available from the Results display for a View or Table.

prt_preview.bmp

Options

Page Movement Controls - Use the inner arrows ( < > ) to move forward or back-ward through the printout one page at a time. Use the outer arrows ( |< >| ) to move to the first page or the last page.

Zoom Options - Zoom in and out of the printout. Use the drop down list to select the zoom percentage.

Page Setup - Set margins, alignment, and orientation for the page. ViewPoint Results 63

Print - Press the Print button to send output to a local printer. Close - Close the Print Preview display.

Printing Results Locally

A standard Windows Print Dialog will be displayed whenever the Print to PC option is chosen from the ViewPoint Explorer, View Results or any of the designer screens.

print_view_pc.bmp

Choose a local printer from the drop-down menu and press OK to print or Cancel to exit.64 ViewPoint User Guide

System i Print

The 'Print Results At' dialog is displayed whenever the Print to AS/400 option is chosen from the ViewPoint Explorer, or the designer screens for a SEQUEL View or Table object.This screen allows you to submit a batch PRINT job on the System i.Press the Schedule button to add this job to the System i Job Scheduler. (page 78)

print view.bmp

Options

Job Description - Change the default Job Description.

Output Queue - Change the default Output Queue.

Page Attributes and Print Density - Change the default page attributes and print densities.

Print SQL Summary Page - Check the is box include an SQL page when printing View results.

Schedule - Press the Schedule button to add the job to the System i Job Scheduler.

OK - Press the OK button to submit the job.

Cancel - Press the Cancel button to exit this screen without submitting a job.ViewPoint Results 65

Displaying Report Results

Choose the Display option from the ViewPoint Explorer or the Report Designer Dis-play to view the report results on the desktop.

rpt_reslt.bmp

Options

Page Movement Controls - Use the inner arrows ( < > ) to move forward or back-ward through the printout one page at a time. Use the outer arrows ( |< >| ) to move to the first page or the last page.

Zoom Options - Zoom in and out of the printout. Use the drop down list to select the zoom percentage.

Page Setup - Set margins, alignment, and orientation for the page.

Print - Press the Print button to send output to a local printer.

Close - Close the Print Preview display. 66 ViewPoint User Guide

System i Report

The Print Report Results dialog (shown below) is displayed whenever the Print AS/400 option is chosen from the ViewPoint Explorer for a SEQUEL Report object or while in the Report Designer.This screen allows you to submit a batch report job on the System i.Press the Schedule button to add this job to the System i Job Scheduler. (page 78)

report report.bmp

Options

Job Description - Change the default Job Description.

Output Queue - Change the default Output Queue.

Title - Change the default title for the report.

Copies - Specify the number of copies.

Hold and Save - Check the boxes to hold or save the generated spool file.

Schedule - Press the Schedule button to add the job to the System i Job Scheduler.ViewPoint Results 67

Create a PDF Image Overlay

Graphic files can be merged with spool file data as an overlay (watermark) when creating PDF output.PDF output can be created from views, tables, and reports either when saving output as an IFS/PC document(page 76), or sending results as an email attachment(page 77).

sample overlay splf.bmp

The selection of the files used, and their individual placement and scale attributes is controlled using the Change Image Overlay display discussed below.68 ViewPoint User Guide

Change Image Overlay

The Change Image Overlay screen is used to import images to create an overlay for PDF output.This display is accessed from the Report - PDF Properties and Send Results (for reports) screens.The display is divided into two main sections - the Thumbnail List and the Preview Pane.Images are added to and organized in the Thumbnail List. Drag images from the thumbnail list to the Preview Pane and to place over the sample 'page'.

chgovrl_main w tags.bmp

This display is used to add and remove images, place images, control order, and set image scaling for the merged PDF/Spool document. Overlay templates can be cre-ated from current settings and applied later to any report. All these actions are described below.ViewPoint Results 69

Add Images

Overlay images are first added to the Thumbnail list and then placed on the preview pane of the Change Image Overlay screen.To add images either right-click in the Thumbnail pane, and select 'Add Images', or select File=>Add Images from the menu.

rclickmenu_thumbs_add.bmp

A file selection window is used to select your images. These files should be placed/stored on the IFS of the System i (tmp is a good place).

chgovrl_import2.bmp

Select the file and press Select to add to the Thumbnail list.70 ViewPoint User Guide

Remove Images

To remove images from either the thumbnail list, or the preview pane, simply right-click and select Remove Image.

Note: Removing an image from the Preview Pane only removes the image from the overlay. The thumbnail is still available for later placement.

Place and Move ImagesImages are moved from the Thumbnail List and placed on the Preview Pane. Simply drag and drop the image onto the sample printout. Once placed, images can be moved again by dragging them around the screen.

drag thumbnail.bmp

rclickmenu_thumbs_remove_small.bmp

rclickmenu_overlay_small.bmp

Remove thumbnail Remove from overlayViewPoint Results 71

Resize Images

Place the mouse cursor over any of the image edges. The cursor will change to a double arrow. Click and pull the edge to the desired size.

chgovrl_resizel.bmp

Create an Overlay TemplateOnce you have images placed and sized on the sample printout in the Preview Pane, these settings can be saved in an Overlay Template. This template can be applied to other report or view printout files. Overlay settings are stored with the report and view objects they are designed over.Select File=>Save Template from the menu.

file_save_temp.bmp

Give the template a name, and save to your local drive.72 ViewPoint User Guide

PDF Attributes

This screen is accessed from the Send Email Results (page 77) and Create System i PC document (page 76) displays for PDF output.

pdf_attributes.bmp

See page 68 for more on Image Overlays.

Options

Title, Subject, Author/Creator, and Keywords - Provide these values for the selected report. Entries here will be reflected in the document properties of the resulting PDF file. This provides users with basic data about the docu-ment and various ways to search for information.

Owner Pwd - An owner password will limit modification access to the PDF docu-ment.

User Pwd - A user password will limit view access to the PDF document.

Page Size - Select a page size from the drop-down for the generated PDF document.

Image List - Existing images for the report are listed here along with their respec-tive placement and scale values.

Edit Image Layout Button - Press Edit Image Layout to modify the images for the report. (see page 68)ViewPoint Results 73

Saving Results as a PC File

The Windows Save As screen will be displayed whenever the 'Save Results as PC File' option is chosen from the ViewPoint Explorer, View Results, or any of the designer screens.

save_pc.bmp

Specify a name for the file, choose a file type from the drop-down menu, specify a path and press Save to create the file or Cancel to exit.

PC Format Types

Delimited (*csv) Text Only (*txt)HTML (*htm)MS Excel (*xls)Lotus 1-2-3 (*wks)DBase (*dbf)Merge (*mrg)Rich Text (*rtf) for ReportsAdobe PDF (*pdf) for Reports74 ViewPoint User Guide

Saving Results as an System i File

The Create File Results dialog is displayed whenever the AS/400 Outfile option is chosen from the ViewPoint Explorer or the View and Table designer screens. This screen allows you to direct View results to a Physical File on the System i. If the file does not exist, it will be created.Press the Schedule button to add this job to the System i Job Scheduler. (page 78)

crt PF.bmp

Options

File name and Library - Specify a name and library for the file to be created. These values are derived from the View name and library.

Member and Format - If available, choose the member or format for the file.

Run Job - Choose 'In Batch' to submit the job. Choose 'Immediately to create the file in QTEMP.

Job Description -Specify a job description. Use the Browse button to navigate to the desired jobd.

Schedule - Press the Schedule button to add the job to the System i Job Scheduler.

File Attributes - Enter a description for the new file.ViewPoint Results 75

OK - When ready press the OK button to create the file. Cancel - Press the Cancel button to exit the display without creating a file.

Saving Results as an System i Document

The Create Document Results dialog is displayed whenever the AS/400 Document option is chosen from the ViewPoint Explorer or the View and Table designer screens. This screen allows you to direct View results to a PC Document on the Sys-tem i in one of seven formats. The document will be placed in a shared folder.Press the Schedule button to add this job to the System i Job Scheduler. (page 78)

crt_doc.bmp

Options

Save In - Choose a Shared Folder to save the PC document into.

File Name - Specify a name for the file to be created.

Save As Type - Choose the PC format from the list.

Save - Press Save to create the file.76 ViewPoint User Guide

Schedule - Press the Schedule button to add the job to the System i Job Scheduler.PDF Attributes - Enter PDF Image Overlay settings when sending PDF output.

Job Description - Specify a different job description and library or leave the default.

Sending Results

The Send Email Results screen is displayed whenever the Send Results option is chosen from the ViewPoint Explorer or any of the designer screens.The results can be sent, via E-mail or FTP, as an attachment in one of several formats depending on the object type.Press the Schedule button to add this job to the System i Job Scheduler. (page 78)

send email results.bmp

Options

To: Cc: Bcc: - Specify a Recipient, Carbon Copy recipient, or Blind Carbon Copy recipient.

Note: For FTP the string must begin with 'ftp:' or '(ftp)' followed by the remote system address. (ex. ftp:111.222.333.444)

Message Text - Specify a subject for the E-mail and any message text you want to include.

Attachment Name - Specify the name of the attachment to send. The default is derived from the View name.

Attachment Type - Choose the PC Format from the list. ViewPoint Results 77

Schedule - Press the Schedule button to add the job to the System i Job Scheduler.PDF Attributes - Enter PDF Image Overlay settings when sending PDF output.

Schedule a ViewPoint Job

Most ViewPoint output can be scheduled to run at a future date and/or time using the System i Job Scheduler. These jobs can be scheduled to run Once, Weekly, or Monthly.The following output types can now be scheduled when: Printing ViewPoint Reports Printing ViewPoint View and Table results Creating a System i physical file Creating a System i PC Document Submitting a ViewPoint Script Sending results via EmailThe Job Scheduling screen is available from the dialogs for any of the output options listed above.Each of the Frequency options has a different combination of scheduling choices. These are detailed below.

sched_once.bmp

Note: Use the commandWRKJOBSCDE to review / access the jobs submitted to the System i Job Scheduler.78 ViewPoint User Guide

Schedule Once

When using the Schedule Once option you can choose only one of the following to set the date for the job to run:

Use the Action if Missed drop down to select what to do with the job if it is missed by the scheduler.(due to power down or the iSereis was in a restricted state)

Run - Run the job once the scheduler is operational. Hold - Hold the job untill released. Skip - Do not run the job once the scheduler is operational.

sched_once w cal.bmp

Use the right arrow to select a month (future dates only). Click on the desired date.

Select a date from the drop down calendar (see below).

Select Beginning of Month or End of Month.

Select one of the week days.Select the time for the job to execute in conjunction with any of the above.Check the Retain Schedule Entry box to keep this job definition for future use.sched_once.bmpViewPoint Results 79

Schedule Weekly

When using the Schedule Weekly option you can choose only one of the following to set the date for the job to run: