SQL/MP Report Writer Guide - NonStopTools

1 HP nonstop SQL/MP Report Writer GuideAbstractThis manual explains how to use the HP nonstop SQL/MP Report Writer commands, clauses, and functions, and the SQLCI options that relate to manual tells how to produce formatted reports using data from a nonstop SQL/MP VersionNonStop SQL/MP D30 Supported Release Version Updates (RVUs)This publication supports and all subsequent D-series RVUs, and and all subsequent G-series RVUs, until otherwise indicated in a new NumberPublished527213-001 September 2003 Document History Part NumberProduct VersionPublished107934 nonstop SQL/MP D30 December 1994527213-001 nonstop SQL/MP D30 September 2003 Hewlett-Packard Company 527213-001iHP nonstop SQL/MP Report Writer GuideIndexFiguresTablesWhat s New in This ManualvManual InformationvNew and Changed InformationvAbout This ManualviiAudienceviiHow to Use This ManualviiRelated ManualsviiiNotation Conventionsx1.

2 Introduction to the nonstop SQL/MP Report WriterData Organization1-1 SQL Database Reports1-2 Report Development Steps1-3 Report Layout and Style1-4 Report Writer Components1-102. Using SQLCI and the Report WriterStarting and Ending an SQLCI Session2-1 Setting Up Your Session Environment2-1 Entering Report Formatting Commands2-10 Specifying Output Files2-12 Executing a Command Repeatedly2-13 Defining Reports in Command Files2-15 Using the Report Writer to Produce Text From Data2-203. Selecting Data for a ReportDeveloping a Query3-1 Grouping Data for Calculations3-15 Selecting Distinct Rows3-20 Using Expressions to Calculate Report Values3-21 Using Parameters With SELECT Commands3-23 Preparing a SELECT Command3-24 ContentsHP nonstop SQL/MP Report Writer Guide 527213-001ii4.

3 Customizing a ReportUsing Views3-26 Using Subqueries3-28 Developing Multistep Queries3-324. Customizing a ReportDefining the Layout4-1 Specifying the Items in a Detail Line4-11 Naming Select List and Detail Line Items4-13 Organizing Rows Into Break Groups4-14 Labeling Information4-16 Formatting Data Values4-28 Formatting Dates and Times4-39 Using TACL to Pass Parameters4-44 Conditional Printing of Items or Line Entries4-46 Redefining Special Characters4-47 Calculating Totals4-48 Calculating Subtotals4-51 Printing Double-Byte Characters4-58A. Comparison of the Report Writer and the Enform LanguageIndexFiguresFigure SQL/MP Library MapixFigure Content of Tables1-2 Figure of Default Report Layout1-5 Figure Sample Formatted Report1-8 Figure of a Formatted Report1-9 Figure of a Formatted Report1-10 Figure Up Your Session Environment2-2 Figure and Displaying Options2-4 Figure of Listing Rows2-5 Figure a Window for Report Output2-7 Figure a Window for Report Output2-7 Figure a Window for Report Output2-8 Figure of Window to Output Line2-9 Figure of Report Formatting Commands2-10 Figure of Repeating Stored Commands2-15 Figure OBEY Commands2-19 ContentsHP nonstop SQL/MP Report Writer Guide

4 527213-001iiiFigures (continued)Figures(continued)Figure of Using the Report Writer to Produce Text From Data2-22 Figure of Using the Report Writer to Produce Text From Data2-23 Figure of Using the Report Writer to Produce Text From Data2-24 Figure for Report Content3-3 Figure Rows from Joined Tables3-4 Figure of Rows Sorted by One Column3-14 Figure of Rows Sorted by Two Columns3-15 Figure in the Select List3-22 Figure in the Detail Line3-23 Figure of an Invoice3-28 Figure of Report With a Subquery3-32 Figure Margin Settings - Displayed Report4-2 Figure Margin Settings - Printer Report4-3 Figure of Pagination Features4-6 Figure of Using the NEED Clause4-8 Figure to a Display or Print Position4-11 Figure of

5 Break Groups4-15 Figure of Report and Page Titles4-19 Figure of a Page Title With a Row Value4-20 Figure of Break Titles4-22 Figure of Report and Page Footings4-23 Figure of a Page Footing4-24 Figure of Break Footings4-25 Figure of Numbering Lines4-27 Figure of Numbering Break Group Lines4-27 Figure of Monetary Total and Subtotals4-33 Figure of a Decoration4-34 Figure of TACL Macro for Passing Parameter4-44 Figure of a Report With Totals4-50 Figure of Formatted Total Values4-51 Figure of Report With Subtotals (Page 1 of 2)4-53 Figure of Report With Subtotals (Page 2 of 2)4-54 Figure Orders Summary4-56 Figure of Subtotals on Conditional Values4-58 Figure Double-Byte Characters With Text Wrapping4-60 Figure the SPACE Clause to Adjust Display Columns4-61 Figure the TAB Clause to Adjust Display Columns4-62 ContentsHP nonstop SQL/MP Report Writer Guide 527213-001ivFigures (continued)Figures(continued)

