Sample 15-C: Rows of Averages
This Essbase report script sample restricts columns during calculation to average rows that contain partly numbers and percentages.
The report must calculate the total regional average percentages using previously calculated rows that contain the total sales for the region. Also, the report must compute (for averaging) a count of regions. The number of regions is set as a constant in the cube outline. If this number changes, the report definition must be modified. If a count of regions is not computed, a hard-to-notice error can result.
Actual Total Sales for the 3 Video Products in Qtr1: 36,914 35,126 25,119
Budget Total Sales for the 3 Video Products in Qtr1: 37,300 34,250 26,940
================================================== ====== ====== ======
Qtr1
Television VCR Camera
Profit Profit_% Profit Profit_% Profit Profit_%
====== ======= ====== ======== ====== =========
New_York Budget 1,020 20.40% 1,382 31.41% 540 16.68%
Actual 847 17.66% 1,243 29.62% 352 11.79%
Boston Budget 1,020 24.88% 1,344 35.37% 277 11.79%
Actual 1,405 33.48% 1,002 27.49% 207 9.28%
Chicago Budget 1,100 25.58% 1,062 31.24% 430 16.54%
Actual 728 16.51% 1,190 30.68% 369 14.72%
San_Fran~ Budget 930 21.63% 718 21.12% 1,270 31.75
Actual 674 15.54% 1,197 31.12% 1,000 27.4%
Seattle Budget 390 15.60% 973 32.98% 376 16.00%
Actual 340 12.20% 977 31.56% 312 13.79%
Denver Budget 690 22.26% 929 30.97% 462 18.86%
Actual 334 11.94% 914 30.48% 361 15.92%
Los_Ange~ Budget 810 18.41% 1,101 29.76% 506 18.40%
Actual 429 9.11% 1,127 28.81% 377 14.62%
Dallas Budget 780 21.08% 1,341 36.24% 333 13.88%
Actual 163 4.69% 1,055 30.28% 243 10.71%
Houston Budget 690 24.64% 1,128 36.39% 432 18.00%
Actual 256 10.44% 1,064 34.98% 241 10.98%
Phoenix Budget 630 20.32% 894 31.93% 498 20.75%
Actual 251 8.49% 940 31.07% 261 11.99%
Total Regions Averages
Avg Budget 806 21.61% 1,087 31.74% 512 19.02%
Avg Actual 543 14.70% 1,071 30.49% 372 14.82% Use the following script to create Sample 15-C:
{ // Declare some of the Calculated Rows to be used
CALCULATE ROW "Avg~Budget" OFF
CALCULATE ROW "Avg~Actual" OFF
CALCULATE ROW "Tot Sales~Budget" OFF
CALCULATE ROW "Tot Sales~Actual" OFF
}
// We need the values of Market->Visual->Qtr1->Sales->Actual and
// Market ->Visual->Qtr1->Sales ->Budget to compute some
// percentages at the bottom, so get them now
Market
<CHILDREN Visual Qtr1 Sales
{ SAVEROW "Actual Sales" } Actual // stores into first 3
// data columns
{ SAVEROW "Budget Sales" } Budget // of these rows, which
// are cols 1-3
// change to columns 2-4 when we
// specify 2 row dimensions in
// next section
// Since this is an example, not a formal report, we'll
// type out the values for Actual Sales and Budget Sales here so
// you can check the numbers:
{ SKIP 2
TEXT 0 "Actual Total Sales for the 3 Video Products in Qtr1:" 55 "*CALC" "Actual Sales"
TEXT 0 "Budget Total Sales for the 3 Video Products in Qtr1:" 55 "*CALC" "Budget Sales"
UCHARACTERS
SKIP 5 }
! // Now we can do the main report
{ AFTER "%" 3,5,7 DECI 2 3,5,7 ZEROTEXT "--" MISSING "--"
WIDTH 10 0 1 }
<PAGE(Year)
Qtr1
<COLUMN(Product,Accounts)
<CHILDREN Visual
Profit // split these 2 accounts onto
// 2 lines to prevent default
Profit_% // to asymmetric mode
// because both column
// dimensions have the same # of
// members selected. Could have
// used <SYM instead.
<ROW(Market,Scenario)
<ONSAMELEVELAS New_York
{ SETROWOP "Avg~Actual" OFF
SETROWOP "Avg~Budget" +
CALCULATE ROW "Count" = "Count" + 1. }
Budget
{ SETROWOP "Avg~Budget" OFF
SETROWOP "Avg~Actual" + }
>{ SKIP }
Actual
{ UCOLUMNS SKIP 2 }
{
// at this point, Avg~Budget and Avg~Actual ARE NOT YET
// AVERAGES--they are the SUM of the Profit rows of each type.
// Before converting them to averages, the report computes
// Profit as a % of total sales for each type. Since we only
// have 1 value for "Budget Sales" and "Actual Sales",
// for each of the three visual products in those
// rows, the report restricts the reference to those rows to
// columns 2-4 while computing the percentage columns 3, 5, and 7,
// based on profits in columns 2, 4 and 6
// calculate the percentages for Budget
CALCULATE ROW "Avg~Budget" 3 = "Avg~Budget" 2 % "Budget Sales" 2
CALCULATE ROW "Avg~Budget" 5 = "Avg~Budget" 4 % "Budget Sales" 3
CALCULATE ROW "Avg~Budget" 7 = "Avg~Budget" 6 % "Budget Sales" 4
// now calculate the averages
CALCULATE ROW "Avg~Budget" 2 = "Avg~Budget" / "Count"
CALCULATE ROW "Avg~Budget" 4 = "Avg~Budget" / "Count"
CALCULATE ROW "Avg~Budget" 6 = "Avg~Budget" / "Count"
// calculate the percentages for Actual
CALCULATE ROW "Avg~Actual" 3 = "Avg~Actual" 2 % "Actual Sales" 2
CALCULATE ROW "Avg~Actual" 5 = "Avg~Actual" 4 % "Actual Sales" 3
CALCULATE ROW "Avg~Actual" 7 = "Avg~Actual" 6 % "Actual Sales" 4
// now calculate the averages
CALCULATE ROW "Avg~Actual" 2 = "Avg~Actual" / "Count"
CALCULATE ROW "Avg~Actual" 4 = "Avg~Actual" / "Count"
CALCULATE ROW "Avg~Actual" 6 = "Avg~Actual" / "Count"
TEXT C "Total Regions Averages"
PRINTROW "Avg~Budget"
PRINTROW "Avg~Actual" }
!