Exercise five: Using calculation fields
In this exercise, we’ll create a report that gives the year-to-date gross profit percentage for each inventory item, sorted by plant size and with an average gross profit figure for each size.
Creating a new report
|
1.
|
Select Operations > Create new report, and name the report “YTD GROSS PROFIT PER ITEM”. |
|
2.
|
Select Design > Files to read. |
|
3.
|
Select Plant item file. |
|
4.
|
Press the Exit shortcut. |
Although all the data we need to calculate the gross profit is stored in the plant item file, there is no gross profit field. To determine the year-to-date gross profit for each item, we’ll need to create a calculation field to define the calculation and store the result.
Creating a calculation field
Before we can define a calculation field, we need to know how to get the calculated result. In this case, we’re going to use the following equation:
((ytd sales $ - ytd cost $)/ytd qty)*100
|
1.
|
Select Design > Fields to create. |
|
2.
|
Select Calculation Fields. |
|
3.
|
Enter “GROSPROF” in the Field name field. |
|
4.
|
Select Numeric for Type. |
|
5.
|
Enter 4 in the Length field and 2 in the Precision field. The field length will be four places, with two to the right of the decimal point. |
Now your cursor is in the GROSPROF field, which is where you define the calculation. Ultimately, our calculation will look like this:
((PLITEM.PLDLYR-PLITEM.PLCSYR)/PLITEM.PLQTYR)*100
There are two ways to enter the calculation: either you can just type in the above string exactly as you see it, or you can build the calculation as follows:
|
7.
|
Press the List selections shortcut. |
|
8.
|
Page down until Sales $ ytd displays, and then select it. |
|
10.
|
Press the List selections shortcut and select Cost ytd. |
|
12.
|
Press the List selections shortcut and select Qty sold ytd. |
|
14.
|
Press Enter. Your completed calculation should now look like the one above. |
|
15.
|
Press Enter in the Alternately field. |
|
16.
|
Enter “Gross profit” in the Description field. This text is used as the default field header in the report. |
|
17.
|
In the Justification field, select Right. This is a numeric value, so we’ll want to right-justify the results. |
|
18.
|
Enter ZX.XX% in the Format field (uppercase letters only). The characters Z and X are placeholders for significant digits. The Z suppresses the digit (prints a blank) if it’s equal to zero, and the X prints a zero if a digit is equal to zero. The percent sign (%) is not a formatting character, so it will be transferred directly to the right of the calculation result. |
|
19.
|
Select No in the Force after sort field. Your screen should look like figure 1. |
|
20.
|
Press the Exit shortcut (or click OK) and then press Exit again. |
Now that the calculation field is defined, let’s define the layout for the report.
Defining the report layout
|
1.
|
Select Design > Fields to print. |
|
2.
|
Move to the top of the list and select Common name and Size in gallons. |
|
3.
|
Press tab to display the TEMP fields. |
|
5.
|
Press the Exit shortcut two times. |
Sorting the report
By default, data is sorted in ascending order on the specified sort field. But it makes more sense to show the most profitable items first, so we’ll change the sort order.
|
1.
|
Select Design > Fields to sort. |
|
3.
|
Press the Exit shortcut. |
|
4.
|
Select Sort functions > Change sorting order. |
Notice that an uppercase “R” appears to the left of the field name. This tells ReportWriter to reverse sort on that field. (See figure 2.)
|
5.
|
That’s it—press the Display report shortcut to view the report, shown in figure 3. When you’re done, press the Exit shortcut. |
|
6.
|
Select Operations > Save report. |
|
7.
|
Select General > Quit to exit ReportWriter. |
Summary
