HP 3000 Manuals HP 3000 Manuals
Table Calculated Items or Layout Calculated Items? You should consider the performance differences between table calculated items and layout calculated items if (BUT ONLY IF) you have many calculations in a report. Optimizing TableCalculated Items Table calculated items are calculated as early as possible, that is, as soon as the items used in their formulas become available during the data access phases of a report. In the early data access phases, there are far fewer records than after the tables are joined. Therefore, calculations can be performed less frequently if they are performed after just one, or a few, datasets or files have been read. For example: in the database TOYDB, each record in the dataset PRODUCTS has several corresponding records in the dataset ORDER-DETAILS because, typically, there are several orders per product. If you join both datasets in a report, you will get the values of the items of PRODUCTS repeated for each related ORDER-DETAILS record. Suppose you defined a table calculated item ON-STOCK-VALUE which uses items from the dataset PRODUCTS. HP ALLBASE/BRW calculates ON-STOCK-VALUE just after a record from the dataset PRODUCTS has been read and the local selection has been performed. Optimizing Selections If you tune the data access of the report so that PRODUCTS is read first and then ORDER-DETAILS is read in keyed mode, or if both datasets are read serially, the item ON-STOCK-VALUE will be calculated much less frequently than when it is calculated for every detail in the report. But, if you have defined a selection on an item from dataset ORDER-DETAILS, you should access ORDER-DETAILS first, because early selections save more time than early calculation of calculated items. Selections are also optimized so that they are performed as early as possible. If a selection or relation condition contains the logical operator AND, for example, X = Y AND Z = 40, the condition is divided into separate, smaller selections, X = Y and Z = 40, and each of these is optimized individually. The compile listing informs you when HP ALLBASE/BRW actually performs the selections and calculates the table calculated items. Table calculated items are not necessarily calculated in the table where they were defined, especially if the report has nested tables. Table calculated items which use only parameters and constants but no other items in their formulas are calculated only once in the report. The compile listing shows them as Constant Calculated Items. It is a good idea to use table calculated items if you use them in the report layout, but do not use those items from which they are calculated. This can make the record length of HP ALLBASE/BRW's internal work files much smaller. For example in HPFA (HP Financial Accounting) the ACCOUNT-MASTER dataset contains about a hundred items that hold the account budget, debit and credit values for 28 periods. In most HPFA reports only the account budget and the account balance for the current period and their corresponding cumulative values are printed. After calculating the four table calculated items, HP ALLBASE/BRW does not need the values of the hundred items and so throws them away. This reduces the record length of the work file by more than 1000 bytes and makes sorting faster. Optimizing Layout Calculated Items Layout calculated items, on the other hand, are preferable if you have calculated items in addition to other items. For example, if you calculate the budget/balance variance and print it along with the items from which it was calculated, it is better to define it as a layout calculated item. This keeps the record length of the workfile smaller. However, if you want to select or sort on this calculated item, you need to define it as a table calculated item. HP ALLBASE/BRW does not optimize layout calculated items automatically. But you can do this manually. For example, if you print the invoices for customers, you typically print the customer information in a break header and footer. The break headings and footings are printed less frequently than the details of the invoices. If you have a layout calculated item which you print only in the break header or footer, you can specify CALC in the corresponding ColCalc field on the Define Lines screen. This tells HP ALLBASE/BRW that you want this item to be calculated only when this lineset is actually printed (and not for every detail). Specifying CALC as above also tells HP ALLBASE/BRW that you want to use the other items in the calculated item's formula with the same column calculation and numeric precision as specified for this lineset. For example, if you have a layout calculated item PERCENT-VARIANCE with the formula 100 * BUDGET / BALANCE, and if you specify TOTAL for the items BUDGET and BALANCE and CALC for PERCENT-VARIANCE, HP ALLBASE/BRW uses the total values of BUDGET and BALANCE to calculate PERCENT-VARIANCE. This is exactly what you want.