HP ALLBASE/BRW Tutorial For MPE/iX Systems ABCDE HP Part No. 35360-90201 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 Second Edition March 1990 May 1992 35360A A.01.00 35360A A.01.
Preface iv This manual documents version A.01.31 of BRW on HP 3000 computer systems running under the MPE/iX operating system (beginning with Release 4.0).
MPE/iX MPE/iX MPE/iX, Multiprogramming Executive with Integrated POSIX, is the latest in a series of forward-compatible operating systems for the HP 3000 line of computers. In HP documentation and in talking with other HP 3000 users you will encounter references to MPE XL. All programs written for MPE XL will run without change under MPE/iX.
Manual Structure Manual Structure The BRW Tutorial contains ve chapters, two appendixes, a glossary, and an index. This manual contains three lessons for new users and some information for database administrators on how to manage this tutorial. This manual assumes that the MPE operating system is correctly installed and operating. It also assumes that you have HP TurboIMAGE/iX correctly installed and that you have access to the sample database, TOYDB.
Manual Structure Conventions Used in This Manual The typographic conventions used in this manual are summarized below. Notation user input Typographic Conventions Description When necessary for clarity, user input is indicated like this. For example: BAMBOO: HELLO MGR.ITF3000 COMPUTER font bold font Italics font In examples, command names, options, and parameters are shown in uppercase letters, in computer font. For example: SUM i = 1 TO ?month OF customers.
Contents 1. Overview What is BRW? . . . . . . . . . . . . The BRW System . . . . . . . . . . Flexible Data Access . . . . . . . . Advanced Calculations and Functions . The Report Writer . . . . . . . . . Run-Time Report Execution . . . . . HP BRW-Desk . . . . . . . . . . . How the Tutorial is Structured . . . . . Quizzes . . . . . . . . . . . . . . Time Needed . . . . . . . . . . . The Tutorial and the Reference Manual BRW Training Course . . . . . . . . Where is the Tutorial Data Located? .
Contents-2 De ning a Table . . . . . . . . . . Make Sure You Can Use All the Items Lesson 2: Quiz 2 . . . . . . . . . . . Exercise 6: De ning the Sorts and Breaks Final Access Table Illustration . . . . Lesson 2: Quiz 3 . . . . . . . . . . . Exercise 7: De ning the Linesets . . . . The Page Heading Lineset . . . . . . Exercise 8: Using Standard Items . . . The Level 1 Break Heading Lineset . . The Detail Lineset . . . . . . . . . Deleting Lines . . . . . . . . . . . The Level 1 Break Footing Lineset . .
The Level 2 Break Footing Lineset The Level 1 Break Footing Lineset The Report Footing Lineset . . . LESSON 3: Quiz 3 . . . . . . . . Exercise 7: Reviewing the Layout . Exercise 8: Compiling The Report . Naming the Execution File . . . Exercise 9: Requesting The Report . Example Printouts . . . . . . . . LESSON 3: Quiz 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5.
Exercise 9: Request the Report . . . . . . . Example Printouts . . . . . . . . . . . . . Run (1) . . . . . . . . . . . . . . . . . Run (2) . . . . . . . . . . . . . . . . . Run (3) . . . . . . . . . . . . . . . . . Run (4) . . . . . . . . . . . . . . . . . Run (5) . . . . . . . . . . . . . . . . . LESSON 4: Quiz 5 . . . . . . . . . . . . . Report Performance . . . . . . . . . . . . Lesson 3 Compilation Listing . . . . . . . Lesson 3 Execution Statistics . . . . . . . Access Blocks and Work les . . . . . . .
B. Managing the BRW Tutorial Managing the Tutorial . . . . . . . . Who Should Read This Appendix? . . Store the Tutorial Files . . . . . . . How the Tutorial is Set Up . . . . . Other Changes to Account ITF3000 . Default User . . . . . . . . . . . . Managing Multiple Users of the Tutorial Managing Students' Files . . . . . . Tutorial Files . . . . . . . . . . . . Example Solutions . . . . . . . . . Speci cation Solutions . . . . . . . Execution Solutions . . . . . . . . Print le Solutions . . . . . . . .
Tables B-1. Speci cation Solution Files . . . . . . . . . . B-2. Execution Solution Files . . . . . . . . . . . . B-3. Print le Solution Files . . . . . . . . . . . .
1 Overview Welcome to the BRW Tutorial . This tutorial will help you become familiar with using BRW to develop reports. You can report on data in serial les, KSAM les, HP TurboIMAGE/iX databases, or HP ALLBASE/SQL databases. Note What is BRW? Although you can use HP ALLBASE/SQL databases with BRW, this tutorial does not use those facilities. This tutorial is based on the TOYDB HP TurboIMAGE/iX database. BRW is a high performance business report writing system for information management professionals.
Manual Structure Flexible Data Access BRW supports multiple data managers. You can access data in HP ALLBASE/SQL tables, HP TurboIMAGE/iX data sets, les, or, using the iterative reporting feature, from other reports. The iterative reporting feature allows you to output data from a speci c report to a le. You can then use that output le to generate further reports.
Manual Structure How the Tutorial is Structured The tutorial consists of four separate lessons. Please do them in order. LESSON 1 Gets you started with BRW|includes logging in and leaving. (Chapter 2) LESSON 2 Takes you step-by-step through the creation and running of a report (Report 1). (Chapter 3) LESSON 3 Builds on the knowledge gained in the rst lesson to produce a more complex report (Report 2). (Chapter 4) LESSON 4 is more advanced still and builds on the second and third lessons.
Manual Structure Where is the Tutorial Data Located? In Case of Difficulty 1-4 Overview Ask your system manager or system administrator from which account to run the tutorial, or see Appendix A. The tutorial documentation assumes that you will run the tutorial in account ITF3000, but your system manager may alter that. The account contains the TOYDB database and example solutions, and has groups for the les you create.
2 LESSON 1: Getting Started With BRW This lesson describes BRW data, shows you how to log in to the MPE operating system, how to run BRW, how to move around the program using the function keys, and how to use the on-line help system. Note This and all other lessons assume that you are using an HP 3000 terminal with the MPE/iX (or MPE XL) operating system installed. If you are making a network connection to the system, make the connection before starting the exercises.
Manual Structure Exercise 1: Learning about BRW Data Before you create a report, you must nd out where the data for the report is located. The examples used for the lessons in this manual are based on data in a sample TurboIMAGE database called TOYDB. TOYDB is supplied with BRW. Unless your system manager states otherwise, the database TOYDB is held in the PUB group of your ITF3000 account.
Manual Structure Exercise 2: Learning about Data Dictionaries All data (except HP ALLBASE/SQL data) used in BRW reports must be described in a dictionary le, called BRWDIC, which is created from a data dictionary. Your system manager or database administrator are probably responsible for the databases and data dictionaries on your system. The BRWDIC le that comes with BRW already describes the TOYDB data.
Manual Structure Exercise 3: Logging In and Running BRW Before you can use BRW you must be logged in to the MPE/iX system. The instructions given here assume that you have a valid login name, group, and account. If not, talk to your system administrator or database administrator before you continue. Log in 1. At the operating system prompt, type: HELLO username.accountname,groupname 2. Press 4Return5. For example, type the following command at the colon prompt: : HELLO MGR.
Manual Structure Exercise 4: Leaving BRW As you work through the BRW tutorial, you use many di erent screens. You can leave BRW at any time during the session. However, if you plan to continue to the next section, do not leave BRW at this time. Leave BRW 1. Press 4Exit5 from any screen in the application. 2. Press 4Exit5 again until you reach the Select Report screen. 3. At the Select Report screen, press 4Exit BRW5. You are prompted if you need to save any changes you have made.
Manual Structure Lesson 1: Quiz 1 Please answer the following questions. The solutions to most of these questions are supplied in Appendix A; however, some of the answers must be supplied by your database administrator or system manager. This quiz covers exercises 1 to 4 of Lesson 1. 1. What is the name of the sample database you will use for this tutorial? 2. Where is the sample database located? 3. What is the name of the data dictionary you will use for the sample database? 4.
3 LESSON 2: A Simple Report This rst report introduces you to BRW and the concepts of report writing. You will de ne, compile, and run a report that reads a data set, formats the information contained within it, calculates totals, and prints the report. The Tasks In this lesson you design a monthly customer details report, broken down by sales area and customer.
Manual Structure De ning Linesets How to de ne linesets. The use of standard items. How to alter the length of an item. How to use Item edit masks. How to add, copy, insert, modify, and delete lines. How to do column calculations, that is, using the ColCalc eld with TOTAL. 60 minutes Reviewing the Layout How to make sure that your report is correct before compiling it. 5 minutes Compiling the Report How to compile a report speci cation le to produce the report execution le.
Manual Structure Exercise 1: Sketching the 1st Report Before you start to create a report, it is a good idea to have a rough idea of its structure. This exercise shows you a typical rough draft for a report. All you need to do is study the sketch so you understand the report you will create. This exercise takes 5 minutes.
Manual Structure The only calculations required are total sales for each sales area and total sales in all sales areas. BRW will calculate these automatically. The report can use the standard paper, page length, and page width.
Manual Structure Exercise 2: Using the Tutorial Glossary What is a Break? As you go through this tutorial, you will be introduced to new terms. Most of these terms are de ned in the glossary section of this manual. This exercise takes 5 minutes. For this exercise, read the following paragraphs. Some words are printed in bold type and have additional information you can look up in the glossary of this manual.
Manual Structure Applying What You Learned The Linesets for This Report The following paragraph describes the linesets for this report. Apply what you learned about the new terms you read about and looked up in the glossary to help you understand this description. This report has ve linesets, as described below. 1. The Page Heading lineset. This lineset prints the report title, page, and date on the top of each page. For this simple report, it also acts as the report heading. 2.
Manual Structure LESSON 2: Quiz 1 Please answer the following questions. The solutions to these questions are supplied in Appendix A. This quiz covers exercises 1 and 2. 1. How many sorts can you have in a report? 2. How many break levels can you have in a report? 3. What total is printed on the break footing on SALES-AREA? 4. \When creating a report, it is best to go directly to BRW without roughing out a plan for the report." Is this statement true or false? Why is it true or false? 5.
Manual Structure Exercise 3: Creating the Report Where to Begin This exercise shows you how to create the report, how to add a password and a description of the report, and how to use the BRW help screens. This exercise takes 5 minutes. If you are not already logged in to the operating system, do so now. Refer to Lesson 1 in Chapter 2 for details on logging in and running BRW, if you are unsure of the procedure. The rst screen that BRW displays is the Select Report screen.
Manual Structure BRW creates the report, and displays the De ne Report screen. d a c b Note the message \Report created", at the bottom of the De ne Report screen. Whenever you perform an action, BRW displays a message here to tell you whether the action was successful. Help Screens Just in case you would like some added information or if you need help understanding a screen, every BRW screen has an associated Help screen. Use the Help screen 1. Choose Help , (which is always 4f75 ).
Manual Structure 4. Choose Exit HELP to return to the De ne Report screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNN For this screen there were two pages of information. When you look at other BRW screens, there may be only one page of help. In that case, the Next Page and Prev Page function labels do not appear.
Manual Structure Exercise 4: Adding a Password and a Report Description Add a password and description 1. Type the password PASS1 in the Report Speci cation Password eld. 2. Press 4Tab5 to get to the Report Description eld. 3. Type the optional description Customer Details Report: Tutorial Lesson 2 in the Report Description eld. 4. 5. 6. 7. 8. This report description is a useful place to enter a short comment about the purpose of the report.
Manual Structure Exercise 5: Defining the Data Access Defining a Table This exercise describes the BRW nal access table, what source tables are, and shows you how to project items from the source tables into your report. This section takes 20 minutes. For this report, you want to de ne your own tables. Define a table NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. At the De ne Report screen, choose Define Table . The De ne Table screen appears.
Manual Structure report) or the name of a data set (as we have done here). If this were an SQL table, you would use the format owner.name for the table. 5. Type TOYDB.PUB in the Location eld. If you did not sign on to the ITF3000 account, or if the TOYDB database does not reside in the account where you signed on, you need to type the account name as well. For example, TOYDB.PUB.ITF3000.
Manual Structure d a c b 3. Look at the items listed on this screen. All the items in the table are listed in alphabetical order. Each row of the source table CUSTOMERS contains a value for every item listed here. The number of rows in the nal access table depends on the number of records used from the CUSTOMERS data set when the report is run. Items that you can use in the report have an X in the Project eld. If you blank out the X, you cannot use the item.
Manual Structure d a c b Notice that there is now an entry, CUSTREP1-DATA, in the Final Access Table eld. This means that the table you just de ned, CUSTREP1-DATA, is the nal table used for this report.
Manual Structure Lesson 2: Quiz 2 Please answer the following questions. The solutions to these questions are supplied in Appendix A. This quiz covers exercises 3, 4, and 5. 1. What is the nal access table? 2. How many nal access tables can you have in a report? 3.
Manual Structure Exercise 6: Defining the Sorts and Breaks In this exercise you will de ne the sort items for the report, set the level of each sort item, and the set direction of the sort. This exercise takes 5 minutes . Define sorts and breaks NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. At the De ne Report screen, choose Define Breaks . BRW displays the De ne Breaks & Pagination screen. d a c b This is where you de ne the structure of the report.
Manual Structure 2. 3. 4. 5. Use the 4Tab5 key to get to the Sort and Break on Item eld. Type SALES-AREA in the Sort and Break on Item eld. Press 4Tab5. Type 1A in the Sort Level and Order eld. 1A means that the item is the rst-level sort item, and that the sort is in ascending order (that is, A to Z or smallest number rst). 6. Use 4Tab5 to get to the second line of the Sort and Break on Item eld. Hint| Press the down cursor arrow and then 4Shift54Tab5. 7. Type the second sort item and order.
Manual Structure The screen should look like this: d a c b BRW displays a message that the breaks and pagination have been modi ed. Don't be concerned about the pagination now. You return to this screen later to set the pagination. Note Break levels Whenever a sort item changes and a break occurs, all lower-level breaks occur as well.
Manual Structure Final Access Table Illustration The following illustration shows how the nal access table is structured after you have sorted it. Notice the callouts showing the rows of the table (these are the report detail lines), the columns of items, and the break when SALES-AREA changes. The item SALES-AREA does not change until all the records for the preceding sales area have been read, because you have sorted the table on SALES-AREA.
Manual Structure Lesson 2: Quiz 3 Please answer the following questions. The solutions to these questions are supplied in Appendix A. This quiz covers exercise 6. 1. If you sort on three sort items, how many break levels can you have? 2. What is meant by a sort level and order of 4D? 3. If you de ne two sort items, what is the maximum number of linesets possible in your report? 4.
Manual Structure Exercise 7: Defining the Linesets You de ne the linesets for the report in this section. Linesets are de ned on the De ne Lines screen. Each type of lineset has a slightly di erent De ne Lines screen. This exercise takes 60 minutes You have already determined what linesets you require when you sketched the report. Refer to the sketch again to remind yourself. Your report has ve linesets. The linesets are described in \The Linesets for This Report". You can de ne the linesets in any order.
Manual Structure The De ne Lines screen is similar for each lineset, so it is worth pausing for a moment to become familiar with it. The banner line shows the name of your report and the type of lineset that you are de ning. The top half of the screen is a window showing how the lineset will look when printed. The lower half shows all the items that are printed in the lineset. The middle of the screen|just below the ruler line|has two elds.
Manual Structure The De ne Lines screen should look like this: Note d a c b Adding lines When you use 4Enter5, BRW places the text on the line speci ed. If there were text already in the line, that text would be overwritten. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN You could use Insert Line instead. Insert Line places the text on a new line above the line speci ed in the line number eld. The lines are renumbered to accommodate the new line.
Manual Structure Exercise 8: Using Standard Items In this exercise, you specify the date and page in your page heading. You use the BRW standard items DATE and PAGE. (A list of BRW standard items is in the HP ALLBASE/BRW Reference Manual .) The step-by-step instructions do not always tell you every keystroke you need to make. For instance, you know how to use 4Tab5 or the cursor keys to move between elds, so this exercise does not tell you to do that. Put a page number on the report 1. 2. 3. 4.
Manual Structure Alter the length of an item 1. Move the cursor to the Len eld for PAGE. 2. Type 3 to overwrite the 9. 3. Press 4Enter5. BRW displays a message to tell you that the item has been modi ed, and cuts the eld length on the display. Note If, during the processing of the report, the number of pages exceeds 999, BRW will generate an exception condition to warn you that you need to change the speci cation of this standard item.
Manual Structure See what the date item mask means NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose other keys . 2. Choose Item Edits . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN BRW displays this prompt at the bottom of the screen: c b 3. Type DATE at the Edit-mask for item prompt to see the meaning of the mask for the DATE standard item. 4. Press 4Enter5. d a c b This is the Item Edits screen for the item DATE. The edit mask for DATE is 0m/0d/yy.
Manual Structure Note Edit Mask Each item has a separate edit mask for each time it is de ned in a line. That is, if you used DATE ve times on the same report or lineset, each occurrence has a separate edit mask and you could have each of the ve dates print in a di erent form. Edit masks are further demonstrated in the fourth tutorial lesson in Chapter 5. Underline the page heading 1. Type 2 in the rst eld to add text to the 2nd line. 2. Type hyphens that overwrite the literal characters in the text eld.
Manual Structure Note Clear Display If you had used the 4Clear Display5 terminal key to clear the line text line, it would have erased the two standard items also. You have now de ned the page heading lineset. Whenever a new page is printed, the three lines you just de ned are printed at the top of the page. The Level 1 Break Heading Lineset From your sketch, you can see that the heading for the rst break (on SALES-AREA) must print the name of the sales area.
Manual Structure The screen should look like this: d a c b Add the headings for the detail lines 1. Type 4 in the line number eld. 2. Type Customer Number at character position 5, Customer Name at character position 25, and Month's Sales at character position 60. 3. Press 4Enter5. Note that if you placed this heading in the detail lineset, a heading would be printed above every detail.
Manual Structure The nal De ne Lines screen for the Heading for Break Level 1 on SALES-AREA should look like this: The Detail Lineset d a c b The next lineset to be de ned is the detail lineset. Define the lines for the detail lineset NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Select Lineset . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN If Select Lineset is not showing, choose other keys until it is showing. 2.
Manual Structure 3. Type 1 in the second eld. That is the lineset level from which you want to copy the line. 4. Type H in the third eld. That is the lineset type (Header) from which you want to copy the line. 5. Type 1 in the fourth eld. That is the line number to which you want the line copied. 6. Press 4Enter5. BRW copies the fourth line from the 1st lineset header to the rst line of the current (detail) lineset. d a c b You can now see where to start the items so that they match the heading.
Manual Structure 3. Indicate that the items are to be placed on line 2. 4. Press 4Enter5. The new line appears in the upper window as shown below. Also, notice that the eld length for each item appears in the Len eld. d a c b If any of the items are not correctly aligned to the heading, adjust the position by changing the Pos eld. Deleting Lines You can see that each item is correctly aligned beneath its respective heading.
Manual Structure Note The Level 1 Break Footing Lineset d a c b If you left the heading in the detail lineset, a heading would be printed above every detail line. You only want the heading when the sales area changes. The next lineset to be de ned is the level 1 break footing lineset. Go to the Define Lines screen for the break footing 1. Choose other keys until you see the Select Lineset function label. 2. Choose Select Lineset .
Manual Structure 5. Type TURNOVER-MTD in the Item eld, > in the Pos eld, and 1 in the Line eld. 6. Move to the ColCalc eld for the TURNOVER-MTD item and type TOTAL. 7. Press 4Enter5. BRW automatically positions the items. When the report runs, BRW calculates the total for all the TURNOVER-MTD values for each sales area. d a c b Other uses for the ColCalc eld include calculating the total, average, maximum value, minimum value, or number of values (count) for any numeric, real number, or integer item.
Manual Structure The Report Footing Lineset d a c b The nal lineset is the report footing lineset. The sketch shows that the report footing will print the total sales for all sales areas. This line will look very similar to the level 1 break footing lineset, except that it will add all the sales for every area. Add a report footing lineset 1. Choose Select Lineset . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. At the prompt, select lineset R (Report) and type F (Footing). 3. Press 4Enter5. 4.
Manual Structure The De ne Lines screen for the Report Footing should look like this: d a c b Edit the lines 1. Go to the Item eld for SALES-AREA. 2. Press 4Clear Line5 (but do not press 4Clear Display5 or you will remove the item TURNOVER-MTD as well.) BRW deletes item SALES-AREA. Note If you are not using a terminal, you may not have a 4Clear Line5 key. In that case, just use the space bar to clear the line. Press 4Enter5 after you delete. NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3.
Manual Structure Your screen should look like this: Note d a c b Quick Browse You can use the Quick Browse feature to move directly to a line. Just type the line number in the line number eld and press either Next Line or Prev Line . BRW moves directly to the line you request. This is very helpful when you have many lines in a lineset.
Manual Structure Lesson 2: Quiz 4 Please answer the following questions. The solutions to these questions are supplied in Appendix A. This quiz covers exercises 6 to 8 of Lesson 2. 1. If you specify the standard item DATE in a lineset, what is printed when the report is run? 2. What screen is used to alter the appearance (edit mask) of an item? 3. What happens if you specify a > character in the Pos eld for an item? 4.
Manual Structure Exercise 9: Reviewing the Layout Your report is now ready to be compiled. Before you compile it, you can review the layout to see, online, how the report will look when printed. This section takes 5 minutes. Review the layout 1. Choose other keys until Review Layout is displayed. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. Choose Review Layout .
Manual Structure Exercise 10: Compiling the Report In this exercise you compile the report speci cation le to produce a report execution le. This section also explains how errors are indicated by compilation messages. This exercise takes 10 minutes. As you compile the report, you must assign a name for the report execution le. You can save the execution le in a di erent group, for example, group BRWEXEC, and then the execution le can have the same name as the speci cation le.
Manual Structure d a c b The message \Report compiled" tells you that the compile was successful. If the compilation failed, print out the compilation listing as explained in the next note. The compilation listing will contain error messages about the processing. 4. When the report is compiled, choose Exit to return to the Select Report screen. NNNNNNNNNNNNNN Note Compilation Messages You can read the compilation listing from the screen or print it out by pressing the Print Complist function key.
Manual Structure Exercise 11: Requesting the Report In this exercise you learn how to stream a job that runs the report execution le; how to set the print device, number of copies, and priority; and how to make sure that the job is running. This exercise takes 10 minutes. When you request the report, BRW streams a job that runs the execution le to produce the report. Request a report 1. Type the name of the execution le, (for instance, CUSTREP1.
Manual Structure 5. Accept the default LP for Print Device. (If you want to print to a disk or see the report on screen, go on to Exercise 12.) 6. Type the print le name CUSTREP1 in the Print le Name eld. For a DISC le, type a new name (not the name of an existing le) in the Print File Name eld and press 4Enter5, or use the same name but save the print le in a di erent group, for example group BRWONLNE. 7. Choose Start Report .
Manual Structure BRW displays a screen similar to this: d a c b Your job is displayed. If the job does not appear, it may already have nished. In that case, go to the printer to collect the report or look at the $STDLIST le. The report should look like the sample report shown after Exercise 12.
Manual Structure Exercise 12: Reviewing the Report Online If you want to view the report online, you can print the report to a disc le and then view it on your screen. 1. On the Request Report screen, change the Print Device eld to DISC. 2. Press 4Enter5. 3. Change the Print File Name eld to the name of a disc le. For instance, CUSTR1D. 4. Start the report. See steps 7 through 9 in Exercise 11. 5. When the report job is completed, you can review the job status using the steps in Exercise 11.
Manual Structure Page: ----- 1 Customer Details Report: 1 -------------------------- Customer Details for Sales Area: -------------------------------Customer Name ------------- 000004 FOURTH ESTATE MAGAZINES Month's Sales ------------- EASTERN Customer Details for Sales Area: -------------------------------Customer Name ------------- 000007 000008 7-UP NOVELTIES EIGHT-BALL CUES INC Customer Name ------------- 000002 000005 SECOND GOODS INC. FIFTH AVENUE FASHIONS is: --- 1038.90 136.55 46.
Manual Structure Execution Statistics 3-48 BRW provides statistics on the amount of CPU and elapsed time the report took to execute. You can use these statistics to improve the performance of a report. The use of execution statistics to improve report performance is fully described in the HP ALLBASE/BRW Reference Manual , and also in the third lesson of this tutorial. The execution statistics are printed in the $STDLIST le.
Manual Structure Lesson 2: Quiz 5 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. What les must you specify when you compile a report? 2. How would you obtain a printed copy of a compilation error? 3. How could you set the output priority of the printed report? 4. If you specify DISC in the Print Device eld of the Request Report screen, what happens to the report? 5.
4 LESSON 3: Refining Your Report This lesson reviews the topics learned in the rst lessons and introduces some new operations. Before You Begin Be sure that your rst report was completed satisfactorily. The Tasks This lesson builds on the rst report to produce an end-of-month report that includes a breakdown of the sales for each customer for the month of June. For each order, your report will now add the order number, product number, price, quantity on order, and total for the order.
Manual Structure Reviewing the Layout Compiling the Report Request the Report Total Time: The Data You Need reset levels with column calculations. 70 minutes 5 minutes 5 minutes 5 minutes 2.75 hours In Lesson 2, the rst report, you used the CUSTOMERS data set from the TOYDB database. For this second report, you will use the following data sets: CUSTOMERS To nd the customer name, sales area, and turnover for the last month. ORDERS To nd the orders, if any, for a customer.
Manual Structure The report has seven linesets, as described below. 1. Page Heading. This lineset prints the report title, page, and date on the top of each page. For this report, it also acts as the report heading. 2. Level 1 Break Heading. This is the lineset that prints a new sales area name when the item SALES-AREA changes. 3. Level 2 Break Heading. This is the lineset that prints a new customer name when the CUSTOMER-NO changes. 4. Detail lineset.
Manual Structure Exercise 1: Creating the Second Report In this lesson, you learn to create a new report by copying an existing one. If you ended your BRW session after the last lesson, restart BRW. See Lesson 1 if you need help in restarting BRW. 1. At the Select Report screen, name the new report CUSTREP2. 2. Choose Add Report . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN c b 3. At the prompt Copy from report, type CUSTREP1. 4. Press 4Enter5. c b 5. At the password prompt, type the password PASS1 6.
Manual Structure Define report 2 If you need more help to do this task, review the steps for de ning a report as outlined in Lesson 2. 1. At the De ne Report screen, type a password, PASS2 in the Report Speci cation Password eld. 2. Type a description in the Report Description eld. For example: Customer Details Report: Tutorial Lesson 3 Since you aren't changing any of the other basic speci cations (such as report width and so on), you don't need to change any of the other elds. 3. Press 4Enter5.
Manual Structure Exercise 2: Defining the Data Access In the rst report, you used only one table. In this report you will use a series of tables that build to one nal access table. This exercise takes about 50 minutes. The following illustration shows how the tables are combined. For more information about how BRW builds tables, read the BRW Reference Manual . Clear the old table NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. At the De ne Report screen, choose Define Table . The De ne Table screen appears.
Manual Structure d a c b Because this report was copied from a previous report, the table name CUSTREP1-DATA appears in the Table eld. 2. Choose other keys until the function label for Delete Table appears. 3. Choose Delete Table . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 4. At the prompt, Confirm deletion of table CUSTREP1-DATA , press 4Enter5 or choose Delete Table again.
Manual Structure d a c b The prompt tells you that the table was added. Defining the relation The next task, de ning a relation, is a new procedure, one that is not necessary when only one table is used, as in your rst report. The relation de nition speci es a common item that must exist between the joined tables. For example, each of the two tables joined in this exercise has an item called ORDER-NO that contains the order number for the sale.
Manual Structure d a c b 2. Type ORDER-NO in the Common Item eld. 3. Press 4Enter5 or choose Add Relation . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Notice that BRW automatically inserts the source table name and the second common item. This happens when there is an item of the same name and type in both joined tables. BRW assumes that the items are related. The message Relation Added appears.
Manual Structure Exercise 3: Defining Relation Conditions Because you have de ned the relation on order number, the table ORDER-TABLE will contain those record where ORDER-NO in ORDERS in equal to ORDER-NO in ORDER-DETAILS. But this will include all orders placed in any month|not just those placed in June. You need to further restrict the records by using a relation condition.
Manual Structure MONTH OF extracts the month from any type of date item as long as the item is described in the BRWDIC (the BRW dictionary). Note The relation condition you just added a ects the table ORDER-TABLE only. Error Checking Checking to see the items BRW checks for syntax errors in the formula immediately. Any errors messages generated are displayed on the screen at the time you enter the formula. You don't have to wait until the report is compiled.
Manual Structure Exercise 4: Relating Another Table Relate the ORDER-TABLE to the PRODUCTS data set. 1. Overwrite the existing entries in the following elds with the new information: Field name New Entry Table ORDER-PRODUCTS Source Tables PRODUCTS ' ORDER-TABLE Location TOYDB.PUB (or TOYDB.PUB.ITF3000) Be sure to blank out any extra characters. You don't need a Location for ORDER-TABLE since it only exists as part of this report, not as a physical le.
Manual Structure d a c b Each record written to the new table, ORDER-PRODUCTS, will contain all the items in the table ORDER-TABLE plus all the items from the PRODUCTS data set for each product number that occurs in the ORDER-TABLE. In this case, you do not need a relation condition to restrict the records because you want all the records in ORDER-TABLE (which is already restricted).
Manual Structure Items with Identical Names Look at the Project Items screen. Notice that there are several items with the same name, PRODUCT-NO and PRODUCT-NO, QUANTITY and QUANTITY, TABLE-REC-NUMBER and TABLE-REC-NUMBER. To avoid confusion, if more than one data item has the same name, BRW projects only the item from the rst source table mentioned on the De ne Table screen.
Manual Structure When you are nished, the De ne Relation screen should look like this: d a c b NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Use the Project Items screen to see if all the items you need are projected. Using Quick Browse d a c b Now the Project Items screen has more items than will t on one screen. There are two ways to look at the rest of the items. a. Choose Next Items to see the next screen of items and then choose Prev Items to return to the previous screen.
Manual Structure The next screen of items begins with the item you just typed. For instance, if you typed CUSTOMER-NAME in the rst Item eld, this is the screen that appears: d a c b You can see from this screen that you have all the items that you need for the report: CUSTOMER-NAME CUSTOMER-NO ORDER-NO PRICE PRODUCT-NO QUANTITY (quantity ordered, not quantity in stock) SALES-AREA TURNOVER MTD At this point, your report will contain all the customers that have placed an order in June.
Manual Structure Define an open join 1. At the De ne Table screen, type ORDER-PRODUCTS in the Open Join on Source Table eld. 2. Press 4Enter5. The message Table modified appears. d a c b NNNNNNNNNNNNNN 3. Choose Exit to return to the De ne Report screen. Notice that the Final Access Table eld shows CUSTREP2-DATA as the Final Access Table for this report.
Manual Structure LESSON 3: Quiz 1 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. Name the three ways in which you can manipulate tables in BRW. 2. If you join two or more tables with the common item ORDER-NO, when will a record be written to the result table? 3. What happens if you do not de ne a relation when joining tables? 4. What is the e ect of de ning the following relation condition? MONTH_OF (SHIPMENT-DATE) = 12 5.
Manual Structure Exercise 5: Define the Sorts and Breaks This exercise describes how to de ne sorts, breaks, and pagination, and how to suppress linesets. It takes about 25 minutes. Define a break NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Define Breaks at the De ne Report screen. The De ne Breaks screen appears. d a c b Look again at your rough sketch, shown on the next page.
Manual Structure The Lineset Notes column of the sketch show the linesets and breaks you need. The breaks on SALES-AREA and CUSTOMER-NO were already de ned in the report CUSTREP1, from which you copied the report for this lesson. You still need to add the sort item, ORDER-NO, to print the details lines in ascending numerical order. Define a sort and break 1. Add ORDER-NO in the Sort and Break on Item eld below CUSTOMER-NO . 2. Type 3A in the Sort Level and Order eld.
Manual Structure This is how the screen will look: d a c b The message tells you that the breaks and pagination are modi ed. Set pagination 1. Look at the right side of the De ne Breaks and Pagination screen. 2. Type 1 in the Reset Page Number on Level eld. This resets the page number to 1 every time a level 1 break occurs. 3. Type B in the Paging eld for the Report Footing. The B causes the new page to print before the report footing is printed. d a c b 4. Press 4Enter5.
Manual Structure Suppress linesets 1. Choose Suppress Lineset . This prompt appears: NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Specify lineset level (D,P,R,1..9): and type (H,F): 2. Type 2 in the lineset level eld. 3. Type F in the (H,F) eld. 4. Press 4Enter5. The Suppress Lineset screen for the Break Level 2 Footing on CUSTOMER-NO appears. 5.
Manual Structure 3. Press 4Enter5. The Suppress Lineset screen appears for the detail lineset. 4. Type this formula in the blank area of the screen: ORDER-NO = NO_VALUE Now, if a customer has no order, nothing will print on the detail line for that customer. 5. Type this comment: << Suppress this lineset, which prints the details of each order, if the customer has no orders. >> 6. Press 4Enter5.
Manual Structure LESSON 3: Quiz 2 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. How would you specify printing a new page after the footing for break on CUSTOMER-NO? 2. How would you make sure that the detail lineset was only printed when the item ORDER-NO was equal to ORD-011? 3.
Manual Structure Exercise 6: Defining the Linesets You de ne the linesets for the report in this section. The report uses multiple break level, layout calculated items, and suppresses certain lines. You will learn about the ColCalc (column calculation) eld logic and how to specify source and reset levels for ColCalc. This exercise takes 45 minutes. Look at the sketch again to see the linesets you need. The report has seven linesets.
Manual Structure 5. Look at the upper part of the De ne Lines Page Heading screen. The screen contains the page heading information you used for the rst report in Lesson 2. 6. Change the report heading to read as follows: Customer Details Report: 2 The screen should now look like this: Note d a c b Did you remember how to get to the text in the upper part of the screen? Type the line number in the rst small eld in the lower part of the screen and type (or modify) the text shown in the longer eld.
Manual Structure d a c b The screen shows the heading for break level 1 on SALES-AREA for the rst report. For the CUSTREP2 report you only need the new sales area in this break heading. You are going to put the customer information in the break level 2 heading. So, you can delete lines 4, 5, and 6 as described in the next steps. 4. Choose other keys until you see Delete Lines . Then choose Delete Lines .
Manual Structure The Level 2 Break Heading Lineset Define the level 2 break heading 1. Choose Select Lineset . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. At the prompt, type 2 and H in the elds of the prompt. 3. Press 4Enter5. This is the De ne Lines screen for the break level 2 heading on CUSTOMER-NO. d a c b This screen contains blank elds because no break level 2 was de ned for the previous report. 4. Look at the rough sketch again.
Manual Structure The screen will look like this: Note d a c b If you had put this heading in the detail lineset, a heading would be printed above every detail. You don't need that many headings. You only want the heading once at the beginning of each set of customers in a sales area. Substituting a line Look at the sketch. It shows a special line that is printed when a customer has no orders. Define a \substitute" line 1. Type 6 in the line number eld. 2. Type this line in the next eld.
Manual Structure Your screen should now look like this: d a c b Suppressing lines You don't always want to print the NO ORDERS . . . line. When you do have a customer with no orders, you don't want to print the introduction lines (the column headings) for the details. Suppress unneeded lines 1. Choose Suppress Line . (Use other keys if you need to bring up more function labels.) 2. At the Suppress condition for line prompt, type 4 to suppress line 4 (the headings for the details). 3. Press 4Enter5.
Manual Structure This screen is similar to the Suppress Lineset screen. 4. Type this text in the blank area provided: ORDER-NO = NO_VALUE << Suppress this line, which introduces the detail lines, if the customer has no orders. >> 5. Press 4Enter5. The prompt tells you that the Suppress Line condition was added. d a c b NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 6. Choose Next Line . The screen typing area of the screen appears blank. The left side of the screen heading shows that you are suppressing line 5. 7.
Manual Structure 11. Press 4Enter5. The line condition is added. When there is one or more orders for a customer, the warning line will not be printed. 12. Choose Exit to return to the De ne Lines screen. NNNNNNNNNNNNNN The Detail Lineset You already have the column headings for the detail lines. Now you add the detail lines to the report. Define the detail lineset NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Select Lineset . 2. At the prompt type D (leave the H,F prompt blank). 3.
Manual Structure Copying lines The items on the Detail line must line up with the headings that you have already de ned on the level 2 break heading lineset (for the break on CUSTOMER-NO). You can ensure that the lines are correctly aligned by temporarily copying the lines from the level 1 lineset and using it as a template. Copy lines from another lineset 1. Choose Copy Lines . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. At the Copy prompt, type 4, the number of the line that you want to copy. 3.
Manual Structure Your screen should show the same Pos for the items as is shown on this screen: d a c b 2. Press 4Enter5 to have the items appear in the upper part of the screen. The elds for Type and Length are automatically lled in by BRW. Using Layout Calculated Items The total for orders is not part of the data set. BRW must calculate this total for the report. You de ne a formula for the calculation and BRW performs the calculation whenever a line is printed that contains the calculated item.
Manual Structure d a c b Later you will learn to set the numeric precision of a result like this. Define the formula NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Define Formula . The Layout Calc Item Formula screen appears. The blank area is where you type the formula. As with all formulas in BRW, you can use up to two screens. You can call user-de ned functions, standard functions, or other calculated items. The formula cannot call itself (this is recursion and is not allowed in BRW).
Manual Structure 2. Type the formula: PRICE * QUANTITY << This item finds the total value of an order by multiplying the price of the product by the quantity ordered. >> d a c b 3. Press 4Enter5. 4. Press 4Exit5 twice to return to the De ne Lines screen. 5. Type the name of the calculated item, ORDER-TOTAL, in the Item eld below the item QUANTITY. 6. Type 47 for the Pos (position) eld and 2 for the line. 7. Press 4Enter5.
Manual Structure You can see that each item is correctly aligned beneath the appropriate heading. (If they aren't correct, take some time to correct them now. You can alter the Len eld to change the eld length or change the Pos eld to correct an alignment.) When you are nished, you don't need the temporary heading line (line 1) anymore. Delete the temporary heading 1. Choose Delete Lines . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. At the prompt, type 1 for the line number you want to delete.
Manual Structure When you nished the screen should look like this: d a c b 4. Type 1 in the line number eld. 5. Start at character position 1 in the text eld and type: Sales for Customer: 6. Space over the other characters in the text eld up to, but not including the text is: 7. Press 4Enter5. The item eld for the total will still appear on the layout. 8. Type CUSTOMER-NAME in the Item eld below TURNOVER-MTD, put it in character position 21 on line 1. 9. Press 4Enter5.
Manual Structure The Level 1 Break Footing Lineset d a c b Define the level 1 break footing 1. Choose Select Lineset and specify lineset level 1, type F. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN The screen shows the footing for break level 1 on SALES-AREA from the previous report. You can use much of this lineset that you copied from the rst report. 2. Look at your sketch for this report. The new lineset for the break on sales area is identical to the old one, except for some new underlining. 3.
Manual Structure Note In your rst report, using the column calculation of TOTAL, BRW printed a running total for that item. Each time a detail line is written, the value for that item is added to the running total. With BRW, the default source level is the detail line. With the column calculation of TOTAL for TURNOVER-MTD on the SALES-AREA break, the total is reset to 0 after each break on a new sales area.
Manual Structure The rough sketch shows that the report footing will print the total sales for all sales areas. 2. Add two lines to emphasize the total. Create a line with double underlining (using equal signs) above the current total line and a line with double underlining under the current total. The screen will look like this: d a c b 3. Adjust the TOTAL in the ColCalc eld for TURNOVER-MTD to reset on the report break level (R) and take values from the break on CUSTOMER-NO (level 2).
Manual Structure LESSON 3: Quiz 3 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. What are the di erences between Suppress Line and Suppress Lineset? 2. When is a layout calculated item calculated? And when is a table calculated item calculated? 3. Can you sort and break on a layout calculated item? If not, why not? 4. Once de ned, can you use a layout calculated item in a formula? 5.
Manual Structure Exercise 7: Reviewing the Layout Your report is now ready to be compiled. But, before you compile it, you can review the layout to see, online, how the report will look when printed. This section takes 5 minutes. Review the layout 1. Use other keys until Review Layout is displayed. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. Choose Review Layout .
Manual Structure Exercise 8: Compiling The Report In this section you compile the report speci cation le to produce a report execution le. This section takes 5 minutes. Compile the report 1. Choose Exit to return to the Select Report screen. NNNNNNNNNNNNNN 2. Choose Compile Report . BRW displays the Compile Report screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Naming the Execution File d a c b Before you compile the report, you must assign a name for the Report Execution File.
Manual Structure When the compilation is nished, the screen display will be similar to the following. d a c b The message \Report compiled" tells you that the compile was successful. You can read the compilation listing from the screen or print it out by pressing the Print Complist . The compilation listing tells you how the data was accessed under the heading DATA ACCESS STRUCTURE. The HP ALLBASE/BRW Reference Manual discusses how to use the compilation listing to improve the performance of reports.
Manual Structure Exercise 9: Requesting The Report When you request the report, BRW streams a job that runs the execution le to produce the report. Request the report 1. Type the name of the execution le, CUSTREP2.BRWEXEC, in the Report eld. 2. Choose Request Report . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. Choose Start Report . BRW prompts for the User, Group, and Account for the job. The default User, Group, and Account are the logon ones. 4.
Manual Structure c b BRW displays the message Job streamed, Job number is #Jnnn . See how the job is progressing 1. Choose Exit to return to the Select Report screen. NNNNNNNNNNNNNN 2. Choose other keys . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. Choose Show Jobs . NNNNNNNNNNNNNNNNNNNNNNNNNNNNN BRW displays a screen similar to the next screen shown here. d a c b Your job status will be displayed. If the job does not appear, it may already have nished.
Manual Structure Example Printouts Page: ----- Your report should look like the illustrations on the following pages. 1 Customer Details Report: 2 -------------------------- Date: ----- Customer Details for Sales Area: EASTERN -------------------------------Customer: FOURTH ESTATE MAGAZINES Number: --------------Order No. Product No. --------- ----------ORD-004 A00002 Price ----12.50 Quantity -------1 Page: ----- 1 000004 Total for Order --------------12.
Manual Structure Page: ----- 1 Customer Details Report: 2 -------------------------- Date: 04/20/92 ----- Customer Details for Sales Area: SOUTHERN -------------------------------Customer: SECOND GOODS INC. --------Order No. --------ORD-002 ORD-008 ORD-010 Product No. ----------A00005 A00007 A00003 Price ----17.45 1.75 1.25 Number: ------Quantity -------5 25 3 Sales for Customer: SECOND GOODS INC. Customer: FIFTH AVENUE FASHIONS --------Order No. --------ORD-005 ORD-006 Product No.
Manual Structure Page: ----- 1 Customer Details Report: 2 -------------------------- Date: ----- 04/20/92 Customer Details for Sales Area: WESTERN -------------------------------Customer: FIRST REALTY INC. --------Order No. --------ORD-001 ORD-009 Product No. ----------A00003 A00001 Price ----1.25 0.75 Number: ------Quantity -------10 10 Total for Order --------------12.50 7.50 Sales for Customer: FIRST REALTY INC. Customer: THIRD-FLOOR DESIGN INC --------Order No.
Manual Structure LESSON 3: Quiz 4 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. Can you supply a password for the report execution le? 2. If an item projected from a table equals NO VALUE, what does that indicate about the relational operation on that table? 3.
5 LESSON 4: Considering Performance This lesson revises the topics learned in the rst three lessons, and introduces some new topics. Before You Begin The Tasks Be sure your last report satis ed all the requirements for the previous lessons. The exercises in this lesson are built on information from the previous reports. In this lesson you will enhance the report to provide a report that restricts information on the total sales in all areas to the board of directors only.
Manual Structure What You Will Learn 5-2 Exercise Description Adding and Describing 10 minutes the 3rd Report De ning the Data How to use many source tables in a table, and how to use more than one relation in Access a table. Introduction to keyed access and BRW's access sequence. How to specify keyed access and access sequence on the Tune Access screen. 60 minutes Understanding Keyed How to use keyed access.
Manual Structure The Report Data The data is the same as the data for the previous report. That is, you will use the contents of the TOYDB data sets CUSTOMERS, ORDERS, ORDER-DETAILS, and PRODUCTS. The plan of the database TOYDB is shown in the gure of the TOYDB database in Chapter 1. All the data in TOYDB is described in the dictionary le, BRWDIC.PUB.
Manual Structure Here is a rough sketch of this report: 5-4 LESSON 4: Considering Performance
Manual Structure The Linesets for This Report This report has eight linesets, as described below. 1. The Report Heading lineset. This lineset prints the report title, the user who requested the report, the time and date on which it was run, a description of the report (whether for one or all sales areas), and the parameters and values selected. 2. The Page Heading lineset. This lineset prints the page number and date on the top of every page except the report heading. 3.
Manual Structure Exercise 1: Adding and Describing the Third Report If you have left BRW since the last lesson, restart BRW and begin at the Select Report screen. This exercise will take about 5 minutes Add the report 1. Type CUSTREP3 in the Report eld. 2. Choose Add Report . BRW displays the following prompt: NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN c b 3. Type CUSTREP2 in the Copy from report eld. 4. Press 4Enter5 or choose Add Report .
Manual Structure Add a Password and Report Description 1. Type a password, PASS3, in the Report Speci cation Password eld 2. Type the description Customer Details Report: Tutorial Lesson 4 in the Report Description eld. As in the previous report, you will use the standard paper type, length, and width as well as the standard character set for string sorting. Therefore, do not change these elds. 3. Press 4Enter5. BRW displays the message \Report modi ed" to tell you that the changes were made.
Manual Structure Exercise 2: Defining the Data Access This exercise describes how to use more than two source tables in a report and how to use more than one relation in a table. This exercise also introduces keyed access and access sequence and shows you how to use the Tune Access screen to specify keyed access and access sequence. This exercise takes 60 minutes . You de ned the data access in the previous report.
Manual Structure 2. Type CUSTREP3-DATA in the Table eld, overwriting CUSTREP2-DATA. 3. Press 4Enter5. 4. When BRW prompts \Press ENTER to con rm renaming the table", press 4Enter5 again to rename the table CUSTREP3-DATA. 5. Choose Next Table until the table ORDER-TABLE is displayed, as shown in the next table. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN d a c b NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 6. Choose Delete Table . 7.
Manual Structure Your screen should look like this: d a c b Define the relation between the tables NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Define Relation . The relation from the old table is shown in the next table. d a c b Note that the source table ORDER-TABLE is highlighted, and that BRW prompts \Source table doesn't exist". This is because you have deleted it. You need to replace it with the correct source table for PRODUCT-NO. 2.
Manual Structure The screen now looks like this: Using More Than Two Relations d a c b Now you must add a new relation between the data sets ORDERS and ORDER-DETAILS. Add the relation between ORDERS and ORDER-DETAILS 1. Type ORDER-NO in the Common Item eld, (overwriting PRODUCT-NO). 2. Press 4Clear Display5 or space over all the remaining text for the common items and source tables. 3. Choose Add Relation . (DO NOT press 4Enter5 because that would overwrite the previous relation.
Manual Structure Note If the item ORDER-NO had been in all three source tables, BRW would have added a further Common Item, ORDER-NO of PRODUCTS, to this relation. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 4. Choose Relation Condit'n . BRW displays the De ne Relation Condition screen. 5. Type the following text: MONTH_OF (ORDER-DATE) = 6 << Selects only those orders placed in June. >> 6. Press 4Enter5. BRW adds the relation formula. d a c b NNNNNNNNNNNNNN 7.
Manual Structure Exercise 3: Understanding Keyed Access One of your tasks for this lesson requires that your reports use keyed access and that you demonstrate the performance improvements of keyed as against serial access. The performance improvements will only be apparent once the report is run. Therefore, this comparison, and a detailed examination of access tuning and methods, are discussed at the end of Lesson 4, when you run the report. This exercise takes about 15 minutes.
Manual Structure Use Tuned Access NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Tune Access to display the Tune Access screen. 2. 3. 4. 5. d a c b The three source tables are listed in the order in which they appear on the De ne Table screen. The default access sequence (the order in which they will be read) is this order. But you want to read ORDERS rst, followed by ORDER-DETAILS, and nally PRODUCTS. Type 3 in the Access Sequence eld beside PRODUCTS.
Manual Structure d a c b BRW tells you that the access sequence has been modi ed. 4. Choose Exit twice to return to the De ne Report screen. NNNNNNNNNNNNNN d a c b Note that the new nal access table is CUSTREP3-DATA.
Manual Structure LESSON 4: Quiz 1 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. What is the maximum number of source tables that can be joined to form a table? 2. If you specify the source tables CUSTOMERS and INVOICES in that order on the De ne tables screen, what will be the default access sequence? 3. Can you change the above access sequence? 4.
Manual Structure Exercise 4: Defining the Selection Sets Overview This exercise shows you how to de ne parameters, and describes the three types of parameters. This exercise also describes the operator SATISFIES, shows you how to de ne selection sets, and selection conditions. This exercise takes 40 minutes. You need to restrict the report so that: sales managers can only obtain information concerning one sales area You can select the customers to be included in the report at run time.
Manual Structure Your Task You want two reports, one for directors that allows access to all sales areas, and one for sales managers, which is restricted to one sales area only. Additionally, you want to be able to choose either all customers or a subset of customers. You can use parameters to select a subset of customers and a sales area, and use two selection sets to provide two execution les, one for directors, and one for sales managers.
Manual Structure d a c b This is the Selection Condition screen. You want a condition that will select only those customers that are requested by the user at run time, that is, only those customers speci ed in a parameter. 2. Type the following text: CUSTOMER-NO SATISFIES ?CUSTOMER-NO << This selection condition selects only those customers whose customer number matches the parameter CUSTOMER-NO. >> 3. Press 4Enter5. The prompt tells you that the parameter CUSTOMER-NO does not exist.
Manual Structure NNNNNNNNNNNNNNNNNNNNNNNNNNNNN The Exit Save key lets you leave an incomplete formula and correct it later. (If you just choose Exit on an incomplete formula, the formula would be erased). NNNNNNNNNNNNNN When you de ne the parameter, this formula will be correct. Define a parameter 1. Choose Define Parms to de ne the parameters. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN BRW displays the Parameter screen. d a c b 2. Type CUSTOMER-NO in the Parameter eld. 3. Type 6 for the Value Length.
Manual Structure At run time the parameter will be converted to uppercase. 7. Choose Add Parm . NNNNNNNNNNNNNNNNNNNNNNNNNN d a c b BRW adds the parameter. Note A parameter and an item can have the same name; you di erentiate between them in formulas and in the report layout by specifying a ? before the parameter name. Finish defining the CUSTOMER-NO parameter 1. Choose Exit to return to the De ne Selection screen. NNNNNNNNNNNNNN 2. Type CUSTOMER-NO (without a preceding \?") in the Parameter eld. 3.
Manual Structure d a c b NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 4. Choose Select'n Condit'n . The Selection Condition screen appears. d a c b Because you have de ned the parameter CUSTOMER-NO, the formula is now correct. No error is indicated and the Exit Save key is not displayed. NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 5. Choose Exit to return to the De ne Selection screen. NNNNNNNNNNNNNN You have now de ned the selection set for directors.
Manual Structure Defining the Selection Set for Sales Managers The selection set for sales managers is much the same as that for directors, except that sales managers must only be allowed to report data on one sales area. So you need one more parameter that selects one, and only one, sales area. Define the selection set NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Define Parms . 2. 3. 4. 5. 6. BRW displays the screen for parameter CUSTOMER-NO.
Manual Structure NNNNNNNNNNNNNN 10. Choose Exit to return to the De ne Selection screen. Define the selection set for sales managers 1. Type SALES-MANAGERS in the Selection Set eld. 2. Type AREA in the parameter eld below CUSTOMER-NO. 3. Choose Add Select'n . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN BRW tells you the selection was added. d a c b Define the selection condition for the selection set NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 1. Choose Select'n Condit'n . 2.
Manual Structure Your screen should look like the following example: d a c b When the report is compiled with selection set SALESMANAGERS, only those records that are in the sales area speci ed, and that satisfy the comparison predicate parameter CUSTOMER-NO, will be used in the report. 4. Choose Exit to return to the De ne Selection screen. NNNNNNNNNNNNNN d a c b Anyone running the report, when compiled with selection set SALES-MANAGERS, must enter a value for the required parameter AREA.
Manual Structure Note There is no restriction on which sales area is reported. If you wanted to restrict the report to one sales area you can de ne a further selection set, with the following selection condition: (SALES-AREA = "EASTERN") AND (CUSTOMER-NO SATISFIES ?CUSTOMER-NO) You could de ne four such selection sets, each with a selection condition restricting the sales area to one of the four areas. In that case, you would not need the parameter AREA.
Manual Structure LESSON 4: Quiz 2 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. How many selection sets and parameters can you have in a report? 2. What is the di erence between the parameter result (or data ) type and the type of parameter ? 3. \The main di erence between a relation condition and a selection condition is that the relation condition is evaluated earlier and so is more e cient." True or False? 4.
Manual Structure Exercise 5: Defining the Sort and Breaks This exercise describes how to use parameters, how to use the standard item SELECTION-SET in a condition, and how to suppress the page heading. This exercise takes 20 minutes. You can use the sorts and breaks that you de ned for the previous report in this report. However, you do not want to print the report footing lineset when the report is compiled with selection set SALES-MANAGERS.
Manual Structure When the selection set is SALES-MANAGERS, you want to suppress the total for all areas. 6. Type the following text: SELECTION-SET = "SALES-MANAGERS" << Suppress this lineset, which prints the total sales for all selected customers in the month, if selection set is for sales managers. >> Note You must enter the value SALES-MANAGERS in uppercase letters, as shown. BRW is case sensitive when information appears inside double quotation marks ("). 7. Press 4Enter5.
Manual Structure Absolute Positioning of Linesets For this report, you have a report heading that introduces the report and prints the page number and date. The report heading is printed at the top of the rst page of the report. The page heading also prints the page number and date and is normally printed at the top of every page of the report. Because the report heading already prints this information on the rst page, you do not need the page heading to print also.
Manual Structure LESSON 4: Quiz 3 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. Assuming a page length of 60, and that the break footing on CUSTOMER-NO has 5 lines, what would happen to the page footing lineset if you positioned the break footing for CUSTOMER-NO on line 56? 2. How can you reserve space for a lineset whether or not that lineset is printed? 3.
Manual Structure Exercise 6: Define the Linesets Note Defining the Report Heading Lineset You de ne the linesets for the report in this exercise.
Manual Structure Your screen should look like the next gure. d a c b Add the USER and TIME standard items 1. Add underlining on line 2 for the text Report: and Date: on line 1. 2. Insert a blank line after line 2 3. Add line 4 to shows who requested the report, and at what time. In the new line, use the standard items USER and TIME. At run time, BRW uses the name of the user reqeusting the report and the time the report is run. 4. Underline the text in line 4. 5. Add an additional blank line.
Manual Structure Add two report descriptions You need one description for a report run by a director and a di erent description when the report is run by a sales manager. 1. Put this text on lines 7 and 8: This report prints order details for selected customers in all Sales Areas in June, followed by the total sales for all Areas in June. 2. Put this text on lines 9 and 10: This report prints the orders for selected customers in Area in June, followed by the total sales in June for that Sales Area.
Manual Structure Print Single-Value Parameters 1. Return to the De ne Lines screen. 2. Type ?AREA in the Item eld. 3. Type 62 in the Pos. eld and 9 in the Line eld. At this position, the area selected at run time will print one space after the word Area on line 9. If this spacing is not correct, adjust the positioning of the text or reposition the parameter. 4. Press 4Enter5. Print the report parameters and values The nal task on the Report Heading is to print the values for the parameters. 1.
Manual Structure Adding the Standard Items for REQUEST-DATA n You have now de ned the heading for the parameters. You can print the parameters, and any values for them, using the standard items REQUEST-DATAn . REQUEST-DATAn prints any parameters and values speci ed on the Request Report screen. REQUEST-DATA1 would be the rst value speci ed on the screen, REQUEST-DATA2 would be the second, and so on. You can specify up to 50 parameters.
Manual Structure 4. Change the literals on line 1 to read: Customer Details Report: 3 5. Press 4Enter5. Your screen should look like the following example: Level 1 Break Heading and Level 2 Break Heading Linesets The Detail Lineset d a c b As you can see from your report sketch, there are no changes to the heading for break on SALES-AREA or the heading for break on CUSTOMER-NO. You do not need to make any changes to these break levels. The next lineset to be altered is the detail lineset.
Manual Structure d a c b NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 3. Choose Item Edits . BRW displays the following prompt: c b 4. Type ORDER-TOTAL. 5. Press 4Enter5. BRW displays the Item Edit screen for the numeric item ORDER-TOTAL, as shown below. d a c b This edit mask is for item ORDER-TOTAL, when positioned on line 1, position 47, on the detail lineset.
Manual Structure Note The Line and Position elds edit mask You can position ORDER-TOTAL (or any other item) as many times as you like in a report, and each position can have a di erent edit mask. Each edit mask a ects only how an item will appear when printed in a certain position. The edit mask is ZZZZZZZZZZZZ9.99. Note that the item length is 16. Therefore, the item can have a maximum of exactly 16 characters, that is, 13 numbers before the decimal point, the decimal point, and two numbers after it.
Manual Structure d a c b Now BRW accepts the edit mask, and tells you the item edits were modi ed. Note You could have also made room for the dollar sign by increasing the length of the edit mask to 17, rather then by taking away a possible digit. However, 11 digits before the decimal point still allows you a maximum number of 99999999999.99: quite large enough for item ORDER-TOTAL. Look at the Adjust field An X in this eld means that the pre x or su x will be printed next to the number.
Manual Structure The maximum number for this item is now 999,999,999. For this report that is su cient. In other reports you create, you can change the edit mask to accommodate the maximum number you need for the report total. The Edit Mask eld should appear as shown in this screen: d a c b Now, if ORDER-TOTAL is 2000.90, it will be printed as $2,000.90. Note that the leading zeros and commas are suppressed. Change the edit mask for PRICE 1.
Manual Structure You have now altered all the edit masks for the detail lineset. The Level 2 Break Footing Lineset You must now alter the edit mask for item TURNOVER-MTD on the level 2 break footing lineset, that is, the footing for break on CUSTOMER-NO. Change the edit mask for TURNOVER-MTD 1. Choose Select Lineset on the Item Edits screen. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN You can go directly to the Item Edits screen for another lineset.
Manual Structure 3. Select F for footing. BRW displays the Item Edits screen for item TURNOVER-MTD on the break footing on SALES-AREA. 4. Add the commas and the dollar sign as you did with item ORDER-TOTAL on the detail lineset. Print a message for a zero value There is one more alteration to make to this edit mask. Note that the item TURNOVER-MTD on this break footing is the sum of all the customers in the area.
Manual Structure d a c b The existing lineset prints the total for all customers in the sales area. But this could be misleading in this report, as the user can select a subset of customers, using the parameter CUSTOMER-NO. You must therefore emphasize that this lineset prints only those customers selected, that is, the value of TURNOVER-MTD will be the total for the selected customers in the sales area.
Manual Structure The Report Footing Lineset The next lineset to be altered is the report footing lineset. The existing report footing lineset prints the total for all customers in all sales area. But, as with the previous lineset, this could be misleading in this report, as the user can select a subset of customers, using the parameter CUSTOMER-NO.
Manual Structure 3. Add the commas and the dollar sign as you did with item ORDER-TOTAL on the detail lineset. Your screen should look like the following screen. d a c b NNNNNNNNNNNNNN 4. Choose Exit to return to the De ne Lines screen.
Manual Structure LESSON 4: Quiz 4 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. From where does BRW take the value of standard item REPORT-DESCRIPT? 2. How could you specify that a negative value for an item be printed in parentheses? 3. \If you de ne TURNOVER-MTD on the Item Edits screen with Scaling = 0, Decimals = 0, and Rounding = T, you will truncate item TURNOVER-MTD so that it has no decimals." True or False? 4.
Manual Structure Exercise 7: Reviewing the Layout Your report is now ready to be compiled. But, before you compile it, you can review the layout to see, online, how the report will look when printed. This exercise takes about 5 minutes. Review the report layout 1. Choose other keys until Review Layout is displayed. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 2. Choose Review Layout . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN BRW displays the following screen.
Manual Structure Exercise 8: Compiling the Report In this exercise you will compile the report speci cation le to produce a report execution le. This exercise takes 10 minutes. You compile the report speci cation le, in combination with a selection set, to produce the report execution le. Compilation of a report is fully described in the HP ALLBASE/BRW Reference Manual . You will need to compile the report twice, once with the selection set DIRECTORS, and once with the selection set SALES-MANAGERS.
Manual Structure When the compilation is nished, the screen display should be similar to the following example: d a c b The message \Report compiled" tells you that the compile was successful. You can read the compilation listing from the screen. Note Look the warning for parameter AREA. This parameter is not de ned in the selection set, and so BRW will use NO VALUE whenever it is used in the report.
Manual Structure Compile the report for sales managers 1. Type SALES-MANAGERS in the Selection Set eld. 2. Type SALESMAN.BRWEXEC in the Report Execution File eld. 3. Press 4Enter5. d a c b The message \Report compiled" tells you that the compile was successful. There is no warning this time: parameter AREA, used in the report, is also de ned in the selection set. 4. Choose Exit to return to the Select Report screen.
Manual Structure Exercise 9: Request the Report This exercise shows you how to enter values for parameters, and explains the use of \required" parameters. This exercise takes 10 minutes. When you request the report, BRW streams a job that runs the execution le to produce the report. For this report you have two execution les, one compiled with selection set DIRECTORS, the other with selection set SALES-MANAGERS. This time, you can restrict the customers reported using parameters.
Manual Structure NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 4. Choose Start Report . 5. Respond to the prompts for user, account, group and passwords until BRW returns the job number. BRW displays the message \Job streamed, Job number is #Jnnn ". The Reqest Report screen is still displayed. Request the directors report with restrictions 1. Type > "000002" in the Values eld beside CUSTOMER-NO. 2. Start the report again.
Manual Structure d a c b BRW highlights the Values eld beside AREA. This is because a value is required for this parameter and so you cannot run the report without a value for it. 4. Type "WESTERN" in the Values eld beside AREA. 5. Choose Start Report . NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 6. Respond to the prompts until BRW returns the job number. Run this report with a subset of customers 1. Type > "000002" in the Values eld beside CUSTOMER-NO and Choose Start Report .
Manual Structure Example Printouts Your ve reports should look like the following illustrations. Run (1) No restrictions on the report execution le DIRECTRS. A copy of this le is held in le SOL3DIR1.BRWONLNE.ITF3000. Report: Customer Details Report: Tutorial Lesson 4 ------Report requested by: DAVE -------------------- Date: 04/20/92 ----- at: 16:22 --- This report prints order details for selected customers in all Sales Areas in June, followed by the total sales for all Sales Areas in June.
Manual Structure Page: ----- 1 Customer Details Report: 3 -------------------------- Date: ----- 04/20/92 Customer Details for Sales Area: NORTHERN -------------------------------Customer: 7-UP NOVELTIES --------- Number: ------- 000007 ******************** NO ORDERS FOR THIS CUSTOMER ******************** Customer: EIGHT-BALL CUES INC --------Order No. Product No. --------- ----------ORD-011 ORD-012 ORD-013 ORD-014 5-56 A00004 A00008 A00003 A00002 Price ----$15.25 $0.95 $1.25 $12.
Manual Structure Page: ----- 1 Customer Details Report: 3 -------------------------- Date: 04/20/92 ----- Customer Details for Sales Area: SOUTHERN -------------------------------Customer: SECOND GOODS INC. --------Order No. Product No. --------- ----------ORD-002 ORD-008 ORD-010 A00005 A00007 A00003 Price ----$17.45 $1.75 $1.25 Number: ------Quantity -------- Customer: FIFTH AVENUE FASHIONS --------- ORD-005 ORD-006 A00001 A00005 Price ----$0.75 $17.
Manual Structure Page: ----- 1 Customer Details Report: 3 -------------------------- Date: ----- 04/20/92 Customer Details for Sales Area: WESTERN -------------------------------Customer: FIRST REALTY INC. --------Order No. Product No. --------- ----------- Price ----- ORD-001 ORD-009 $1.25 $0.75 A00003 A00001 Number: ------Quantity -------- Total for Order --------------- 10 10 $12.50 $7.50 Sales for Customer: FIRST REALTY INC. Customer: THIRD-FLOOR DESIGN INC --------Order No. Product No.
Manual Structure Report: Customer Details Report: Tutorial Lesson 4 ------Report requested by: DAVE -------------------- Date: 04/20/92 ----- at: 16:23 --- This report prints order details for selected customers in all Sales Areas in June, followed by the total sales for all Sales Areas in June. Customers were selected for this report depending upon the values you gave for the parameters shown below. Note: If CUSTOMER-NO is blank, all customers are selected.
Manual Structure Page: ----- 1 Customer Details Report: 3 -------------------------- Date: ----- 04/20/92 Customer Details for Sales Area: NORTHERN -------------------------------Customer: 7-UP NOVELTIES --------- Number: ------- 000007 ******************** NO ORDERS FOR THIS CUSTOMER ******************** Customer: EIGHT-BALL CUES INC --------Order No. Product No. --------- ----------ORD-011 ORD-012 ORD-013 ORD-014 A00004 A00008 A00003 A00002 Price ----$15.25 $0.95 $1.25 $12.
Manual Structure Page: ----- 1 Customer Details Report: 3 -------------------------- Date: 04/20/92 ----- Customer Details for Sales Area: WESTERN -------------------------------Customer: THIRD-FLOOR DESIGN INC --------Order No. Product No. --------- ----------ORD-003 ORD-007 A00010 A00006 Price ----$1.85 $25.00 Number: ------Quantity -------- 000003 Total for Order --------------- 10 11 $18.50 $275.
Manual Structure Report: Customer Details Report: Tutorial Lesson 4 ------Report requested by: DAVE -------------------- Date: 04/20/92 ----- at: 16:30 --- This report prints the orders for selected customers in Area WESTERN in June, followed by the total sales in June for that Sales Area. Customers were selected for this report depending upon the values you gave for the parameters shown below. Note: If CUSTOMER-NO is blank, all customers are selected.
Manual Structure Run (4) Report execution le SALESMAN run with parameter AREA = \WESTERN" and parameter CUSTOMER-NO > \000002". No restriction on the customers reported. A copy of this le is held in le SOL3SMN2.BRWONLNE.ITF3000. Report: Customer Details Report: Tutorial Lesson 4 ------Report requested by: DAVE -------------------- Date: 04/20/92 ----- at: 16:34 --- This report prints the orders for selected customers in Area WESTERN in June, followed by the total sales in June for that Sales Area.
Manual Structure Run (5) Report execution le SALESMAN run with parameter AREA = \WESTERN" and parameter CUSTOMER-NO = \000006". Note that SUBSET ONLY is printed in the totals line for the sales area. This shows the use of the When zero print text eld on the Numeric Item Edits screen.
Manual Structure LESSON 4: Quiz 5 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. If you have a report with ve di erent selection sets, and you compile the report with each set: (a) How many execution les will you have? (b) How many speci cation les will you have? 2. Can you request a report containing a \required" parameter without supplying a value for that parameter? 3.
Manual Structure Report Performance This exercise explains how to read the compile listing and execution statistics, and how BRW accesses tables. This exercise also explains access blocks, the use of keyed access, and how to improve report performance. This exercise takes 30 minutes. You have written and run the report, now you need to show the di erences in performance when using keyed access.
Manual Structure T A B L E 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 ================================================================= CUSTREP2-DATA (WORK007) WORK005 ORDER-PRODUCTS (WORK006) WORK005 CUSTOMERS,TOYDB.PUB ORDER-PRODUCTS (WORK006) WORK003 ORDER-TABLE (WORK004) WORK003 PRODUCTS,TOYDB.PUB ORDER-TABLE (WORK004) WORK001 WORK002 WORK001 ORDERS,TOYDB.PUB Selection: month_of (ORDERS.ORDER-DATE) = 6 WORK002 ORDER-DETAILS,TOYDB.
Manual Structure Lesson 3 Execution Statistics HP ALLBASE/BRW (A.01.31) HP35360 (c) Copyright Hewlett-Packard GmbH 1986-1992 KSAM (A.07.03) Configuration File opened is BRWCONF.PUB.SYS Execution File: CUSTREP2.BRWEXEC.
Manual Structure Access Block 5: read-dset serial CUSTOMERS,TOYDB write WORK005 sort WORK005 process time totals for block (0 segment loads for block ) Access Block 6: read-mpe read-mpe write sort process serial WORK003 search WORK004 WORK006 WORK006 time totals for block (0 segment loads for block ) Access Block 7: read-mpe read-mpe write sort process serial WORK005 search WORK006 WORK007 WORK007 time totals for block (0 segment loads for block ) Access Block 8: read-mpe print process serial WORK007
Manual Structure Access Blocks and Workfiles Access Structure Performance Information The BRW reporting process is split into steps called access blocks. Each block has one or more input les and an output le (work le). A work le is sorted if it is to be used later in a join. The work les are temporary les, called WORKnnn . That is, the rst work le used is called WORK001, the second WORK002, and so on. The nal work le is the nal access table for the report.
Manual Structure Lesson 3 Execution Statistics Explanation Look at the execution statistics for lesson 3. In the rst Access Block, BRW reads the ORDERS data set serially and writes it into a work le, WORK001. This work le is sorted on ORDER-NO. In the second access block BRW reads the data set ORDERDETAILS into a second work le, WORK002, and also sorts it on ORDER-NO. In the third access block, BRW reads the data set PRODUCTS serially, writes it to a work le WORK003 and sorts the work le on PRODUCT-NO.
Manual Structure with ORDER-NOs that match the values of ORDER-NO in ORDERS. The same is true of data set PRODUCTS. You only want information on those products that have been ordered in June, that is, that are present in the join of tables ORDERS and ORDER-DETAILS. So, if you use keyed access on PRODUCT-NO, using the values of PRODUCT-NO found in ORDER-DETAILS, you will automatically have the records that you want without any sorting.
Manual Structure Lesson 4 Compilation Listing HP ALLBASE/BRW (A.01.31) HP35360 (c) Copyright Hewlett-Packard GmbH 1986-1992 Configuration File opened is BRWCONF.PUB.SYS Specification File: CUSTREP3/.BRWSPEC.ITF3000 Selection Set : DIRECTORS Execution File : DIRECTRS/.BRWEXEC.
Manual Structure Lesson 4 Execution Statistics HP ALLBASE/BRW (A.01.31) HP35360 (c) Copyright Hewlett-Packard GmbH 1986-1992 KSAM (A.07.03) Configuration File opened is BRWCONF.PUB.SYS Execution File: DIRECTRS.
Manual Structure Access Block 4: read-mpe print process serial WORK003 DIRECTRS time totals for block (2 segment loads for block ) Report Info : time totals for report (3 (3 (3 #records cpu-sec ---------- ----------16 0.036 99 0.340 0.082 =========== 0.458 elapsed-sec ----------0.099 0.640 0.083 =========== 0.822 cpu-sec =========== 1.480 elapsed-sec =========== 3.
Manual Structure Why Does the Third Report Execute Faster? You can see that the number of access blocks and work les has been reduced, and the report executes signi cantly faster, even with a small example database like TOYDB. This is because, using keyed access: You avoided reading the entire PRODUCTS and ORDERDETAILS data sets; you just extracted the records you wanted. You avoided writing two work les for the data sets PRODUCTS and ORDER-DETAILS.
Manual Structure LESSON 4: Quiz 6 Please answer the following questions. The solutions to these questions are supplied in Appendix A. 1. What is the default access sequence and method? 2. Is it necessary to know about Image databases/KSAM les: (a) Before using BRW? (b) Before tuning BRW reports? 3. Where could you nd out when BRW makes a data selection? 4. Where could you nd out how much time a report took to run? 5.
A Answers to Quiz Questions Lesson 1: Quiz 1 1. What is the name of the sample database you will use for this tutorial? The sample database is TOYDB. 2. Where is the sample database located? In the PUB group of the ITF3000 account, unless you or your database administrator/system manager have changed it. If you have changed the location of TOYDB, write it's new location here: 3. What is the name of the data dictionary you will use for the sample database? BRWDIC 4.
Manual Structure Lesson 2: Quiz 1 1. How many sorts can you have in a report? You can have up to 9 sort levels in a report, that is up to 9 sort items. 2. How many break levels can you have in a report? You can have up to 9 break levels in a report, that is, a break on each sort item, up to the maximum of 9 sort items. 3. What total will be printed on the break footing on SALES-AREA? The total sales in the month for each sales area will be printed on the break footing for item SALES-AREA.
Manual Structure Lesson 2: Quiz 3 1. If you sort on three sort items, how many break levels can you have? Up to three break levels, one for each sort item. Note that this does not include breaks for page and report. 2. What is meant by a sort level and order of 4D? It means that the item is the fourth sort level, and that the item will be sorted in descending order. 3. If you de ne two sort items, what is the maximum number of linesets possible in your report? Nine.
Manual Structure You can obtain the average (AVG), the minimum value (MIN), the maximum value (MAX), or the number of times the item occurred (COUNT). Lesson 2: Quiz 5 1. What les must you specify when you compile a report? When you compile a report, you must specify the name of the report speci cation le and of the report execution le on the Compile Report screen. 2.
Manual Structure Lesson 3: Quiz 1 1. Name the three ways in which you can manipulate tables in BRW. Join, Open Join, and Merge. 2. If you join two or more tables with the common item ORDER-NO, when is record written to the result table? A record is written to the result table whenever an identical value for ORDER-NO appears in every table. 3. What happens if you do not de ne a relation when joining tables? BRWjoins each record of one table with each record in the others. (A Cartesian product.
Manual Structure 3. How would you specify printing a new page before each sales area without resetting the page number? By specifying a \B" in the Paging eld of the heading for SALES-AREA. Lesson 3: Quiz 3 A-6 Answers to Quiz Questions 1. What are the di erences between Suppress Line and Suppress Lineset? You suppress a line from the De ne Lines screen; one line only is suppressed. You suppress a lineset from the De ne Breaks screen; an entire lineset is suppressed.
Manual Structure Lesson 3: Quiz 4 1. Can you supply a password for the report execution le? Yes. You specify the password in the Password eld on the Compile Report screen. (The password becomes the execution le's lockword). 2.
Manual Structure Lesson 4: Quiz 1 1. What is the maximum number of source tables that can be joined to form a table? You can join up to 16 source tables on one screen. You can also have an unlimited series of tables. That is, you can join 16 source tables into an intermediate table, and then join that intermediate table with another 15 source tables, and so on. 2.
Manual Structure 3. \The main di erence between a relation condition and a selection condition is that the relation condition is evaluated earlier and so is more e cient." True or False? False. There is no di erence in e ciency; both conditions are evaluated as early as possible. The only di erence between relation and selection conditions is that relation conditions always apply to a report, and selection conditions apply only if the report is compiled with the appropriate selection set. 4.
Manual Structure Lesson 4: Quiz 4 A-10 1. From where does BRW take the value of standard item REPORT-DESCRIPT? From the Report Description eld on the De ne Report screen. 2. How could you specify that a negative value for an item be printed in parentheses? Specify ( in the negative Pre x eld, and ) in the negative Su x eld. Specify X in the Adjust elds for both pre x and su x. 3.
Manual Structure Lesson 4: Quiz 5 1. If you have a report with ve di erent selection sets, and you compile the report with each set: (a) How many execution les will you have? (b) How many speci cation les will you have? a. Five. b. One. 2. Can you request a report containing a \required" parameter without supplying a value for that parameter? No. If a parameter is required, you must supply a value for it. 3. What will happen if a sales manager, when requesting the report execution le SALESMAN.
Manual Structure Lesson 4: Quiz 6 A-12 1. What is the default access sequence and method? The default access sequence is the order of the source tables on the De ne Table screen, and the default access method is serial read. 2. Is it necessary to know about Image databases/KSAM les: (a) Before using BRW? (b) Before tuning BRW reports? a. No. By default, BRW considers each data set, le or KSAM le to be a serial le of records. b.
B Managing the BRW Tutorial Managing the Tutorial This appendix describes the account, groups, and les used with the tutorial in this manual. It describes how the tutorial is set up and gives hints on how to manage the tutorial if many students wish to use the tutorial at the same time. Who Should Read This Appendix? Store the Tutorial Files The person who reads this should be familiar with the HP3000, and, ideally, familiar with BRW. No special knowledge is assumed for those who take the tutorial.
Manual Structure Other Changes to Account ITF3000 The tutorial installation does not otherwise a ect account ITF3000, that is, no passwords are set and no new users are added. The les' and database's creator is MGR. You can add users or passwords to the account ITF3000, and to its groups and users, but see \Default User". Note that account ITF3000 can be used by other training courses, for example, LearnDESK (the HPDESK training).
Manual Structure Tutorial Files Example Solutions Specification Solutions The following les are supplied with the tutorial. There are three example solutions, one for each task. Speci cation les, execution les, and print les (examples of printed output directed to disk les) are supplied for each task. The speci cation le solutions are: Table B-1. Specification Solution Files SOLUTN1 Solution to the rst report. SOLUTN2 Solution to the second report. SOLUTN3 Solution to the third report.
Manual Structure Printfile Solutions The print les are: Table B-3. Printfile Solution Files SOLUTN1 Solution to the rst report. SOLUTN2 Solution to the second report. SOL3DIR1 Solution to the third report. (Execution le SOL3DIR run without data restriction.) SOL3DIR2 Solution to the third report. (Execution le SOL3DIR run with parameter CUSTOMER-NO > \000002".) SOL3SMN1 Solution to the third report. (Execution le SOL3SMN run with parameter AREA = \WESTERN".
Manual Structure There are no passwords or access restrictions to the TOYDB database. The database creator is MGR. Amending the TOYDB You can add or amend records in the TOYDB, for example using QUERY, but if you do so, you can spoil the output of the task reports. Therefore, if you wish to add or amend records, please copy the TOYDB and work on the copy.
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.
Manual Structure BRWACCSD The program used to create a BRWDIC dictionary le from HP Access Central Dictionary. BRWAPPD The program used to create an BRWDIC dictionary le from HP Application Dictionary. BRWCOMP See report compile program . BRWCONV See report conversion program . BRWDIC See dictionary le . BRWD3000 The program used to create a BRWDIC dictionary le from HP Dictionary/V. BRWEMPTY Program used to clear intermediate report les. Filename BRWMPTY.PUB.SYS BRWEXEC See report execution program .
Manual Structure calculation formula An expression which results in a numeric, string, date or time value. The formula of a calculated item or a function. calculation language BRW has a powerful calculation language that allows you to perform simple and complex arithmetic operations. Operations can be performed on numeric data, string values, and date and time gures. The calculation language allows nested operations, conditional execution, array calculations, and string manipulation.
Manual Structure con guration program Used to con gure BRW. The le name is BRWSETUP.PUB.SYS. DBA Database Administrator. The individual responsible for the creation and maintenance of HP TurboIMAGE/iX or HP ALLBASE/SQL databases, including granting and revoking access authority. database Physical storage of application data. Consists of one or more datasets for an HP TurboIMAGE/iX database, or tables and views for an HP ALLBASE/SQL database. Provides various access methods for use to retrieve data.
Manual Structure lineset, each occurrence has a separate edit mask and you could have each of the ve dates print in a di erent form. exception condition An exception condition occurs when the value of an item ,function, parameter, or expression is invalid. When an exception condition occurs, BRW issues an execution warning message, which is printed at the end of the report. execution le Used to produce the report. The le code is REXNM. Cannot be modi ed.
Manual Structure frozen speci cation le Same as speci cation le, but allows only to be copied, listed or compiled. The le code is RSPCF. function An expression frequently used in other formulas. Can have up to nine function arguments. function argument A placeholder for an actual expression supplied when the function is used in another formula. heading Data printed at the top of a page, report or before a detail. Lineset for a break, page or report.
Manual Structure HP ALLBASE/SQL view A table derived by placing a \window" over one or more tables to let users access only certain data. HP ALLBASE/SQL system catalog Contains information on data structures of HP ALLBASE/SQL databases. HP ALLBASE/SQL autostart mode An autostart ag is set to determine how the DBEnvironment is opened. It must be set to \on" to allow the CONNECT command that is always used by BRW.
Manual Structure 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. Usually consists of two MPE les: a data le and a key le. However, with MPE/iX Native Mode, KSAM consists of only one MPE le.
Manual Structure You can have a maximum of 9 sort items in a report and up to 23 linesets. link See join . 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 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. MPE le Sequential le supported by MPE. BRW supports standard MPE les with xed length records.
Manual Structure 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. page heading The lines that are printed at the top of a new page, normally the page number, date, report title, and so on, are called the page heading lineset. BRW prints the page heading lineset (unless you use a suppress condition) at the top of each page of the report.
Manual Structure 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. record A row in a table. Each record in the nal access table will be printed as one detail lineset. record number See TABLE-REC-NUMBER.
Manual Structure report layout The way the report looks in its nal form, after all calculations have been performed, rows and columns have been placed on the page, and so on. report list program Used to list the speci cations of a report. The le name is BRWLIST.PUB.SYS. Can be used stand-alone or within the report speci cation program. report request program A program used to request BRW reports. The lename is BRWSTART.PUB.SYS. Can be used stand-alone.
Manual Structure data-dependent security. Can contain default values for parameters, print le and report schedule. serial le MPE le format. sort Arranging items on the report by ascending or descending order, alphabetically or numerically. sort item An item that is used as the basis for a sort. For example, if the report is sorted by the customer name, then the CUSTOMER-NAME item is a sort item. A report can be sorted by up to nine items; each item in ascending or descending order.
Manual Structure syntax diagram Describes the syntax of the 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. Tables can be nested, thus can consist of other tables as source tables. table calculated item Is a calculated item de ned within a table. After it is projected from that table, it becomes a normal item.
Index A B C absolute positioning, 5-30 access blocks, 5-70 access sequence, 5-14 access structure, 5-70 adding blank lines, 4-38 adding lines, 4-33 alias names, 4-14 answers to quizzes, 1-3 blank lines adding, 4-38 break, 3-5 break footing level 1 lineset, 4-39 breaks de ning, 4-19 BRWDIC, 2-3 calculated items formula for layout, 4-35 layout, 4-34 calculation language, 4-10 calculations for report 3, 4-2 column calculations, 3-35, 4-40 comlile report, 3-41 comments, 4-10 compile listing, 5-50 compile mes
D E F edit mask, 3-26 restrictions, 5-39 edit masks, 5-38 error checking, 4-11 execution statistics, 3-48 Exit Save function key, 5-19 nal access table, 3-11, 4-14 footing level 1 break, 4-39 report footing lineset, 4-40 footings level 2 break footing lineset, 4-37 formula for layout calculated item, 4-35 G general performance tips, 5-76 grant message, 3-9 H help screens, 3-9 HP BRW-Desk, 1-2 I J Index-2 data access de ning, 4-6 DATE, 3-25 date items, 4-10 default access, 5-13 de ning a relation,
K L N O P keyed access, 5-13, 5-14 layout calculated item formula, 4-35 layout calculated items, 4-34 length of standard item, 3-25 level 1 break footing lineset, 4-39 level 1 break heading lineset, 4-26 level 2 break footing lineset, 4-37 level 2 break heading lineset, 4-28 lines adding, 4-33 copying, 4-33 substituting, 4-29 suppressing, 4-30 lineset, 3-5, 4-3 linesets de ning, 4-25 detail, 4-32 level 1 break footing, 4-39 level 1 break heading, 4-26 level 2 break footing, 4-37 level 2 break heading,
Q R S Index-4 quick browse, 3-38, 4-15 quiz answers, 1-3 relating tables, 4-12 relation conditions de ning, 4-10 relations de ning, 4-8, 4-12 report 1 breaks, 3-17 compiling, 3-41 creating, 4-4 de ne linesets, 3-22 nal access table, 3-20 linesets, 3-6 review layout, 3-40 sketch, 3-3, 4-2 sorts, 3-17 report 2 compiling, 4-44 linesets, 4-3 review layout, 4-43 report 3 breaks, 5-28 compiling, 5-49 de ne linesets, 5-32 de ning sorts and breaks, 4-19 detail lineset, 4-32 level 1 beak heading lineset, 4-26 le
report 3, 5-3, 5-4 sort items, 3-3 sorts de ning, 4-19 standard items DATE, 3-25 length, 3-25 PAGE, 3-25 REPORT-DESCRIPT, 5-32 REQUEST-DATAn , 5-36 SELECTION-SET, 5-28, 5-29 TIME, 5-33 USER, 5-33 using, 3-25 structure of the tutorial, 1-3 substituting lines, 4-29 suppressing detail lines, 4-22 suppressing lines, 4-30 suppressing linesets, 4-22 suppress page heading and footing, 5-30 T U V W tables deleting, 4-7 nal access, 4-14 relating, 4-12 TIME, 5-33 TOYDB contents, 2-2 pictured, 2-2 TOYDB described
