サンプル15-C: 平均行
このEssbaseレポート・スクリプトのサンプルでは、計算中に数値とパーセントが部分的に含まれる平均行に列が制限されます。
レポートでは、地域の合計売上高が格納された、以前に計算された行を使用して、地域の平均パーセンテージの合計を計算する必要があります。また、レポートでは(平均を出すために)地域の数も計算する必要があります。地域の数は、キューブ・アウトラインでは定数として設定されます。この数に変更があった場合は、レポート定義を修正する必要があります。地域の数が計算されない場合は、検出が容易でないエラーが発生する可能性があります。
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% 次のスクリプトを使用してサンプル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" }
!