Column calculation formulas manipulate the column value of a particular row or a constant. In this report sample, each % column represents the quarterly values as a percent of Sales for the respective quarter. In addition, the Avg column represents an average value for the two quarters.
Actual Product Market Qtr1 % Qtr2 % Avg ======== ====== ======== ====== ======== Sales 141,245 100.00 136,193 100.00 138,719 Cost_of_Goods_Sold 58,104 41.14 56,281 41.32 57,193 Margin 83,141 58.86 79,912 58.68 81,527 Marketing 11,211 7.94 11,302 8.30 11,257 Payroll 43,817 31.02 43,827 32.18 43,822 Miscellaneous 302 0.21 1,859 1.36 1,081 Total_Expenses 55,330 39.17 56,988 41.84 56,159 Profit 27,811 19.69 22,924 16.83 25,368 Profit_% 20 0.01 17 0.01 18 Margin_% 59 0.04 59 0.04 59
Use the following script to create Sample 10:
// This report performs column calculations based on values in a // report row. <PAGE (Scenario, Product, Market) Actual <COLUMN (Year) Qtr1 Qtr2 { DECIMAL 2 3 4 } { NAMEWIDTH 22 WIDTH 7 3 4 } { ORDER 0 1 3 2 4 5 } <ROW (Accounts) { SAVEROW } Sales ! { CALCULATE COLUMN "%" = 1 % "Sales" 1 } { CALCULATE COLUMN "% " = 2 % "Sales" 2 } { CALCULATE COLUMN "Avg" = 1 + 2 / 2. } <DESCENDANTS Accounts !
Note: | You can include comments in the report by preceding the text with //. The Report Extractor ignores everything that follows the double slash. You can use comments to explain report processing. |
The SAVEROW command reserves space for a row member that the CALCULATE COLUMN command calculates. In this case, the calculation affects SALES. The ! is required after the member name.
The CALCULATE COLUMN command allows column numbers, row names, or constants in formulas. You can read the first calculation this way: "% equals column 1 as a percent of Sales in column 1."
Each calculated column label must be unique. Note how the second calculated column label has a blank space after the % sign.
To specify a constant, define a number followed by a period. You can use a constant in either a column or row calculation. The last column calculation takes the sum of columns 1 and 2 and divides by the value 2. This formula is interpreted as (1+2)/2, not 1 + (2/2.).
As noted in Sample 7: Using Aliases, the ORDER command arranges columns in the specified order. By default, calculated columns are added to the end of existing columns retrieved from the database. In this example, columns 0-2 are automatically retrieved, based on selected members. Columns 3-5 are the calculated columns. The ORDER command applies to both retrieved and calculated columns.
This report script, COLCALC1.REP, is available in the \ARBORPATH\App\Demo\Basic directory, and is displayed in Enterprise View in Administration Services.