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. 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.

2 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. 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.

3 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 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 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

4 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)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.

5 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 . 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.

6 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. 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.

7 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; 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.

8 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). The library includes the following manuals: Introduction to NonStop SQL provides an overview of the SQL/MP relational database management system. SQL Quick Start describes how to run SQLCI, how to execute simple queries on a database, how to modify data, and how to produce a formatted Report . SQL/MP Installation and Management Manual explains how to plan, install, create, and manage a NonStop SQL database; describes the syntax of installation and management commands; and describes NonStop SQL catalogs and file structures. SQL/MP Query Guide describes how to retrieve and modify data in a SQL/MP database and how to analyze and improve query performance.

9 SQL/MP Version Management Guide describes the rules governing version management for the NonStop SQL software, catalogs, objects, messages, programs, and data structures. SQL/MP Programming Manual for C and COBOL85 and the SQL Programming Manual for Pascal and TAL describe the programmatic interface for the particular language. SQL/MP Reference Manual describes the SQL language elements, expressions, functions, and statements. (See this manual for the complete description and syntax of the Report Writer commands and the SELECT statement.) SQL/MP Messages Manual describes NonStop SQL/MP messages for the conversational interface, the application programming interface, and This ManualHP NonStop SQL/MP Report Writer Guide 527213-001ixRelated ManualsFigure SQL/MP Library MapIntroductionto NonStopSQL( )*NonStopSQLQ uickStart( )*Introductory ManualsNonStopSQL/MPQueryGuideNonStopSQL /MPReportWriterGuideNonStopSQL/MPInstall andManagementGuideNonStopSQL/MPVersionMa nagementGuideUsage GuidesNonStop SQLP rogrammingManual forPascal( )*NonStop SQLP rogrammingManual forTAL( )*NonStopSQL/MPProgrammingManual for CNonStopSQL/MPProgrammingManual forCOBOL85 Programming ManualsNonStopSQL/MPMessagesManualNonSto pSQL/MPReferenceManualReference This ManualHP NonStop SQL/MP Report Writer Guide 527213-001xNotation ConventionsNotation ConventionsHypertext LinksBlue underline is used to indicate a hypertext link within text.

10 By clicking a passage of text with a blue underline, you are taken to the location described. For example:This requirement is described under Backup DAM Volumes and Physical Disk Drives on page Syntax NotationThis list summarizes the notation conventions for syntax presentation in this letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example:MAXATTACH lowercase italic italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example:file-namecomputer type letters within text indicate C and Open System Services (OSS) keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For computer computer type letters within text indicate C and Open System Services (OSS) variable items that you supply. Items not enclosed in brackets are required. For example:pathname[ ] enclose optional syntax items.