HP ALLBASE/BRW Reference Manual for MPE/XL ABCDE HP Part No. 35360-90051 Printed in U.S.A.
NOTICE The information contained in this document is subject to change without notice. HEWLETT-PACKARD PROVIDES THIS MATERIAL \AS IS" AND MAKES NO WARRANTY OF ANY KIND, EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
Printing History New editions of this manual will incorporate all material updated since the previous edition. The software product part numbers printed alongside the date are the versions and update levels of the software products at the time the manual edition or update was issued. First Edition First Update Second Edition April 1988 December 1988 January 1992 35360 A.00.00 35360 A.00.01 35360 A.01.
Preface This manual documents the A.01.20 release of HP ALLBASE/BRW on HP 3000 computer systems running under the MPE XL operating system. This manual assumes that the MPE operating system is correctly installed and operating. It also assumes that you have HP TurboIMAGE/XL correctly installed and that you have access to the sample database, TOYDB. The HP ALLBASE/BRW Reference Manual contains 22 chapters, a glossary, and an index.
The typographic conventions used in this manual are summarized below. Conventions Used in This Manual Notation Description user input When necessary for clarity, user input is indicated like this. For example: BAMBOO: HELLO MGR.ITF3000 In examples, command names, options, and parameters are shown in uppercase letters, in computer font. For example: COMPUTER font SUM i = 1 TO ?month OF customers.
Contents 1. About HP ALLBASE/BRW At a Glance . . . . . . . . . . Features of HP ALLBASE/BRW Report tuning . . . . . . . Layout features . . . . . . Calculation language . . . . Interface to HP DeskManager Multiple application support . Multiple pass reporting . . . This chapter covers . . . . . . Elements of BRW . . . . . . . The Developer Version . . . . The Report Writer . . . . . BRWCONV . . . . . . . . Run-time Only Version . . . . HP BRW-Desk . . . . . . . . Requirements . . . . . . . . .
2. Understanding Relational Data and At a Glance . . . . . . . . . . This chapter covers . . . . . . About Tables . . . . . . . . . Source Tables . . . . . . . . Single-source Tables . . . . Multiple-source Tables . . . Access Tables . . . . . . . . About Relations . . . . . . . . What Next? . . . . . . . . . . Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2 2-2 2-3 2-3 2-3 2-4 2-4 2-5 2-5 3.
6. De ning Data Access At a Glance . . . . . . . . . . . . . . . . . . This chapter covers . . . . . . . . . . . . . . Joins and Relations . . . . . . . . . . . . . . . Joining Tables . . . . . . . . . . . . . . . . De ning Relations . . . . . . . . . . . . . . Open Joins . . . . . . . . . . . . . . . . . Multiple Open Joins . . . . . . . . . . . . . Open Join on HP ALLBASE/SQL Tables . . . . Joining a Source Table To Itself . . . . . . . . Joining HP ALLBASE/SQL Tables With Identical Table Names . . . . .
Item Edits For Exception Cases . . . . Layout Calculations . . . . . . . . . . Column Calculations . . . . . . . . . Restrictions on Column Calculations . . Row Calculations (Layout Calculated Items) Precision of Numbers . . . . . . . . . Suppressing Lines and Linesets . . . . . . Suppress Line Conditions . . . . . . . Suppress Lineset Conditions . . . . . . Special Cases . . . . . . . . . . . . . What Next? . . . . . . . . . . . . . . 8. Sorts, Breaks, and Pagination At a Glance . . . . . . . .
Standard Parameters . . Default Job Schedule . . Print File Characteristics Comments . . . . . . . Report Job File Body . . . Logon Command . . . . Parameters . . . . . . $Print le . . . . . . . $Parms . . . . . . . . What Next? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12.
Contents-6 14. The HP ALLBASE/SQL Interface At a Glance . . . . . . . . . . . . . . . This chapter covers: . . . . . . . . . . Using HP ALLBASE/SQL . . . . . . . . . HP ALLBASE/SQL Characteristics . . . . System Catalog . . . . . . . . . . . . Table Owner . . . . . . . . . . . . . . Access Authorization . . . . . . . . . . Transaction Handling and Locking . . . . Table Lock Mode . . . . . . . . . . . . Transaction Isolation Levels . . . . . . .
Syntax Diagrams . . . . . . Syntax Rules For Calculations Time . . . . . . . . . . . Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15-63 15-67 15-69 15-71 16. Using HP ALLBASE/BRW Screens At a Glance . . . . . . . . . . . . Compile Report Screen . . . . . . Date Item Edits Screens . . . . . . De ne Breaks Screen . . . . . . . De ne HPDesk Con guration Screen De ne Lines Screen . . . . . . . . De ne Relation Screen . . . . . . De ne Report Screen . . . . . . .
(Number ) . . . . . . . . . . . . . . . . . Rounding .5 to 1 or 0 . . . . . . . . . . . . Date Format . . . . . . . . . . . . . . . . Execution Values Screen . . . . . . Field Descriptions . . . . . . . . Default Print Device . . . . . . Use new Spoolfile after . . . pages If printed on Disc, max . . . lines Output/Work File Size . . . . . . Database Open Mode . . . . . . . Maximum Number of Lock Retries . . . . . . . . . . . . . . . . . Wait Interval between Lock Retries . . . . . . . . . . . . . . .
Mail Values Screen . . . . Field Descriptions . . . . Sender Name/Address . . Password . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Maximum Number of Deliverable Pages . . Maximum Number of Records for Files . . . Print Delivery Audit Trail . . . . . . . Security Features . . . . . . . . . . . . . . Password Encryption . . . . . . . . . . . BRWLIST . . . . . . . . . . . . . . . . $TELLREPORT . . . . . . . . . . . . . Dictionary Passwords . . . . . . . . . . .
Finding an Element's Attributes . . . . . . . . ELEMENT-SUBTYPE attributes . . . . . . . Keys . . . . . . . . . . . . . . . . . . . . Datasets . . . . . . . . . . . . . . . . . . Databases . . . . . . . . . . . . . . . . . . MPE Files . . . . . . . . . . . . . . . . . . KSAM Files . . . . . . . . . . . . . . . . . Using Byte-o sets . . . . . . . . . . . . . . De ning Overlays . . . . . . . . . . . . . . Preparing a BRWDIC Dictionary File With HP System Dictionary . . . . . . . . . . . . .
Modifying BRWDIC . . . . . . . . . . Automatically Recompiled Reports . . . . Automatic Upgrade of the Speci cation File Manual Upgrades to Speci cation Files . . Part IV: Administrative Tasks . . . . . . . . . . . . 18-64 18-64 18-65 18-65 19. Performance Considerations At a Glance . . . . . . . . . . . . . . . . . . This chapter covers . . . . . . . . . . . . . . In uencing Performance . . . . . . . . . . . . . What Your System Can Do For Performance . . .
BRW_PARAMETERS . . . . . . . . . . . . . . . BRWSTREAM BRWEXEC . BRWCLOSE . Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21. HP ALLBASE/BRW and HP BRW/V At a Glance . . . . . . . . . . . . . . . . . . This chapter covers . . . . . . . . . . . . . . Comparing HP ALLBASE/BRW and HP BRW/V . File names in HP ALLBASE/BRW and HP BRW/V File codes in HP ALLBASE/BRW and HP BRW/V Modes . . . .
Glossary Index Part IV: Administrative Tasks Contents-13
1 About HP ALLBASE/BRW An overview of HP ALLBASE/BRW Part I: Concepts About HP ALLBASE/BRW 1-1
At a Glance At a Glance HP ALLBASE/BRW is a high performance business report writing system for information management professionals. Its ease-of-use signi cantly reduces the time needed for report development. It runs on any HP 3000 running MPE XL. HP ALLBASE/BRW is available in two versions: the full developer system, and the run-time version. The developer system contains all the programs and utilities necessary to develop reports and to run them.
At a Glance Multiple pass reporting You can output data from a speci c report to an intermediate report le and then use the report le to generate further reports.
Elements of BRW The Developer Version The Report Writer The report writer is the main part of the HP ALLBASE/BRW developer system. You use the report writer to specify, compile, and execute reports. BRWCONV BRWCONV converts existing reports, created with Report/V or Inform/V, into HP ALLBASE/BRW format. BRWCONV translates all Inform/V features; no manual modi cations are necessary. BRWCONV does not translate all Report/V features; some are not available or not necessary in HP ALLBASE/BRW.
Requirements Requirements What You Need The following sections describe the hardware and software requirements for using HP ALLBASE/BRW. To use the developer version Have the MPE operating system correctly installed and operating. Have the developer version of HP ALLBASE/BRW installed and con gured. For non-SQL reports, you need the BRWDIC le, which is used to create and compile non-SQL reports.
Requirements 1-6 About HP ALLBASE/BRW Part I: Concepts
BRW Files BRW Files When you create a report with ALLBASE/BRW, you use as well as create les on the MPE XL system. The gure shown on the previous page represents the basic les used or created by ALLBASE/BRW and also shows some logical components of the ALLBASE/BRW report le. This section describes these les and logical components: data sources created les temporary les logical components of the speci cation le Data Sources You build a table using from 1 to 14 source tables.
BRW Files You can save certain linesets from your report by sending them to an output le. The output le contains only the items from the lineset. Output les and formats are discussed in Chapter 12. Temporary Files While ALLBASE/BRW is creating and processing your report, some temporary les, like work les and sort les, are created. These les are eliminated when the processing is complete.
Sample Databases Sample Databases Note The TOYDB Database HP ALLBASE/BRW is supplied with one sample database (TOYDB), an HP TurboIMAGE/XL database. Another database (EXDBE) is an example database for HP ALLBASE/SQL data. The EXDBE database is not supplied with HP ALLBASE/BRW. In this manual, the term, IMAGE database, is used to represent both IMAGE and HP TurboIMAGE/XL databases.
Sample Databases The EXDBE Database In this manual, a database called EXDBE is used as the basis for several HP ALLBASE/SQL examples. This database is an example only, and does not exist as part of HP ALLBASE/BRW. The following gure shows the organization of this sample database. If you would like to try some sample reports from a real HP ALLBASE/SQL database, use the PARTSDBE database provided with HP ALLBASE/SQL.
Sample Databases The PARTSDBE Database Part I: Concepts The HP ALLBASE/SQL database PARTSDBE.SAMPLEDB.SYS is composed of tables that contain data on parts and vendors. The database is supplied with HP ALLBASE/SQL.
Limits on HP ALLBASE/BRW Limits on HP ALLBASE/BRW Item Types HP ALLBASE/BRW supports the following item types: Strings Strings with up to 255 characters. Items greater than 255 characters are truncated. Numeric Packed decimals, zoned decimals, and display numerics in the range +/-9,999,999,999,999.999999. HP ALLBASE/BRW internally converts them into binary-coded decimal numbers with a total of 19 digits, including 6 decimals. Zoned decimals (type Z) must be Trailing Overpunch or No Sign. See Chapter 18.
Limits on HP ALLBASE/BRW Database and File Types Printers HP ALLBASE/BRW supports the following types of databases and les: IMAGE and TurboIMAGE databases. The database IMAGE passwords supplied with the datasets speci ed in HP Database ALLBASE/BRW reports must allow access to all items from these datasets, unless Item Level Security is speci ed in the Tune Access screen. HP SQL database. Access authorization is checked HP ALLby HP ALLBASE/SQL for MPE logon user.
Report Characteristics What Next? HP ALLBASE/BRW reports have the following characteristics: up to 300 characters in the report width up to 999 lines in the page length up to 9 sort levels up to 50 parameters and MATCHES statements in formulas (that is, both numbers together cannot exceed 50) Chapter 2 contains concept information about relational data, including tables. If you need help using editing keys or using the on-line help, read Chapter 3, \Communicating With HP ALLBASE/BRW.
2 Understanding Relational Data and Tables Describing the relational model Part I: Concepts Understanding Relational Data and Tables 2-1
At a Glance At a Glance HP ALLBASE/BRW uses a relational model for manipulating data. The relational model looks at data as if it were in a table, with rows and columns. The model is concerned with data structure, data integrity, and data manipulation. All data is represented by explicit value. The table below represents a typical relational model for data: CUSTOMER-NO CUSTOMER-NAME CITY SALES-AREA STATE ZIPCODE 223 Nancy Watson 1411 Oak Roseville CA 95999 789 Mike Barnes 232 Benton Dr.
About Tables There are two main kinds of tables: source tables and access (result) tables. About Tables Source Tables You build an access table using from 1 to 14 source tables. Source tables can be any of the following: IMAGE data sets HP ALLBASE/SQL tables KSAM les MPE les HP ALLBASE/BRW tables|That is, other access tables de ned in the same report. These tables are useful when you are using a series of tables in the same report.
About Tables Multiple-source Tables Multiple-source tables access data from more than one data set, table, or le. Multiple-source tables are listed on the De ne Table screen. You can join or merge a multiple source table. d a c b Joining and merging tables is completely described in Chapter 6.
About Relations A relation speci es which records from the source tables are combined in the new table. Each relation consists of an item that is common to several source tables. The new, joined table consists of records in which the common item entries of all source tables have identical values. Relations can be thought of as lters which extract from all possible combinations only those records which match the relation (that is, have the same value). Relations need not a ect all source tables.
3 Communicating with HP ALLBASE/BRW Moving from screen to screen, entering data, editing elds, understanding messages and prompts, and getting online help Part I: Concepts Communicating with HP ALLBASE/BRW 3-1
At a Glance At a Glance Note If you are just learning HP ALLBASE/BRW and have not used another report writing system or other Hewlett-Packard database management product, this chapter can help you communicate with HP ALLBASE/BRW. Information in this chapter assumes that you are using HP ALLBASE/BRW from an MPE XL terminal and are using a standard terminal keyboard.
Using HP ALLBASE/BRW Screens Using HP ALLBASE/BRW Screens Entering and Editing Data HP ALLBASE/BRW is screen driven. You de ne a report by moving from screen to screen and entering the speci cations for the report. HP ALLBASE/BRW data screens have one or more input elds that allow you to enter and edit data. Data screens can also have display-only elds on them. These elds display system or application information. You cannot change the information in display-only elds.
Using Softkeys The 4Clear Display5 key clears all input elds from the current cursor position to the bottom of the screen. Note Using Softkeys The 4Return5 key has no function in HP ALLBASE/BRW. If you press 4Return5, the cursor moves to the rst column of the terminal screen. Eight labels (softkeys), displayed at the bottom of all HP ALLBASE/BRW screens, indicate the functions that you can access from the screen. The softkeys correspond to the eight function keys on the terminal keyboard, 4f15 to 4f85.
Using Quick Browse NNNNNNNNNNNNNNNNNNNNNNNNNN Using Quick Browse NNNNNNNNNNNNNNNNNNNNNNNNNN If a screen displays Next ... and Prev ... , you can skip directly to a particular element in a list. To skip to a particular element in a list 1. In the highlighted eld, type the name of the element (item, table, parameter, and so on) you want to see. 2. Choose Next ... or Prev ... .
Getting Online Help Getting Online Help Online help is available throughout HP ALLBASE/BRW. When you press the Help function key, a help screen with information about that screen appears. NNNNNNNNNNNNNN Often, the help screens have more than one \page." HP ALLBASE/BRW help screens display these softkeys to help you move through the information.
Understanding Messages and Prompts Understanding Messages and Prompts HP ALLBASE/BRW displays several types of messages on the screen (usually on the last line): system-active messages grant messages warning messages error messages System-Active Messages HP ALLBASE/BRW displays a system-active message while it performs the action you requested. System-active messages are displayed in normal blinking video.
For more information about error messages and a list of common messages for di erent tasks, see the index section of this manual under \errors." Prompts On occasion, when you need to enter information, HP ALLBASE/BRW displays a prompt on the screen. You can do any of the following when a prompt appears: Type an answer and press 4Enter5 or press the function key that initiated the action. Press the Cancel Prompt function key and resume working on the screen.
4 Getting Started With HP ALLBASE/BRW Logging in to the MPE operating system and starting HP ALLBASE/BRW Part II: Tasks Getting Started With HP ALLBASE/BRW 4-1
Logging in to MPE At a Glance This chapter covers Logging in to MPE logging in to MPE running HP ALLBASE/BRW leaving HP ALLBASE/BRW To log in to MPE 1. At the operating system prompt, type: : HELLO username.acctname,groupname 4Return5 For example: : HELLO MGR.ITF3000,BRWSPEC 4Return5 2. If the system manager has assigned a password, MPE prompts you to enter it. When you see the password prompt, type your password and press 4Return5.
Leaving BRW Running HP ALLBASE/BRW To run HP ALLBASE/BRW 1. At the MPE prompt, type: : RUN BRWXL.PUB.SYS 4Return5 The Select Report screen appears. d a c b The Select Report screen is the main screen for HP ALLBASE/BRW All report tasks begin here. Additional notes If you have other information about starting HP ALLBASE/BRW (such as UDCs), write it in the space provided here.
Leaving HP ALLBASE/BRW To leave HP ALLBASE/BRW 1. Press Exit until you arrive at the Select Report screen. NNNNNNNNNNNNNN 2. At the Select Report screen, press Exit BRW . NNNNNNNNNNNNNNNNNNNNNNNNNN 3. Press 4Enter5 to con rm leaving HP ALLBASE/BRW. What Next? The next chapters describe HP ALLBASE/BRW tasks you do for creating reports. For information about creating, compiling, or running the report, see the following chapters: To create a report, go to Chapter 5 through Chapter 9.
5 Report Writing with HP ALLBASE/BRW Creating a report speci cation le Part II: Tasks Report Writing with HP ALLBASE/BRW 5-1
At a Glance At a Glance This chapter is a general overview of the process of creating a report. First, de ne the general report characteristics, such as report width and page length, on the De ne Report screen. Supply data access information. Next, on the De ne Breaks screen, specify how the report is to be sorted and paginated. De ne calculations and conditions. Then, compose the report layout by positioning text and items for each lineset on the bottom portion of the De ne Lines screen.
At a Glance We highly recommend going through the HP ALLBASE/BRW tutorial, HP ALLBASE/BRW Self-Paced Training , if you are new to HP ALLBASE/BRW. This chapter, as well as all other chapters in this reference manual, will make more sense if you have had some HP ALLBASE/BRW hands-on experience.
Planning Ahead Planning Ahead Before you attempt to create a report with HP ALLBASE/BRW, you need to have a plan for the report. The following table shows the steps you need to take to plan the report and shows how the plan can be implemented in HP ALLBASE/BRW. Planning and Implementing Planning Steps Find out where the information you need is stored.
Creating a Report Creating a Report After you have planned the report to your satisfaction, start HP ALLBASE/BRW and create the report. To create a report 1. Start at the Select Report screen (the rst screen when you start HP ALLBASE/BRW). (For instructions on starting HP ALLBASE/BRW, see Chapter 4). 2. Type the name of the report you want to create in the Report eld. This will be the name of the report speci cation le that HP ALLBASE/BRW creates. d a c b NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3.
Creating a Report d a c b 4. Press 4Enter5 to record the report description. The message: Report modified appears. Global Report Characteristics On the De ne Report screen, there are several global report characteristics that have a default value already assigned. These characteristics are described below to help you select the correct ones for your report.
Creating a Report while you are reading the data, you may get only partially updated information. RC (read committed) is the default. If this eld is blank, the execution value in the BRW Con guration File is used. Data Access Data access can be de ned in either of these two ways: USE Access by typing the data access de nition of another report at the Select Report screen (in the USE Access of Report eld).
What Next? If you want to know more about data access, read Chapter 6. If you are ready now to start de ning the details of the report. See chapters 6 through 10 of Part II for complete task information. To look up reference information, use Part III. Part IV is useful information for system managers and programmers.
6 Defining Data Access Adding tables, de ning relations, placing items, and tuning access Part II: Tasks Defining Data Access 6-1
At a Glance At a Glance Data access includes all the methods you use to access the data in source tables or les. These methods can be one or more of the following: adding tables joining or merging tables de ning relations deciding on which items to use in the nal report tuning the data access At the simplest level, you access one data source and use all the items in the nal report.
Joins and Relations Joins and Relations Joining Tables You join two or more source tables by: naming the table specifying the type of join listing the data sources de ning a relation between the records in each table. The relation is based on a common item. You may specify an open join to further qualify the results you get from the join. A join is not complete until you have de ned the relation between the joined tables. To join tables 1.
Joins and Relations NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 7. Choose Add Table to add the joined table as an access table for this report. Defining Relations To define a relation NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Go to the De ne Relation screen by choosing Define Relation on the De ne Tables screen. An example of the De ne Relation screen is shown below. 2. Type the name of each item from the source tables that you want to de ne as a common item.
Joins and Relations Note HP ALLBASE/BRW does not require that the relation is logical. That is, you could relate, say, CUSTOMER-NO item of the INVOICES table with the SALES-AREA item of the CUSTOMERS table, since both items are the same data type (strings). (If two strings of di ering lengths are compared, the shorter is padded with blanks.) A record would then be written to the new table, CUST-INVOICES, when the value for CUSTOMER-NO was equal to the SALES-AREA.
Joins and Relations The join of CUSTOMERS and INVOICES on CUSTOMER-NO to produce the result table CUST-INVOICES is illustrated in the following gure: Each record of the table CUST-INVOICES contains invoice information and all the customer information for the customer to whom the invoice applies.
Joins and Relations Open Joins Open joins let you join source tables and report all records from one source table, even if these have no corresponding records in the other source table. Items in the open join source table are set to NO VALUE when there are no corresponding records. Note NO VALUE is an exception condition and is described in Chapter 15. NO VALUE indicates that an item has no value.
Joins and Relations You can specify only one source table on each De ne Table screen as an open join. If you want open joins on more than one source table, see \Multiple Open Joins" later in this chapter. The next illustration shows what happens when there is an open join on INVOICES. All items coming from INVOICES will be set to NO VALUE in the resulting row when no invoice is present. NO VALUE is signi ed in this table by a long dash (-------).
Joins and Relations 4. Return to the De ne Table screen. 5. Type the de nition information for the next table. You can type over the previous de nition and use the space bar to remove extra characters if you need to. 6. Choose Add Table . Do not press 4Enter5 (instead of Add Table ).! Pressing 4Enter5 will rede ne the rst table instead of adding a new table. 7. De ne the relation for the new table.
Joins and Relations d a c b The other table, shown next, joins CUSTOMERS and PAYMENTS with an open join on PAYMENTS (the access table created by the previous join). The De ne Table for this open join looks like this: d a c b CUSTINV is the nal access table.
Joins and Relations Open Join on HP ALLBASE/SQL Tables With HP ALLBASE/BRW, a user can de ne an open join using HP ALLBASE/SQL tables. Example: Open join on HP ALLBASE/SQL table The following example is for a report of all vendors and the parts they sell, including those vendors that do not sell parts. The two data sources are VENDORS and SUPPLYRPICE in the SUPPLYDB DBEnvironment, as shown on this De ne Table screen.
Joins and Relations HP ALLBASE/SQL will create a separate work le for each HP ALLBASE/SQL query. For more information about tuning the data access, see \Data Access Methods" later in this chapter. Joining a Source Table To Itself You can join records from one source table to other records in the same source table. Since the source table names in a table must be unique, nested tables can be used to join a source table to itself, as in the following example.
Joins and Relations d a In your new table, you want the item EMPLNAME from the SUPERVISORS table to be called SUPERVISOR-NAME. To rename it, give the item an alias name as shown here: d a The nal joined table looks like this: Joining HP ALLBASE/SQL Tables With Identical Table Names Note Within HP ALLBASE/BRW, tables are identi ed by their table names only. Therefore, HP ALLBASE/SQL table names belonging to one BRW table must be unique.
Joins and Relations d a c b Create the HP ALLBASE/BRW table COMBINED-PARTS. This holds the results of joining the HP ALLBASE/BRW table (STORED-PARTS) and the HP ALLBASE/SQL table (CUSTOMERORDERS.PARTS). d a c b Join the two HP ALLBASE/BRW tables by using the PARTNUMBER in each table.
Merged Tables Merged Tables By merging tables, one source table is appended to another to form a new table containing all the information from both tables. To merge tables 1. On the De ne Table screen, type the name for a new table in the Table eld. 2. Type M in the Type eld (for merge). 3. In the Source Table elds, type the names of the source tables from which the data for the new table is to be extracted. Specify owner name.table name for each HP ALLBASE/SQL table. 4.
Merged Tables In this example, the CUSTOMERS source table does not contain the items INVOICE-NO and AMOUNT which appear in the merged table. Records coming (projected) from this source table will contain NO VALUEs for these items in the merged table. Likewise, items CITY, NAME, and so on, appear only in source table CUSTOMERS and will be set to NO VALUE in records projected from source table INVOICES.
Merged Tables Note that records are not related when tables are merged. Thus customer Boot Inc, (CUSTOMER-NO C2), has more than one record in the result table. The following section of the De ne Table screen shows how the above source tables are merged: d Note Part II: Tasks You cannot specify calculated items or relation conditions in a merged table.
Data from Different Sources Data from Different Sources With HP ALLBASE/BRW it is possible to show, on one report, data coming from an IMAGE database, an HP ALLBASE/SQL database, MPE les, and KSAM les. To combine data from different sources 1. De ne the table, relations, and relation conditions just as you would any other table. Remember to specify the owner name.table name for any HP ALLBASE/SQL tables. d a c b 2.
Data from Different Sources d a c b Join the two HP ALLBASE/SQL tables PARTS and PRODUCTPARTS using PARTNUMBER. This provides the product numbers for each part. d a Join the HP ALLBASE/SQL table PRODUCTPARTS and the IMAGE data set PRODUCTS using the product number. This provides the product information for each product the part is used in. d Create the formula that selects only parts with a part price greater than or equal to 1000.00 and products with a product price less than 5000.00.
Data from Different Sources Access Sequence No. 1 Table Explanation PRODUCTPARTS and PARTS HP ALLBASE/BRW generates an HP ALLBASE/SQL query to retrieve parts with a PARTPRICE greater than or equal to 1000.00 where there is a corresponding PRODUCTNUMBER (according to the relation de ned for these two SQL tables). For the HP ALLBASE/SQL tables, the default access sequence number is the same if the tables belong to the same DBEnvironment and are listed consecutively on the De ne Table screen.
Items In HP ALLBASE/BRW, the columns from the data source table are known as items. Projecting an item means that the values for an item are made available for the new table and can be used in later tables or reports. Items Projecting Items By default HP ALLBASE/BRW projects all items with unique names from their source tables to the result table. If items have identical names in a joined table, only one of them is projected.
Items Exclude items from projection. Excluding items is not necessary for performance but can help maintain security. Change numeric precision of an item when it is projected from a table. To exclude items from projection 1. At the De ne Table, De ne Relation, or Relation Condition screen, choose Project Items . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN All items for all the sources you used for your table appear on the Project Items screen.
Items letter you type is a method code indicating how any super uous digits are treated: Method Code Digits are: T truncated R rounded C ceiled Explanation All the digits outside the precision are dropped. Rounded up if the rst digit outside the precision is a 5 or greater and rounded down if the rst digit outside the precision is less than 5. Always rounded up.
Items Note HP ALLBASE/BRW always projects a common item from a source table not subject to an open join, no matter what table you select for projecting the item. For example: suppose you selected the common item CUSTOMER-NO to be projected from the open-join table INVOICES. If there are no invoices for a customer, that customer's CUSTOMER-NO will contain NO VALUE, as you projected it from INVOICES, the open-join source table. (All other information held in the CUSTOMERS table would be projected normally.
Precision of Numbers Precision of Numbers You can de ne the precision for any type of number on the Project Items screen if you want the precision changed at this stage of the report. Changing the precision of an item on the Project Items screen only a ects how the item is used outside the current table.
Data Access Methods Data Access Methods You de ne the data access when you de ne the table. In most cases, you use both the De ne Relation screen, the Parameter screen, and the Tune Access screen to de ne the data access.
Data Access Methods Secure locks on IMAGE datasets or les. De ne Open Mode capabilities such as \Read, allow concurrent modify". To tune data access 1. De ne the source tables, relations, relation conditions, and parameters for your report, as described in previous chapters. 2. From the De ne Table screen, choose Tune Access . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN The Tune Access screen lists the source tables you have speci ed and also shows the table type. d a c b 3.
Data Access Methods sequence number for each table. (If the tables are listed consecutively and no open join is indicated, the same access sequence number is assigned by default.) If di erent access sequence numbers are entered for HP ALLBASE/SQL tables, separate queries are generated. If keyed access is used for the rst source table in the access sequence, a relation must be de ned and a single-value or list-of-values parameter must provide the value for that key.
Data Access Methods d a d a The Tune Access screen shows the access sequence and keys used for these source tables: d a Access by Record Number You can access a record in an IMAGE data set, KSAM le or MPE le directly by using its logical record number (TABLE-REC-NUMBER) in a relation. In the following example, the le PRODX (format PRODUCTINDEX) contains an item (PRODUCT-REC#) that relates to the logical record numbers (TABLE-REC-NUMBER) for the records in the data set PRODUCTS.
Data Access Methods The access sequence is de ned on the Tune Access screen: d Once the report is de ned, you can experiment separately with ways of accessing the data. You can use serial read or chained/calculated (keyed) reads to see which takes the less time. The access method will not change the result of the report. HP ALLBASE/BRW veri es that the access methods are compatible with the relational access de nition.
Relation Conditions Relation Conditions Relation conditions restrict the records written to the result table to those that match a speci c condition. Relation conditions are independent of relations. That is, you do not need to de ne a relation in order use a relation condition. Relation Conditions have these characteristics They select a subset of records to be included in a table. They are calculated from items, calculated items, or functions de ned in the same table, parameters or constants.
This condition selects shipment dates in the month equal to the parameter value provided at run time for ?month: MONTH_OF (shipment-date) = ?month When you de ne a relation condition and a relation, the relation condition further re nes the data selection. For example, suppose you de ned a new table (CUST-INVOICES) from the CUSTOMER table and INVOICES table with a relation between the CUSTOMER-NOs for each table.
7 Defining Report Layout De ning linesets, and placing and editing items Part II: Tasks Defining Report Layout 7-1
At a Glance Features of Report Layout This chapter covers 7-2 Defining Report Layout You de ne the report layout by lling in elds on the De ne Lines screen. The speci cations you enter are checked on-line for correctness. As you are de ning the layout, you can review what you have already de ned for the report by looking at the Review Layout screen. This makes it easy for you to create new reports and to maintain existing ones.
Using the Screen Using the Screen This is one example of the De ne Lines screen where the report is laid out. This particular screen shows the lines for the heading (including the column headings) for a rst-level break in the report. There is a similar screen for details, report headings, report footings, and break level footings. d a c b In the top half of the De ne Lines screen, XXX's and 999's are used to indicate where data from items de ned in the bottom half of the screen will be printed.
Using the Screen These softkeys let you create and revise lines on the report: inserts a line at any position in the lineset. Insert Line NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Delete Lines deletes one line or a line range. moves a line or a line range to an other position in the lineset. Items positioned on that lines are also moved to the new location.
Using the Screen The initials indicate: RH A report heading (at the beginning of the report) PH A page heading (at the top of each page) BH A break heading (repeated at the beginning of each break) D A detail line (repeated for each item in the report) BF A break footing (repeated at the end of each break) PF A page footing (at the bottom of each page) RF A report footing line (at the end of the report) The number to the left of the break lineset initials is the break level number.
Defining Linesets Defining Linesets A lineset is a unit printed when a certain event occurs in a report, for example, when a detail occurs. A lineset can consist of a single line of text and items, multiple lines, or no lines. You de ne linesets at the De ne Lines screen. There is a di erent De ne Lines screen for each lineset type. For instance, if you want to de ne the lines for the page heading, you must go to the De ne Lines screen for Page Heading. To get to the Define Lines screen 1.
Defining Linesets d a c b 2. Type a line number for the rst line of the page heading in the rst eld of the line. 3. Type any text you want to appear on the line in the second eld of that line. 4. Press 4Enter5. 5. Repeat the three previous steps for every line you want in the page heading. 6. Type the item name and information for any items you want to use in the page heading. As you enter the information for each line, the top portion of the screen will show what the report layout looks like.
Defining Linesets To position items on the lines of the lineset 1. Type the name of the item in the Item eld. 2. In the Pos eld, type the column number where you want the rst character of the eld to be placed. 3. In the Line eld, type the number of the line where you want the item placed. This is optional. If you want to use the current lineset you can leave the number blank. The current lineset is indicated by an asterisk (*) in the left-hand (line number) eld of the upper screen. 4.
Detail Linesets Detail Linesets 100114 100213 100311 100321 100327 100365 100432 100498 100537 100568 100827 100888 100946 100953 The detail lineset is printed for each record in the nal access table, that is, for each detail. You use it to print the detail data in your report. For example, the speci cation of a simple listing report only consists of a detail lineset, one for each record in the report, as shown in this example: ACME INDUSTRIES BLACK & WHITE TOOLS EQUIPMENT CORP.
Page and Report Linesets Page and Report Linesets Page Heading Lineset In addition to specifying break heading and footing linesets for a report, you can de ne page linesets and report linesets. The page heading lineset speci es that certain data will be printed on the top of each page. Example: Printing the page number, date, and a title This example shows the page number, date, and title being inserted on the page heading.
Page and Report Linesets Example: Repeating the break heading on each page Suppose you want to specify a new page before printing the break heading line set, but you do not want to de ne lines for that break heading lineset. Instead, use the page heading lineset to print the break heading data. Look at the following example: d a c b The page heading lineset prints data from the detail that is used to print the next lineset.
Page and Report Linesets Page Footing Lineset The page footing lineset is printed at the bottom of each page and can contain the page number and summarised data for the page. The page footing can be replaced by another lineset. You can specify that a break footing lineset is printed instead of the page footing lineset on each page where the break occurs.
Page and Report Linesets Report Heading Lineset The report heading lineset is printed once at the beginning of the report. You can use it to print global data for the report (see next sample screen). Normally, a report heading lineset is preceded by a page heading lineset. Example: Suppressing a page heading You have de ned a page heading and a report heading. The report heading contains some of the same information as the page heading (such as the name and date of the report).
Page and Report Linesets Report Footing Lineset The report footing lineset is printed at the end of the report. You use it to print summarized data for the whole report. Example: Defining a report footing This screen sample shows the De ne Lines screen for a two-line report footing showing the outstanding receivables as of a particular day: d a c b The report footing lineset prints data and column calculations from the last detail in the report.
Break Linesets Break Linesets Break Heading Linesets Text, items, or summarized data can be printed each time the value of a sort item changes (breaks). You can create lines that print before the beak (a break heading) or lines that print after the break (a break footing). Break heading linesets can be de ned for each sort item, so that heading information is printed for the new sort item value.
Break Linesets Break Footing Linesets You can de ne break footing linesets for each sort item, so that summary information of the previous details is printed. Example: Printing summary information You can print totals or average values as break footings, as shown here: d a c b In this example, totals for the items invoice-amount, paid-amount and due-amount will be printed as the break footing for each customer.
Positioning and Editing Items Positioning and Editing Items Item values can be printed anywhere in the lineset, as long as there is no overlap with text or other items. HP ALLBASE/BRW provides a variety of editing capabilities for item values. You can position and edit items, array items, and parameters. As indicated in the task description earlier in this chapter, HP ALLBASE/BRW provides defaults if you omit line and length in an item speci cation.
Positioning and Editing Items Suppressing Repeating Item Values You can make a tabular report more readable by omitting its redundant elements.
Positioning and Editing Items 2. A prompt asks you to type the name of the item to be edited: Type the item edit name NNNNNNNNNNN 3. Press 4Enter5. If the item is a string, the String Item Edit screen appears. d a c b If the item is a numeric, the Numeric Item Edit screen appears. d a c b String item edits and numeric item edits are described in the next sections. Note Part II: Tasks Upper-and lower-case characters are treated literally in edit masks.
Positioning and Editing Items Editing items is optional. You need not explicitly supply item edits for all items. HP ALLBASE/BRW copies the default item edits from the con guration le, once you have speci ed an item on the De ne Lines screen. This default item edit is displayed automatically on the Item Edits screen for an item. See Chapter 17 if you want to alter the default edit mask. Item edits are performed in the sequence in which the edits are displayed on the screen.
Positioning and Editing Items Numeric and Integer Item Edits Items of types N and I need an edit mask for printing. However, the default edit mask is often all that is needed. To change the default edit masks, see Chapter 17, \Con guration and Security." Numeric item edits are de ned at the Numeric Item Edits screen. d a c b If you do not want the default item edits, you can explicitly do one of these types of edits: Scale the number.
Positioning and Editing Items De ne a pre x and a su x for both positive and negative numbers. For example, this Numeric Item Edits screen shows a pre x assigned to the item for the positive as well as the negative instance of the value. d a c b This table shows the result of a su x edit on a positive and a negative value. Original Values Edited Values -12.37 Cr 12.37 +50.92 Dr 50.92 De ne an edit mask to print the number with or without leading and trailing zeros (in the fraction part).
Positioning and Editing Items d a c b Original Values Edited Values 0 ||-.| Real Item Edits See the screen Real Item Edits in Chapter 16 for a description of how you can print Real Items. Date Item Edits Date items always need an edit mask for printing. If you do not want the default item edits, you can specify another edit mask to print the date item, as shown here: Time Item Edits Justify And Prefill d a c b Time items always need an edit mask for printing.
Positioning and Editing Items d a c b Value Results In: 50 Item Edits For Exception Cases ***50.00 NO VALUE and error values (see Chapter 15, \Calculations") do not permit item edits to be performed and require default edits. System defaults for these edits are described in Chapter 17, \Con guration". These and other exception cases are described below: NO VALUE is edited by lling the print eld with the default no-value character.
Positioning and Editing Items In addition, a warning message, such as those listed below, is printed at the end of the report if one of these exception cases occurs. The following exceptions occurred in this report: Field overflow occurred with item PAID-AMOUNT Error values occurred with itme AMT-DUE (54*) (24*) The number to the right of the message indicate the number of times the exception occurred in the report.
Layout Calculations Layout Calculations Layout calculations are those which are performed when the report is formatted.
Layout Calculations d a c b Running column calculations begin again after being reset. 3. If you want to specify the source level for the calculation, type an optional su x preceded by a comma after the calculation type. Use one of these su xes: ,R To use the report break as the source ,P To use the page break as the source ,1-,9 To use one of the break levels (1 through 9) as the source no su x The default. The value is taken from the detail line.
Layout Calculations The item name for column calculations on array items is speci ed in one of two ways: With an index If you want to print only the item. Without an index If you use the column calculation result of the array item in a calculation formula, or if you want to write it to an output le. See Chapter 9, Multi-pass Reporting.
Layout Calculations Suppose the column calculation TOTAL is used on the item TURNOVER-PY (turnover for the previous year) without a source level. The value for TURNOVER-PY will be added for every detail. However, the report requires that the turnover be added only at the break on CUSTOMER-NO, since the turnover has only one value per customer. If it is added at every detail, the turnover would have a value equal to the actual turnover times the number of records for the same customer.
Layout Calculations The resulting report looks like this: SALES AREA: EAST Customer: 100888 Invoice-No ---------IN5325 IN5331 Amount ------3029.85 850.00 -------3879.85 Previous-year Turnover: 2439.75 Amount ------1775.00 2125.00 13400.00 4137.50 -------21437.50 Previous-year Turnover: 19765.50 Total Sales Area Invoice Amount: 25317.35 Previous-year Turnover: 22105.
Layout Calculations Example: Using both reset and source levels You can specify both the source and reset level for column calculations. The reset level is the rst character after the column calculation, followed by a comma, followed by a character for the source level. See the following table: Restrictions on Column Calculations Part II: Tasks Restrictions on column calculations are as follows: For break heading/footing and detail linesets, the reset level must be less than the source level.
Row Calculations Row Calculations (Layout Calculated Items) Row calculations are layout calculated items. They are calculated when the report is formatted.
Row Calculations To define row calculations NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Layout Calc Item on the De ne Report screen or on the De ne Lineset screen. 2. Type the name of the item, result type, result length, and numeric result precision in the appropriate elds. d a c b NNNNNNNNNNNNNNNNNNNNNNNNNN 3. Choose Add Item . 4. Choose Define Formula . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 5. At the Layout Calc Item Formula screen, type the calculation formula.
Row Calculations To calculate an item on another lineset 1. 2. 3. 4. 5. Go to the De ne Lines screen. Type the name of the item in the Item eld. Type the position and line for the item in the Pos and Line elds. Type CALC in the ColCalc eld for that item. Type any other information as appropriate for that item (such as numeric precision or suppression of repetition). 6. Press 4Enter5.
Row Calculations Here is an example of the report using these items: Actual -----500 300 900 -----Totals: 1700 Budget -----250 250 450 -----950 Variance-% ---------100.00 20.00 100.00 ---------78.95% VARIANCE-% is calculated each time the value of the item account changes. Because CALC is speci ed in the ColCalc eld for this item, the total values of arguments ACTUAL and BUDGET are used to nd the percentage of variance of these totals.
Row Calculations Use one of the following method codes to indicate how super uous digits are to be treated: Method Code Digits are: R rounded T truncated C ceiled T means truncate any remainder. C means round up if the remainder is non-zero. The default method is to round. In the con guration le you specify whether the exact half is to be rounded up or down. See Chapter 17. Some examples of how to use numeric precision are shown below.
Row Calculations To obtain total sales values where the total is built from single sales values changed to K$, specify the item in the detail lineset on the De ne Lines screen as shown here: d c And in the break footing lineset with a TOTAL in the ColCal eld as shown here: d c Example: Controlling numeric precision As another example, if you wanted to obtain the result of the layout calculated item tax-amount with two decimals, you would specify -2R as the numeric precision.
Suppressing Lines and Linesets Suppressing Lines and Linesets Suppress Line Conditions HP ALLBASE/BRW lets you specify condition formulas which control when lines and linesets are printed. Conditions use the Calculation Language described in Chapter 15; see the section in that chapter called Rules For Formulas and Conditions. For each line in a report you can de ne a condition formula that expresses when that line is to be suppressed.
Suppressing Lines and Linesets To suppress a lineset 1. At the De ne Breaks screen, choose Suppress Lineset . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. Answer the prompt for the appropriate lineset level: Specify lineset level (D,P,R,1-9): FFFF FFFF and type (H,F): : 3. At the Suppress Lineset screen, type the formula for the condition. 4. Press 4Enter5. Example: Suppressing a lineset Suppose you want to suppress a detail line if there is no order.
Special Cases Special Cases This section provides some examples for special uses of Report Layout features. Example: Using alternate lines Instead of NO VALUE item edits, you can specify another line in the detail lineset with a phrase to be used when the item has no value. Note, however, that the two lines need complementary suppress line conditions. The following Customer Order Report has an open join on the orders table.
Special Cases Example: Bucketing reports Suppose you want to consolidate several reports into one. In order to do that, you need to rearrange some of the numeric data from one table from a vertical orientation to a horizontal orientation. The report below is an example of bucketing, in which numeric data has been rearranged. Report: SALESREP 11/11/86 Page 1 Area Sales Report by Product ____________________________ Product Line: 60 Product ------- EAST ---------- 47392A 14326A 47392X 78532J 352.
Special Cases In order to do this using the sample database TOYDB, you can de ne four required table calculated items as listed below: SALES-EAST IF sales-area = "EAST" THEN quantity * price ELSE 0 SALES-NORTH IF sales-area = "NORTH" THEN quantity * price ELSE 0 SALES-WEST IF sales-area = "WEST" THEN quantity * price ELSE 0 SALES-SOUTH IF sales-area = "SOUTH" THEN quantity * price ELSE 0 On the De ne Lines screen below, the break footing on product-number is de ned.
Special Cases Example: Reports for pre-printed forms HP ALLBASE/BRW lets you set up a report to print on pre-printed forms, provided you specify the correct positioning of items on the report layout. Follow the steps below to set up a report for a pre-printed form: 1. Set up a template report with columns and row numbers which count across and down the layout, as shown on the next page. 2. Print the template on the pre-printed form paper. 3.
Special Cases 10 2 3 4 5 6 7 8 9 20 1 2 3 4 5 6 7 8 9 30 1 2 3 4 5 6 7 8 9 40 1 2 7-44 45678901234567890123456789012345678901234567890123456789012345678901234567890 2 CUSTOMER-NO: ___________ CUSTOMER-NAME: ____________________ 3 4 ADDRESS: ____________________ 5 ____________________ 6 ____________________ 7 __ ___________ 8 9 45678901234567890123456789012345678901234567890123456789012345678901234567890 1 2 CUSTOMER-NO: ___________ CUSTOMER-NAME: ____________________ 3 4 ADDRESS: ____________________ 5 _
Special Cases Example: Voiding unused check forms The report shown on the next page and the following series of screens illustrate how to void unused check forms with HP ALLBASE/BRW. The check report, shown below, has two alternate footings, which print the check stub (grey areas). The rst footing is a page footing. The second footing is a break footing on vendor. The De ne Breaks screen below shows the break footing as positioned absolutely in the same position as the page footing.
Special Cases Sample voided check form The following two De ne Lines screens show the page footing lineset and the break footing lineset. The page footing prints text which voids the check stub. The break footing prints the actual check amount.
Special Cases d a c d b a c b Example: Using horizontal pages You can set the number of horizontal pages for each physical page. HP ALLBASE/BRW will then print a series of horizontal pages on one physical page. You set the number of horizontal pages from a eld on the De ne Report screen, labeled \Number of Horizontal Pages". For example, if you want to print address labels, and the address label paper is set up so that the labels are three abreast, you can set the number of horizontal pages to 3.
column. At the bottom of the third column, the paper ejects and the next set of labels starts at the top of the left-hand column of the next sheet. Note that HP ALLBASE/BRW considers each label to be one report - only the manner of printing changes. The default is one horizontal page, or one page per sheet. The number of horizontal pages multiplied by (*) the page width cannot exceed the maximum physical page width of 300 characters.
8 Sorts, Breaks, and Pagination Structuring the basic report Part II: Tasks Sorts, Breaks, and Pagination 8-1
At a Glance After you decide on the items you want in your report, you are ready to specify which items are to be sorted, and in what order. You can also decide to break the report into logical divisions. You might want the CUSTINV report broken up by sales area, for instance. You can also de ne when a new page should start.
Using the screen Using the Screen The De ne Breaks and Pagination screen is broken into two sides, the Sort & Break side and the Pagination side. d a c b The top part of the screen gives you some important information about the existing linesets, as described below for the example screen show above. The ( Detail Lines ) information shows that there is 1 detail line. The ( Page Break ) information shows that there are 3 page break heading lines, requiring an absolute (Abs) page position.
Sorts and Breaks Sorts and Breaks Data of a report is arranged by using up to nine nested sort items, each in ascending or descending sequence. Breaks occur each time a sort item value changes. For instance, if you sort on CUSTOMER-NAME, all the occurrences of a particular customer name are grouped together. When the next group of customer names is ready to print, that is a break.
Sorts and Breaks d a c b For each customer name, order numbers are sorted, and for each order number, sales amounts are sorted. Also, for CUSTOMERNAME and ORDER-NO, ascending sort order is speci ed, and for SALES-AMOUNT, descending sort order is speci ed. Customizing Breaks Part II: Tasks Every time the value of a sorted item changes, you can customize your report at the break that occurs.
Sorts and Breaks To indicate a change degree for a break 1. At the De ne Breaks and Pagination screen, type the instructions for the change degree in the Change Degree for Break eld. Use the table below as a guide. For . . . String items Numeric items Type in the Change Degree for Break eld: A number for the last character position where you want the break to begin if there is a change.
Pagination Pagination Pagination lets you control when di erent groups of lines (linesets) in your report are printed on the pages of the report. For instance, pagination controls these elements: How many lines per page? Do you want page numbering to start over when a break begins? At which line should a new page always start? If a lineset won't t on the remaining page, should the lineset be split, or printed completely on the next page? You de ne pagination on the De ne Breaks screen.
Pagination The default is 60 lines. The limits of the length depend on your printer and the size of the paper. For instance, a 8 1/2\x 11" paper can print up to 66 lines per page (assuming 6 lines per inch). The default of 60 lines gives a 1/2 inch (3 lines) margin at the top and bottom. You can set the page number to 1 everytime a speci c break level occurs.
Pagination Example: Splitting across page boundries In this example, the footing lineset is split across the page boundry if there isn't enough room for the lineset on the rest of the page. This is indicated by the S in the Paging eld for CUSTOMER-NO footing. d a c b When a lineset is split, the page footing and page heading linesets are printed between the split parts of the lineset.
Pagination The report based on this de nition looks like this: Customer No. 10044 Inv. No. Amount Paid Amount -------- ------ ----------- 4458A 700.00 700.00 5569B 350.00 350.00 5570B 554.00 554.00 66723 1079.00 1000.00 ------- ------- 2683.00 1683.00 Total For This Customer The heading for CUSTOMER-NO always begins on line 1 of the report. Since the space is not reserved, other pages that don't have a break heading can begin on line 1, if necessary.
What Next? After sorting your report to create breaks and after setting the pagination criteria, you can begin the detailed layout of your report. See Chapter 7 for information about de ning the report layout.
9 Defining Data Selection Security and exibility of data selection Part II: Tasks Defining Data Selection 9-1
At a Glance HP ALLBASE/BRW data selection lets you use these features for exible, but secure data selection: execution parameters default values for parameters frozen and required defaults multiple selection sets calculation language for complex data selection formulas data-value-dependent security provided by multiple selection sets and passwords There are two ways to select data: For security You can de ne data selection when you specify the report so that it becomes an integral part of the report.
(that is, details) from the nal access table are included in the report.
Selecting Data at Specification Time Selecting Data at Specification Time Note The simplest method of data selection is to de ne a complete selection set when you de ne the report speci cations. A complete selection set includes all the information about run-time parameters and printer setups. When the report is executed, no additional information is needed.
Selecting Data at Execution Time A selection condition speci es selection rules that determine which records from the nal access table are included in the report. Parameters [put a de nition here]. Selecting Data at Execution Time Note For exibility in the selection of data for a report, you can de ne the data set so that the person requesting the report can enter the values and operators for previously de ned parameters.
Selecting Data at Execution Time d a c b You can enter multiple parameters. If you run out of lines available, use Next Values to access more lines. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 5. Type the print speci cations (such as number of copies) in the appropriate elds. 6. Press Add Selection when you are satis ed with your de nitions. 7. Press Select'n Condit'n to go to the Selection Condition screen if you are de ning selection conditions.
Selection Conditions Parameters There are three times when you can de ne parameters: When you de ne layout calculated items When you de ne table calculated items When you create a selection set You access the Parameter screen from either the Layout Calc Item screen, the Table Calc Item screen, or the De ne Selection screen. To define parameters: 1. At the Table Calc Item or Layout Calc Item screen, press Parms/Funct'ns and type P (for Parameters) at the prompt.
Selection Conditions Selection Conditions After you have the parameters and the selection set de ned, you can set up some selection rules at the Selection Condition screen. Remember that you must de ne the parameters and enter the name in the Parameter eld in De ne Selection before you can use the parameter in either a selection condition or in a relation condition. To define Selection Conditions NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1.
Execution Defaults Execution Defaults For convenience when you execute a report, you can specify report execution defaults on the De ne Selection screen.
Execution Defaults The schedule and parameter values in the following selection set cannot be changed at run time because there is an asterisk for the parameter in the Default Values eld and for the time value in the Scheduled eld. d a c b Required parameters can be speci ed if a formula in the report speci cation requires a parameter value. When an R is in the right hand corner of the Default Values eld, the report requester is required to enter a value at report execution time.
Execution Defaults In the example below, the parameter month must have a value entered at execution time. d a c b The di erence between these two methods is that when you mark Value Required at the Parameters screen, that requirment a ects any and all reports executed, regardless of the selection set used. If you type the R on the De ne Selection screen, that parameter is only required when you run a report using that selection set.
Security Dependent on Data Value Security Dependent on Data Value For greater control of the data available to di erent groups of users or departments, HP ALLBASE/BRW provides data-value-dependent security. You can de ne one report and several selection sets, one for each user group, and secure each department's data with a password. This restricts the data that can be selected by a speci c group.
Security Dependent on Data Value Go to the De ne Selection screen and create Selection Set named ADMIN and one named PROD. (Do one rst, and copy it for the other one.) De ne a default value of ADMIN or PROD for the Parameter DEPARTMENT (depending, of course, on which department selection set you are creating). Type an asterisk (*) in the far right of the Default Value eld for DEPARTMENT. This means that the value is frozen and cannot be overridden when the report is requested.
Security Dependent on Data Value d For these selection sets, type this calculation for the de ned parameters at the Selection Condition screen: c a b When compiled with the ADMIN selection set, the report is restricted to records for the Administration department. When compiled with the PROD selection set, the report contains only information for the Production Department.
What Next? The nal tasks for your report are saving the report and compiling the report. Chapter 10 shows the steps necessary for saving and compiling. When the report is saved and compiled, you can run it (\request" it). Chapter 11 describes the steps needed to request your report. If you need help with multi-pass reporting, see Chapter 12.
10 Saving and Compiling a Report It isn't over until you save and compile the report Part II: Tasks Saving and Compiling a Report 10-1
Compiling a Report At a Glance This chapter contains Saving a Report When you nish specifying the report, you must save the report. If you exit HP ALLBASE/BRW, a prompt reminds you that you have made changes that need to be saved, and if you respond positively to the prompt, HP ALLBASE/BRW does the saving for you.
Compiling a Report HP ALLBASE/BRW creates an execution le that contains the resulting report. To compile a report with BRWCOMP Type RUN BRWCOMP.PUB.SYS Respond to these prompts: Specification File: Selection Set: Execution File: To include a password for either the speci cation le or for the execution le, use this syntax: lename/password.groupname What Next? After a report is saved and compiled, you can request to execute it. See Chapter 11 for step-by-step procedures on requesting reports.
11 Requesting Reports Other terms for requesting a report include running the report or executing the report Part II: Tasks Requesting Reports 11-1
Compiling a Report At a Glance When you are ready to print the report or write it to a disk, you request the report. When you request the report, you must have the Report Execution le (created when you compiled the report). You can request the report interactively, through HP ALLBASE/BRW screens, or in batch (in the background). You can use the standard job le, and amended standard job le, or a report job le.
Requesting Reports Interactively Requesting Reports Interactively Requesting a report interactively means that you start HP ALLBASE/BRW and request the report using the Request Report screen. If your installation does not include the developer version, you can still run reports interactively. (See the section \Requesting Reports Using BRWSTART.") To Request a Report Interactively 1. De ne and compile your report as described in the previous chapters of this manual. 2.
With BRWSTART 9. If there is a password for the user, account, or group, type it in the appropriate eld and press 4Enter5. The job number for the stream job appears at the bottom of the screen. For more information about each eld on this screen and how to use this screen, see the Request Report Screen section in Chapter 16. Note Requesting Reports Using BRWSTART HP ALLBASE/BRW is supplied with a special job le, called the standard job le, that is used to stream the job that runs the report.
With BRWSTART d a c b You can then use the Select Report, Online Review, and Request Report screens the same way described in the earlier section, \Requesting Reports from the Request Report screen." Note Part II: Tasks You cannot use BRWSTART to access those features that concern the speci cation or compilation of a report.
With BRWEXEC Requesting Reports with BRWEXEC Note With BRWEXEC, you can run a report successfully outside of HP ALLBASE/BRW, even if the HP 3000 you use does not have HP ALLBASE/BRW installed. The BRWEXE program is part of the MPE XL operating system and is all you need to run a compiled HP ALLBASE/BRW report. The version of the operating system on the HP 3000 on which the report is to be run must be the same as, or later than, the version on the HP 3000 on which the report was developed.
With BRWEXEC You must know which parameters are used in the report, and the type of each parameter (single-value, list-of-values, or comparison predicate). The type determines which operator is valid for that parameter. The report executes immediately. All other details, for example the output le name, number of copies, and destination, are taken from the execution le's Selection Set. For more information about selection de nitions (Parameters and Selection Sets), see Chapter 9.
Using Job Files Using Job Files There are three types of job les used with HP ALLBASE/BRW to start a background job (in batch mode) to execute the report: The standard job le (BRWJ000.PUB.SYS) Your own amended version of the standard job le (BRWJOB.yourgroup .youracct or BRWJOB.PUB.
Using Job Files Note that the standard job le calls BRWEXEC.PUB.SYS (line 23 of the job le). The BRWEXEC program actually executes the report. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 DEFINE JOB TEMPLATE COMMENT COMMENT This is the Standard Report Job for HP ALLBASE/BRW COMMENT reports. COMMENT $USER $USERPASS $GROUP $GROUPPASS $ACCT $ACCTPASS !JOB ^$REPORT,^$USER/^$USERPASS.^$ACCT/^$ACCTPASS,^$GROUP/^$GROUPPASS& !;INPRI=$INPRI !TELL ^$USER.
Using Job Files Amending the Standard Job File You can substitute an amended job le for the standard job le. That is, you can write a job le to your own speci cations (or edit a copy of the standard job le) and substitute it for the standard job le. You give it the name BRWJOB. Then, whenever you type an execution le name on the Report eld in the Request Report screen, HP ALLBASE/BRW will use your amended job le in place of the standard job le.
Using Job Files Using Report Job Files In addition to the standard job le (or an amended standard job le), you can create your own job les called report job les. You use the job les to start and schedule multiple reports (multi-pass reports) and application batch programs. To avoid starting each report and batch program of a multi-pass report separately, you can set up a report job le with parameters. You can schedule and start the multi-pass report like a single report.
Using Job Files HP ALLBASE/BRW standard parameters. These are parameters supplied with HP ALLBASE/BRW. For example, the user, group, and account from which the job will be streamed, the print le characteristics, report name, and so on. You de ne these in the report job le header, but outside the BEGINPARMS/ENDPARMS clause. Only if you de ne these parameters will you be able to assign a value for them on the Request Report screen.
Using Job Files 1 DEFINE JOB TEMPLATE 2 BEGINPARMS 3 sales-area (P, SU16) 4 sales-% (P, I) >=10 4 product-line (P, SU2) 5 comment sales-% is percent share of product sales per area 6 ENDPARMS 7 $PRINTFILE salesan; DEV=LP,12,1 8 comment end of report job file header 9 !JOB salesan, mgr.rdemo,pub 10 !TELL mgr.rdemo; *** Sales Analysis Report started *** 11 !CONTINUE 12 !RUN BRWEXEC.PUB.SYS 13 salesan1.rspec 14 sales-area ^sales-area 15 !EOD 16 !IF JCW >= FATAL THEN 17 ! TELL mgr.
Using Job Files Report Job File Header The report job le header is de ned with declaration commands. Except in the rst line in the le, you can enter each command with upper- and lower- case, leading and separating blanks. A report job le header always begins with: DEFINE JOB TEMPLATE This line is always left-justi ed in the rst line of the le. The header always ends with the command: !JOB The !JOB command is always the rst line of the report job le body.
Using Job Files Sytax for User-Defined Parameters Description Statement Element parmname The parameter name (&parmname ) must follow these rules: It can have up to 19 characters. It must begin with a letter, $, @ or #. It cannot contain ^ characters, blanks or parentheses. It must not be the name of a standard parameter.
Using Job Files Standard Parameters HP ALLBASE/BRW standard parameters are parameters supplied with HP ALLBASE/BRW that can be used in any report job le. They refer to values used to stream a job, and are associated with elds on the Request Report screen or to values assigned in response to a prompt at the Request Report screen. Standard parameters are de ned in the report le header, but outside the BEGINPARMS/ENDPARMS clause.
Using Job Files Example: Using combinations of parameters You can use a combination of some or all standard parameters in a report job le header. For example, to allow a report job le to log-on with a variable user name and password but with a xed account, specify: DEFINE JOB TEMPLATE BEGINPARMS . . . ENDPARMS $user $userpass !JOB ^$user/^$userpass.MYACCT,PUB !TELL ^user.MYACCT; *** report job file starts*** . . .
Using Job Files Default Job Schedule A default job schedule can be de ned for a report job le in this format: SCHEDULED schedule Where schedule can be like one of these examples: DATE = 08/31/86 DAY = MON DAY = 15 (that is, on the 15th day of the next month) DAY = -2 (that is, 1 day before month-end) IN = 2, 12 (that is, after 2 days, 12 hours) AT = 13:30 (that is, at 1:30 pm) DATE = 02/08/87; AT = 14:45 (that is, on 8th February 1987, at 2:45 pm) DAY = 15 ; AT = 10:15 (that is, on the 15th day of the ne
Using Job Files The $PRINTFILE parameter is de ned in the header as follows: 2 2 $PRINTFILE formal le ;DEV=device, 3outpri,copies 2 ;CCTL/NOCCTL ;ENV = environment le formal le device outpri copies CCTL/NOCCTL environment le 33 the print le name speci ed in the report (on the De ne Selection screen); the optional, default print device, and if speci ed, then also: an optional, default output priority, used with DEV an optional, default number of copies, used with DEV either CCTL, to include carriage-c
Using Job Files Example The following is an example of de ning the $PRINTFILE parameter in the header of the report job le and using it in the body of the report job le. DEFINE JOB TEMPLATE BEGINPARMS . . . ENDPARMS $PRINTFILE expenses; DEV=,,3;CCTL;ENV = ENV1.ENV.SYS $INPRI 5 !JOB expenses,MGR.MYACCT;inpri=^$INPRI . . . ^$PRINTFILE !CONTINUE !RUN BRWEXEC.PUB.SYS expense2 . . . The above example sets the Job Input Priority to 5 using the standard $INPRI parameter.
Report Job File Body Report Job File Body Logon Command The report job le body is an MPE job containing parameters for which actual parameter values are inserted. Each parameter name in the body must be preceded by a ^ character and must have been de ned in the report job le Header. The report job le body must begin with the logon command for the job, !JOB. The logon command can be de ned for a xed user, account and group, for example: !JOB Expenses,MGR.
Report Job File Body ^sales-area[>=4] In above example parameter values shorter than 4 characters are padded with blanks. A replacement option only e ects the replacement where it is speci ed. To supply parameters for the single reports executed in the report job le, you must specify the parameter name de ned in the report followed by a ^ character and the parameter name de ned in the report job le header, as shown here: !RUN BRWEXEC.PUB.SYS salesan2.
Report Job File Body $Parms This command is used when you need all parameters in a report, and the parameter names in the report and in the report job le are the same. It causes all parameter names and their values to be inserted at this place, for example: DEFINE JOB TEMPLATE BEGINPARMS sales-area (P, SU16) sales-% (P, I) >= 10 product-line (P, SU2) ENDPARMS !JOB salesan, mgr.rsdemo,pub . . . !RUN BRWEXEC.PUB.SYS salesan2.rspec ^$PARMS !EOD . . .
What Next? Now you should have a copy of your report. If it isn't what you need or what you expected, you can return to any of the tasks chapters and redo steps to perfect the report. Remember to save the changes you make before you request the updated report. For help with speci c screens or with calculations, see the Reference section, Part III. If you need help with multi-pass reporting, see Chapter 12.
12 Multi-Pass Reporting and Output Files Writing results into a variety of output les Part II: Tasks Multi-Pass Reporting and Output Files 12-1
At a Glance At a Glance You can write results from one report into a variety of output les. You can use output les as source tables when creating other reports or as data for use with other application programs (not necessarily HP ALLBASE/BRW). Using one or more output les from one or more reports in another report is called multi-pass reporting.
Multi-pass Reporting Multi-pass Reporting Multi-pass reporting means that you create multiple reports, each one used as a source le for the next report. In some cases, this process improves performance because the database is not re-read for every interim report.
Output Files Output Files An output le does not contain the entire report (including headings, details, and footings). Instead, an output le contains only item values, not printer formatting information. One output le can be de ned for each lineset: Detail lineset Break footing lineset Break heading lineset Page heading lineset Page footing lineset Report heading lineset Report footing lineset Every time the lineset occurs in the report, a record is written to the output le.
Output Files Defining an Intermediate Report File You use the Output File screen to de ne an intermediate report le and to specify which items are to be written to it. d a c b To define an intermediate report file 1. From the De ne Report screen, choose Output File . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. At the prompt, type the lineset information for the lineset you want to use. Press 4Enter5. The Output File screen appears.
Output Files To define items for an output file 1. Type the name of each item that you want written to the output le in the Name in File eld or type an X (or a number for QB and DIF les) in the Select/Sequence eld. If the output le is an intermediate report le, you can choose any name for an item's Name in File.
Output Files Using Intermediate Report Files You can create an intermediate report le if you want to use data from one HP ALLBASE/BRW report with data from other HP ALLBASE/BRW reports. An intermediate report le is a self-describing le since it contains a report's format de nition (name, type, length, and position) in the user label. You do not de ne it in the data dictionary or BRWDIC. Intermediate report les can only be used as source tables for HP ALLBASE/BRW reports or with HP Information Access.
Output Files If any other type of le exists with the same name as that speci ed on an Output File screen, and you compile the report, the compile will fail and the le will not be purged. When you delete an intermediate report le with the PURGE command, you can only create it again by re-compiling the report. So, to free disc space when data in an intermediate report le is no longer needed, you can run the program REMPTY to clear the le, as shown here: :RUN REMPTY.PUB.SYS . . .
Dictionary Output Files Dictionary Output Files Dictionary output les can be de ned if you want to use the data from a report with other programs. Data in a dictionary output le can be used from either HP ALLBASE/BRW reports or other application programs. The format of the le must be de ned in the data dictionary before it is speci ed in the report.
Dictionary Output Files String items can have di erent lengths, in which case values are truncated on the right or padded with blanks. For example, these items are speci ed on the Output File screen below. As with intermediate report les, you use the Output File screen to de ne a dictionary output le and to specify which items are to be written to it. d a c b Items are only written to the dictionary output le if you enter their names in the Name in File eld, or put an X in the Select Sequence eld.
Quoted BASIC and Data Interchange Format Quoted BASIC (QB) and Data Interchange Format (DIF) Files Creating QB and DIF Files These les are in formats used by PC applications. You can therefore use HP ALLBASE/BRW to process data and create output les which can then be accessed by PC applications. QB and DIF les cannot be used as HP ALLBASE/BRW source tables. QB and DIF les are not described in a data dictionary. QB and DIF les are created when the report is executed, not when it is compiled.
Quoted BASIC and Data Interchange Format Quoted BASIC Format Quoted BASIC les are used as data transfer les by some standard programs, including Lotus 1-2-3, RBASE 5000, DBASE, and BASIC programs. DSG/3000 can also read Quoted BASIC les as free-formatted data les. In BASIC, quotes are used to delimit strings. File Layout The items in the le are separated by commas; strings are enclosed in quotes; and numeric values can have decimals. Each record in a QB le has the same layout.
Quoted BASIC and Data Interchange Format DIF File Layout Example TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,2 "" DATA 0,0 "" -1,0 BOT 1,0 " January" 1,0 "01/23/1987" 1,0 "12:30:10" 0,235.78 V 0,75 V 0,-.3E+2 V -1,0 BOT 1,0 " February" 1,0 "02/15/1987" 1,0 "12:10:01" 0,478.25 V 0,874 V 0,.
Using Output Files Using Output Files Database Extract Files There are several ways that you can use the di erent types of output les described in this chapter. The following sections describe some suggestions and examples. A database extract le contains a subset of data from a database.
Using Output Files The following diagram shows how database extract les work: Part II: Tasks Multi-Pass Reporting and Output Files 12-15
Bundling Reports Bundling Reports In addition to tuning single reports for best run-time performance, you can improve performance of a whole set of reports by using database extract les. For example, the Production Forecast report, PRODFCST, and the Customer Statement report, CUSTORD, both access datasets PRODUCTS, ORDERS and ORDER-DETAILS. The report CUSTORD also accesses the dataset CUSTOMERS.
Bundling Reports The source tables for the PRODFCST report are de ned as shown here on the De ne Table screen: d a c b On the Output File screen an output le is created for PRODFCST, as shown here: Part II: Tasks d a c b Multi-Pass Reporting and Output Files 12-17
Bundling Reports The following section of the De ne Table screen shows how the output le for PRODFCST is joined to the source table for CUSTORD: Note d a c b You tell HP ALLBASE/BRW that the source table is an intermediate report le by entering an character before the le name/location in the Location eld. Whenever you specify an intermediate report le on the De ne Tables screen, enter an arbitrary name in the Source Table eld, and an > character, followed by the le name/location, in the Location eld.
Integrating Reports with Batch Processing Integrating Reports with Batch Processing HP ALLBASE/BRW reports can be integrated with other (non-HP BRW/XL) batch programs. For example: Batch programs can produce les that are used by HP ALLBASE/BRW reports. HP ALLBASE/BRW reports can produce dictionary output les that are used by batch programs. Example In the following example, a job generates invoices for all orders that have been shipped and then prints the invoices to be sent to the customers.
What Next? The nal tasks for your report are saving the report and compiling the report. Chapter 10 shows the steps necessary for saving and compiling. When the report is saved and compiled, you can run it (\request" it). Chapter 11 describes the steps needed to request your report.
13 HP BRW-Desk/XL Sending HP ALLBASE/BRW reports through HP Desk Part II: Tasks HP BRW-Desk/XL 13-1
At a Glance At a Glance Note HP BRW-Desk/XL is a product you purchase separately from HP BRW/XL. You use HP BRW-Desk/XL to send reports through HP DeskManager or through HP DeskManagerPlus. In this manual, HPDesk is a short term for either HP DeskManager or HP DeskManagerPlus. HP This is the original HP Desk product. It has simple DeskManager messaging capability for small to medium electronic mail users.
Requirements This chapter covers: Requirements Note Part II: Tasks requirements identify senders outline of steps de ne HPDesk delivery for the report request the report security To send reports via HP Desk, you need the following: HP BRW-Desk/XL on the developer system HP DeskManager, version B.03.
Identify Senders Identify Senders Use the program BRWSETUP.PUB.SYS to identify the sender(s) on the Mail Values screen of the appropriate BRWCONF con guration le. That is, set one or more names/addresses under which HP BRW-Desk/XL can log on to HPDesk and send report(s). You only need to identify the sender once for all your reports. Thereafter, HP ALLBASE/BRW looks to the BRWCONF le to nd an available sender.
How It Works How It Works The steps performed by HP ALLBASE/BRW at report execution (when HP BRW-Desk/XL has been selected to deliver a report or output le to users on the HPDesk network) are listed and explained in detail below. HP ALLBASE/BRW signs on to HPDesk using the sender name and password de ned in the appropriate HP ALLBASE/BRW BRWCONF con guration le. A number of retries will be made if this sender is already signed on to HP Desk. The HP ALLBASE/BRW execution program (BRWEXEC.PUB.
Define HPDesk Delivery for the Report Define HPDesk Delivery for the Report To define HPDesk delivery for the report 1. On the De ne Report screen, choose Define Delivery . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN HP ALLBASE/BRW displays the De ne HPDesk Delivery screen for the entire report. 2. Type an optional description of the report in the Subject eld. This description will be the subject of the HP Desk message. As an alternative, you can type an item name in the contained in Item eld.
Define HPDesk Delivery for the Report 3. Go on to the next eld, or if you are nished with the delivery de nition, press 4Enter5. If you select to switch the destination of a report on a break level, any higher break levels will not be delivered. To set the delivery to URGENT and/or PRIVATE 1. Type X in the Urgent or Private elds. To require an acknowledgment of the report (optional) 1.
Define HPDesk Delivery for the Report The report will be mailed at 10:00 p.m. 2. If you are nished with the delivery de nition, press 4Enter5. To send an output file 1. Create an output le for the break level you want to send. For example, the report footing. (Use the task instructions in Chapter 12.) 2. At the Output File screen for the break, choose other keys until the Define Delivery softkey appears. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3.
Request the Report You request the report just as you would request any other report, as described in Chapter 11. Request the Report Delivery Audit Report When HPDesk delivery is used, the HP ALLBASE/BRW execution program (BRWEXEC.PUB.SYS) generates delivery audit trail messages in the execution statistics that print to your screen.
Request the Report In addition to the execution statistics generated by HP ALLBASE/BRW, usual HPDesk reporting of a successful signon is done to the console and to the HPDesk Statistics le. Examples This section gives the details of some example reports. Example: Switching destinations Suppose you have a report that lists the turnover for each sales area. The customer records are sorted on SALES AREA as the rst-level break, and the total for each area is calculated on the rst break footing.
Request the Report Page: ----- 1 - Customer Details Report: 1 --------------------------- 07/11/89 -------- Customer Details for Sales Area: EASTERN ----------------------------------------Customer Number --------------- Customer Name ------------- 100213 100432 100498 BLACK & WHITE SUR C. WINSTON F & S Total Sales for Sales Area: --------------------------- Page: ----- 1 - EASTERN Month's Sales ------------2451.91 9100.25 3654.
Request the Report Page: ----- 1 - Customer Details Report: 1 --------------------------- 07/11/89 -------- Customer Details for Sales Area: SOUTHERN -----------------------------------------Customer Number --------------- Customer Name ------------- 100365 100888 100946 LEMWAY INC. REGULAR OIL INDUSTRIES BROWN BROTHERS Total Sales for Sales Area: --------------------------- Page: ----- 1 - SOUTHERN Month's Sales ------------236.54 8925.00 26455.
Request the Report Specify the MIS department on the De ne HPDesk Delivery screen for the output le. Type Area Turnover Total (Output File) in the Subject eld. Type TO in the FROM, TO, CC, or BCC eld. Type the address of the MIS department in the Name/Address eld. Press 4Enter5. d a c b Note that this will send the output le, not a report, to the MIS department. The output le cannot be read in the In Tray, however, HPDesk will automatically deliver the output le.
Security To prevent someone from sending messages without authority, it is recommended that you require users to specify a password before signing on to HP Desk. Authorized sender names and passwords can be entered in the BRWCONF le as described earlier in this chapter. Passwords are encrypted in the BRWCONF le to ensure that security is not compromised.
Error Handling Address Errors HPDesk will accept all messages, even if they contain bad or incomplete names/addresses (mailnodes). HP BRW-DESK/XL's action are shown in the following table.
14 The HP ALLBASE/SQL Interface Retrieving and combining HP ALLBASE/SQL data Part II: Tasks The HP ALLBASE/SQL Interface 14-1
At a Glance At a Glance HP ALLBASE/BRW let you access all non-relational data (IMAGE datasets, KSAM les and MPE les) as if the data were stored in a relational database. HP ALLBASE/SQL uses relational data that also can be accessed by HP ALLBASE/BRW. It is possible to retrieve and combine data from IMAGE, HP ALLBASE/SQL, KSAM and MPE within the same report. It is also possible to retrieve and combine data from multiple SQL DBEnvironments.
Using HP ALLBASE/SQL Using HP ALLBASE/SQL To create, compile, and execute an HP ALLBASE/SQL report 1. Specify the report in the same way that you specify any other report, except that you give the Source Table name using the SQL pathname (including the owner name). 2. On the De ne Report screen you can enter an SQL Transaction Isolation Level. This entry is optional. See the \Transaction Handling and Locking" section later in this chapter. 3.
Using HP ALLBASE/SQL Notice that the HP ALLBASE/SQL table name is preceded by the owner name (SUPPLYDB). Note also that no password has to be speci ed; refer to the \Access Authorization" section later in this chapter. You de ne the relation between the tables the same way that you de ne other relations. The information on this De ne Relation screen tells HP ALLBASE/BRW the item name (VENDORNUMBER) to use to join the tables shown on the De ne Table screen.
Using HP ALLBASE/SQL If you want to be able to decide on a speci c vendor at run time, use a parameter for the VENDORNAME. You de ne a parameter as shown in the example De ne Parameter screen shown next. d a c b In this example, ?VENDORPARM is de ned as a single value parameter. In addition to de ning the parameter, you need to de ne a selection condition to equate the VENDORNAME to the ?VENDORPARM parameter. d a c b For more information on using BRW parameters, refer to Chapter 6.
Using HP ALLBASE/SQL WHERE (T1.VENDORNUMBER=T2.VENDORNUMBER) AND (T1.VENDORNAME=?VENDORPARM) Notice that the query above consists of one SELECT statement. This is because the same access sequence number was speci ed for both tables on the Tune Access screen. This is the default if the HP ALLBASE/SQL tables are from the same DBEnvironment and are listed con secutively on the De ne Table screen.
HP ALLBASE/SQL Characteristics HP ALLBASE/SQL Characteristics System Catalog A data dictionary does not have to be set up in HP ALLBASE/BRW for HP ALLBASE/SQL tables. Therefore, there is no HP ALLBASE/BRW dictionary (BRWDIC le) to maintain. HP ALLBASE/BRW takes advantage of HP ALLBASE/SQL's System Catalog by retrieving information on data de nitions directly from the System Catalog. This provides an active link to data de nitions.
HP ALLBASE/SQL Characteristics the CONNECT command instead of the STARTDBE command. The DBEnvironment parameter \User Mode" should be set to multi to allow multiple access to the DBEnvironment. Transaction Handling and Locking There are two ways of specifying locking within SQL: table lock mode and transaction isolation level. Locking controls concurrency of di erent applications running in the same DBEnvironment. In HP ALLBASE/BRW, table lock mode is speci ed on the Tune Access screen.
HP ALLBASE/SQL Characteristics Guarantees consistency for single data but not for the query as a whole. Possibility of deadlocks. RU No locks while reading the data. Allows most concurrency, but there is a likelihood for reading uncommitted data. Is recommended if there is no need for an exact report. No deadlocks with concurrent data manipulations. The locking strategy should be chosen in accordance with the DBA depending on the DBEnvironment and the applications running on it.
HP ALLBASE/SQL and HP ALLBASE/BRW HP ALLBASE/SQL and HP ALLBASE/BRW Modifying the DBEnvironment The BRW programs BRWCOMP and BRWEXEC work on the current data de nitions in the SQL System Catalog. This is also true when a new report is speci ed with BRWXL. If the speci cations of an existing report are modi ed by the user, the speci cations already done are not automatically checked against the current System Catalog entries.
HP ALLBASE/SQL and HP ALLBASE/BRW HP ALLBASE/SQL Names NULL value NO VALUE CHAR (1 to 3996) String VARCHAR (1 to 3996) String DECIMAL NUMERIC FLOAT INTEGER SMALLINT DATE TIME DATETIME INTERVAL NULL Part II: Tasks HP ALLBASE/BRW Names in double quotation marks are not supported Names containing native language characters are not supported. A NULL value is sorted as the highest value. Two NULL items are compared as unequal. NO VALUE is sorted as the highest value.
Joining Joining HP ALLBASE/BRW sets NULL values retrieved from HP ALLBASE/SQL to BRW's exception condition, NO VALUE. Both HP ALLBASE/SQL and HP ALLBASE/BRW sort NULLs/NO VALUEs highest. HP ALLBASE/SQL does not retrieve rows joined on two NULL item values. HP ALLBASE/BRW joins NO VALUE = NO VALUE, HP SQL evaluates NULL = NULL as false.
Native Language Support HP ALLBASE/SQL supports native languages. This includes sorting and comparison of data stored as 8-bit or 16-bit native language characters in a DBEnvironment. HP ALLBASE/SQL allows the data language of an SQL database - speci ed with the START DBE NEW command - to be overwritten for speci c tables and columns. This means HP ALLBASE/SQL permits a combination of one local language and the default language NATIVE-3000 for storing data within one DBEnvironment.
15 Calculations De ne calculations for numbers, strings, dates and time Part III: Reference Calculations 15-1
If you need to review some calculation terms, the following table may be of help. At a Glance Some Calculation Terms Arguments Arguments are the values used in a calculation. Arguments include: items, standard items, and array items functions and standard functions function arguments constants parameters column calculation results Calculated Items These are items created from a formula that you de ne.
Some Calculation Terms (continued) Conditions A condition is a calculation that produces a Boolean result, that is, a result that can be either TRUE or FALSE. For example: (CUSTOMER-NO = 000004) or ORDER-NO < "012" There are four types of condition in HP ALLBASE/BRW: Relation conditions. You de ne relation conditions from the De ne Table screen. They determine whether or not to write a record to a table. Suppress line conditions. These are used to determine whether or not to print a line.
Some Calculation Terms (continued) Functions A function is like a program procedure or subroutine. You can use a function to avoid typing complicated formulas. Functions can be used in formulas or conditions; however, functions cannot be used as items. Each function must have at least one argument, and can have up to nine. A function argument is a value that can be passed to the function when it is run. There are three categories of functions: Table functions.
Aggregate/Array Operations Aggregate/Array Operations HP ALLBASE/BRW includes many aggregate/array operations that act on arrays. One example is an operation that sums all the values of an array. Aggregate/array operations are calculations very similar to column calculations except that they a ect array items.
Arguments Priority of Arguments Arguments are the values used in a calculation. Arguments include the following: items, standard items, and array items functions and standard functions function arguments constants parameters column calculation results expressions Items, array items, standard items, user-de ned table or layout functions, user-de ned global functions, function arguments and standard functions can have the same names.
Arguments Standard items and standard functions can be explicitly speci ed as shown in this example: STANDARD.DATE which is the standard item date .
Arrays and Array Items An array is a combination of one or more items of the same type. Each item in an array (also called a component of an array) is treated exactly like a normal item. Arrays are de ned in a data dictionary; you can manipulate but not create arrays in HP ALLBASE/BRW.
Arrays and Array Items Arrays are usually referenced by component; only on the Project Items and Output File screens do you refer to an entire array. HP ALLBASE/BRW makes no distinction between normal items and array items. (To HP ALLBASE/BRW, a normal item is an array with one component.
Calculation Language Related Sections 15-10 Calculations The Calculation Language uses arguments, expressions, and operators. Arguments and expressions are used in calculations: items, standard items, arrays, functions, constants, and column calculation results. Operators can perform mathematical or logical operations on arguments.
Column Calculation Results Column Calculation Results Column calculations do not use the Calculation Language, and are described in Chapter 8, \De ning Report Layout." However, column calculation results can be used as arguments within layout calculations or layout functions. This is necessary, for example, to calculate the percentage of variance between two column total values, as shown in the following example.
Column Calculation Results VARIANCE-% is calculated each time the value of the item ACCOUNT changes. Because CALC is speci ed in the ColCalc eld for this item, the total values of arguments ACTUAL and BUDGET are used to nd the percentage of variance of these totals. If you had not speci ed CALC, arguments ACTUAL and BUDGET would have the value of whatever was on the last detail line when the lineset was printed.
Conditions Conditions A condition is any form of calculation that produces a Boolean result. That is, a result that can be either true or false. Conditions include: selection conditions relation conditions suppress line conditions suppress lineset conditions The screen for each condition is described in Chapter 16 in alphabetical order. The screens are also pictured in sections of the manual describing tasks that are done using that screen.
Conditions Characteristics Selection Conditions have the following characteristics: They select a subset of data from the nal access table. They are calculated from items, parameters or constants. They can use table calculated items, since a table calculated item is used like any other item after it is projected from its table. Example Suppose you want to produce a report that would include only products from Product Line 60 that have been ordered during December.
Conditions Suppress Line Conditions Suppress line conditions are used to de ne criteria under which the printing of a line is suppressed. It is especially useful when there is only one condition you want to exclude from the report. Suppressing the line for that condition is simpler than selecting lines based on multiple conditions. Suppressed lines are de ned on the Suppress Line screen. See Chapter 16 for the screen description.
Conditions any records to be written to the lineset's output le column calculations to be calculated, if the lineset is a detail or a break footing Example Suppose you are creating a Customer Order report and you do not want any details printed when a customer has no orders. You can use this Suppress Lineset condition: ORDER-NO = NO_VALUE With this condition, no details are printed for a customer who has no orders. When the condition is true, the lineset is suppressed.
Conditions/Boolean Operations Conditions/Boolean Operations Comparison Operators The HP ALLBASE/BRW Calculation Language can be used to express conditions in IF THEN ELSE operations or as formulas in selection, relation or suppress line/lineset conditions. Conditions use comparisons and Boolean operators, and the results are always true or false.
Conditions/Boolean Operations String Comparisons String comparisons have these characteristics: When strings of di erent lengths are compared, the shorter one is padded with trailing blanks. Patterns Patterns have these characteristics: They are constants with optional mask characters. They are always enclosed in double quote marks.
Constants as Arguments Constants as Arguments Constants are arguments that have xed values in calculations. Constants can be of any HP ALLBASE/BRW type (Real, Numeric, Integer, String, Date, or Time). Consider the following examples: Numeric constants such as 5.68, -3.14; Integer constants such as 0, 45, -2134; Real constants such as 3.45E2. But see the explanation of Real syntax under Real numbers in the \Numbers" section in this chapter.
Four built-in standard conversion functions are available for HP ALLBASE/BRW's calculation language. They are described in the following table. Conversion Functions Standard Conversion Functions TO INTEGER(expr ) TO REAL(expr ) TO NUMERIC(expr ) The TO INTEGER function converts the argument, expr to an integer. This function is valid for real, numeric, and string values of expr.
Dates You can manipulate dates using HP ALLBASE/BRW Date types. Items of type Date are de ned in the data dictionary. (See Chapter 18 for the data dictionary de nitions of Date types.) Internally, a date always consists of a month, a day, and a 4-digit year (1992, not 92). However, Date items can be output in di erent ways, depending on the edit-mask (for output) and the data dictionary de nition (for input). HP ALLBASE/BRW converts all dates to the form YYYYMMDD and handles them internally in this form.
Dates Date Duration Functions Date duration functions calculate the di erence between two date values as positive numbers. Date duration is speci ed as shown here: DAYS_DIFF WEEKS_DIFF MONTHS_DIFF YEARS_DIFF (date1, (date1, (date1, (date1, date2) date2) date2) date2) The result of a date duration function is an integer-type item. The date duration functions calculate: WEEKS_DIFF MONTHS_DIFF YEARS_DIFF as as as DAYS_DIFF / 7 DAYS_DIFF / 30.4385 DAYS_DIFF / 365.
Exception Handling Exception Handling Exception values indicate whether the value of an item, function, parameter or any expression is invalid. HP ALLBASE/BRW distinguishes between NO VALUEs and ERROR VALUEs: NO VALUE signi es that an item or a result has no value. Note that NO VALUE means \no value." It does not, for example, mean that a numeric item has a value of 0. NO VALUEs can occur, for example, when you specify an open join on a source table. NO VALUE is set for an SQL item having a NULL value.
Exception Handling Automatic Exception Value Handling Automatic exception value handling means that a calculation produces an exception value if one of the arguments has an exception value. For example, if one of the arguments in an addition has no value, the result of the addition will also have no value. HP ALLBASE/BRW uses an exception algorithm for calculations. If one of the arguments has an exception value, then an exception value results. Note that this does not apply to operators =, <>, AND, OR.
Exception Handling Testing For and Assigning Exception Values You can test for exception values or assign the result of a calculation to an exception value. Example Suppose you might want to have the report automatically insert the current month as the value for the parameter ?MONTH if the parameter has no value.
Expressions Expressions are calculations inside parentheses. The Calculation Language lets you use expressions anywhere a single argument can be speci ed. Example In the calculation below, two arguments are used, the item PRICE and the constant 10. The result will be the value of PRICE multiplied by 10: PRICE * 10 You could substitute an expression for the constant, as follows: PRICE * (1 + 9) This, of course, produces the same result.
Formulas Formulas Formulas include: table calculated items layout calculated items table functions layout functions Calculated Items Calculated items are items that are created from a formula that you de ne. Once created, calculated items can be used in a report just like an item projected from a nal access table, or an HP ALLBASE/BRW standard item.
Formulas They cannot be used on the De ne Relation screen for the same table in which they are de ned. They cannot use column calculation results (or certain standard items, such as PAGE) as arguments. See \Standard Items" later in this chapter for a list of available standard items. Example Here is an example of a table calculated item, BALANCE-DUE: d a c b This is the Table Calc Item screen for BALANCE-DUE. It is a numeric item.
Formulas its syntax rules. These are described in the \Arguments and Syntax" section of this chapter. When the table is constructed, every row of the table will have an item called BALANCE-DUE that you can use in the report. This item will behave exactly like any other Numeric item that you project from the table. Each value of BALANCE-DUE will be equal to the value of AMOUNT, minus the value of PAID-AMOUNT, on that row of the table.
Formulas Note For better performance: Table calculated items are calculated as early as possible in the data access for a report. This means that they are only calculated when their arguments change. Table calculated items generally can improve performance if they replace the items from which they are calculated, so reducing the width of the table. Layout calculated items are calculated, by default, once for each detail.
Functions Functions A function is like a program procedure or subroutine. You can use a function to avoid typing complicated formulas. Functions can be used in formulas or conditions; however, functions cannot be used as items. Each function must have at least one argument, and can have up to nine. An argument is a value that can be passed to the function when it is run. Function arguments can (optionally) be used in the function formula.
Functions Example Here is an example of a table function, AMOUNT-IN-WORDS: d a c b This is the Table Function screen for item AMOUNT-IN-WORDS. It has one Numeric argument, AMOUNT, and returns a String-type result. It has been de ned from the table FINAL. Therefore it can only be used in table calculated items, relation conditions, and other table functions for table FINAL. d a c b In this example, the formula for AMOUNT-IN-WORDS is de ned on the Table Function Formula screen.
Functions AMOUNT, and HP ALLBASE/BRW returns the string depending on the value of AMOUNT. Layout Functions Layout functions are similar to table functions but are de ned for the report layout. The Layout Function screen and the Layout Function Formula screen are used to de ne layout functions.
Functions Functions as Arguments You can use a function as an argument in a calculation. Such functions can be user-de ned table functions, layout functions, global functions, or HP ALLBASE/BRW standard functions. User-de ned functions are described in the \Formulas and Conditions" section of this chapter, and Standard Functions are described in the \Standard Functions" section of this chapter.
Functions as Real or Integer is automatically coverted to Numeric. (See \Type Conversion" under the \Types" section of this chapter.) The function argument AMOUNT can be used in the formula for the function where it is de ned.
IF THEN ELSE Operations You can use the construction IF THEN ELSE in a calculation formula to allow di erent calculations to be used, depending on whether a condition is true or false. Example Suppose in your report you want to show the discounted amount for an invoice. The discount for amounts under $1000 is 3 percent, the discount for amounts over $1000 but under $10,000 is 5 percent, and the discount for all other amounts is 10 percent.
Items Items provide a single value each time a calculation is performed. Most items, of course, are projected from the nal access table. You can also de ne your own items, for example, a calculated item is an item that you de ne. You can also use any HP ALLBASE/BRW standard items. Items Characteristics All items have these characteristics: Items have a single value per record. That is, on every record, or detail line, an item can have only one value.
List Operations List operations select from a list of values.
Literals and System Values Literals and System Values Some data dictionaries, in particular the HP Application Dictionary support the de nition of pre-de ned constant values refered to as System values or Literals. In either case, a symbolic name is associated with a constant value stored in the data dictionary. Literals are always string constants, and System Values can be either a string constant or an integer. A Literal has a maximum length of 16, and a System Value can be up to 255 characters long.
Naming Conventions Related Sections 15-40 Calculations Formula and condition names must follow HP ALLBASE/BRW item name rules. That is: They must start with a letter, $, # or @. The rst letter can be followed by up to 19 characters. The name cannot contain points, parentheses, spaces or commas (letters, digits, and dashes are allowed). Items of the same type must have unique names.
Numbers There are three number types in HP ALLBASE/BRW: Integer Fixed-point Numeric Floating-point Real Numbers For each type you can: use all the arithmetic operators use the standard function DIGIT OF Additionally, you can use functions that only apply to Real numbers (square root, etc). Numeric and Integer types are covered rst, and then Real numbers. It is more e cient to use Numeric types than Real types. The range of each type of number is shown below.
Numbers Arithmetic Operators Arithmetic operators include: Operator Description Example + * / ** MOD Addition Subtraction Multiplication Division Exponentiation Modulo 5+4=9 5-4=1 5 * 4 = 20 5.0 / 4 = 1.25 5 ** 4 = 625 5 MOD 4 = 1 The following are some notes on arithmetic in HP ALLBASE/BRW: Integer Division . If both arguments are integers HP ALLBASE/BRW uses Integer division and the result is an Integer. Using Integer division, the result of the division (5 / 4) would be 1.
Numbers 13 MOD 2 Precision of Numbers = 13 - 2 * INT ((3 / 2 )) = 13 - 2 * 6 = 1 Calculation precision in a calculation can be controlled by any of these options: selecting the appropriate item type (Numeric, Real, or Integer) optionally changing the precision of the calculation result changing the precision of arguments used in the calculation formula You can change the calculation precision when: there is a result of a table calculated item, de ned on the Table Calc Item screen an item is projected fr
Numbers If you round a number, it is rounded up if the rst digit outside your precision is a 5 or greater, and rounded down if the rst digit outside your precision is less than 5. (You can choose, on the BRWSETUP program, whether you want 5 to round up or down.) If you ceil a number, it will always be rounded up. See the \Real Numbers" description in the \Numbers" section of this chapter for information on real numbers and precision.
Numbers Digit Extraction Function Had you speci ed a precision of: the result would be: -5C -4T -3T -1T -1R 0C 1R 1T 2T 2R 3R 3C 6C 29.18650 29.1865 29.186 29.1 29.2 30 30 20 0 0 0 1000 1000000 This function is used to extract single digits from a number. The function uses a digit index to determine which digit to extract. The index looks like this: . . . 9 8 6 5 4 3 2 1 0 -1 -2 -3 -4 . . . The blank between 0 and -1 corresponds to the decimal point.
Numbers Real Numbers This section describes features, functions, and operations that apply to Real numbers only. You can use Real numbers the same way as other number types. For example, you can de ne le or database items, HP ALLBASE/BRW parameters, or calculated items of type R (Real). When to Use Real Numbers Unless a Real number is required, it is more e cient to use a Numeric number. Take care when using Real numbers.
Numbers There must be no embedded spaces in the value. Signs can optionally occur at the beginning of the value and/or following the exponent (E). The exponent must not have more than 2 digits. How to Write Real Numbers in HP ALLBASE/BRW Parameters You must use a set syntax for parameter values so that the SATISFIES selection condition will work with comparison predicate parameters. Default and run-time parameter values obey the following syntax rules: They can have an optional sign.
Numbers Real Numeric Precision You can set the Numeric precision for Real items in the same way as you can for Numeric and Integer items. Set Numeric precision for Real items by entering a number from -90 to 76 and a letter T, R, or C. The number speci es the degree of precision and the letter speci es whether to truncate, round, or ceil the value. The degree of precision is based on how the Real number looks in Fixed Point form. For example: Real Value Fixed Point NumPrec New Real Value .12347E2 12.
Operators/ Operations Operators/ Operations The term operator is used in the normal mathematical sense, that is, an operator is a mathematical or logical symbol that speci es an operation to be performed on an argument or arguments. For instance, in the example below: PRICE * 10 The asterisk (*) is an operator that tells HP ALLBASE/BRW to multiply the PRICE by 10.
Parameters Parameters are arguments which get their values when the report is run (for example, you can supply values for parameters on the Request Report screen). Characteristics Parameters have these characteristics: They can be of any HP ALLBASE/BRW type: Real, Integer, Numeric, String, Date, or Time. You supply a value or values of that type when you run the report. See the \Request Report screen" section of Chapter 16 for details about assigning parameter values when the report is run.
Parameters ((PRICE * QUANTITY) * 10) / 100) for a 10 percent sales tax. However, if you de ne a parameter TAX-RATE, and change the formula to: ((PRICE * QUANTITY) * ?TAX-RATE) / 100 You could run the report many times, supplying a di erent value for TAX-RATE each time. Parameters can be used in HP ALLBASE/BRW to do the following: Perform calculations. Control report format, for example, using suppress line conditions. Control reported data, using a parameter to restrict data written to a table.
Parameters List-of-Values Parameters List-of-values parameters can be substituted by a list of one or more items of the same type. Characteristics List-of-values parameters have these characteristics: The values you substitute for the parameter must be of the same type. You cannot have both Real and String values in the same list. A list of values can have any number of values. You cannot use values in a list-of-values parameter as items.
Parameters values less than or equal to a speci c value values greater than a speci c value values greater than or equal to a speci c value all values (when parameter value is missing) They allow the use of the following masks in parameter values: @ zero or more occurrences of any character ? one occurrence of any character The following table shows how comparison predicate parameters work with operators.
Parameters Operator SATISFIES Comparison Predicate parameters must be used with the operator SATISFIES. A comparison predicate returns a Boolean result if the left-hand argument satis es the comparison predicate parameter. The following example shows a comparison predicate parameter used in a report selection condition: CUSTOMER-NO SATISFIES ?CUSTOMER CUSTOMER-NO is an item (the left-hand argument in the condition); SATISFIES is the operator, and ?CUSTOMER is the comparison predicate parameter.
Printing Values As Bar Graphs Printing Values As Bar Graphs Part III: Reference You can print numeric values as horizontal bar graphs with the calculated item shown in this example: SUBSTRING ("*****************************",l,amount) Calculations 15-55
Reserved Words Reserved words are words that have a speci c meaning in HP ALLBASE/BRW and cannot be used as item names and parameter names.
Standard Deviation Standard Deviation A common computation in a report is to compute a Standard Deviation. You can compute a Standard Deviation for any HP ALLBASE/BRW numeric type. Example For example, the Standard Deviation for a Real item X that occurs in a report detail can be computed as described in the following steps. To compute the Standard Deviation of a Real item X 1. De ne a Real layout calculated item XSQUARE with this formula: sqr(X) 2.
Standard Functions HP ALLBASE/BRW includes many built-in functions. These are listed in the Standard Functions table, and each is described on the following pages.
Standard Functions Standard Functions Table (continued) Type REAL REAL NUMERIC Function TO REAL ABS ARCTAN ARCCOS COS EXP LN LOG SIN SQR SQRT TAN TO NUMERIC Format (number) (string) (number) (number) (string) STRING TO STRING Related Sections Part III: Reference (number) Explanation Returns a real number equivalent to number Returns a real number equivalent to string (string must be a numeric string) Returns absolute value of number Returns arc tangent of number Returns arc cosine of number Ret
Standard items are items supplied with HP ALLBASE/BRW. They are listed in the following table.
Strings Strings A string is a series of characters enclosed in double quote marks, For example: "This is a string" Strings can be up to 255 characters long. String Operations String Concatenations String operations are used to concatenate string values or to extract a subset out of a string value. During a calculation, string length is unlimited; however, you must specify a string length for the result of the calculated item (maximum length 255 characters).
Strings Example 3 SUBSTRING ("Whiskey", -3, 4) In this example, \Whiskey" is the source string, -3 the position from which the extraction starts, and 4 the length of the extracted string. The result is: "Whis" Note that if a position less than 1 is speci ed, position 1 is used. Using double quote marks (") in Strings You can use double quote marks in strings with HP ALLBASE/BRW version A.00.10 and later.
Syntax Diagrams Syntax Diagrams Part III: Reference Syntax diagrams provide you with the complete logic, elements, and structure of the Calculation Language. The diagrams are made up of three related parts: Diagram 1 shows expressions, including calculated items and functions. Diagram 2 shows the conditions (or logical-expressions). Diagram 3 shows the primaries of the rst two diagrams, that is, names and constants.
Syntax Diagrams Syntax Diagram 1: Expressions 15-64 Calculations Part III: Reference
Syntax Diagrams Syntax Diagram 2: Conditions/Logical Expressions Note that Diagram 2 uses expressions de ned in Diagram 1, and IF expressions in Diagram 1 use conditions in Diagram 2.
Syntax Diagrams Syntax Diagram 3: Primaries for Diagrams 1 and 2 15-66 Calculations Part III: Reference
Syntax Rules For Calculations Syntax Rules For Calculations Rules For Formulas and Conditions Part III: Reference The following general rules apply when de ning calculations for all types of HP ALLBASE/BRW formulas and conditions. Note that all formulas and conditions are examined by HP ALLBASE/BRW after you enter them, so that misspellings, type con icts, and other errors will be immediately found. No item with the same name as an HP ALLBASE/BRW reserved word can be used in a calculation.
Syntax Rules For Calculations Argument names and reserved words can be written in any combination of uppercase and lowercase characters. That is, except in strings, HP ALLBASE/BRW will not di erentiate between and uppercase and lowercase characters. You must leave at least one blank space between operators and their arguments. (HP ALLBASE/BRW lets you include characters such as 0, /, and & in the middle of names). You can add comments between double angle-brackets: for example: <>.
Time You can manipulate times using HP ALLBASE/BRW Time types. Items of type Time are de ned in the data dictionary. (See Chapter 18 for the data dictionary de nitions of Time types). A time always consists of hours, minutes, and seconds. Time items can be output in many di erent ways, depending on the edit-mask (for output) and the data dictionary de nition (for input). HP ALLBASE/BRW converts all times into packed decimal numbers with 19 digits, including 6 decimal places.
Time Time Duration Functions Time duration functions calculate the di erence between two time values. These are speci ed as: HOURS_DIFF MINUTES_DIFF SECONDS_DIFF (time1, time2) (time1, time2) (time1, time2) They result in integer-type items. If the duration is not an integral number of hours or minutes, the di erence is rounded to the nearest integral number.
Types This section describes each type of argument in HP ALLBASE/BRW, (Real, Integer, Numeric, String, Date, or Time), and the operations that can be performed upon each type. It describes the standard functions that are supplied with HP ALLBASE/BRW, and also covers exception handling and exception conditions.
Type Conversion HP ALLBASE/BRW automatically converts numbers from type to type. That is, if you assign an Integer value to a Numeric or Real item, the Integer is converted to the approriate type. For example, the index of an array item and the modulo divisor must be speci ed as integers. If you assign a Real or Numeric value to the modulo divisor or array index, HP ALLBASE/BRW automatically does a type conversion, and truncates super uous decimals.
16 Using HP ALLBASE/BRW Screens Alphabetical reference for HP ALLBASE/BRW screens Part III: Reference Using HP ALLBASE/BRW Screens 16-1
At a Glance Use this screen hierarchy to see how you get to a particular screen.
Compile Report Screen Compile Report Screen Use this screen to compile a report. d a c b HP ALLBASE/BRW checks that speci cations have been correctly entered and displays them on this screen. After the compilation, HP ALLBASE/BRW displays the compilation listing on this screen. To compile a report 1. Make sure that you have saved the latest speci cations for the report you want to compile. 2. Choose Compile Report from the Select Report screen to get to the Compile Report screen.
Compile Report Screen To review the compile listing 1. Choose Next/Prev Page to review the compile listing online. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To print the compiled listing 1. Choose Print Listing to print a hard copy of the compile listing. 2. At the prompt, type the printer ID and press 4Enter5 or choose Print Listing again.
Date Item Edits Screens Date Item Edits Screens Use this screen to specify how date items are to be printed in the report. d a c b Anytime you specify a DATE item on the De ne Lines screen, you can go to the Date Item Edits screen to edit the way the date appears in the report. To edit a DATE item 1. De ne a line in the report that contains a DATE item. 2. Choose Item Edits from the De ne Lines screen where the DATE item is de ned. 3.
Date Item Edits Screens To select a different lineset NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Select Lineset to select a di erent lineset. 2. At the prompt, type the lineset type (D for detail, P for page, R for report, or a break level number) and type H for heading or F for footing if applicable. 3. Press 4Enter5. Banner Line The banner line (at the top of the screen) shows in which lineset the current item is positioned.
Date Item Edits Screens "No Value" Character "Error Value" Character Optional. The print eld is lled with this character if a resulting item has no value. For example, for a date eld with no value, you could specify that a hypen (-) be used (as shown on the sample screen). Optional. The print eld is lled with this character if an error value occurs.
Define Breaks Screen Use this screen to set the sort sequence and specify pagination requirements for a report layout. d a c b To define a sort and break 1. Type the name of the Sort Item, Level and Order, and Change Degree for Break. 2. Press 4Enter5. To delete a sort and break 1. After associated linesets have been deleted, blank out the name of the item. 2. Press 4Enter5. Other items are rearranged in sort order.
Define Breaks Screen To change sort levels 1. Type in new sort levels. 2. Press 4Enter5. The system automatically rearranges items according to the new sort levels. Optional settings (RESET PAGE on LEVEL, SUPPRESS REPITITION, SWITCH DESTINATION LEVEL and COLCALC LEVEL) which are related to break items are changed accordingly. To delete a lineset NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Delete Lineset . 2. At the prompt, type the level and type of the lineset you want to delete. 3.
Define Breaks Screen Sort Level Order Change Degree for Break Sort level for this item, from 1 (highest) to 9 (lowest). These can be non-sequential numbers, for example, 4, 8, 9. Non-sequential numbers are reduced to sequential when you press 4Enter5. For example, if you selected sort levels 4, 8, and 9, they would become 1, 2, and 3. Or, if you had selected 1, 2, and 3 but had deleted 2 before pressing 4Enter5, the sequence would automatically become 1 and 2. Optional.
Define Breaks Screen Page Length Reset Page Number on Level Lines Paging Optional. The number of lines on the page. The same value is displayed on the De ne Report screen. This can be changed here or on the De ne Report screen. Optional. The sort level at which the report's page numbering resets to page 1. A new page is started automatically when the page number is reset to 1.
Define HPDesk Configuration Screen Use this screen to de ne all the information necessary to allow HP ALLBASE/BRW to submit a report or output le to HPDesk. d a c b HPDesk distributes the report according to the information entered here. Note You will only be able to access this screen if you have purchased HP BRW-Desk/XL. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Access this screen by choosing Define Delivery on the De ne Report screen or appropriate Output File screen.
Define HPDesk Configuration Screen Field Descriptions Subject Optional. Used by HPDesk as the subject of the message. You can use a string constant, a database item, or a calculated item. (The item type must be S(tring). Name/Address Fields Use these elds to specify the recipients of a message, using the standard HPDesk conventions for recipient categories. These elds are used to describe the complete distribution list for a message.
Define HPDesk Configuration Screen Urgent Private Acknowledgment Optional. Use this eld to make your message URGENT. If not speci ed, a default of NORMAL is taken for a message. Optional. Use this eld to de ne the security of the message. If not speci ed the message will be given a security level of NORMAL (that is, not PRIVATE). Optional. Use this eld to de ne the level of Acknowledgment required on the message. As per standard HPDesk conventions, it will apply to just the TO recipients.
Define HPDesk Configuration Screen Softkeys WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Delete Delivery WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Prev/Next Values Part III: Reference Press this key to delete the delivery speci cation. At the prompt, press the key again to con rm the deletion. Use these keys to add more than 8 recipients to a message. Up to 200 recipients may be de ned for a message.
Define Lines Screen Use this screen to layout text and items in each lineset for a report. d a c b c b c b c b This screen consists of three parts. The top section is called the report window and displays the current report layout. The middle section contains both the current line number eld and the line text eld in which literal text is entered. The bottom of the screen contains item layout details.
Define Lines Screen To continue typing text that is longer than 75 characters 1. Use Scroll ---> to access additional room for the text. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. Type the remaining text for this line. 3. Press 4Enter5. Inserting a line in front of another line in the lineset 1. Type the line number before which the new line will be inserted. 2. Type text for the line, using Scroll ---> to access more space for the line if necessary. 3. Press Insert Line .
Define Lines Screen Replacing a line of text To replace a line 1. In the line number eld, type the number of the line on which you want to replace text. 2. In the text eld under the ruler bar, type the replacement text, overwriting previous text if necessary. Use Scroll ---> to access more space for the line. 3. Press 4Enter5. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Deleting and Copying To delete lines in a lineset NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Delete Lines . 2.
Define Lines Screen S for sample layout 3. Press 4Enter5. Only the layout (from the report window) is copied; items are not copied. This feature provides you with a working layout in the current lineset. Moving lines and items To move lines and their positioned items 1. Press Move Lines . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. When you see the prompt, type: a single line number or number/number, to indicate a range of numbers that you want to move; the destination line number. 3. Press 4Enter5.
Define Lines Screen At the top and bottom of the report window, a ruler indicates column positions along the line. For any number along the ruler, the rst digit corresponds to the exact column position. In this example, ....|....10... the digit 1 indicates position 10 on the line. Field Descriptions Line Number Number of the current line being edited. This eld is not labelled on the screen. It is the small eld on the left side of the screen.
Define Lines Screen Line Optional. The line number on which this item is positioned in this lineset. Default is the current line. Len Optional. The print eld length for this item. The default is determined by the item length and edit mask de ned on the Item Edits screen. ColCalc Optional. The type of column calculation to be performed on this item and the level at which the calculation is to be reset.
Define Lines Screen Note the following examples: If the value is: with this numeric precision: the result with changed precision: 1024.3849 -2R 1024.38 864186.46 3C 865000.00 SuppRep Optional. A number (from 1 to 9) corresponding to the sort level to suppress repeated printing of an item value, that is, an item is only printed on the rst detail within this break.
Define Lines Screen WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Shows the next group of items if the list of items is too large for one screen. WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Copy Lines Choose this softkey to copy a line or a group of lines. Answer the prompt with: a single line number or a range of numbers. Use this syntax for a range of numbers: number /number .
Define Relation Screen Use the De ne Relation screen to specify common items to be used for a join. d a c b To specify a new relation 1. Type the name of the Common Item and its Source Table. If the common item has the same name in more than one source table, you can, if you want, enter the one name common to both items (and do not specify a source table). HP ALLBASE/BRW looks up all occurrences of the item in all the source tables and uses all of them as the relation de nition.
Define Relation Screen Banner Line The banner line displays the name of the joined table for which you are de ning this relation and the number of relations de ned. For example, \Relation 3 of 3" means you are now de ning relation number three from a total of three that exist. Field Descriptions Common Item The name of the item for which you are de ning a relation.
Define Report Screen Use this screen to specify global characteristics for your printed report. d a c b c b To define global characteristics for a report 1. Type global characteristic de nitions in the appropriate elds. 2. Press 4Enter5. See the following Field Descriptions for details on each eld.
Define Report Screen Field Descriptions Report Specification Password Report Description Report Width Number Of Horizontal Pages Optional. Password restricting access to the speci cations for this report. Optional. Description of the report which is displayed on the De ne Report screen. Can also print this Report Description using the standard item REPORT-DESCRIPT. Maximum width is 300 characters. Default width is 132.
Define Report Screen SQL Transaction Isolation Level Optional. The locking strategy for SQL transactions. For read only applications, RC (read committed) is the best suited locking strategy. Consult the HP ALLBASE/SQL Reference Manual for a description of the other isolation levels (RU = read uncommitted, CS = cursor stability, RR = repeatable reads). If this eld is left blank, the execution value in the BRW Con guration File is used. Use Access of Report Optional.
Define Table Screen Define Table Screen Use this screen to specify the source tables and the type of combination to be performed to create the result table. d a c b c b To add a new table 1. Type the new Table, Type, Source Table, Location, and Password. For an open join table, also type the name of the source table for the open join. 2. Choose Add Table . NNNNNNNNNNNNNNNNNNNNNNNNNNNNN To modify a table 1. Use the Next Table or Prev Table softkeys to access the table you want to modify.
Define Table Screen 3. Press 4Enter5. 4. Press 4Enter5 again to con rm the rename. To delete a table NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Use Next Table or Prev Table to access the table you want to delete. You can also use quick browse to access a table. See Chapter 1 for details about the quick browse feature. 2. Choose Delete Table . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. Press 4Enter5 or choose Delete Table to con rm the deletion.
Define Table Screen Note For HP ALLBASE/SQL tables you must specify both the owner name and the table name in the Source Table eld of this screen, however, elsewhere within HP ALLBASE/BRW you only need to specify the table name. To de ne a report, you need the HP ALLBASE/SQL production database or an HP ALLBASE/SQL test database with the same table structures, owner names, and access rights as the production database.
Define Table Screen Open Join on Source Table Optional. The source table for which NO VALUE is to be used if the other source tables contain records not found in this source table. The default is that only records found in all source tables will be used. Softkeys NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN There are two sets of softkeys for this screen. other keys takes you to the other set of softkeys. The softkeys for this screen take you to the screen named on the key.
Layout Calc Item Screen Layout Calc Item Screen Use this screen to de ne a layout calculated item. d a c b To add a new item 1. Type the name of the Item, Result Type, Result Length, and Numeric Result Precision. 2. Choose Add Item . NNNNNNNNNNNNNNNNNNNNNNNNNN To copy an item 1. Type the name of the new item and press 4Clear Display5. 2. Choose Add Item . NNNNNNNNNNNNNNNNNNNNNNNNNN 3.
Layout Calc Item Screen 2. Choose Delete Item . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. Press 4Enter5 or choose Delete Item to con rm the deletion. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN a. The item and its formula are deleted. To rename an item NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Use Next/Prev Item key to access the item you want to rename. You can also use QUICK BROWSE to access an item. Chapter 1 for details about the QUICK BROWSE feature. 2. Type the new name over the old item name. 3.
Layout Calc Item Screen This is illustrated in the following examples: If calculation results in: With this numeric precision: 1024.3849 864186.46 -2R 3C The result with changed precision is: 1024.38 865000.00 See the description of Real Numbers in Chapter 15 for a description of Real output forms and precision.
Layout Calc Item Formula Screen Use this screen to de ne a formula for a layout calculated item. d a c b To define a formula 1. Type the calculation formula. Leave blanks around any operators in the formula. You can use all terminal editing and arrow keys when typing and editing a formula. Use 4Prev Page5 or 4Next Page5 keys on the terminal to access the next page of the formula. 2. Press 4Enter5. The formula is checked for correctness.
Layout Function Screen Layout Function Screen Use this screen to de ne a layout function. d a c b To add a new function 1. Type the name of the Function, Result Type, the Function Argument and Type. 2. Press Add Function . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To copy a function 1. Type the name of the new function and press 4Clear Display5. 2. Choose Add Function . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3.
Layout Function Screen 3. Press 4Enter5 or Delete Function to con rm the deletion. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To rename a function NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Use Next/Prev Function key to access the function you want to rename. You can also use QUICK BROWSE to access a function. See Chapter 1 for details about the QUICK BROWSE feature. 2. Type the new name over the old function name. 3.
Layout Function Formula Screen Layout Function Formula Screen Use this screen to de ne a formula for the layout function de ned at the Layout Function screen. The top portion of the screen shows the function name and the arguments already de ned for the function. d a c b To define a formula 1. Type the calculation formula. Leave blanks around any operators in the formula. You can use all terminal editing and arrow keys when typing and editing a formula. 2.
Mail Values Screen This screen is part of the BRWSETUP.PUB.SYS program. Use this screen to de ne default speci cations for HP BRW-Desk/XL. This screen is for use only when you have HP BRW-Desk/XL in addition to HP ALLBASE/BRW. d a c b To change the default specifications 1. Type the desired value in the appropriate elds. See the Field Descriptions section for details on each eld. 2. Press 4Enter5.
Mail Values Screen Field Descriptions Sender Name/Address Password Required. The HPDesk user name/address that is used when signing on to HPDesk. This address is not validated at speci cation time. You can specify up to 9 di erent senders, and a minimum of 1 sender is required. HP BRW-Desk/XL will use this pool of senders to do the sign on to HPDesk.
Numeric/Integer Item Edits Screen Use the Numeric Item Edits screen to specify how Numeric and Integer item values are edited before being printed in a report. You get to this screen from the De ne Lines screen. Choose other keys until you can choose Item Edits . At the prompt, you type the name of the item you want to edit.
Numeric/Integer Item Edits Screen Banner Line The banner line (at the top of the screen) shows in which lineset the current item is positioned. This example screen shows a Detail lineset. Field Descriptions Item Line/position Length Display. The current item. Display. The line and print eld position of the item. Display. The print eld length de ned for this item on the De ne Lines screen. You can optionally change the length for this item. Scaling Optional.
Numeric/Integer Item Edits Screen Prefix/Suffix positive/negative Optional. Type a pre x/su x for positive and negative occurrences of this item. For example, the pre x or su x can be a minus sign, Cr, Db, brackets, or so on. Use ^ for indicating trailing blanks. The pre x or su x takes up space in the eld. Be sure to allow for this additional space. Adjust Optional. To position the pre x/su x directly next to the number, type an X.
Numeric/Integer Item Edits Screen "No Value" Character Optional. The print eld is lled with this character if the item has no value. "Error value" Character Optional. The print eld is lled with this character if an error value occurs. "Field overflow" Character Optional. The print eld is lled with this character if the item value is too long for the edit mask.
Online Review Screen Use this screen to review a report which is stored in a disc le. d a c b To review all parts of a report 1. Use the Scroll--> , Next/Prev Page , or Go To Page keys to review parts of the report that do not appear on the screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To print a report 1. Choose Print File . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2.
Output File Screen Output File Screen You use the Output File screen to de ne an intermediate report le and to specify which items are to be written to it. d a c b To define an output file 1. From the De ne Report screen, choose Output File . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. At the prompt, type the lineset information for the lineset you want to use. Press 4Enter5. The Output File screen appears.
Output File Screen To define items for an output file 1. Type the name of each item that you want written to the output le in the Name in File eld or type an X (or a number for QB and DIF les) in the Select/Sequence eld. If the output le is an intermediate report le, you can choose any name for an item's Name in File.
Output File Screen So, to free disc space when data in an intermediate report le is no longer needed, you can run the program REMPTY to clear the le, as shown here: :RUN REMPTY.PUB.SYS . . . Intermediate report file: ORDERS.PUB END OF PROGRAM REMPTY deletes all records from an intermediate report le but leaves the le and the data descriptions intact. REMPTY cannot be used for other types of le.
Output File Screen Field Descriptions Output File Password File Size Access Mode File Type The name of the output le. Optional. The lockword for this output le. Optional. The maximum size of the le in records. If this is blank, the default de ned in HP ALLBASE/BRW's con guration le is used. The access mode determines what HP ALLBASE/BRW does if another le of the same name is encountered on the system at run time.
Output File Screen Item Display. Displays all items selected for this lineset as speci ed on the De ne Lines screen. Type Display. Displays type de ned for this item: S= N= D= I= R= T= Select/Sequence ColCalc string numeric xed point date integer real time Specify an X or a number to indicate that you want an item in the output le. With QB and DIF les, the number will indicate the position of the items in the record. If you leave this eld blank, the item will NOT be included in the output le.
Output File Screen NumPrec Optional. Numeric precision of this item if you want precision to be changed for the output le. Type a number and method code, as described in the following tables. The number indicates the power of 10, or 10th exponent that indicates the precision. Valid range for Numeric (type N): -6 through 12; for Integer (type I): 0 through 8 for Real (type R): -90 through 76.
Parameter Screen Parameter Screen Use this screen to de ne parameters for which you supply values at report execution time. d a c b To add a new parameter 1. Type the name of the Parameter, Value Length, Result Type, Parameter Type, Value Required and Upshift Value. See the Field Descriptions section for details on each eld. 2. Choose Add Parm . NNNNNNNNNNNNNNNNNNNNNNNNNN To copy a parameter 1. Type the name of the new parameter and press 4Clear Display5. 2. Choose Add Parm .
Parameter Screen To modify a parameter 1. Use Next/Prev Parm key to access the parameter you want to modify. You can also use QUICK BROWSE to access a parameter. See Chapter 1 for details about the QUICK BROWSE feature. 2. Make the required changes to the parameter. 3. Press 4Enter5. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To delete a parameter 1. Use Next/Prev Parameter key to access the parameter you want to delete. You can also use QUICK BROWSE to access a parameter.
Parameter Screen Value Required Upshift Value Note Part III: Reference Optional. Type X if a value must be input at report execution. Recommended if parameter type is S or L. Optional. Type X to shift automatically to upper-case the value of this parameter. This is useful if the parameter is used to select values of an item containing only upper-case data in the database.
Project Items Screen Use this screen to edit the list of items in the table. This screen shows all items from the source tables and which items are to be projected from the table. d a c b To review the list of items NNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Use Prev Item or Next Item to review the list of items or use quick browse. See Chapter 1 for details about quick browse. To project items from a table 1. Type X in the Project eld to project an item . 2.
Project Items Screen Banner Line Displays the table name and type of combination for this table, either J (join) or M (merge). Field Descriptions Item Source Table Type Project Alias Name Display. All items from the source tables in alphabetical order. Display. For items located in the source tables, the source is listed. For a table calculated item, this eld is left blank. Display. The type of this item. An X for all items to be projected from the table.
Project Items Screen Method Code Digits are: R T C rounded truncated ceiled See the description of setting precision, including the special cases for Real precision, in Chapter 15. Note Project, Alias Name, and NumPrec only apply outside the table, for example, in a report layout de nition, or if the table is used as source table, in another table. Therefore, if items have the same name and any are used in a formula for a table calc item, the name must be quali ed with the source table name.
Real Item Edits Screen Real Item Edits Screen Use the Real Item Edits screen to specify how Real item values are edited before being printed in a report. d a c b To specify a Real item edit 1. Use the Prev/Next Item or the Select Item function key to access a di erent item. 2. Type the item edit de nition for the item. See the Field Descriptions section for details on each eld. 3. Press 4Enter5.
Real Item Edits Screen Banner Line The banner line (at the top of the screen) shows in which lineset the current item is positioned. Field Descriptions Item Line/position Dw.d Ew.d Fw.d 16-60 Display. The current item. Display. The line and print eld position of the item. Length Optional. The print eld length de ned for this item on the De ne Lines screen. You can optionally change the length for this item. Format Optional. Format for Real numbers.
Real Item Edits Screen Gw.d w = eld width d = number of The value is printed using E format if its absolute value is less than 0.1 or greater than or equal to 10**d (after rounding to d digits signi cant digits). Otherwise it is printed using F format with blanks in the 4 spaces reserved for the exponent part. For example: Format G12.4 (width 12, digits 4. ^ denotes a space) Value Output -12345 ^^-.1235E+02 1234 ^^^1234.^^^^ -12 ^^-12.00^^^^ Explanations for the above examples: 1.
Real Item Edits Screen "No Value" Character Optional. The print eld is lled with this character if the item has no value. "Error Value" Character Optional. The print eld is lled with this character if an error value occurs. "Field Overflow" Character Optional. The print eld is lled with this character if the item value is too long for the edit mask.
Relation Condition Screen Relation Condition Screen Use this screen to enter the formula which selects a subset of data for the joined table. d a c b To define a relation condition 1. Type the condition formula. Leave blanks around any operators in the formula. You can use all terminal editing and arrow keys when typing and editing a formula. 2. Press 4Enter5. The formula is checked for correctness. If the system detects an error, the part of the formula with the error is highlighted.
Request Report Screen Use this screen to request execution of a report or reports and programs contained in a report job le. d a c b You also use this screen to access the Online Review screen if you want to display a report on the screen. To request a report 1. Type schedule, printer details, and parameter values for this report. Use the Next/Prev Values if you want to use more than 9 parameters. 2. Choose Start Report .
Request Report Screen Field Descriptions Report Parameter The name of the report you want to request. The default display is the report execution le that is currently loaded. You can use one of the following: the name of the execution le if you are requesting a report to be printed; the name of a report job le if you are executing reports and programs contained in that le, or the name of the print le if you are reviewing the report online. Display. The parameters in the selection set for this report.
Request Report Screen For string parameters it is possible to pass a value that contains a double quote ("). For example, to specify the string ABC"D, type the values as "ABC""D". Using two double quotes ("") in a string results in one double quote ("). If you see the message \frozen values" in these elds, the value cannot be changed from the default. Scheduled Instructions for when the report is to be printed.
Request Report Screen Job Input Priority The job input priority for the report job. See Chapter 11 for information on the jobs that run HP ALLBASE/BRW reports. If you see the message \frozen values" in these elds, the value cannot be changed from the default. Environment File Optional. The name of the environment le for printing this report. [What is the default?] If you see the message \frozen values" in these elds, the value cannot be changed from the default. CCTL/NOCCTL Optional.
Review Layout Screen Use this screen to review a sample of the report layout on-line and to print a copy of the sample. d a c b To review on-line NNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Use the Scroll and Prev/Next Page functions keys to review the entire sample layout. To print a sample 1. Press Print Layout . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. When you see the prompt, type the print device you want to use. 3. Press 4Enter5.
Review Layout Screen Field Descriptions Line The description of the line. This unlabeled eld is located to the left of the layout area. The following are examples of the symbols used on the sample layout. Report Heading, line 7. Use 4Prev Page5 to see previous RH7 lines in the Report Heading. Report Heading, line 8. RH8 NNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNN 1BH1 NNNNNNNNNNNNNNNNN 1BF1 NNNNNNNNNNNNNNNNN RF1 < > Part III: Reference First break level heading, line 1.
Select Report Screen This screen is the entry point to HP ALLBASE/BRW. Use it to select and maintain reports, list report speci cations, current les or jobs. d a c b c b c b To add a report 1. Type the name of the report speci cation le in the Report eld. 2. Choose Add Report . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. When you see the prompt, type the name of the report from which you want to copy, or press 4Enter5 to create a new report. To modify a report 1.
Select Report Screen To delete a report 1. Type the name of the le you want to delete. 2. Choose Delete Report . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. When you see the prompt, choose Delete Report again to con rm the deletion. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To list report specifications 1. Type the name of the report speci cation le. 2. Choose List Report . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. When you see the prompt, type the print device. 4. Press 4Enter5. To compile a report 1.
Select Report Screen To display a particular file 1. Type the name of the le in the Show Files eld. 2. Type the name of the group in the of Group eld. 3. Choose Show Files . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To display all HP ALLBASE/BRW files in a group 1. Leave the Show Files eld blank. 2. Type the name of the group in the of Group eld. 3. Choose Show Files . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN You can use the ?, #, or @ wildcards in either the Show Files eld or the of Group eld.
Select Report Screen For example: d a This displays all customer reports in this account. That is, all HP ALLBASE/BRW reports that begin with the letters CUST. Softkeys WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Add Report WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Delete Report WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW List Report Begins the de nition of a new report speci cation le. Deletes an existing report speci cation le. Lets you print a listing of the report speci cation.
String Item Edits Screen Use this screen to specify how the values of string items are to be edited before they are printed on a report. String item edits are optional. d a c b To specify a string edit 1. Use Prev/Next Item or Select Item to access the item. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. Type item edits for this item. 3. Press 4Enter5.
String Item Edits Screen Banner Line The banner line (at the top of the screen) shows in which lineset the current item is positioned. Field Descriptions Item Line/position Length Shift String Display. The current item. Display. The line and print eld position of the item. Optional. The print eld length de ned for this item on the De ne Lines screen. You can also change the length for this item. Optional.
String Item Edits Screen "Error Value" Character Optional. The print eld is lled with this character if an error value occurs. Softkeys WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Select Item WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Select Lineset 16-76 Prompts for the name of the item to be accessed. Prompts for the level and type of lineset to be accessed.
Suppress Line Screen Suppress Line Screen Use the Suppress Line screen to set the conditions under which speci c lines are not printed. d a c b Access this screen from the De ne Lines screen. Respond to the prompt at the De ne Lines screen Suppress condition for line by typing the line number you want to suppress. To specify a suppress line condition 1. Type the suppress line condition. Leave blanks around any operators in the condition.
Suppress Lineset Screen Use this screen to set the conditions under which speci c linesets should not be printed or sent to the output le. d a c b Access this screen from the De ne Breaks screen. To specify a suppress lineset condition 1. Type the suppress lineset condition. Leave blanks around any operators in the condition. You can use all terminal editing and arrow keys when typing or editing a condition. 2. Press 4Enter5. The condition is checked for correctness.
Table Calc Item Screen Table Calc Item Screen Use this screen to de ne a table calculated item. d a c b To add a new item 1. Type the name of the Item, Result Type, Result Length, and Numeric Result Precision. 2. Choose Add Item . NNNNNNNNNNNNNNNNNNNNNNNNNN To copy an item 1. Type the name of the new item and press 4Clear Display5. 2. Choose Add Item . NNNNNNNNNNNNNNNNNNNNNNNNNN 3.
Table Calc Item Screen 2. Choose Delete Item . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. Press 4Enter5 or choose Delete Item to con rm the deletion. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN The item and its formula are deleted. To rename an item NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Use Next/Prev Item key to access the it em you want to rename. You can also use QUICK BROWSE to access an item. See Chapter 1 for details about the QUICK BROWSE feature. 2. Type the new name over the old item name. 3.
Table Calc Item Screen Field Descriptions Item Result Type Result Length Numeric Result Precision The name of the calculated item. One of the codes listed for the Result Type. Type the result length of the string. Maximum is 255; minimum is 1. Required only if this item is a string. Type a number and method code for the numeric precision of the calculated item, if you want precision to be changed at this stage of the report.
Table Calc Item Formula Screen Use this screen to de ne a formula for a table calculated item. This screen consists of two blank pages on which you can type a formula. d a c b To define a formula 1. Type the calculation formula. Leave blanks around any operators in the formula. You can use all terminal editing and arrow keys when typing and editing a formula. Use the 4Prev Page5 or 4Next Page5 keys on the terminal to de ne the next page of the formula. 2. Press 4Enter5.
Table Function Screen Table Function Screen If you use a particular calculation several times in a table, use this screen to de ne this routine as a table function. d a c b To add a new function 1. Type the name of the Function, Result Type, the Function Argument and Type. 2. Choose Add Function . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To copy a function 1. Type the name of the new function and press 4Clear Display5. 2. Choose Add Function . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3.
Table Function Screen 2. Choose Delete Function . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. Press 4Enter5 or choose Delete Function to con rm the deletion. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To rename a function NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Use Next/Prev Function key to access the function you want to rename. You can also use QUICK BROWSE to access a function. See Chapter 1 for details about the QUICK BROWSE feature. 2.
Table Function Formula Screen Table Function Formula Screen Use this screen to de ne a formula for a table function. d a c b To define a formula 1. Type the calculation formula. a. Leave blanks around any operators in the formula. b. You can use all terminal editing and arrow keys when typing and editing a formula. c. Use 4Prev Page5 or 4Next Page5 terminal keys to de ne the next page of the formula. 2. Press 4Enter5. The formula is checked for correctness.
Use this screen to specify how a time item value is to be edited before it is printed in the report. Time Item Edits Screen d a c b To specify a time item edit 1. Use the Prev/Next Item or the Select Item function key to access the item. 2. Type the item edits for this item. 3. Press 4Enter5. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Banner Line NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN The banner line (at the top of the screen) shows in which lineset the current item is positioned.
Time Item Edits Screen Justify Optional. To change the justi cation, type one of these codes: L C R left justify center right justify Default is that the value is left-justi ed. Prefill with Character "No Value" Character "Error Value" Character Optional. The character to be printed instead of leading blanks before the item value. Optional. The print eld is lled with this character if a resulting item has no value. Optional. The print eld is lled with this character if an error value occurs.
Tune Access Screen Use this screen to specify how data is to be retrieved from the source tables. d a c b To define how the data is to be retrieved 1. Type the Access Sequence, Key, Lock Mode (Open Mode and Item Level Security for IMAGE data sets) for each source table. 2. Press 4Enter5. Banner Line Shows for which table you are tuning access. Field Descriptions Source Table Display. All source tables de ned for this table. Table Type Display.
Tune Access Screen Key Optional. The name of the key from the following table, if the report requires keyed access. If this eld is left blank, les are read serially. There is no keyed access to HP ALLBASE/SQL tables. Key Source Table Type Result IMAGE detail search item chained read IMAGE master search item calculated read KSAM le key element read by value Anything but HP ALLBASE/SQL TABLE-REC-NUMBER directed read Items de ned as relations can be used as keys.
Tune Access Screen Open Mode Optional. (IMAGE only) The mode to be used by HP ALLBASE/BRW when opening an HP IMAGE/3000 database. HP ALLBASE/BRW will use DBOPEN with this mode when opening the database. The possible DBOPEN modes are: Access Mode Capabilities 5 Read, allow concurrent modify 6 Read, allow concurrent modify 7 Read, exclusive 8 Read, allow concurrent read Consult the HP IMAGE/3000 Data Base Management Reference Manual for a full description of the DBOPEN modes and capabilities.
Use Items Screen Use Items Screen Use this screen to select the items that you want to appear in the current lineset of your report. These items are from the nal access table, layout calculated items and parameters. d a c b To include an item in your report 1. Type an 'x' or a number between 1 and 99 in the Select eld adjacent to the items that you want to include.
Use Items Screen To search for a particular item or array 1. Choose Search Item . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. Type the name of the item at the prompt. You need to type only enough characters to uniquely identify the item name. 3. Press 4Enter5. If no item matches the characters you enter, HP ALLBASE/BRW positions the cursor at the next item, in ASCII order, that begins with character following those you entered.
Use Items Screen WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW All Items WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Search Element Press this to redisplay all the items in the nal access table. Prompts for the number of the element that you want to position the cursor at. WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Displays the elements of the previous array (if any). WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Displays the elements of the next array (if any).
17 Configuration and Security System, account, and individual group level con guration and security Part IV: Administrative Tasks Configuration and Security 17-1
At a Glance You change the con guration of HP ALLBASE/BRW with the con guration program BRWSETUP. Here are the speci c things that you can con gure using BRWSETUP: Name of the default print device The number of pages after which a new spool le is started The maximum size of print les on a disk The maximum size of execution work les Open mode, lock retries, and wait intervals for IMAGE databases Transaction isolation levels and transaction priority for HP ALLBASE/SQL databases Whether to round .
Configuration Levels Configuration Levels System-Level Configuration Con guration can be de ned on the system, account, or individual group level.
Running BRWSETUP Running BRWSETUP To run the configuration program for HP ALLBASE/BRW 1. Log on in the group and account of the con guration le you want to change. 2. Run the con guration program, as shown here: RUN BRWSETUP.PUB.SYS The rst con guration screen appears. d a c b 3. Modify the con guration for data language, rounding, and data format. See the details for the Global Values Screen, later in this chapter. 4.
Global Values Screen Global Values Screen Use this screen to specify the data language, rounding rule, and input date format to be used by HP ALLBASE/BRW. This is the rst screen you see when you run BRWSETUP. d a c b To modify global values 1. Type the data language or number, the rounding rule, and the date format to be used. See the Field Descriptions section for details on each eld. 2. Press 4Enter5 To verify MPE V Compatibility 1. Choose Verify .
Global Values Screen Type X after .5 to 1 to con gure HP ALLBASE/BRW so that 0.5 values are rounded up. Or, enter X after 0 so values are rounded down. Rounding .5 to 1 or 0 For example, if you have speci ed the numeric precision -2R for an item, the values are rounded as shown in these examples: Value 54.865000 54.864999 54.865001 Round 0.5 up 54.87 54.86 54.87 Round 0.5 down 54.86 54.86 54.
Execution Values Screen Execution Values Screen Use this screen to specify the print le con guration and internal con guration for BRWEXEC. Get the this screen by choosing Exec Values at the Global Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b 1. Type print le and other BRWEXEC internal con guration details. 2. Press 4Enter5. Field Descriptions Default Print Device Print File fields Type the ID for the device on which reports should usually be printed. (The default is LP.
Execution Values Screen You can estimate the number of pages which t into a spool le with the following formula: # pages = sectors per spool file extent * 7500 report width * non-blank lines per page The spool le size is con gured for your system as the number of sectors per spool le extent. A spool le can have up to 32 extents. Other limiting factors are the overall number of sectors for spool les con gured for the system and the size of the free space portions on the discs.
Execution Values Screen Maximum Number of Lock Retries Type the number of times BRWEXEC should retry if a report locks a dataset or a le with lock mode \retry." BRWEXEC tries the lock until it is granted or until the con gured number of retries is exceeded. Wait Interval between Lock Retries Type the number of seconds to elapse between retries. The default is 10 seconds. See Chapter 6, De ning Data Access, for details about data retrieval methods.
Execution Values Screen Print Execution Statistics Disable Automatic Recompile Optional. Type an X in this eld if you want to print the full execution statistics. If this eld is blank, only the Report Summary (one line) is printed. Optional. If this is eld blank, HP ALLBASE/BRW compares the time-stamp on the report with the time-stamp on the BRWDIC le and automatically recompiles the report, when the report is executed, if the BRWDIC le was created since the report was last run.
Exception Output Values Screen Exception Output Values Screen Use this screen to de ne values written to a dictionary output le if an item results in NO VALUE or an error value. To get to this screen, choose Except'n Output on the Global Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b To define exception output values 1. Type an exception value for each of the data types displayed on the screen. For each data type, specify a Char or Hex value, or type a set to Value.
Exception Output Values Screen Field Descriptions Char The left column of the screen lists the data types for HP ALLBASE/BRW. Type a Char or Hex ll value (as shown below) for each data type, or type a speci c value. Type the character. The eld will be lled with the speci ed character. Hex Type a Hex digit between 00 and FF. This can be used to ll a eld with non-printable ASCII characters. or set to Value Type a value. The table below explains how di erent data types are converted in HP ALLBASE/BRW.
Specification Values Screen Specification Values Screen Use this screen to de ne default report speci cations. Use the function keys to specify default item edits. To get to this screen, choose Spec Values at the Global Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b This screen shows the HP ALLBASE/BRW defaults. Print width, page length, and sort method can be changed for individual reports on the De ne Report screen for that report.
Specification Values Screen Field Descriptions Report Width Page Length String Sort\Sequence De nes the default report width. The default is 132 characters. De nes the default number of lines per page. The default is 60 lines. Use this eld to specify the default sort-character-set for strings. The possible entries are: A Sort according to the ASCII character set. E Sort according to the EBCDIC character set.
Default Items Edits Screens Default Item Edits Screens There are six Item Edits screens you can con gure in BRWSETUP, one for each item type. These con guration screens correspond closely to the Item Edit screens in HP ALLBASE/BRW. If you need additional help in lling out these screens, press 4f85 to get help, or see the section for that screen in Chapter 16. The next subsections show and describe the default con guation for each item edit screen. To change these defaults 1.
Default Items Edits Screens Default String Edits Screen This is the screen for string item edits. To get this screen, choose String Edits on the Speci cation Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b The default for the edit mask is blank. The Shift String eld is also blank, but the default is that character strings are accepted as they are, that is, no shifting occurs. Justi cation is to the left. No character is pre lled.
Default Items Edits Screens Default Numeric Item Edits Screen This is the screen for numeric item edits. To get this screen, choose NumFixed Edits on the Speci cation Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b The default for the edit mask is that it has 6 decimal places, a point (period), a 9 in the rst place left of the decimal point, and twelve Z's. ZZZZZZZZZZZZ9.
Default Items Edits Screens Default Real Item Edits Screen This screen shows the defaults for the Real Item Edits ( oating-point real number items). To get this screen, choose NumFloat Edits on the Speci cation Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b The default is E 22.16. For a full description of what this format means, and for a description of the other elds on this screen, see the \Real Item Edits Screen" section in Chapter 16.
Default Items Edits Screens Default Integer Item Edits screen This screen shows the defaults for the Integer Item Edits. To get to this screen, choose Integer Edits on the Speci cation Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b For integer item edits, the default is that the mask has no decimals, a 9 in the rightmost place, and Z's ll the remaining places in the eld. The 9 indicates one digit. The Z's indicate digits without leading or trailing zeros.
Default Items Edits Screens Default Date Item Edits screen This screen shows the defaults for the Date Item Edits. To get to this screen, choose Date Edits on the Speci cation Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b The default for date items is 0m/0d/yy. For details on the edit mask format or for other elds on this screen, see the \Date Item Edits Screen" section of Chapter 16.
Default Items Edits Screens Default Time Item Edits Screen This screen shows the defaults for the Time Item Edits. To get to this screen, choose Time Edits on the Speci cation Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b For time items, the default if HH:0M. For details on the edit mask format or for other elds on this screen, see the \Time Item Edits Screen" section of Chapter 16.
Global Functions Global Functions Global functions are very similar to table and layout functions but are de ned for use by more than one report. The Global Function screen and the Global Function Formula screen are used to de ne Global Functions in the BRWSETUP program. For information on proper function syntax for the formula, refer to the Syntax Diagrams in Chapter 15. Global Functions Screen Use this screen to de ne a calculation that you want to use in several reports.
Global Functions To modify a function 1. Use Next/Prev Function key to access the function you want to modify. You can also use QUICK BROWSE to access a function. See Chapter 1 for details about the QUICK BROWSE feature. 2. Make the required changes to the function. 3. Press 4Enter5. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN To delete a function NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Use Next/Prev Function key to access the function you want to delete.
Global Functions Soft Keys WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Add Function WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Delete Function WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Define Formula WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Prev/Next Function 17-24 Adds a new function. Deletes the current function. Displays the Global Function Formula screen. These keys bring the previous or next function information to the screen.
Global Function Formula Screen Global Function Formula Screen Use this screen to de ne a global function formula. Get to the screen by chooseing Define Formula on the Global Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b Use this screen to de ne a formula for a global function. To define a formula 1. Type the calculation formula. Leave blanks around any operators in the formula. You can use all terminal editing and arrow keys when entering and editing a formula.
Define BRW-DESK/XL Configuration Mail Values Screen Use this screen to de ne default speci cations for HP BRWDesk/XL. Get to this screen by choosing Mail Values on the Global Values screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b Note that this is only used if you have BRW-Desk/XL. You must specify a global sender name and desk address. Other global values most likely to be needed are the maximum number of deliverable pages and the maximum number of records for les.
Define BRW-DESK/XL Configuration Field Descriptions Sender Name/Address Password This is the HPDESK user name/address that is used when signing on to HPDESK. This address is not validated at speci cation time. You can specify up to 9 di erent senders, and a minimum of 1 sender is required. HP BRW-Desk/XL will use this pool of senders to do the sign on to HPDESK.
Security Features with BRWPASSEXIT Security Features Note Password Encryption BRWLIST $TELLREPORT Dictionary Passwords 17-28 The BRWPASSEXIT program is not necessary with SQL tables. The reason for this is that there are no passwords for SQL tables. HP ALLBASE/SQL security is established through access rights set in the HP ALLBASE/SQL System Catalog. HP ALLBASE/BRW encrypts passwords in HP ALLBASE/BRW les. For example, it will encrypt the password of a report speci cation le.
Automatic Password Retrieval (BRWPASSEXIT) Automatic Password Retrieval BRWPASSEXIT Why use BRWPASSEXIT? Note HP ALLBASE/BRW can use a user-supplied program, called BRWPASSEXIT, to specify both the actual location names for source tables and the source table passwords. The location name for a source table is the full name of the database or le where report data is to be found, with optionally the group and account where the database or le is kept.
Automatic Password Retrieval (BRWPASSEXIT) a. The program must not write over any HP ALLBASE/BRW data or code! (This is very unlikely if you code the program in PASCAL or COBOL, but possible if you use ASSEMBLER) b.
Automatic Password Retrieval (BRWPASSEXIT) If your BRWPASSEXIT procedure does not recognize the Location Name, or does not want the given user, group, or account to have access to the location, it should return a status=-1 (or any value besides zero). A non-zero status from BRWPASSEXIT will cause report execution to stop immediately. If BRWPASSEXIT is found, and access to the password and location of the table is allowed, then the report is executed. The return status=0.
Automatic Password Retrieval (BRWPASSEXIT) label 9999; begin with passexit_rec do begin if location = 'ADB' then begin location :='ADB.ADBGROUP.ADBACCT'; password := 'READADB'; fread access only requiredg status :=0; flocation recognized:} goto 9999; end; f and so on through the various short locations g {PAYROLL database only accessible to people logged on to the FINANCE account. For anyone else, reject access.} if location = 'PAYROLL' then if account = 'FINANCE' then begin location := 'PAYROLL.PUB.
Automatic Password Retrieval (BRWPASSEXIT) COBOL Example Compile the program: :COB85XL %%progname%% LINKAGE SECTION 01 COM-RECORD. 05 LOCATION 05 REPORTNAME 05 LOGON-USER 05 LOGON-GROUP 05 LOGON-ACCOUNT 05 PASSWORD 05 COMAREA OCCURS 05 RETURN-STATUS PIC PIC PIC PIC PIC PIC 100 PIC X(30). X(30). X(8). X(8). X(8). X(8). TIMES PIC S9(4) COMP. S9(4) COMP. COBOL Parameter The gure on the next page shows a COBOL sample program.
$CONTROL DYNAMIC IDENTIFICATION DIVISION PROGRAM-ID. BRWPASSEXIT. AUTHOR. DATE-WRITTEN. DATE-COMPILED. ENVIRONMENT DIVISION. DATA DIVISION. LINKAGE SECTION. 01 COM-RECORD. 05 LOCATION PIC X(30). 05 REPORTNAME PIC X(30). 05 LOGON-USER PIC X(8). 05 LOGON-GROUP PIC X(8). 05 LOGON-ACCOUNT PIC X(8). 05 PASSWORD PIC X(8). 05 COMAREA OCCURS 100 TIMES PIC S9(4) COMP. 05 RETURN STATUS PIC S9(4) COMP. PROCEDURE DIVISION USING COM-RECORD. P1 IF LOCATION = "ADB" THEN MOVE "ADB.ADBGROUP.
18 Data Dictionaries Creating an HP ALLBASE/BRW dictionary le (BRWDIC) Part IV: Administrative Tasks Data Dictionaries 18-1
At a Glance Note HP ALLBASE/BRW requires that the structure of the data (for example, in databases and les) you use when you de ne reports must be described in a data dictionary. HP ALLBASE/BRW uses the information in the data dictionary to construct its own dictionary le, called BRWDIC. The BRWDIC le contains all the data-description information HP ALLBASE/BRW needs to create, compile, and execute reports. A data dictionary is not required for HP ALLBASE/SQL data.
Using Data Dictionaries Part IV: Administrative Tasks Data Dictionaries 18-3
This chapter covers 18-4 Data Dictionaries HP ALLBASE/BRW dictionary le (BRWDIC) HP Dictionary/3000 HP System Dictionary HP Application Dictionary HP Information Access data dictionary user-generated BRWDIC source le changes in dictionary les Part IV: Administrative Tasks
Dictionary File (BRWDIC) HP ALLBASE/BRW Dictionary File (BRWDIC) Removing the Data Dictionary These are the major features of BRWDIC: It supports HP Dictionary/3000, HP System Dictionary, HP Application Dictionary, HP Information Access data dictionary, and user-generated dictionaries. It lets you use multiple data dictionaries in one report.
Dictionary File (BRWDIC) Using Multiple Data Dictionaries or a Central Dictionary You can combine data described in several dictionaries in a single report. This is especially useful if you want to combine data from di erent applications in one report. Typically, each application uses its own dictionary. Also, it is typical that each application uses a separate account on the system. Therefore, each account that contains databases or les used in reports must have its own BRWDIC dictionary.
Dictionary File (BRWDIC) For two or more BRWDICs to exist in the same account, they must be given di erent names, as shown in the steps below: To have two BRWDIC files in the same account 1. Create the rst BRWDIC le, using any of the dictionary generators: :RUN BRWxxx.PUB.SYS The result is a le named BRWDIC.PUB.logonaccount . 2. To create the second BRWDIC le, rst use a le equation and then run the dictionary generator: :FILE BRWDIC.PUB = BRWDICx.PUB :RUN BRWDICxxxx.PUB.
HP Dictionary/3000 HP Dictionary/3000 Setting up HP Dictionary/3000 See the HP Dictionary/3000 manual for a full explanation of HP Dictionary/3000. Use the following programs to set up HP Dictionary/3000: DICTINIT For each dictionary you want, run this program to create an empty dictionary. DICTDBD This program loads the data dictionary with the item and dataset de nitions of an IMAGE database. It de nes every item, but does not know about decimal places or date and time items.
HP Dictionary/3000 Elements/Items For the element (item) name, follow these rules: Use a maximum of 20 characters for the element name, unless the report is to run with BRW/V, in which case you can use a maximum of only 16. Use a letter as the rst character of the element name. Do not include spaces, dots, commas and parentheses in the element name. For existing elements whose names con ict with the above rules, de ne alias names. If an element has an alias name, that name is used by HP ALLBASE/BRW.
HP Dictionary/3000 This table illustrates the HP Dictionary/3000 item types, and how they are handled by HP ALLBASE/BRW. A number su x equals the number of words. For example I2 equals a 2-word (4-byte) integer.
HP Dictionary/3000 Dictionary/3000 DATE-ITEM Table (DISPLAY and INTEGER Format) Dictionary/3000 Long Name Length Required (1 wd = 16 bits) Base Type Example: (Date is 04/28/92) !DATE-DMY-D !DATE-DMYY-D !DATE-MDY-D !DATE-MDYY-D !DATE-YMD-D !DATE-YYMD-D !DATE-DDY-D !DATE-DDYY-D !DATE-YDD-D !DATE-YYDD-D 8 Bytes 10 Bytes 8 Bytes 10 Bytes 8 Bytes 10 Bytes 6 Bytes 8 Bytes 6 Bytes 8 Bytes X X X X X X X X X X 28/04/92 28/04/1992 04/28/92 04/28/1992 92/04/28 1992/04/28 118/92 118/1992 92/118 1992/118 !DATE-
HP Dictionary/3000 Dictionary/3000 DATE-ITEM Table (NUMERIC and ALPHANUMERIC Format) Dictionary/3000 Long Name Length Required (1 wd = 16 bits) Base Type Example: (Date is 04/28/92) !DATE-DMY !DATE-DMYY !DATE-MDY !DATE-MDYY !DATE-YMD !DATE-YYMD !DATE-DDY !DATE-DDYY !DATE-YDD !DATE-YYDD 6 Bytes 8 Bytes 6 Bytes 8 Bytes 6 Bytes 8 Bytes 5 Bytes 7 Bytes 5 Bytes 7 Bytes X or 9 X or 9 X or 9 X or 9 X or 9 X or 9 X or 9 X or 9 X or 9 X or 9 280492 28041992 042892 04281992 920428 19920428 11892 1181992 92118
HP Dictionary/3000 Notes: The EXAMPLE column shows how the date and time are held. There is no EXAMPLE for integers because they are held as 16-bit words. When held as integers, date or time item types have the same Long Names as when held in numeric or alphanumeric form (X or 9). However, the base type must be an integer (I, J, or K). Where no year is held, (for example, in date item !DATE-DM), the year is assumed to be the current year. HP ALLBASE/BRW adds the current year when the date is accessed.
HP Dictionary/3000 Datasets Datasets are de ned as les of type DETL, MAST or AUTO. When specifying a le name, follow these rules: The le name must be the real name of the IMAGE dataset. Do not include spaces, points, commas and parentheses in the le name. File/element relations describe which elements are in the dataset. Those elements which are search elements can also be de ned. If a search element consists of several components, it is called a composite search element.
HP Dictionary/3000 KSAM Files KSAM les are de ned as les of type KSAM, where the le name must be the le format name in the data dictionary without group and account. File/element relations describe which elements are in the KSAM le. HP ALLBASE/BRW only supports KSAM les with xed length records. If no long name is supplied, HP ALLBASE/BRW assumes that a real number contained in a KSAM le is in IEEE format. Locations Locations are de ned as groups in the account of the data dictionary and are optional.
HP Dictionary/3000 BRWD3000 opens the HP Dictionary/3000 dictionary with the logical name DICT.PUB, and creates the BRWDIC le with the logical name BRWDIC.PUB. If you want to use le equations on either of these les, remember to use the logical names. If the program encounters an end of le in the dictionary le, you can increase the le size of the dictionary le by entering a FILE command, as shown here: :FILE BRWDIC.PUB; DISC=5000 Then, run the BRWD3000 program again.
HP System Dictionary HP System Dictionary The following programs are used to set up HP System Dictionary: SDINIT The System Dictionary initialization utility. For each dictionary you want, run this program to create an empty dictionary. SDDBD Run this program to load the IMAGE database root le. SDDBD loads the data dictionary with the element and dataset de nitions of an IMAGE database. SDMAIN The System Dictionary program used to create, maintain, and report entries.
HP System Dictionary If you assign the attribute UNIQUE to this relationship, it will indicate that all the SEARCH-ELEMENT chains in a detail dataset have length of 1. That is, there is only one element in each chain. This saves you having to read for the end-of-chain marker for SEARCH-ELEMENT chains with only one element. ELEMENT-SUBTYPE attributes You can add an extra attribute, ELEMENT-SUBTYPE, wherever you can use the attribute ELEMENT-TYPE.
HP System Dictionary Terminology The terminology in HP System Dictionary and HP ALLBASE/BRW is not always the same.
HP System Dictionary Items/Elements Items are de ned as entities of type ELEMENT in HP System Dictionary. When de ning the element name in HP System Dictionary, follow these rules: Use a maximum of 20 characters for the element name; however, only use a maximum of 16 characters if the report is to run with HP BRW/V. (HP System Dictionary allows 32 characters.) Use a letter as the rst character of the element name. Do not include spaces, dots, commas and paren theses in the element name.
HP System Dictionary HP ALLBASE/BRW supports the following element data types: HP ALLBASE/BRW supports the following element data types: Strings Numeric elements Maximum length of 255 characters Maximum of 13 integer digits and 6 decimal places Packed numeric elements Maximum of 13 integer digits and 6 decimal places, with Trailing Overpunch or No Sign Zoned numeric elements Maximum of 13 in teger digits and 6 decimal places, with Trailing Overpunch or No Sign 16-Bit integers 32-Bit integers 64-Bit i
HP System Dictionary System Dictionary Element Types Dictionary Element Type Decimals I, I2, J, J2, K, K2 0 I, I2, J, J2, K, K2 >0 BRW Type Remarks I (integer) N (numeric) I4, J4, K4 N (numeric) P, Z, 9, Z+, D, P+ N (numeric) Maximum 13 integer digits and 6 decimals Z (SIGN ' ' or sign = 'TO') N (numeric) Trailing Overpunch assumed Z (sign = 'TS' 'LS' or 'LO') R (real) R2, R4, E2, E4 R (real) U, X S (string) Maximum length 255 bytes X, 9, I, J, K D (date) ELEMENT-SUBTYPE is a date
HP System Dictionary System Dictionary DATE-ITEM Table (DISPLAY and INTEGER Format) Example: System Dictionary Length Required Element Type (Date is 04/28/92) ELEMENT-SUBTYPE (1 word = 16 bits) Part IV: Administrative Tasks DMY-D DMYY-D MDY-D MDYY-D YMD-D YYMD-D DDY-D DDYY-D YDD-D YYDD-D 8 Bytes 10 Bytes 8 Bytes 10 Bytes 8 Bytes 10 Bytes 6 Bytes 8 Bytes 6 Bytes 8 Bytes X X X X X X X X X X 28/04/92 28/04/1992 04/28/92 04/28/1992 92/04/28 1992/04/28 118/92 118/1992 92/118 1992/118 DM-D MD-D MY-D MYY-D
HP System Dictionary System Dictionary DATE-ITEM Table (DISPLAY and INTEGER Format) (continued) Example: System Dictionary Length Required Element Type (Date is 04/28/92) ELEMENT-SUBTYPE (1 word = 16 bits) DM MD DD MY MYY YM YYM 2 Words 2 Words 1 Word 2 Words 2 Words 2 Words 2 Words I, J, or K I, J, or K I, J, or K I, J, or K I, J, or K I, J, or K I, J, or K CALENDAR JULIAN 2 Bytes 4 Bytes I, J, or K I, J, or K System Dictionary DATE-ITEM Table (NUMERIC and ALPHANUMERIC Format) Example: System Dicti
HP System Dictionary System Dictionary TIME-ITEM Table (All Formats) System Dictionary Length Required Element Type Example: (Time is 15:43:24) ELEMENT-SUBTYPE Notes: HM24 HMS24 HM12 4 Bytes 6 Bytes 5 Bytes X or 9 X or 9 X 1543 154324 0343P HM24-D HMS24-D HM12-D 5 Bytes 8 Bytes 6 Bytes X X X 15:43 15:43:24 03:43P HM24 HMS24 2 Words 2 Words I, J, or K I, J, or K CLOCK 4 Bytes I, J, or K Notes for the previous tables: The EXAMPLE column shows how the date and time are held.
HP System Dictionary The BYTE-LENGTH is always 2 and the COUNT is 1, 2, or 3 depending on the number of words. (The above item is actually held as 3 * 16-bit integers). A JULIAN date is a 4-byte integer. The Julian date is calculated using the CACM Algorithm 199. CALEND and CLOCK are in the format returned by the corresponding HP 3000 Intrinsics. They must be de ned as 2- and 4-byte integers respectively.
HP System Dictionary Datasets Datasets are de ned as entities of type IMAGE- DATASET with attribute image-dataset-type = DETAIL, AUTOMATIC, or MANUAL. Elements that are related to the rst primary record (or, if no primary record exists, elements that are related to the rst record) become the elements of the dataset. When specifying an IMAGE-DATASET name, follow these rules: The le name must be the real name of the IMAGE dataset. Do not include spaces, points, commas and parentheses in the le name.
HP System Dictionary KSAM Files KSAM les are de ned as entities of type KSAMFILE, where the le name must be the le format name in the data dictionary without group and account. The relationship KSAMFILE contains RECORD describes the record format for the KSAM le. HP ALLBASE/BRW only supports KSAM les with xed-length records. Elements that are related to the rst primary record (or, if no primary record exists, elements that are related to the rst record) become the elements of the KSAM le.
HP System Dictionary Preparing a BRWDIC Dictionary File With HP System Dictionary BRWSD prepares a BRWDIC dictionary le from an HP System Dictionary-type dictionary le. To create BRWDIC with the System Dictionary 1. Run BRWSD: :RUN BRWSD.PUB.SYS 2. Respond to these prompts. A description of the choices follows these steps.
HP System Dictionary If the program encounters an end of le in the dictionary le, you can increase the le size of the dictionary le by entering a FILE command, as shown here: :FILE BRWDIC.PUB; DISC=5000 Then, run the BRWSD program again.
HP Application Dictionary HP Application Dictionary HP Application Dictionary is the data dictionary used with HP-customizable applications, including HP Financial Accounting, HP Financial Budgeting, Materials Management/3000, Production Management/3000, and Maintenance Management/3000. Refer to the appropriate application reference manual for more details about customizable applications.
HP Application Dictionary Note Avoid name changes in the HP Application Dictionary. HP ALLBASE/BRW assumes that a eld with a changed name is a new eld. For example, if you change the name of eld CUSTOMER-NO to KUNDEN-NUMMER, (to assist your German branch), HP Application Dictionary recognizes that the two elds are identical. However, HP ALLBASE/BRW loads the eld into the BRWDIC as the new eld name, KUNDEN-NUMMER.
HP Application Dictionary Array Fields (ARRAYDEF) Since HP Application Dictionary is not capable of de ning arrays, they must be de ned as single elds. HP ALLBASE/BRW can convert these single elds into arrays for more e cient use in reports. Array elds are described in a KSAM le called ARRAYDEF, which is read by the BRWAPPD program to identify array elds for use by BRW/XL. An ARRAYDEF le is required, whether or not you have arrays to de ne.
HP Application Dictionary Building the ARRAYDEF File To build the ARRAYDEF file (if necessary) 1. If you have arrays to de ne, create an editor le (ARRAYTMP) with the following format: a. Field Name: 16 characters These rst 16 characters form a KSAM key. b. 2 blank spaces c. Number of elements in array: unsigned number d. 2 blank spaces These 2 blank spaces are required to separater the number of elements from the array name. e.
HP Application Dictionary :FCOPY FROM=ARRAYTMP;TO=ARRAYDEF.PUB Note Synonyms (SYNDEF) If you don't have arrays to de ne, you only need to build the empty les using step 3. Synonyms are elds which appear more than once in a dataset because they are components of more than one composite search eld. The HP Application Dictionary requires that all elds in a format, including components of group elds, have unique names. This means that each appearance of a synonym eld must have a di erent name.
HP Application Dictionary Building the SYNDEF File A le called SYNDEF.PUB.application account keeps track of these synonyms. This le is checked when BRWDIC is created and only the synonym (or primary eld) is used.
HP Application Dictionary Preparing a BRWDIC Dictionary File With HP Application Dictionary BRWAPPD prepares a BRWDIC dictionary le from an HP Application Dictionary data dictionary. To create the BRWDIC with BRWAPPD 1. Run BRWAPPD: :RUN BRWAPPD.PUB.SYS 2. Respond to the following prompts: APPDIC Password: not displayed Include Group Items (N/Y) ? Include Screen Formats (N/Y) ? If you press 4Return5 for the password prompt, the program will terminate.
HP Information Access Data Dictionary HP Information Access Data Dictionary HP Information Access Server is the HP 3000 side of HP Information Access, a data-retrieval system that gives users access to PC databases and HP 3000 data on the host HP 3000. If you have Access Server on your system already, you can use the Information Access data dictionary to create the HP ALLBASE/BRW dictionary le, BRWDIC.
HP Information Access Data Dictionary c. You purchase HP ALLBASE/BRW and HP Information Access together. You should rst set up the HP Information Access data dictionary as described in the HP NewWave O ce: Information Access Server Database Administration manual, and then construct the BRWDIC le as descibed in the next section. Terminology You should note that not all terms used in the HP Information Access data dictionary mean the same thing in HP ALLBASE/BRW.
HP Information Access Data Dictionary The following table lists the HP Information Access Data Dictionary element-types and describes how they are handled by HP ALLBASE/BRW.
HP Information Access Data Dictionary De ne the Date and Time items in the ACCSDEF le as shown in the following example: Example: -------: Comment : : The entries of the example are taken from TOYDB ; ; SUBBEGIN TABLE order-details sh-date-year = SUB (shipment-date,1,C2) sh-date-month = SUB (shipment-date,3,C2) sh-date-day = SUB (shipment-date,5,C2) TABLE orders order-date-year = SUB (order-date,1,2) order-date-month = SUB (order-date,3,5) order-date-day = SUB (order-date,5,2) SUBEND EDITBEGIN TABLE orde
HP Information Access Data Dictionary in the dataset ORDERS Here is an outline of the steps used to change the items: a. Create three sub-items (children), each with a two-digit value for year, month and day. b. Change the type of the parent item (currently C for character) to date. c. A short one-line explanation of what has been changed in the item appears below the name of that item. Syntax for ACCSDEF file 18-42 Data Dictionaries The gure on the next page illustrates the syntax for the ACCSDEF le.
HP Information Access Data Dictionary ACCSDEF syntax Part IV: Administrative Tasks Data Dictionaries 18-43
HP Information Access Data Dictionary The gure on the previous page shows the syntax for ACCSDEF. The rules below and also the following sections describe this syntax in greater detail. Comments You must enclose any comments in semicolons (;) ;COMMENT; and you may not type a comment within a comment. Sub-item Part The Sub-item part implements the SUB function used in the View Tables in the HP Information Access data dictionary.
HP Information Access Data Dictionary A valid shipment date could be: "880309" SHIP-DATE-YEAR SHIP-DATE-MONTH SHIP-DATE-DAY = SUB (SHIPMENT-DATE,1,C2) = SUB (SHIPMENT-DATE,3,C2) = SUB (SHIPMENT-DATE,5,C2) This is then converted to: SHIP-DATE-YEAR = "88" SHIP-DATE-MONTH = "03" SHIP-DATE-DAY = "09" You can now access these subitems with HP ALLBASE/BRW.
HP Information Access Data Dictionary DATE-ITEM Table (DISPLAY and INTEGER Format) Dictionary/3000 Length Required Base Type Example: (Date is 04/28/92) Long Name (1 wd = 16 bits) 18-46 Data Dictionaries DATE-DMY-D DATE-DMYY-D DATE-MDY-D DATE-MDYY-D DATE-YMD-D DATE-YYMD-D DATE-DDY-D DATE-DDYY-D DATE-YDD-D DATE-YYDD-D 8 Bytes 10 Bytes 8 Bytes 10 Bytes 8 Bytes 10 Bytes 6 Bytes 8 Bytes 6 Bytes 8 Bytes C C C C C C C C C C 28/04/92 28/04/1992 04/28/92 04/28/1992 92/04/28 1992/04/28 118/92 118/1992 92/118
HP Information Access Data Dictionary DATE-ITEM Table (DISPLAY and INTEGER Format) (continued) Dictionary/3000 Long Name Length Required Base Type Example: (Date is 04/28/92) (1 wd = 16 bits) DATE-DM-I DATE-MD-I DATE-DD-I DATE-MY-I DATE-MYY-I DATE-YM-I DATE-YYM-I 2 Words 2 Words 1 Word 2 Words 2 Words 2 Words 2 Words I, L I, L I, L I, L I, L I, L I, L DATE-CALENDAR DATE-JULIAN 2 Bytes 4 Bytes I, L I, L DATE-ITEM Table (NUMERIC and ALPHANUMERIC Format) Date/Time Keyword Example: Length Required HD
HP Information Access Data Dictionary TIME-ITEM Table (All Formats) Length Required HDPDIC Type Required Date/Time Keyword Example: (Time is 15:43:24) TIME-HM24-I TIME-HMS24-I TIME-HM12-I 4 Bytes 6 Bytes 5 Bytes C C C 1543 154324 0343P TIME-HM24-D TIME-HMS24-D TIME-HM12-D 5 Bytes 8 Bytes 6 Bytes C C C 15:43 15:43:24 03:43P TIME-HM24-I TIME-HMS24-I 2 Words 2 Words I, L I, L TIME-CLOCK 4 Bytes I, L Preparing a BRWDIC with HP Information Access Data Dictionary After you have speci ed which i
HP Information Access Data Dictionary 3. Type the dictionary password and press 4Return5. If you press 4Return5 without typing in anything, the BRWACCSD program terminates. The password does not appear on the screen when you type it. 4. Respond to the next prompt: Dictionary Password : not displayed Use Parent Items (N/Y): _ a. Type Y (yes) if you wish to include (use) the parent items in the dictionary le, BRWDIC. b. Type N (no) or press 4Return5 if you do not wish to include them.
User-Generated Dictionary Source File User-Generated Dictionary Source File You can generate a dictionary source- le that is a simple MPE le containing a list of \statements" you specify according to your needs. These are later converted into the HP ALLBASE/BRW BRWDIC by the program BRWGEND. Typically, you include the statements in a text le | an unnumbered MPE le that you create with a text editor.
User-Generated Dictionary Source File The next example, shown below and continued on the next page, illustrates a user-generated dictionary:source le, using the sample database TOYDB.
User-Generated Dictionary Source File (Continued . . .
User-Generated Dictionary Source File Statements The statements in the user-generated source le consist of three parts, in the following order: Statement type Description A keyword is a word de ned by the system. These words can be used as le names only if a percent sign (%) in inserted before the word (i.e. % le) A name must: Keyword Name Be 1 to 20 characters long.
User-Generated Dictionary Source File The FILE Statement With the FILE statement you specify a format and its type. A format can be either one of the following: An MPE or a KSAM le. A dataset: IMAGE MASTER IMAGE AUTOMATIC MASTER IMAGE DETAIL The FILE statement consists of the keyword FILE, followed by the name, the type, and the OPEN option (this is optional).
User-Generated Dictionary Source File In the example above, the element's type is character (X) which is a customer's name thirty characters long, and the quantity is numeric, a number ve digits long. Additionally, if the element's type is numeric, you can also specify the number of decimal positions. In the example above, turnover is a numeric element seventeen digits long, including two digits to the right of the decimal point.
User-Generated Dictionary Source File Element Type Item Type X 9 9 9 9 9 9 9 9 9 9 CHARACTER LOGICAL INTEGER DOUBLE BINARY4 REAL LONG PACKED ZONED DECIMAL FPDECIMAL2 9 FPDECIMAL4 Length (words) (1 wd - 16 bits) BRW Type 2 S I I I I R R N N N N 4 N 1 1 2 4 2 4 DATE date-types TIME time-types D T Remarks Max 255 Bytes logical packed zoned num, display short oating point decimal HP Business Basic oating point decimal listed below listed below For integer, decimal, packed, or zoned data, you c
User-Generated Dictionary Source File DATE TYPES Table Date/Time Keyword Required Length (1wd = 16 bits) Example: (Date is Required 04/28/92) Element Type DATE-DMY-D DATE-DMYY-D DATE-MDY-D DATE-MDYY-D DATE-YMD-D DATE-YYMD-D DATE-DDY-D DATE-DDYY-D DATE-YDD-D DATE-YYDD-D 8 Bytes 10 Bytes 8 Bytes 10 Bytes 8 Bytes 10 Bytes 6 Bytes 8 Bytes 6 Bytes 8 Bytes DATE DATE DATE DATE DATE DATE DATE DATE DATE DATE 28/04/92 28/04/1992 04/28/92 04/28/1992 92/04/28 1992/04/28 118/92 118/1992 92/118 1992/118 DATE-DM-D
User-Generated Dictionary Source File ACCSDEF DATE-ITEM Table (NUMERIC and ALPHANUMERIC Format) Date Keyword Required Length (1wd = 16 bits) Example: (Date is Required 04/28/92) Element Type DATE-DMY DATE-DMYY DATE-MDY DATE-MDYY DATE-YMD DATE-YYMD DATE-DDY DATE-DDYY DATE-YDD DATE-YYDD 6 Bytes 8 Bytes 6 Bytes 8 Bytes 6 Bytes 8 Bytes 5 Bytes 7 Bytes 5 Bytes 7 Bytes DATE DATE DATE DATE DATE DATE DATE DATE DATE DATE 280492 28041992 042892 04281992 920428 19920428 11892 1181992 92118 1992118 DATE-DM DATE
User-Generated Dictionary Source File You can specify the following three item options when you are de ning an item itself: UNIQUE KEY KEY OCCURS n If the record is either an IMAGE dataset or a KSAM le, you can label an item as a KEY. If the item is an array, you can specify the number of occurrences in this array. ITEM turnover PACKED SIZE 8 OCCURS 12 (turnover is an array with 12 occurrences representing the turnover of every month of a year.
User-Generated Dictionary Source File The BUILD Statement With the BUILD statement you give BRWGEND instructions to build the HP ALLBASE/BRW dictionary le, BRWDIC, from the dictionary source- le which you have generated, (in our examples, the GENDEF le). The BUILD statement is the last one in the process of generating the dictionary source le and consists only of the keyword BUILD. The BRWDIC can only be built if no error occurs during the analysis of the le containing the statements you created.
User-Generated Dictionary Source File Part IV: Administrative Tasks Data Dictionaries 18-61
User-Generated Dictionary Source File 18-62 Data Dictionaries Part IV: Administrative Tasks
User-Generated Dictionary Source File Creating A BRWDIC Dictionary File With the User-Generated Dictionary Once you have generated the source le, you can create the HP ALLBASE/BRW dictionary le, BRWDIC. To create a BRWDIC with a User-Generated Dictionary 1. Run the BRWGEND program: : RUN BRWGEND.PUB.SYS 2.
Changes in Dictionary Files Changes in Dictionary Files Modifying BRWDIC This section describes the consequences when a data dictionary is modi ed. You cannot directly modify a BRWDIC dictionary le. If a data dictionary has been modi ed, you must run the appropriate program (BRWD3000, BRWAPPD, or BRWSD) to create a new BRWDIC le from the modi ed data dictionary. The BRWDIC le is time-stamped upon creation. There fore, when a new BRWDIC is created, a new time stamp is added.
Automatic Upgrade of the Specification File HP ALLBASE/BRW checks the creation time-stamp of the BRWDIC dictionary le used for the report. If the dictionary le was prepared after the last modi cation of the report speci cation le, HP ALLBASE/BRW automatically upgrades the le while loading the report. The changes made during this upgrade are listed on the Select Report screen. You can print out the listing by pressing the terminal 4Print5 key.
19 Performance Considerations Improve execution performance for your reports Part IV: Administrative Tasks Performance Considerations 19-1
At a Glance For every report produced with HP ALLBASE/BRW, report execution performance is in uenced by the following basic factors: the amount of data to be printed in the report the amount of data in the datasets, les or HP SQL tables accessed by the report how data from datasets, les or HP ALLBASE/SQL tables is combined (joined or merged) calculations and formatting performed during report execution In addition to these basic factors, you can a ect execution performance in other ways without impairing t
Data Access Methods Influencing Performance What Your System Can Do For Performance Proper system con guration can have an important impact on all database and I/O on your system. Consult your system reference manuals for information about system con guration. Datasets and les spread over several disks can improve performance of a report when it accesses multiple datasets or les at the same time, which is when you use keyed access.
Data Access Methods Data Access Methods Data Access Methods For Joins This section describes the data access methods that you can use to improve performance. The emphasis here is on access methods for reports using joined source tables. Joining datasets, les or HP ALLBASE/SQL tables means that their records are combined such that records are joined where the common items in the relation de nition have identical values. This is described in Chapter 6, De ning Data Access.
Data Access Methods direct access if the key is the item table-rec-number Keyed Access for a Joined Table Since keyed access causes random access to datasets and les, it results in good execution performance, if any of the following are true: The report selects a small amount of data out of a large amount, for example, 5,000 out of 300,000 records. The accessed datasets or les are small enough to be bu ered to a high degree by the disk caching.
Data Access Methods All temporary work les are sorted by common items in the relation de nitions. The temporary work les are joined by reading them in parallel in one pass. Serial Access Although serial access requires three passes over the data, it may be necessary if common items do not have keys.
Data Access Methods Access Sequence For Joins The sequence in which the datasets, les, or HP ALLBASE/SQL tables are accessed can also in uence the report execution performance. You should choose an access sequence that can reduce the amount of data as early as possible. See the compile listing, discussed later in this chapter, for when and which selections are performed.
Using the Compile Listing SELECT SQLA.item1 FROM SQLA; and SELECT SQLB.item2 from SQLB; Let HP ALLBASE/BRW join this information to the non-SQL data in the report. When combining HP ALLBASE/SQL data with HP IMAGE/3000 or KSAM data, it is usually best to use the access sequence numbers in the Tune Access screen to load the HP ALLBASE/SQL tables rst.
Using the Compile Listing the table calculated item ordered-amount with the following formula: PRODUCTS.PRICE * ORDER-DETAILS.
Using the Compile Listing the result of the join. The join is performed by reading the dataset ORDER-DETAILS serially and the dataset PRODUCTS by the key product-no . The TABLE CALCULATIONS AND SELECTIONS section shows what is performed when each data set is read: When the dataset ORDER-DETAILS is read, the following is performed: MONTH_OF (ORDER-DETAILS.
Using the Compile Listing The compiler generates the following compile listing: D A T A A C C E S S S T R U C T U R E ========================================= PRINT-TABLE (WORK003) join of: -------------------------------WORK001 PRODUCTS, TOYDB.PUB serial WORK002 ORDER-DETIALS, TOYDB.
Using the Compile Listing Example 3: Accessing HP ALLBASE/SQL Below is the Tune Access screen from \Combining Data from Di erent Sources" of Chapter 6. In this example, the two HP ALLBASE/SQL tables default to the same access sequence number. d a c b The compile listing on the next page shows that when the access sequence number is the same, only one HP ALLBASE/SQL query is generated for both tables.
Using the Compile Listing D A T A A C C E S S S T R U C T U R E ========================================= ROOT (WORK001) join of: ----------------------CUSTOMERS,EXPLSQL.EXPLDB.TESTSQL INVOICES,EXPLSQL.EXPLDB.TESTSQL ORDERS,EXPLDB.EXPLDB.TESTSQL T A B L E SQL SQL key: ORDER-NO C A L C U L A T I O N S A N D S E L E C T I O N S ================================================================= ROOT (WORK001) CUSTOMERS,EXPLSQL.EXPLDB.TESTSQL INVOICES,EXPLSQL.EXPLDB.TESTSQL ORDERS,EXPLDB.EXPLDB.
Execution Statistics Execution Statistics During report execution, HP ALLBASE/BRW optionally provides statistics on the amount of time each part of the report needed (CPU and elapsed time), and how many records have been read, written or sorted. The following are the associated execution statistics for the compile listings for Examples 1, 2, and 3.
Execution Statistics Example 2: Statistics of Serial Access Access Block 1: $records -------30 30 30 cpu-sec ------0.279 0.336 0.942 0.966 ======= 2.523 elapsed-sec ----------0.380 0.681 3.473 1.450 ========== 5.984 $records -------read-mpe serial ORDER-DETAILS, TOYDB 98 write WORK002 23 sort WORK002 23 process cpu-sec ------0.500 0.284 1.065 0.813 ======= 2.662 elapsed-sec ----------0.657 0.734 2.665 0.907 ========== 4.963 $records -------30 23 23 23 cpu-sec ------0.265 0.358 0.315 1.150 0.
Execution Statistics Access Block 1: #records cpu-sec elapsed-sec --------- --------- ----------- 4 0.686 0.815 read-sql QUERY 1,EXPLSQL read-dset chain ORDERS,EXPLDB 4 0.031 0.039 write WORK001 4 0.079 0.272 1.556 3.469 ======= ========== 2.352 4.595 process time totals for block (0 segment loads for block) Access Block 2 read-mpe serial WORK001 print TESTLIST process #records cpu-sec elapsed-sec --------- -------- ----------- 4 0.147 0.146 4 0.109 0.500 0.
Effective Calculations Effective Calculations This section discusses some rules for e ective calculations, especially with more complicated reports. For more details about Calculations, refer to Chapter 15. The Calculation Language for HP ALLBASE/BRW is expressionoriented. That is, every formula has a resulting value | the value of the calculated item or function. Programming languages like COBOL or PASCAL are statementoriented. You specify both the control ow and the data ow explicitly.
Effective Calculations these dependencies and automatically generates code such that A is calculated before E. If A is used in more than one other calculated item, for example in E, F and G, A is only calculated once before E, F and G. These items then only use the result value of A. This means that HP ALLBASE/BRW generates very e ective code if calculated items are used by other calculated items. HP ALLBASE/BRW does not permit cycles or recursion in calculations.
Effective Calculations function and arguments ADD (a, b) SUB (a, b) with the formula: a+b a-b Then you could use the two functions in a simpler way with the calculated items: item name with the formula: SUM ADD (k, l) DIFFERENCE SUB (m, n) Function X is also not e cient in terms of performance, since the IF THEN ELSE must be evaluated each time the function is calculated. You can avoid this by using the functions ADD and SUB.
Effective Calculations The most important points are listed below: RULE 1 Avoid repeating identical blocks of code. In a programming language, you would not write code with a block that is identical, like the following: a := c * (d - e + 4 * (f / (100 - g))); b := h * (d - e + 4 * (f / (100 - g))); Instead, you would use a temporary variable: i := d - e + 4 * (f / (100 - g)); a := c * i; b := h * i; You can also use temporary variables in HP ALLBASE/BRW.
Effective Calculations RULE 2 Try to use calculated items instead of functions. Sometimes structuring may not be so easy.
Table Calculated Items or Layout Calculated Items? Table Calculated Items or Layout Calculated Items? Optimizing Table Calculated Items You should consider the performance di erences between table calculated items and layout calculated items if (BUT ONLY IF) you have many calculations in a report. Table calculated items are calculated as early as possible, that is, as soon as the items used in their formulas become available during the data access phases of a report.
in HPFA (HP Financial Accounting) the ACCOUNT-MASTER dataset contains about a hundred items that hold the account budget, debit and credit values for 28 periods. In most HPFA reports only the account budget and the account balance for the current period and their corresponding cumulative values are printed. After calculating the four table calculated items, HP ALLBASE/BRW does not need the values of the hundred items and so throws them away.
20 Intrinsics The description and operation of HP ALLBASE/BRW intrinsics Part IV: Administrative Tasks Intrinsics 20-1
At a Glance This chapter covers 20-2 Intrinsics You can use the intrinsics described in this chapter to build HP ALLBASE/BRW reports into your application programs. Your application programs can initiate and control the execution of reports. The two types of intrinsics are called Request Report Intrinsics and Execution Report intrinsics respectively.
Refreshing the Terminal Refreshing the Terminal Character Mode Block Mode Part IV: Administrative Tasks The BRWSTARTREQUEST intrinsic calls BRWSTART.PUB.SYS, which switches your terminal to block-mode. Your application program will have to refresh the terminal after control has been passed from BRWSTART back to your application. The method used to refresh your terminal varies depending upon whether your application program uses block mode or character mode.
Refreshing the Terminal The following PASCAL program fragment gives an example of the process for character mode. TYPE fmt_vp_comarea = RECORD cstatus language comarea_len filler . . : : : : short_int; short_int; short_int; ARRAY [1..50] OF short_int; END; {RECORD} VAR vp_comarea : fmt_vp_comarea; terminal ; PACKED ARRAY [1..
Refreshing the Terminal The following PASCAL program fragment gives an example of the process for block mode. TYPE fmt_vp_comarea = RECORD cstatus language comarea_len . . nfname . : short_int; : short_int; : short_int; : PACKED ARRAY [1..16] OF CHAR; END; {RECORD} VAR vp_comarea : fmt_vp_comarea; BEGIN BRWSTARTREQUEST (REQUEST_COMAREA, parm_defaults); vp_comarea.nfname := '$REFRESH'; VGETNEXTFORM (vp_comarea); { Then re-display the function key labels } VSHOWFORM (vp_comarea) END.
Request Report Intrinsics Request Report Intrinsics HP ALLBASE/BRW includes a program called BRWSTART which allows you to request HP ALLBASE/BRW or report job les, using the screens set up by HP ALLBASE/BRW for the purpose. You can use the request report intrinsics described here to call BRWSTART and therefore to access the Request Report, Select Report, and Online Review screens from within your application program. The following diagram shows how the request report intrinsics operate.
Request Report Intrinsics This table is a quick overview of the request report intrinsics: Intrinsic Name Description BRWINITREQUEST This intrinsic initializes the parameter REQUEST COMAREA. Parameter REQUEST COMAREA is used by all the request report intrinsics. BRWSTARTREQUEST This intrinsic creates a son process and runs the BRWSTART program. It can also pass information about the default values of selections and parameters.
Using BRWINITREQUEST BRWINITREQUEST Call BRWINITREQUEST like this: BRWINITREQUEST (REQUEST_COMAREA) This intrinsic initializes the common area (REQUEST COMAREA) prior to the rst call to BRWSTARTREQUEST. You must set the parameter item comarea len to 100 before calling BRWSTARTREQUEST.
Using BRWINITREQUEST comarea len : 2 bytes. (Set to 100); You must set comarea len to 100 before calling BRWINITREQUEST. Comarea len is an integer containing the length in bytes of the allocated area (that is, the REQUEST COMAREA parameter) that can be used by the HP ALLBASE/BRW Request Report intrinsics. exec filename : 36 bytes; Byte array containing the name of the Report or the report job le.
BRWSTARTREQUEST BRWSTARTREQUEST Call BRWSTARTREQUEST like this: BRWSTARTREQUEST (REQUEST_COMAREA, parm_defaults) This intrinsic starts the BRWSTART program. REQUEST_COMAREA REQUEST COMAREA is a 100-byte array with the following structure: return_status error_parm comarea_len exec_filename start_screen listf_id listf_group process_info : : : : : : : : 2 2 2 36 2 8 8 40 byte integer; byte integer; byte integer; bytes; byte integer; bytes; bytes; bytes.
BRWSTARTREQUEST start_screen = 2 With the value set at 2, the Online Review screen is called rst. No other screens can be called from the Online Review screen. The illustration below shows the start screen parameter and where you enter the HP ALLBASE/BRW screens for each parameter value. listf id: file or fileset name Set to the le designator of the reports to be listed if the rst screen is the Select Report screen.
BRWSTARTREQUEST PARM_DEFAULTS PARM DEFAULTS is an integer array parameter. The array structure is: nbr_parms : 2 byte integer; Then, for each parameter parm_name default_value filler : 20 bytes; : 55 bytes; : 1 byte; The array size is calculated using the formula below. No.
BRWSTOPREQUEST BRWSTOPREQUEST Call BRWSTOPREQUEST like this: BRWSTOPREQUEST (REQUEST_COMAREA) This intrinsic stops the BRWSTART program. REQUEST_COMAREA REQUEST COMAREA is described under BRWINITREQUEST. The parameter item process info is set by prior call to BRWSTARTREQUEST. You do not have to set any parameter items If you keep the BRWSTART program alive when returning to the application program, it provides a fast switch to the BRWSTART screens for all subsequent calls to BRWSTARTREQUEST.
Execution Intrinsics Execution Intrinsics Intrinsic Name BRWOPEN BRWINFO BRWSTREAM BRWEXEC BRWCLOSE The following table gives a brief description of each HP ALLBASE/BRW intrinsic: Description The intrinsic BRWOPEN opens a le or les and returns information about the les. The les opened can be either a report execution le and the standard job le, or a report job le.
Execution Intrinsics The following diagrams illustrate how each intrinsic works.
Execution Intrinsics 20-16 Intrinsics Part IV: Administrative Tasks
BRWOPEN Use the BRWOPEN intrinsic to open an existing report execution le and the standard job le, or a report job le. The intrinsic checks to see if the le given as the call argument is a report job le or an execution le. BRWOPEN The job le (report or standard job le) is kept open for use by other intrinsics. The report execution le is closed, and all the information it contains that will be needed by the other intrinsics is kept in the BRW COMEREA eld.
BRWINFO Use this intrinsic to get information about the parameters in a report or report job le. The information necessary to access such les will have been returned in the parameter BRW COMAREA by your previous call to BRWOPEN. The BRWINFO intrinsic will return information on both HP ALLBASE/BRW Standard Parameters (such as $USER, $USERPASS) and user-de ned parameters.
BRWINFO BRW PARAMETERS array is lled with information about each parameter. Each item of the parameter BRW PARAMETERS is discussed in detail below. max num parms : 2 bytes; This item is an integer and is REQUIRED by the intrinsic.
BRWINFO result length : 2 bytes If the result is of type String, this is an integer showing the length of the string. parm_mode : 2 bytes; Integer that indicates whether the parameter is Required or Frozen. 0 = Parameter is neither Required nor Frozen 1 = The parameter value is Frozen (cannot be altered) 2 = A parameter value is Required upshift : 2 bytes; Integer that indicates whether any letters in the parameter should be shifted to upper-case.
BRWINFO BRWSTREAM Use this intrinsic to stream a batch report job. The intrinsic will pass a value for each parameter in BRW PARAMETERS to the appropriate parameter name in the job. If the job is streamed successfully, the job number is returned in error parm. If not, the appropriate error number is returned (see Error Handling). Call BRWSTREAM like this: BRWSTREAM (brw_comarea, brw_parameters) where the call arguments have the same characteristics as in the intrinsic BRWOPEN.
BRWINFO BRWEXEC Use the BRWEXEC intrinsic to execute a report on- line. You cannot use this intrinsic with report job les. Call BRWEXEC like this: BRWEXEC (brw_comarea, brw_parameters) where the call arguments have the same characteristics as in BRWOPEN. BRW PARAMETERS contains the parameters to be passed to the son process which runs the program BRWEXEC.PUB.SYS.
BRWINFO BRWCLOSE Use the BRWCLOSE intrinsic to close the HP ALLBASE/BRW les which are opened using the BRWOPEN intrinsic. Call BRWCLOSE like this: BRWCLOSE (brw_comarea) where BRW COMAREA has the same characteristics as in the other intrinsics.
Error Handling Error Handling If an error occurs, the intrinsics return a non-zero value in the rst 2-byte integer (the return status parameter item) of the BRW COMAREA or REQUEST COMAREA parameters. The second 2-byte integer of these parameters (the error parm parameter item) provides additional error information.
Error Handling # Return Status Intrinsic a ected: Meaning BRW- BRW- BRW- BRW- BRW- BRW- BRW- BRWINIT START STOP OPEN INFO STREAM EXEC CLOSE 11 If you requested BRWOPEN to open a report X execution le, it will also try to open a standard job le. It looks for BRWJOB in this order: BRWJOB; BRWJOB.PUB; BRWJOB.PUB.SYS; BRWJxxx.PUB.SYS (xxx is the Native Language Support number if the JCW NLDATALANG is set); BRWJ000.PUB.SYS. The FSERR is returned in BRW COMAREA.ERR PARM. 12 FCLOSE failed to close the le.
Error Handling # Return Status Intrinsic a ected: Meaning BRW- BRW- BRW- BRW- BRW- BRW- BRW- BRWINIT START STOP OPEN INFO STREAM EXEC CLOSE 22 An error occurred when creating the temporary le X 23 An error occurred when closing the temporary le X 25 An error occurred when reading the temporary le X 26 A syntax error was found when substituting X TRJOB0. TRJOB0. TRJOB0. The FSERR is returned in BRW COMAREA.error parm. parameter values for parameters.
Error Handling # Return Status Intrinsic a ected: Meaning BRW- BRW- BRW- BRW- BRW- BRW- BRW- BRWINIT START STOP OPEN INFO STREAM EXEC CLOSE 34 An error occurred when calling the MPE command X STREAM via the COMMAND intrinsic. The CIERR is returned in BRW COMAREA.ERROR PARM. 35 An error occurred when calling the intrinsic X 36 BRWSTARTREQUEST tries to activate the son X 37 The son process BRWSTART terminated X CREATE PROCESS.
21 HP ALLBASE/BRW and HP BRW/V Comparing HP ALLBASE/BRW to HP BRW/V Part IV: Administrative Tasks HP ALLBASE/BRW and HP BRW/V 21-1
At a Glance HP ALLBASE/BRW is the version of Business Report Writer that creates reports that run in native mode on an HP 3000 series 9xx , the series of computers that use the MPE XL operating system. HP BRW/V is the version of Business Report Writer used on any HP 3000 running MPE V, that is the HP 3000 series 3x , 4x , 5x , 6x , or 7x . This chapter describes the relationships between HP ALLBASE/BRW and HP BRW/V.
Comparing HP ALLBASE/BRW and HP BRW/V Comparing HP ALLBASE/BRW and HP BRW/V There are three notable di erences between HP BRW/V and HP ALLBASE/BRW: HP ALLBASE/BRW allows for 20 characters in a name eld (that is, table, item), compared with 16 in HP BRW/V. HP ALLBASE/BRW supports access to HP ALLBASE/SQL tables, whereas HP BRW/V does not.
Comparing HP ALLBASE/BRW and HP BRW/V The following table shows the HP ALLBASE/BRW les names compared to the HP BRW/V les: HP BRW/V (Compatibility Mode) BRW RAPPDIC RCOMP RCONVERT RDIC3000 RDICLIST RLIST RSDEXT RSYSDIC RCONF000 REMPTY REXEC RJOB000 RSETUP RSORT RSTART RSTREAM RLIT000 RDLIT000 RFORM000 RMSG000 HP BRW/XL (Native Mode) BRWXL BRWAPPD BRWCOMP BRWCONV BRWD3000 BRWDLIST BRWLIST BRWSDEXT BRWSD BRWC000 REMPTY BRWEXEC BRWJ000 BRWSETUP BRWSTART BRWSTRM BRWL000 BRWDL000 BRWF000 BRWM000 Description o
Comparing HP ALLBASE/BRW and HP BRW/V The following list shows such les and their functions. HP BRW/V (Compatibility Mode) RDIC RCONF RJOB HP BRW/XL (Native Mode) BRWDIC BRWCONF BRWJOB Modes Description of File Dictionary Extract File Customized Con guration File Customized Standard Job File HP ALLBASE/BRW runs in native mode or in compatibility mode. Native mode is simply MPE XL. Any report created using HP ALLBASE/BRW on an MPE XL computer will automatically be in native mode.
Transferring Reports between MPEV and MPE XL Transferring Reports between MPE V and MPE XL MPE V to MPE XL (to run in Native Mode) You can transfer reports from HP BRW/V to HP ALLBASE/BRW. However, you must recompile an HP BRW/V report with HP ALLBASE/BRW if you want to obtain the full performance bene ts of an MPE XL system.
Transferring Reports between MPEV and MPE XL 5. Rename the following les: a. standard job les from RJOB to BRWJOB b. the con guration les from RCONF to BRWCONF 6. Using the programs BRWXL.PUB.SYS or BRWCOMP.PUB.SYS, compile the report speci cation les to create HP ALLBASE/BRW report execution les The reports are now ready to run in MPE XL native mode. MPE V to MPE XL (to run in Compatibility Mode) To tranfer from MPE V to MPE XL for Compatibility Mode 1.
Transferring Reports between MPEV and MPE XL To verify compatibility for a report 1. Go to the HP ALLBASE/BRW Select Report screen or the Global Values screen of BRWSETUP. 2. Choose 4Verify5 to check whether a report accesses an SQL database or if it contains names with a length greater than 16 characters. Reports that access an SQL database cannot be used on MPE V. If a report contains names with a length greater than 16 characters, they must be changed to 16 characters or less.
Transferring Reports between MPEV and MPE XL 5. Using BRW.PUB.SYS or RCOMP.PUB.SYS on HP ALLBASE/BRW, compile the report speci cation les to create HP BRW/V report execution les. 6. Store the HP BRW/V report execution les from the MPE XL system and restore them on the MPE V system. The reports are now ready to run on the MPE V system. Note Part IV: Administrative Tasks The MIT version of the MPE V operating system where you restore the execution le must be V-Delta-1 MIT (G.03.01) or later.
Running HP BRW/V Reports on MPE XL Running HP BRW/V Reports on MPE XL You can run HP BRW/V reports on your MPE XL system in either compatibility mode or native mode. The choice depends on the program you execute. To run HP ALLBASE/BRW in native mode on MPE XL 1. Run the execution program for HP ALLBASE/BRW: RUN BRWEXEC.PUB.SYS or BRWSTART.PUB.SYS, or BRWXL.PUB.SYS 2.
Error Messages Error Messages Note The following error messages may be displayed when you are operating in the BRW/V environment. The error messages are listed in alphabetical order and are followed by a brief explanation. These errors can be detected before entering BRW/V by using the Verify function key from the HP ALLBASE/BRW Select Report screen. Configuration file is incomplete This error message may occur when running the program RSTART, RCOMP, REXEC, or RSETUP.
Error Messages File contains SQL ACCESS definitions This error message may occur from the Select Report screen. It indicates that the report references an HP ALLBASE/SQL table. It is not possible to access an HP ALLBASE/SQL table using HP BRW/V. To solve this problem, use HP ALLBASE/BRW to remove the HP ALLBASE/SQL table access de nitions.
Error Messages Referenced access file contains SQL ACCESS definitions This error message may occur from the Modify Report screen. It indicates that the report points to an access de ntion le which references an HP ALLBASE/SQL table. It is not possible to access an HP ALLBASE/SQL table using BRW/V.
Transfer Reference Transfer Reference Data MPE les, KSAM les, IMAGE databases, intermediate report les, and dictionary output les can be used without amendment in either HP ALLBASE/BRW or HP BRW/V. Data from HP ALLBASE/SQL tables cannot be accessed in HP BRW/V. Names In HP ALLBASE/BRW names can be up to 20 characters in length, but in HP BRW/V they are restricted to 16 characters. Configuration Files You can transfer con guration les from HP BRW/V to HP ALLBASE/BRW as long as you rename the le BRWCONF.
Report Job Files You must alter any appropriate program names when transferring report job les between HP BRW/V and HP ALLBASE/BRW. (See the list of di erent program le names near the beginning of this chapter.) For example, a report job le for HP BRW/V might contain: . . . $PRINTFILE !CONTINUE !RUN REXEC.PUB.SYS salesan.rexec sales-area = "WESTERN" !EOD . . Before using this report job le with HP ALLBASE/BRW, you must amend the le as below: . . . $PRINTFILE !CONTINUE !RUN BRWEXEC.PUB.SYS salesan.
22 Converting Reports Use BRWCONV to upgrade Report/V or Inform/V reports Part IV: Administrative Tasks Converting Reports 22-1
At a Glance At a Glance The HP ALLBASE/BRW program BRWCONV upgrades Report/V or Inform/V reports to HP ALLBASE/BRW reports by creating an HP ALLBASE/BRW speci cation le from the source le. This chapter describes how to use the BRWCONV program.
Required Software Required Software Location of BRWDIC, REPCOMP, and INFCNV Part IV: Administrative Tasks To run BRWCONV, you must have the following software installed on your system in addition to HP ALLBASE/BRW: REPCOMP.PUB.SYS The Report/V compiler. It is used by BRWCONV to verify Report/V reports before the conversion. INFCNV.PUB.SYS Required to convert Inform/V reports. BRWCONV looks for a BRWDIC le in BRWDIC. PUB.logonaccount . The two les REPCOMP.PUB.SYS and INFCNV.PUB.
Using BRWCONV Using BRWCONV This section describes the steps you must follow to run the BRWCONV program. In addition to this description, the steps are illustrated in the conversion example at the end of this chapter. To convert reports 1. De ne les and elements in the data dictionary. 2. Verify that item names correspond to HP ALLBASE/BRW syntax. 3. Identify DATE and TIME items. 4. Create the BRWDIC le. 5. Create group sets if desired. 6. Run BRWCONV. The following sections describe each step in detail.
Using BRWCONV Define Date and Time Items Create BRWDIC Use the dictionary long-name to identify Date and Time items. In Report/V, dates and times are ordinary String items. HP ALLBASE/BRW allows greater exibiliy for these items. To allow the HP ALLBASE/BRW options, modify the long-names as described in Chapter 18 of this manual. Run the appropriate dictionary interface program to create a BRWDIC le. See Chapter 18 of this manual.
Using BRWCONV lenames. For example, if you want to convert the INFORM le IIREP01, just enter REP01 when BRWCONV prompts for the lename. BRWCONV prompts you for the names of the HP ALLBASE/BRW speci cation le and execution le (or the groups, if you are converting a set of reports). 4. Type the execution le or speci cation le name and press 4Return5.
Using BRWCONV File Lockwords/Passwords During the conversion, BRWCONV prompts you for database passwords and le lockwords. If you specify passwords during report conversion, they are included in the HP ALLBASE/BRW speci cations. To convert reports with file passwords 1. Follow the same steps as when converting reports without le passwords. After the veri cation of the report, BRWCONV prompts you for the passwords or lockwords. 2. Respond to the prompts with the appropriate lockword or password.
Using BRWCONV Error Messages (RWARN) Any exceptions that occur during the conversion, such as items which are not de ned in the dictionary or features which do not convert, are written to a le called RWARN. You can use this le to determine whether you must make any manual modi cations to the HP ALLBASE/BRW speci cations. RWARN has a maximum size of 10000 records. In the unlikely event of 10000 errors, the RWARN le will ll up, and any other warnings found will be lost (unless you enlarge the le).
Incompatible Features BRWCONV does not translate all Report/V features. Some are not available or not necessary in HP ALLBASE/BRW and some must be modi ed manually after you convert the reports. This section lists those features which do not convert automatically and tells you which ones require manual modi cation. Incompatible Features BRWCONV can translate all Inform/V features. The following table shows the incompatible features between Report/V and HP ALLBASE/BRW.
Incompatible Features SELECT BRWCONV cannot convert Report/V generic constants if they contain a ^ character and, at the same time, they are to be used in relations with the operators: >, <, <=, and >=. That is, BRWCONV will convert all SELECT statements in relations that use the operators = or <>, and/or that do not include generic constants containing an ^ character. Note that you can use generic constants with the ^ character if the ^ character appears at the end of the name.
Translation Rules Translation Rules This table contains a list of Report/V commands and a description of how they are translated into HP ALLBASE/BRW speci cations. Commands ACCESS HP ALLBASE/BRW Speci cations If given, the access statement is used to de ne data access for the report. If no ACCESS statement exists, BRWCONV obtains the access speci cations from the Report/V compiler. The table PRINT-TABLE is created. It has type JOIN and contains all les speci ed in the ACCESS statement as source tables.
Translation Rules Commands HP ALLBASE/BRW Speci cations PAGE FOOTING Used as page footing lineset. PAGE HEADING Used as page heading lineset. REPORT SUMMARY Used as report footing. REPORT TITLE Used as report heading. SELECT A selection set SET, which contains the selections from all select statements, is created. If an element is named without selection criteria, a Comparison Predicate parameter with the same name as the item is created.
Translation Rules Commands HP ALLBASE/BRW Speci cations LNG=number LNG is ignored if the item has an edit-mask de ned either using a lineset, or in DEFINE(item ). If no such edit-mask is de ned, the print eld of the item has length number . If edit-masks are de ned in both DEFINE(item) and lineset, the lineset edit-mask is used. NOHEAD Suppresses a header for the item. NOSIGN A layout calculated item is created. It is named NOSIGN-n where n is a number that makes the name unique.
Translation Rules Example Report Conversion This section contains a step-by-step example of how to convert a Report/V report into an HP ALLBASE/BRW report. The source for the Report/V report is: REPORT PRODFR; DISPLAY "TEST REPORT FROM TOYDB"; OPTION NOHEAD,NOBANNER; ACCESS ORDER-DETAILS (TOYDB.PUB), LIST= (PRODUCT-NO, QUANTITY, SHIPMENT-DATE); PRODUCTS (TOYDB.
Translation Rules The printed report is shown below: Report: PRODFC Date: 03/20/92 Time: 08:23 Page: 1 Production Forecast Report __________________________ Product Line: 50 -----------Product Number -------------61-288-F 61-288-M 61-288-R 68-401-F 61-288-M 61-288-M Product Name -----------FILE, FINE FILE, MIDDLE FILE, ROUGH EMERY PAPER FINE EMERY PAPER MTD. EMERY PAPER RGH Define Data Quantity -------784 356 480 414 545 895 -------3474 Unit Price ---------*****11.02 *****11.59 *****12.64 *****23.
Translation Rules The last section of the listing is the Data De nition section: DATA ITEM DEFINITIONS: D D D D D D $PAGE $TIME $TODAY PRICE PRODUCT-LINE PRODUCT-NAME PRODUCT-NO QUANTITY SALES-VALUE SHIPMENT-DATE I T D P X X X I P X ( ( ( ( ( ( ( ( ( ( 4, 64, 64, 7, 2, 16, 6, 4, 12, 4, 0, 0, 0, 2, 0, 0, 0, 0, 2, 0, 2) 2) 2) 4) 2) 16) 6) 2) 6) 4) CODE FILE STATUS: REPLACED 0 COMPILATION ERRORS PROCESSOR TIME=00.00.
Translation Rules Create BRWDIC Convert the Report Run BRWDIC3000.PUB.SYS. Answer Y to the prompt asking if Parent/Group items should be included. After completing the preparation steps, you are ready to convert the report using the steps given in the section in this appendix called Running BRWCONV. BRWCONV issues a message that the report has been converted with warnings.
Translation Rules to eliminate the di erences, which were caused by the ignored BLOCK-BLOCKEND statement. The Report/V BLOCK-BLOCKEND statement modi es the price of products in product line 50. To duplicate this in the new report, modify the HP ALLBASE/BRW report as follows: De ne a calculated item, ADJ-PRICE, with the formula: If PRODUCT-LINE = 50 then PRICE * .95 else PRICE Modify the formula for SALES-VALUE so that it is computed based on ADJ-PRICE instead of PRICE.
Glossary access block A unit of data access, when the report is executed. Locks on databases or les are only held during execution of one access block. Produces an intermediate work le, or if it is the last access block, the nal report. The compile program BRWCOMP splits a table into several access blocks, or combines several tables into one access block, depending on the selected access methods.
Translation Rules BRWAPPD The program used to create an BRWDIC dictionary le from HP Application Dictionary. BRWCOMP See report compile program . BRWCONF The system con guration le you use (depending on your user interface language) in the account or group you want to con gure. You create the BRWCONF le when you copy it from BRWC000.PUB.SYS. See con guration le . BRWCONV See report conversion program . BRWDIC See dictionary le .
Translation Rules calculation formula An expression which results in a numeric, string, date or time value. The formula of a calculated item or a function. child The parent element in a source table may be subdivided into several child elements. In this way, included parts of the parent element can be referenced separately. See also, composite search element. column calculations Vertical calculations, which summarize data for a break, page or report.
Translation Rules con guration le The le name is BRWC000. Contains defaults to be used in RSPEC, con guration for BRWEXEC, and global functions. HP ALLBASE/BRW looks for the le in the following order: 1. BRWCONF 2. BRWCONF.PUB 3. BRWCONF.PUB.SYS 4. BRWCnnn .PUB.SYS, where nnn is the number returned by the Native Language Support intrinsics. 5. BRWC000.PUB.SYS con guration program Used to con gure HP ALLBASE/BRW. The le name is BRWSETUP.PUB.SYS.
Translation Rules data dictionary Central data storage for data de nitions. Contains de nitions of items, datasets, databases, les, keys, passwords, and so on. HP Dictionary/V, HP System Dictionary, HP Application Dictionary, HP Access Central Dictionary, and user-generated dictionaries can be used with HP ALLBASE/BRW. For each account in which HP ALLBASE/BRW is to be used, a dictionary le must be created rst by using the programs BRWD3000, BRWSD, BRWAPPD, BRWACCSD, or BRWGEND.
Translation Rules Can be used to print column calculations for that break, page or report. Can be used to print data from the previous detail, or previous lineset if it is a page footing. format Description of the items of a table (name, type, length, position in record, number of array components). The format of each dataset or le used in a report must have been speci ed in the dictionary. The format of intermediate report les is included in these les (self-describing les).
Translation Rules HP ALLBASE/SQL SELECT A command that retrieves data from one or more tables or views. The retrieved data is in the form of a table, called the result table or query result. HP ALLBASE/SQL table Data in the relational database is organized into a table consisting of rows and columns. A row is a record and a column is an item. HP ALLBASE/SQL table owner Within HP ALLBASE/SQL a table or view is accessed by specifying both the owner and table name.
Translation Rules calculated item, provides a single value each time the calculation is performed. item edit Rule for how an item value is to be formatted for printing. item types Are: S = string N = xed point numeric I = Integer R = oating point real D = date T = time join Method of combining source tables in which the columns of two or more tables are joined side-by-side. KSAM Keyed sequential access method on the HP3000 system. KSAM le Allows sequential and keyed access to the data in the KSAM le.
Translation Rules link See join . literals A text constant of up to 16 characters that is stored in BRWDIC and can be referenced by a symbolic name. logical-expessions See conditions lock mode Used to secure a lock on an IMAGE dataset, HP ALLBASE/SQL, table, KSAM or MPE le. You can specify a lock mode on the HP ALLBASE/BRW Tune Access screen. merge Method of combining two similar tables by stacking (appending) one on the other. MPE XL Operating system on the HP3000 computer.
Translation Rules output le Saves data for later reporting or processing. Can be speci ed for each lineset; can be an intermediate report le or a dictionary output le. page A page of the printed report. Page heading and footing linesets can be de ned to be printed on each page. Other linesets can be printed as they are, or they can be positioned at an absolute position of the page. pagination The control of the page layout.
Translation Rules projected items When source tables are combined, their items are projected to the newly formed table. A projected item can be given an alias name, excluded from projection, and given numeric precision; these attributes are only in e ect outside the item's source table. Quoted Basic Files Files used as data transfer les by some standard programs, including Lotus 1-2-3, RBASE 5000, DBASE, and BASIC programs. DSG/3000 can also read Quoted BASIC les as free-formatted data les.
Translation Rules report execution program Used to produce a report. The le name is BRWEXEC.PUB.SYS. Can be used stand-alone or within the report speci cation program. report job le For producing multi-pass reports; used for bundling the production of multiple reports. Is created using HP EDITOR/3000 or any compatible editor; the optional le code is RJOB.
Translation Rules selection Selects a subset of the data in the nal access table. selection condition Criteria to select a subset of data from the nal access table. Is expressed as a condition formula and uses the calculation language. Can be exible by using parameters. selection criteria Control the amount of data which goes into a report, as well as when and how the report is printed. selection set One group of data selection criteria used for a report.
Translation Rules suppress condition Criteria to suppress printing of a line or lineset; can be speci ed for each line or lineset. Is expressed as a condition formula and uses the calculation language. syntax diagram Describes the syntax of the HP ALLBASE/BRW calculation language in a graphic form. table A collection of data in which the rows are records and the columns are items. Basis for the report layout or other tables; joins or merges the source tables.
Index A absolute page position, 8-10 absolute position for break footing, 7-12 ABS, standard function, 15-58 acccess sequence, 6-20 ACCDEF le de ning Time and Date items, 18-40 access authorization HP ALLBASE/SQL tables, 14-7 access blocks, 6-26 access methods for data, 6-26 access sequence de ning, 6-27 for HP ALLBASE/SQL reports, 14-3 for join on HP ALLBASE/SQL, 6-11 for SQL tables, 6-27 Tune Access screen, 6-11 access sequence numbers HP ALLBASE/SQL, 14-10 ACCESS statements with Report/V, 22-4 account
alternate lines for NO VALUE item edits, 7-40 amending the standard job le, 11-10 AND operator, 15-49 APPDIC HP Application Dictionary, 18-37 ARCCOS, standard function, 15-58 ARCTAN, standard function, 15-58 arguements, 15-2 arguments, 15-10 constants as, 15-19 example, 15-26 in functions, 15-33 parameters in, 15-49 priority of, 15-6 type, 15-4 arithmetic operators, 15-42 array components, 7-27 ARRAYDEF le, 18-31 array elds, 18-33 building, 18-34 array elds, 18-33 array items, 7-27 example, 7-27 printing, 7
break heading linesets, 7-15 break headings, 7-9 repeating, 7-11 break level numbers, 7-5 BREAK-LEVEL, standard item, 15-60 break linesets, 7-15 breaks, 8-2, 8-4 change degree for, 8-6 BRWACCSD program, 18-4, 18-5 dictionary generator, 18-2 HP Information Access data dictionary, 18-38 BRWAPPD, 18-2, 18-4, 18-5 HP Application Dictionary generator, 18-37 BRWC000, 17-3 BRWCLOSE intrinsic, 20-14, 20-23 BRW COMAREA, 20-14, 20-17 BRW COMAREA intrinsic, 20-18 BRWCOMP program with HP ALLBASE/SQL, 14-10 BRWCONF le,
BRW PARAMETERS parameter, 20-14, 20-18, 20-21 BRWPASSEXIT creating, 17-29 examples, 17-31 password retrieval, 17-29 BRWSD, 18-2, 18-4, 18-5 HP System Dictionary, 18-29 BRWSDEXT, 18-18, 18-22 scope password, 18-18 BRWSETUP program, 15-4, 15-19, 15-21, 17-3 default items edits for reals, 15-48 identifying senders, 13-4 running, 17-4 BRWSTART, 20-6, 20-7, 21-10 BRWSTARTREQUEST intrinsic, 20-3, 20-7, 20-10 BRWSTOPREQUEST intrinsic, 20-7, 20-13 BRWSTREAM intrinsic, 20-14, 20-21 bucketing reports, 7-41 BUILD stat
change degree for breaks, 8-6 changes in dictionary les, 18-64 changing BRWDIC, 18-5 character mode, 20-3 PASCAL program, 20-4 check forms, voiding, 7-45 child and parent elements, 18-22 child elements, 18-10, 18-14, 18-15 BRWDIC, 18-29 synonyms, 18-27 CIERR, the command interpreter error number, 20-24 COBOL example BRWPASSEXIT, 17-33 column calculations, 7-26, 15-2, 15-10 multi-pass reporting, 12-4 numeric precision, 7-35 restrictions, 7-31 comarea len, 20-8, 20-9, 20-10 combining non-relational (SQL) and
Default Time Item Edits screen, 17-21 Exception Output Values screen, 17-11 Execution Values screen, 17-7 global functions, 17-22 Global Functions Formula screen, 17-25 Global Functions screen, 17-22 Global Values screen, 17-5 Item Edits screens, 17-15 Mail Values screen, 17-26 running BRWSETUP, 17-4 Speci cation Values screen, 17-13 con guration and security, 17-2 con guration les, 21-4 transferring, 21-14 con guration levels, 17-3 consolidating reports, 7-41 constants as arguments, 15-19 date, 15-19 integ
D data di erent sources, 6-18 data access by record number, 6-29 methods, 6-26 tuning, 6-26 data area for intrinsics, 20-7 database extract les, 12-14, 12-16 databases, 18-14, 18-27 data dictionaries, 18-2 multiple, 18-5 data dictionary altering, 18-5 converting reports, 22-4 removing, 18-5 data ow diagram, 12-16 data format con guration, 17-4 Data Interchange Format (DIF) les, 12-11, 12-12 headers, 12-12 keywords, 12-12 data language con guration, 17-4 data screens, 3-3 data selection de ning, overview, 9
date items format, 18-8 Date-Item Table HP Dictionary/3000, 18-10, 18-11 HP System Dictionary, 18-23, 18-24 DATE-JULIAN, 18-13 dates calendar, 18-10 Julian, 18-10, 18-13, 18-26 types, 15-21 DATE standard item, 15-60 date types, 15-21 Date types ACCSDEF le, 18-45 Date Types Table user-generated dictionary, 18-56 DAY OF MONTH, standard function, 15-21, 15-58 DAY OF WEEK, standard function, 15-21, 15-58 DAY OF YEAR, standard function, 15-21, 15-58 DAYS DIFF, standard function, 15-22, 15-58 DBASE, 12-12 DBEnvir
DEFINE statements with Report/V, 22-4 De ne Table screen, 6-3, 6-9, 16-29{32 example of one source table, 6-12 open join, 6-7 open join example, 6-10, 6-11 de ning access sequence, 6-27 de ning data access, 6-26 de ning Date and Time items converting reports, 22-5 de ning les, items, and elements converting reports, 22-4 de ning linesets, 7-6 de ning overlays, 18-28 de ning relations, 6-3, 6-4 de ning report layout, 7-2 de ning row calculations, 7-33 DEFN option Report/V reports, 22-4 delivery audit report,
dictionary generator BRWGEND, 18-2, 18-4 dictionary output les, 12-9 creating, 12-10 multi-pass reporting, 12-4 dictionary password, 18-49 dictionary passwords, 17-28 DICT option checking dictionary, 22-4 DIF le type, 16-50 Digit Extraction Function, 15-45 disk space freeing, 12-10 DIVIDE BY ZERO, 15-23 DIVISION parameter, 20-12 DSG/3000, 12-12 dummy account, 18-7 duplicate key entries, 18-26 E Index-10 editing elds, 3-3 editing items, 7-17, 7-18 editing report job les, 21-6 editing standard job les, 21-6
during conversion, 22-8 non-zero value, 20-8, 20-24 with ACCSDEF, 18-49 ERROR VALUE, 15-23 error values, 7-24 examples array items, 7-27 De ne Relation screen, common item, 6-11 De ne Table screen, one source table, 6-12 De ne Table screen, open join, 6-11 joining a source table to itself, 6-12 joining HP ALLBASE/SQL tables, 6-13 joining tables, 6-5 keyed access, 6-28 nested tables, 6-9 NO VALUE, 6-8 open joins, 6-7 pre-printed forms, 7-43 relation conditions, 6-31 exception cases item edits, 7-24 exception
F Index-12 eld editing, 3-3 le codes, 21-3 FILE command, 11-22, 18-16 le equations, 18-6, 18-7 when converting reports, 22-5 with dictionaries, 18-29 le layout Quoted BASIC (QB) les, 12-12 le lockwords converting reports, 22-7 le mnemonics, 21-3 FILENAME parameter, 20-14, 20-17 le names, 21-3 table , 21-3 les, 18-8 amending the standard job le, 11-10 ARRAYDEF and SYNDEF, 18-31 con guration, 21-4 con guration:transferring, 21-14 database extract les, 12-14 Data Interchange Format (DIF), 12-11, 12-12 de nin
frozen defaults, 9-9 FSERR, the le system error code, 20-24 Function Arguments in Calculations, 15-34 function keys help screen, 3-6 other keys, 3-4 using, 3-4 functions as an argument, 15-33 categories of , 15-4 conversion, 15-19 date extraction, 15-21 digit extraction , 15-45 global, 15-4, 15-33 layout, 15-4, 15-33 Performance Note, 15-33 string extraction, 15-61 table, 15-4, 15-31 what is, 15-4 functions and operations, real item, 15-48 G global functions, 15-4, 15-33 Global Functions con guration, 17-2
NULL values, 14-12 open join example, 6-11 passwords, 14-7 performance considerations, 14-10 query example, 6-11 system catalog, 14-10 table owner, 14-7 transaction handling and locking, 14-8 Tune Access screen, 14-4 using, 14-3 HP ALLBASE/SQL tables combining tables with other sources, 14-2 dictionary le not required, 14-2 joins, open joins, and merges, 14-2 passing user-de ned parameters, 14-2 HP Application Dictionary, 18-2, 18-4, 18-5, 18-31 ARAYDEF, 18-33 BRWAPPD generator, 18-37 de ning arrays, 18-33
I IEEE format, 18-21, 18-22, 18-28, 21-6 for long names, 18-14 IEEE formats real numbers, 21-8 IF THEN ELSE operation, 15-36 IMAGE/3000 combining with HP ALLBASE/SQL, 14-12 image-alias, 18-20 IMAGE compound-items data dictionaries, 18-39 IMAGE-DATASET, 18-27 IMAGE datasets, 14-2 data access by record number, 6-29 in user-de ned dictionary, 18-59 securing locks, 6-26 IMAGE tables, 6-20 incompatible features converting reports, 22-9 index numbers, 7-27 INDEXVIOLATION, 15-23 INFCNV program, 22-3 location of,
BRWSTARTREQUEST, 20-3, 20-7, 20-10 BRWSTOPREQUEST, 20-7, 20-13 BRWSTREAM, 20-14 diagrams, 20-15 execution, 20-14 FILENAME parameter, 20-17 HP Desk, 13-3 request report, 20-6 transferring reports, 21-14 intrinsic status, 20-8 invalid element type, 18-16 invalid syntax in format name, 18-16 INVALID VALUE, 15-23 IRF le type, 16-50 item edits numeric and integer, 7-21 real numbers, 7-23 time, 7-23 item edits for exception cases, 7-24 Item Edits screens defaults, 17-15 item level security, 16-90 item name, 16-52
J JOB command, 11-16 job control words, 14-13, 20-24 job les, 11-8, 20-14 parameters for report job les, 11-11 report, 11-11, 20-17 report header, 11-14 standard, 20-17 standard, in transferring reports, 21-15 job template header, 20-18, 20-19 joining tables, 6-2, 6-3 example, 6-5 example of joining a source table to itself, 6-12 HP ALLBASE/SQL tables, 14-12 HP ALLBASE/SQL tables, example, 6-13 identical names, 6-13 source table to itself, 6-12 joins access sequence for HP ALLBASE/SQL, 6-11 multiple open,
Performance Notes, 15-30 restrictions, 15-29 row calculations, 7-33 screen description, 16-33{35, 16-83{84 Layout Calculated Item screen, 7-33 layout calculations, 7-26 layout functions characteristics, 15-33 formula screen description, 16-39 screen description, 16-37{38 what is, 15-33 leaving HP ALLBASE/BRW, 4-4 length page, 8-7 line conditions suppressing, 7-38 line numbers, 7-5 lines de ne screen description, 16-16 lines and linesets suppressing, 7-38 lineset conditions suppressing, 7-38 LINESET LEVEL, s
logical expressions, syntax diagram, 15-65 logical record number, 6-29 logon command in job le, 11-21 logon UDCs, 18-6 LOG, standard function, 15-58 long names, 18-10, 18-14 KSAM les, 18-15 longnames, 21-6 Lotus 1-2-3 les, 12-12 M Mail Values screen description, 16-40{41 Mail Values screen, 13-4, 17-26 master datasets, 18-26 MATCHES operator, 15-49 max num parms, 20-19 merging tables, 6-2, 6-15 example, 6-15 message bu er, 17-9 messages, 3-7 method code for numeric precision, 6-23, 7-35 MINUTE OF, standard
N Index-20 names and datatypes HP ALLBASE/SQL, 14-10 Native Language Support, 14-13 native mode, 21-5 $NBR-COPIES, 20-19, 20-22 nested sort items, 8-4 nested tables, 2-4, 6-8 example, 6-9 new page, 8-7 nfname parameter, 20-3 NLDATALANG, 14-13 non-printable characters, 7-25 non-SQL source tables, 6-26 non-zero value errors, 20-8, 20-24 NOT operator, 15-49 NO VALUE, 6-7, 7-24, 15-23 example, 6-8 HP ALLBASE/SQL, 14-12 NO VALUE item edits using alternate lines, 7-40 NULL values HP ALLBASE/SQL tables, 14-12 nu
O OCCURS item options, 18-59 on-line help with BRWCONV, 22-8 online help, 3-6 online review, 16-46 open join on HP ALLBASE/SQL table, 6-11 on HP ALLBASE/SQL table, example, 6-11 open joins, 6-3, 6-7, 6-23 De ne Table screen, 6-7 De ne Table screen, example, 6-10 De ne Table screen example, 6-11 example, 6-7 multiple, 6-8 open mode, 6-27 Open Mode capabilities, 6-27 operating systems, 21-2, 21-9 operations, 15-48 aggregate (array), 15-5 dates, 15-22 IF THEN ELSE, 15-36 list, 15-38 on numbers, 15-41 real ite
P Index-22 packed numeric, 18-9, 18-21 page absolute position, 8-10 length, 8-7 resetting number, 8-7 splitting across boundaries, 8-9 starting new, 8-7 page footing lineset, 7-12 page heading linesets, 7-10 page headings suppressing, 7-13 page linesets, 7-10 pages horizontal, 7-47 PAGE standard item, 7-38, 15-60 pagination, 8-2, 8-7 parameters, 9-7 as arguments, 15-49 BRW PARAMETERS, 20-18 characteristics, 15-50 comparison predicate, 15-52 default values, 15-54, 20-7 de ne screen description, 16-53{55 de
dictionary, 18-49 password encryption, 17-28 password retrieval BRWPASSEXIT, 17-29 passwords de ning when executing BRWCONV, 22-7 for HP ALLBASE/SQL tables, 14-3 HP ALLBASE/SQL, 14-7 lockwords when converting reports, 22-7 pathname for dummy account, 18-7 performance calculated items, 15-30 functions, 15-33 HP ALLBASE/SQL, 14-10 relation conditions, 15-14 positioning items, 7-17 precision de nition, 15-43 valid number ranges, 6-25 precision of numbers, 6-22, 6-25, 15-43 column calculations, 7-35 pre lling,
R Index-24 RBASE 5000, 12-12 RCOMP program, 21-7 RDIC program, 21-7 real constants, 15-19 real item edits, 7-23 Real Item Edits screen, 16-59{62 default con guration, 17-18 real items edits, 15-48 functions and operations, 15-48 numeric precision, 15-47 real items and numeric items, 15-46 real numbers, 15-46{48, 18-22, 18-27, 21-6 32-bit, 18-9 32-bit and 64-bit, 18-21 64-bit, 18-9 computing standard deviation, 15-57 how HP ALLBASE/BRW holds, 15-46 how to write in formulas, 15-46 how to write in parameters
relationship types for System Dictionary, 18-17 relative MPE les, 18-27 removing the data dictionary, 18-5 REMPTY program intermediate report les, 12-8 REPCOMP program, 22-3 location of, 22-3 repeating item values, 7-18 $REPORT, 20-19, 20-22 REPORT-DESCRIPT, standard item, 15-60 REPORT-EXEC-FILE, standard item, 15-60 report execution les, 20-14, 20-18, 21-7 report le header, 11-16 report footing De ne Lines screen, 7-14 report footing lineset, 7-14 report heading De ne Lines screen, 7-13 report heading line
converting and transferring, 22-3 veri cation of, 22-6 REQUEST COMAREA parameter, 20-7, 20-8, 20-10, 20-13 common area, 20-8 REQUEST-DATA, standard item, 15-60 requesting reports interactively, 11-3 requesting reports with BRWEXEC, 11-6 requesting the report, 13-9 request report intrinsics, 20-6 Request Report screen, 16-64{67 required software to run BRWCONV program, 22-3 requirements for HP BRW-Desk, 13-3 reserve, 20-20 reserved words, 15-56 RESET, 18-7 resetting calculations at break levels, 7-28 resetti
De ne Table, 6-3 screen format HP Application Dictionary, 18-37 screen formats HP Application Dictionary, 18-32 screens Date Item Edits, 7-23 Default Date Item Edits screen, 17-20 Default Integer Item Edits screen, 17-19 Default Items Edits screens, 17-15 Default Numeric Item Edits screen, 17-17 Default Real Item Edits screen, 17-18 Default String Edits screen, 17-16 Default Time Item Edits screen, 17-21 De ne Breaks, 7-45 De ne Breaks and Pagination, 8-3 De ne HPDesk Con guration, 16-12 De ne Lines for rep
search-elements chains, 18-26 search elements, 18-27 composite, 18-27 search items, 6-26 SECOND OF, standard function, 15-58, 15-69 SECONDS DIFF, standard function, 15-58, 15-70 security, 17-2, 17-28 data value, 9-12 data-value-dependent, 9-2 frozen defaults, 9-9 item level, 16-90 switching destinations, 13-14 when printing HPDesk reports, 13-14 with HP ALLBASE/BRW, 13-14 selecting data, 9-4 at execution, 9-5 selection conditions, 9-8, 15-3, 15-13 example, 15-14 selections default values, 20-7 selection set
Speci cation Values screen, 17-13 splitting across page boundries, 8-9 splitting the report with HP Desk, 13-6 SQL data dictionary not needed, 18-2 SQL databases, 21-8 SQL les, 21-8 SQL transaction isolation level, 14-3 SQR, standard function, 15-58 SQRT, standard function, 15-58 standard deviation, 15-57 Standard Functions, Summary of, 15-58 standard items, 15-59 PAGE and LINE, 7-38 standard job les, 20-14, 20-17, 21-4, 21-7 amending, 11-10 editing, 21-6 transferring reports, 21-8, 21-15 standard parameter
string elements, 18-10 string item edits, 7-20 String Item Edits screen, 7-20, 16-74{76 SUB DAYS, standard function, 15-22, 15-58 SUB HOURS, standard function, 15-58, 15-70 sub-items ACCSDEF le, 18-44 SUB MINUTES, standard function, 15-58, 15-70 SUB MONTHS, standard function, 15-22, 15-58 SUB SECONDS, standard function, 15-58, 15-70 SUB WEEKS, standard function, 15-22, 15-58 SUB YEARS, standard function, 15-22, 15-58 su xes, 7-22, 18-10 supported features HP Information Access data dictionary, 18-40 suppres
table function formula screen, 15-32 screen description, 16-85 table functions example, 15-31 restrictions, 15-31 screen , 15-32 what is, 15-4, 15-31 table lock mode HP ALLBASE/SQL, 14-8 table owner with HP ALLBASE/SQL, 14-7 TABLE-REC-NUMBER, 6-29 tables de ning for HP ALLBASE/SQL, 14-3 HP ALLBASE/SQL table, open join example, 6-11 nested, 6-8 owner name for HP ALLBASE/SQL tables, 14-3 TAN, standard function, 15-58 TELLREPORT parameter, 17-28 terminal refreshing the screen, 20-3 terminology element, 18-19 f
TO NUMERIC, 15-20 TO NUMERIC, standard function, 15-58 TO REAL, 15-20 TO REAL, standard function, 15-58 TO STRING, 15-20 TO STRING, standard function, 15-58 TOYDB contents, 1-9 overview, 1-9 pictured, 1-9 transaction handling and locking HP ALLBASE/SQL, 14-8 transaction isolation level, 17-9 HP ALLBASE/SQL, 14-3, 14-8 transaction priority, 17-9 transfering reports MPE V to MPE XL, 21-7 transfer reference, 21-14 transferring reports, 21-6 con guration les, 21-14 dictionary output les, 21-14 IMAGE databases,
user-de ned parameters, 11-14 user-generated dictionary creating source- le, 18-50 Date Types Table, 18-56 ELEMENT statement, 18-54 ITEM statement, 18-55 item types, 18-55 RECORD statement, 18-55 source le, 18-50 user-generated dictionary source le terminology, 18-50 $USERPASS, 20-19, 20-20 USER standard item, 15-60 using BRWCONV, 22-4 using BRWDLIST, 18-5 using function keys, 3-4 using horizontal pages, 7-47 using HP ALLBASE/SQL, 14-3 using intermediate report les, 12-7 using output les, 12-14 using the ve
Index-34 Y year, format for, 18-25 YEAR OF, standard function, 15-21, 15-58 YEARS DIFF, standard function, 15-22, 15-58 Z zero numbers printing, 7-22 zoned numeric, 18-9, 18-21
