|
Detail
Report Writer (DRW)
Introduction
We use Dun & Bradstreet's Detail Report Writer (DRW) to meet many
of our reporting requirements. For balance sheet, income statement
and trial balance type reports it is ideal, especially when reporting
across our many reporting structures and among their many levels
is called for. Quite a few of our month-end cost center reports,
particularly those that support financial reporting, are produced
with DRW.
Like most report writers, though, DRW has its quirks. Experienced
users know that report writing is part science and part art. Some
think that a dash of magic helps. But understanding the subtleties
of our tools can often mean the difference between adequate and
exceptional reports.
The following articles describe a few tricks that we've incorporated
into DRW to improve our reports. If you have any questions about
them, or if you have a DRW suggestion for us, you can contact
the author, Paul Amrhein,
via email.
Better
Rounding in DR
Saving Cents in DRW
Taming the Budget Buckets
Better
Rounding in DRW
There are differences of opinion on the issue of full dollar-and-cents
reporting vs. rounded amount reporting. Operative accountants
tend to opt for pennies, while financial reporting types prefer
rounded amounts. Sometimes rounding might be needed to fit more
columns on a page. In any case, rounding should never interfere
with the proper footing and cross-footing of columns of numbers
on a report.
To retain accuracy in footing and cross-footing, particularly
when data are presented at various summary levels, it is important
to round before adding. Rounding after adding is a design flaw
that undermines the accuracy of reports. Unfortunately, discrepancies
due to this flaw are too often dismissed, when, with a little
extra effort, cleanly footed reporting could have been achieved.
Rounding detail items is best accomplished through scaling. Scaling
is a technique that uses multiplication and division by powers
of ten to achieve certain effects. Scaling in DRW can be implemented
through the use of data items such as the example that follows.
In this data item, the current period balance (CYCP000) is divided
by 10000. Since the internal storage format for numeric values
has five decimal places, the effect of this division is to shift
the decimal point to left five places and force the original decimal
digits to be rounded off. To return the value to its original
format, the value is then multiplied by 10000. The scaled up numeric
has a rounded integral component and a decimal component of all
zeroes. The rounded data item can be used in format specs without
concern about footing, cross-footing or summary level accuracy.
SHORT NAME --------------- DATA ITEM DESCRIPTION ----------------
ROUND-CP CURRENT PERIOD - ROUNDED TO WHOLE DOLLARS
M FORMULA M FORMULA
1--> A CYCP000 2--> D 0010000
3--> M 0010000 4--> _ _______
--------- FORMAT SPECS DETAIL ---------
V FORMULA -MATH SPEC- S LITNN --PRINT LAYOUT--
CORE FIELD A -DATA ITEM- WT E REL REL REL
COL TYPE R ROUNDING OPTION---* NO L ,-++. POS POS POS
CL01 DATA ROUND-CY A YB090 1039 0000 0000
CL02 DATA _ ROUND-CY B YB090 1052 0000 0000
CL03 DATA _ ROUND-CY C YB090 1065 0000 0000
CL04 DATA _ ROUND-CY D YB090 1078 0000 0000
CL05 MATH _ 01+02+03+04 _ YB090 1091 0000 0000
CL06 DATA _ ROUND-CY E YB090 1104 0000 0000
CL07 MATH _ 05+06 _ YB090 1117 0000 0000
Scaling
is a cool tool. It gives the writer of reports more control over
the content, format and presentation of numeric data. This simple
example of rounding by scaling down is only one of the many uses
of the technique. For an example of scaling up, see the following
article.
Saving
Cents in DRW
The inaccuracies associated with rounding after adding can be
overcome by rounding at the detail level, as described in the
preceding article. However, DRW has an unusual and sometimes troublesome
habit of automatically rounding column items that are added in
a MATH statement. Cents are lost, and with them the footing and
cross-footing accuracy that is essential to good accounting reports.
Once again, arithmetic can be engineered through scaling. Cents
can be preserved by scaling up decimal values so that they are
spared the effects of automatic rounding. Having passed through
the mathematical storm unscathed, numeric values can be scaled
back down to their original format. The technique is simple, the
effect, sublime.
Scaling directly in MATH statements is implemented by the use
of a WEIGHT record. In the following example, WEIGHT 30 has a
divisor of 1 and a dividend of 100 in every period. Thus, the
WEIGHT is always 100/1, or simply 100. When used as a multiplier,
this weight has the effect of shifting a decimal point two positions
to the right, that is, moving the cents into the dollars position.
Those scaled-up digits are unaffected by the automatic rounding
in subsequent operations.
----------- WEIGHT RECORDS ----------
PERIOD DIVIDEND VALUES
-01- -02- -03- -04- -05- -06- -07- -08- -09- -10- -11- -12- -13-
NO--> 30 SHORT NAME--> ROUNDING DIVISOR--> 1
100 100 100 100 100 100 100 100 100 100 100 100 100
In
the following format spec, column CL06 receives the value of WEIGHT
30. The value in CL06 is then used to scale up the values of each
of actual detail values, placing the results in CL07 through CL10.
These new columns can then be combined (in CL11) without a rounding
effect. In CL12, the sum is scaled back down through division,
and combined with one more column to produce the final effect.
--------- FORMAT SPECS DETAIL ---------
V FORMULA -MATH SPEC- S LITNN --PRINT LAYOUT--
CORE FIELD A -DATA ITEM- WT E REL REL REL
COL TYPE R ROUNDING OPTION---* NO L ,-++. POS POS POS
CL01 FORM LYPE013 A YB092 1034 0000 0000
CL02 FORM _ CYTD000 B YB092 1050 0000 0000
CL03 DATA _ REV-CYTD000 C YB092 1066 0000 0000
CL04 DATA _ REV-CYTD000 D YB092 1082 0000 0000
CL05 DATA _ REV-CYTD000 E YB092 1098 0000 0000
CL06 SWCP _ 30 _ 000 0000 0000 0000
CL07 MATH _ 02*06 _ 000 0000 0000 0000
CL08 MATH _ 03*06 _ 000 0000 0000 0000
CL09 MATH _ 04*06 _ 000 0000 0000 0000
CL10 MATH _ 05*06 _ 000 0000 0000 0000
CL11 MATH _ 07-08-09-10 _ 000 0000 0000 0000
CL12 MATH _ 11/06+01 _ YB092 1114 0000 0000
The
few additional lines of spec are a small price to pay for the
perfectly accurate results. And there is usually no reason to
accept what some lesser writers dismiss as "rounding error." There
are many other ways to use scaling. For some more elaborate examples,
see the Macro Cookbook.
Taming
the Budget Buckets
The following table represents the pairing of General Ledger amount
classes. This table is hard-coded in program G2X905 and is used
to perform the conversion of formulas from current year to prior
year. For each pair of amount classes, the one on the left is
the current year, which is referenced when the Year-of-Reports
switch (G22) is set to zero.
B1:__
B2:__ B3:__ B4:__ B5:__ B6:__ CY:LY CA:LA LY:2Y LA:2A 2Y:3Y 2A:3A
3Y:__ 3A:__
The
one on the right is the prior year class, which is referenced
when the Year-of-Reports switch (G22) is set to 1. Whenever a
referenced class is blank, an error message is generated by DRW.
Note that there are no prior year references for 3Y or 3A, so
that any time these classes are referred to while the YOR switch
is 1, errors result. Note, too, that none of the budget classes
have prior year counterparts, so that references to these classes
will also result in errors unless the budget flags (G20) are set
to indicate which classes have corresponding prior year classes.
Unfortunately, the budget switches only allow for three budget
classes to be designated for prior year conversion, and the three
can only be converted among themselves. The three classes are
designated by the codes 0, 1 and 2. Multiple occurrences of these
codes among the six budget switches are ignored. Only the left-most
occurrence of each code is accepted. The swapping of classes happens
as follows: when the YOR switch is 1, the budget switches are
scanned to determine which class is 0, which is 1 and which is
2. Then, the prior year assignments are made: this-year class
0 is assigned a last-year class of 2; this- year class 1 is assigned
a last-year class of 0; this-year class 2 is assigned a last-year
class of 1.
For example, if the YOR switch is 1 and the budget switches are
set to 102111, the prior year assignments are made in the table
as follows:
B1:B2
B2:B3 B3:B1 B4:__ B5:__ B6:__
This
method of designating prior year classes proved to be inadequate
for our needs. We had already made our budget class assignments
for the current year (B1, B3 and B5) and their corresponding prior
year classes (B2, B4 and B6 respectively). There is no arrangement
of budget switches that would result in these assignments under
the current program limitations. We therefore decided that the
prior year class assignments would be coded directly into the
program table as follows:
B1:B2
B2:B2 B3:B4 B4:B4 B5:B6 B6:B6
This
coding scheme will allow for all formulas and data items to be
converted to prior year with only the setting of the YOR switch.
No reassignment will occur if the budget switches are disabled
by the user. In the above scheme, when the YOR switch is set to
1, a reference to B1 will result in the corresponding value from
B2. A direct reference to B2 always results in a value from B2,
regardless of the YOR switch. This allows for budgets, reserves
and commitments (our three budget classes) to be driven by a single
switch, with no need to change reports or formulas.
There is one caveat: some implementations (not ours) use data
item formulas (diforms) in filter programs as auxiliary fields
and/or search fields. The YOR switch and budget switches affect
the resolution of these formulas.
TOP
|