6 Figure Double-Byte Characters With an Odd-Numbered Display Descriptor4-63 Figure Double-Byte Characters With Incorrectly-Sized Display Descriptors4-64 Figure Double-Byte Characters With the TAB Clause4-65 Figure Characters With Different Break and Subtotal Columns4-66 Figure Characters With Same Break and Subtotal Columns4-67 Figure an Even-Numbered Display Descriptor for Subtotal Labels4-68 Figure a Correctly-Sized Display Descriptor for Subtotal Column Lables4-69 Figure Report and SQLCI ReportA-2 TablesTa b l e 1 -1 . Report Formatting Commands1-12Ta b l e 1 - 2 . Report Formatting Command Clauses1-12Ta b l e 1 - 3 .Style Options1-13Ta b l e 1 - 4 .Layout Options1-14Ta b l e 1 - 5.

7 Report Functions1-14Ta b l e 1 - 6 .SQLCI Commands1-15Ta b l e 3 - 1 .Search Condition Predicates3-7Ta b l e 3 - 2 .Comparison and LIKE Predicates3-12Ta b l e 4 - 1 .Default Display Formats4-28Ta b l e 4 - 2 .Format Characters in AS DATE and AS TIME Clauses4-41Ta b l e 4 - 3 .DATEFORMAT Display Formats4-42Ta b l e A - 1 .Enform StatementsA-3Ta b l e A - 2 .Enform ClausesA-3Ta b l e A - 3 .Enform Option VariablesA-5Ta b l e A - 4 .Enform System VariablesA-6Ta b l e A - 5 .Enform CommandsA-6HP nonstop SQL/MP Report Writer Guide 527213-001vWhat s New in This ManualManual InformationHP nonstop SQL/MP Report Writer GuideAbstractThis manual explains how to use the HP nonstop SQL/MP Report Writer commands, clauses, and functions, and the SQLCI options that relate to manual tells how to produce formatted reports using data from a nonstop SQL/MP VersionNonStop SQL/MP D30 Supported Release Version Updates (RVUs)This publication supports and all subsequent D-series RVUs, and and all subsequent G-series RVUs, until otherwise indicated in a new History New and Changed Information This publication has been updated to reflect new page formats and product names.

8 Product names in graphic representations are consistent with the current product interface. The technical content of this publication has not been updated and reflects the state of the product at the release version update (RVU). Part NumberPublished527213-001 September 2003 Part NumberProduct VersionPublished107934 nonstop SQL/MP D30 December 1994527213-001 nonstop SQL/MP D30 September 2003 What s New in This ManualHP nonstop SQL/MP Report Writer Guide 527213-001viNew and Changed InformationHP nonstop SQL/MP Report Writer Guide 527213-001viiAbout This ManualThis manual provides task-oriented instructions on how to design and produce reports using the Report Writer and information from a nonstop SQL/MP database.

9 The manual also provides an overview of the Report Writer commands and SQLCI commands that are directly related to formatting Readers should have some experience in using command interpreters and be familiar with the HP nonstop Kernel operating system. If you are not a programmer, you might need to request a programmer's assistance in creating reports that require the use of the more advanced a reader of this manual, you should have read the Introduction to nonstop SQL so that you are familiar with the terminology. You can also read the nonstop SQL Quick Start for a quick introduction to expressing ad hoc queries and producing simple to Use This ManualThis manual is organized to make instructions for specific Report writing tasks easy to find:The version of SQL implemented by HP and referred to in this manual conforms with the ANSI SQL standard.

10 Minor exceptions are noted in the SQL/MP Reference Manual, which fully documents the HP implementation of 1, Introduction to the nonstop SQL/MP Report Writerintroduces the Report Writer , describes its components, and compares the default Report format with a custom formatted reportSection 2, Using SQLCI and the Report Writergives instructions for running SQLCI and using Report Writer commands. This section describes various ways to define and produce reportsSection 3, Selecting Data for a Reportshows you how to develop queries and specify calculations to process the source data for a reportSection 4, Customizing a Reportgives instructions for defining the Report layout and style. You can give detailed specifications for output lines, line items, column headings, titles, and footingsSection A, Comparison of the Report Writer and the Enform Languagelists the SQLCI and Report Writer features that correspond to features of the Enform languageAbout This ManualHP nonstop SQL/MP Report Writer Guide 527213-001viiiRelated ManualsRelated Manuals This manual is part of the nonstop SQL library of manuals (as shown in Figure i).