Examples of Report Scripts

This chapter includes report scripts that demonstrate report procedures and formats that are most frequently required in business settings. If you examine the techniques in these scripts and the resulting output, you can adapt them for use in your own reports.

The samples use both the Demo Basic and Sample Basic databases provided with your Hyperion Essbase server. Each sample identifies the database used. The scripts for these examples are available in your\ESSBASE\APP\DEMO\BASIC directory or your\ESSBASE\APP\SAMPLE\BASIC directory.

The sample reports in this chapter demonstrate the following techniques:

• Sample 1: Creating a Different Format for Each Page
• Sample 2: Handling Missing Values
• Sample 3: Nesting Columns
• Sample 4: Grouping Rows
• Sample 5: Reporting on Different Combinations of Data
• Sample 6: Formatting Different Combinations of Data
• Sample 7: Using Aliases
• Sample 8: Creating Custom Headings and % Characters
• Sample 9: Creating Custom Page Headings
• Sample 10: Using Formulas
• Sample 11: Placing Two Page Layouts on the Same Page
• Sample 12: Formatting for Data Export
• Sample 13: Creating Asymmetric Columns
• Sample 14: Calculating Columns
• Sample 15: Calculating Rows
• Sample 16: Sorting by Top or Bottom Data Values
• Sample 17: Restricting Rows
• Sample 18: Ordering Data Values
• Sample 19: Narrowing Member Selection Criteria
• Sample 20: Using Attributes in Member Selection

For fundamental information about reports and report scripts, see Quick Start to Report Scripts. For detailed information about using Report Writer commands to write reports and reports scripts, see Developing Report Scripts. For the syntax and usage of each Report Writer command, see the online Technical Reference in the DOCS directory.

Sample 1: Creating a Different Format for Each Page

This sample report contains data for Actual Sales. Each report page shows a different Product. The report lists products on the same page until the maximum page length is reached. To place each Product on a separate page, you must use the PAGEONDIMENSION format command, as shown in Sample 2.

Because none of the cities in South sell Stereo or Compact_Disc, the data values indicate #MISSING. You can represent missing values by suppressing the row or substituting a replacement text string, such as N/A. See Sample 2: Handling Missing Values for an example of substituting page breaks and labels for missing values.

Sales Actual Stereo Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== East 7,839 7,933 7,673 10,044 West 11,633 11,191 11,299 14,018 South #Missing #Missing #Missing #Missing Market 19,472 19,124 18,972 24,062 Sales Actual Compact_Disc Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== East 10,293 9,702 9,965 11,792 West 14,321 14,016 14,328 17,247 South #Missing #Missing #Missing #Missing Market 24,614 23,718 24,293 29,039 Sales Actual Audio Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== East 18,132 17,635 17,638 21,836 West 25,954 25,207 25,627 31,265 South #Missing #Missing #Missing #Missing Market 44,086 42,842 43,265 53,101

Use the following script to create Sample 1:

<PAGE (Accounts, Scenario, Product)
Sales
Actual
<IDESCENDANTS Audio

    <COLUMN (Year)
    <CHILDREN Year

<ROW(Market)
<ICHILDREN Market
    !

The ! report output command is required to generate the report.

Because the IDESCENDANTS selection command is used for Audio, the report selects all three members. Only a single member is selected from the other page dimensions, Sales and Actual. As a result, the script creates three report pages. They display as one long report page unless you use the PAGEONDIMENSION format command, as shown in Sample 2.

This report script, ACTSALES.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 2: Handling Missing Values

This report has the same layout and member selection as Sample 1, and shows you how to use page breaks and labels for missing values.

Sales Actual Stereo Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== East 7,839 7,933 7,673 10,044 West 11,633 11,191 11,299 14,018 South N/A N/A N/A N/A Market 19,472 19,124 18,972 24,062

Sales Actual Compact_Disc Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== East 10,293 9,702 9,965 11,792 West 14,321 14,016 14,328 17,247 South N/A N/A N/A N/A Market 24,614 23,718 24,293 29,039

Sales Actual Audio Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== East 18,132 17,635 17,638 21,836 West 25,954 25,207 25,627 31,265 South N/A N/A N/A N/A Market 44,086 42,842 43,265 53,101

Use the following script to create Sample 2:

<PAGE (Accounts, Scenario, Product)

Sales
Actual
<IDESCENDANTS Product
{ PAGEONDIMENSION Product }
{ MISSINGTEXT "N/A" }

      <COLUMN (Year)
      <CHILDREN Year

<ROW(Market)
<ICHILDREN Market
    !

The PAGEONDIMENSION format command creates a page break whenever a member from the specified dimension changes. Because the report selects eight Product members, this creates an eight-page report.

The MISSINGTEXT format command substitutes any strings enclosed within double quotes into the #MISSING string. To suppress missing values, use the SUPMISSINGROWS command.

You can also combine format commands within one set of braces:

{ PAGEONDIMENSION Product MISSINGTEXT "N/A" }

This report script, MISS_LBL.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 3: Nesting Columns

Each page produced by this report sample contains Sales information for a given Market. The report has two groups of columns across the page. The Actual and Budget members are the nested column group below Year members.

Note that the Actual and Budget members are on the same line in the report. You can put multiple commands on one line, but report commands are easier to read if they are spread out.

Sales East Jan Feb Mar Qtr1 Actual Budget Actual Budget Actual Budget Actual Budget ======= ====== ======= ======= ======== ======= ======= ======= Stereo 2,788 2,950 2,482 2,700 2,569 2,700 7,839 8,350 Compact_Disc 3,550 3,450 3,285 3,250 3,458 3,250 10,293 9,950 Audio 6,338 6,400 5,767 5,950 6,027 5,950 18,132 18,300 Television 5,244 4,800 4,200 4,300 3,960 4,300 13,404 13,400 VCR 4,311 4,200 3,734 3,700 3,676 3,700 11,721 11,600 Camera 2,656 2,850 2,525 2,670 2,541 2,670 7,722 8,190 Visual 12,211 11,850 10,45 10,670 10,177 10,670 32,847 33,190 Product 18,549 18,250 16,226 16,620 16,204 16,620 50,979 51,490

Sales West Jan Feb Mar Qtr1 Actual Budget Actual Budget Actual Budget Actual Budget ====== ====== ======= ======= ====== ====== ====== ====== Stereo 4,102 4,000 3,723 3,600 3,808 3,600 11,633 11,200 Compact_Disc 4,886 4,700 4,647 4,400 4,788 4,400 14,321 13,500 Audio 8,988 8,700 8,370 8,000 8,596 8,000 25,954 24,700 Television 5,206 5,100 4,640 4,600 4,783 4,600 14,629 14,300 VCR 4,670 4,650 4,667 4,200 4,517 4,200 13,854 13,050 Camera 3,815 4,050 3,463 3,750 3,478 3,750 10,756 11,550 Visual 13,691 13,800 12,770 12,550 12,778 12,550 39,239 38,900 Product 22,679 22,500 21,140 20,550 21,374 20,550 65,193 63,600

Sales South Jan Feb Mar Qtr1 Actual Budget Actual Budget Actual Budget Actual Budget ======== ======= ======= ======= ====== ======= ======== ======== Television 3,137 3,400 2,929 3,100 2,815 3,100 8,881 9,600 VCR 3,225 3,400 3,206 3,100 3,120 3,100 9,551 9,600 Camera 2,306 2,400 2,167 2,400 2,168 2,400 6,641 7,200 Visual 8,668 9,200 8,302 8,600 8,103 8,600 25,073 26,400 Product 8,668 9,200 8,302 8,600 8,103 8,600 25,073 26,400

Sales Market Jan Feb Mar Qtr1 Actual Budget Actual Budget Actual Budget Actual Budget ====== ======= ======= ======= ======== ======= ======== ====== Stereo 6,890 6,950 6,205 6,300 6,377 6,300 19,472 19,550 Compact_Disc 8,436 8,150 7,932 7,650 8,246 7,650 24,614 23,450 Audio 15,326 15,100 14,137 13,950 14,623 13,950 44,086 43,000 Television 13,587 13,300 11,769 12,000 11,558 12,000 36,914 37,300 VCR 12,206 12,250 11,607 11,000 11,313 11,000 35,126 34,250 Camera 8,777 9,300 8,155 8,820 8,187 8,820 25,119 26,940 Visual 34,570 34,850 31,531 31,820 31,058 31,820 97,159 98,490 Product 49,896 49,950 45,668 45,770 45,681 45,770 141,245 141,490

Use the following script to create Sample 3:

<PAGE (Accounts, Market)
Sales 
<ICHILDREN Market
{ PAGEONDIMENSION Market }
{ SUPMISSINGROWS }

      <COLUMN (Year, Scenario)
      <ICHILDREN Qtr1
       Actual Budget

<ROW(Product)
<IDESCENDANTS Product
    !

The report selects four Markets because the <ICHILDREN command is applied to Market. Only Sales is selected from the other page dimension, so the report has four pages.

For the South, all the rows of Product data are not displayed. Recall that the cities in the South do not sell every Product. The report uses the SUPMISSINGROWS format command to suppress the output of any member rows with all missing values.

This report script, COLGROUP.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 4: Grouping Rows

Each page of this report contains Sales information for a given Market. The report page contains members for both Product and Year as groups of rows down the page. This script creates a four-page report because the page dimensions and their member selections are the same as in Sample 3. The row/column layout is switched because the row and column dimensions are different.

Sales East Actual Budget Variance ======== ======== ======== Stereo Qtr1 7,839 8,350 (511) Qtr2 7,933 8,150 (217) Qtr3 7,673 8,350 (677) Qtr4 10,044 10,400 (356) Year 33,489 35,250 (1,761) Compact_Disc Qtr1 10,293 9,950 343 Qtr2 9,702 9,750 (48) Qtr3 9,965 10,050 (85) Qtr4 11,792 12,550 (758) Year 41,752 42,300 (548) Audio Qtr1 18,132 18,300 (168) Qtr2 17,635 17,900 (265) Qtr3 17,638 18,400 (762) Qtr4 21,836 22,950 (1,114) Year 75,241 77,550 (2,309) Television Qtr1 13,404 13,400 4 Qtr2 12,115 12,900 (785) Qtr3 15,014 14,200 814 Qtr4 17,861 17,300 561 Year 58,394 57,800 594 VCR Qtr1 11,721 11,600 121 Qtr2 10,999 11,100 (101) Qtr3 13,217 11,800 1,417 Qtr4 14,386 14,900 (514) Year 50,323 49,400 923 Camera Qtr1 7,722 8,190 (468) Qtr2 7,581 8,210 (629) Qtr3 8,181 8,630 (449) Qtr4 10,853 11,550 (697) Year 34,337 36,580 (2,243) Visual Qtr1 32,847 33,190 (343) Qtr2 30,695 32,210 (1,515) Qtr3 36,412 34,630 1,782 Qtr4 43,100 43,750 (650) Year 143,054 143,780 (726) Product Qtr1 50,979 51,490 (511) Qtr2 48,330 50,110 (1,780) Qtr3 54,050 53,030 1,020 Qtr4 64,936 66,700 (1,764) Year 218,295 221,330 (3,035)

Sales West Actual Budget Variance ======== ======== ======== Stereo Qtr1 11,633 11,200 433 Qtr2 11,191 11,050 141 Qtr3 11,299 11,650 (351) Qtr4 14,018 14,500 (482) Year 48,141 48,400 (259) Compact_Disc Qtr1 14,321 13,500 821 Qtr2 14,016 13,500 516 Qtr3 14,328 14,300 28 Qtr4 17,247 16,700 547 Year 59,912 58,000 1,912 Audio Qtr1 25,954 24,700 1,254 Qtr2 25,207 24,550 657 Qtr3 25,627 25,950 (323) Qtr4 31,265 31,200 65 Year 108,053 106,400 1,653 Television Qtr1 14,629 14,300 329 Qtr2 14,486 13,800 686 Qtr3 14,580 14,000 580 Qtr4 20,814 19,400 1,414 Year 64,509 61,500 3,009 VCR Qtr1 13,854 13,050 804 Qtr2 13,156 12,600 556 Qtr3 15,030 13,750 1,280 Qtr4 18,723 17,950 773 Year 60,763 57,350 3,413 Camera Qtr1 10,756 11,550 (794) Qtr2 10,573 11,400 (827) Qtr3 10,735 11,550 (815) Qtr4 13,906 15,000 (1,094) Year 45,970 49,500 (3,530) Visual Qtr1 39,239 38,900 339 Qtr2 38,215 37,800 415 Qtr3 40,345 39,300 1,045 Qtr4 53,443 52,350 1,093 Year 171,242 168,350 2,892 Product Qtr1 65,193 63,600 1,593 Qtr2 63,422 62,350 1,072 Qtr3 65,972 65,250 722 Qtr4 84,708 83,550 1,158 Year 279,295 274,750 4,545

Sales South Actual Budget Variance ======== ======== ======== Television Qtr1 8,881 9,600 (719) Qtr2 8,627 9,300 (673) Qtr3 8,674 9,300 (626) Qtr4 12,919 12,600 319 Year 39,101 40,800 (1,699) VCR Qtr1 9,551 9,600 (49) Qtr2 9,049 9,300 (251) Qtr3 9,998 10,000 (2) Qtr4 12,923 13,600 (677) Year 41,521 42,500 (979) Camera Qtr1 6,641 7,200 (559) Qtr2 6,765 7,350 (585) Qtr3 6,798 7,500 (702) Qtr4 9,486 10,200 (714) Year 29,690 32,250 (2,560) Visual Qtr1 25,073 26,400 (1,327) Qtr2 24,441 25,950 (1,509) Qtr3 25,470 26,800 (1,330) Qtr4 35,328 36,400 (1,072) Year 110,312 115,550 (5,238) Product Qtr1 25,073 26,400 (1,327) Qtr2 24,441 25,950 (1,509) Qtr3 25,470 26,800 (1,330) Qtr4 35,328 36,400 (1,072) Year 110,312 115,550 (5,238)

Sales Market Actual Budget Variance ======== ======== ======== Stereo Qtr1 19,472 19,550 (78) Qtr2 19,124 19,200 (76) Qtr3 18,972 20,000 (1,028) Qtr4 24,062 24,900 (838) Year 81,630 83,650 (2,020) Compact_Disc Qtr1 24,614 23,450 1,164 Qtr2 23,718 23,250 468 Qtr3 24,293 24,350 (57) Qtr4 29,039 29,250 (211) Year 101,664 100,300 1,364 Audio Qtr1 44,086 43,000 1,086 Qtr2 42,842 42,450 392 Qtr3 43,265 44,350 (1,085) Qtr4 53,101 54,150 (1,049) Year 183,294 183,950 (656) Television Qtr1 36,914 37,300 (386) Qtr2 35,228 36,000 (772) Qtr3 38,268 37,500 768 Qtr4 51,594 49,300 2,294 Year 162,004 160,100 1,904 VCR Qtr1 35,126 34,250 876 Qtr2 33,204 33,000 204 Qtr3 38,245 35,550 2,695 Qtr4 46,032 46,450 (418) Year 152,607 149,250 3,357 Camera Qtr1 25,119 26,940 (1,821) Qtr2 24,919 26,960 (2,041) Qtr3 25,714 27,680 (1,966) Qtr4 34,245 36,750 (2,505) Year 109,997 118,330 (8,333) Visual Qtr1 97,159 98,490 (1,331) Qtr2 93,351 95,960 (2,609) Qtr3 102,227 100,730 1,497 Qtr4 131,871 132,500 (629) Year 424,608 427,680 (3,072) Product Qtr1 141,245 141,490 (245) Qtr2 136,193 138,410 (2,217) Qtr3 145,492 145,080 412 Qtr4 184,972 186,650 (1,678) Year 607,902 611,630 (3,728)

Use the following script to create Sample 4:

<PAGE (Accounts, Market)
Sales 
<ICHILDREN Market
{ PAGEONDIMENSION Market }
{ SUPMISSINGROWS }

      <COLUMN (Scenario)
      <CHILDREN Scenario

<ROW(Product3, Year)
<ICHILDREN Year
<IDESCENDANTS Product
    !

This report script, ROWGROUP.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 5: Reporting on Different Combinations of Data

Each page represents a different combination of Product, Market, and Budget data. The total number of pages is determined by the number of Market and Product members. This section shows a representative part of the output.

Some data values have four decimal places. The number of decimal places, by default, is output to the true number of decimal values of the data cell. Sample 6: Formatting Different Combinations of Data uses the DECIMAL format command to define a specific number of places.

The member selection commands select three Product members and fourteen Market members. This produces a 42-page report. The number of report pages is determined by multiplying the number of members selected from each page dimension.

Budget Audio New_York Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Sales 6,400 6,400 6,700 8,350 27,850 Cost_of_Goods_Sold 3,012 3,012 3,146 3,973 13,143 Margin 3,388 3,388 3,554 4,377 14,707 Marketing 525 515 475 555 2,070 Payroll 1,950 1,950 1,950 1,950 7,800 Miscellaneous 0 0 0 0 0 Total_Expenses 2,475 2,465 2,425 2,505 9,870 Profit 913 923 1,129 1,872 4,837 Profit_% 14 14 17 22 17 Margin_% 53 53 53 52 53

Budget Audio Boston Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Sales 6,050 5,750 5,900 7,350 25,050 Cost_of_Goods_Sold 2,829 2,695 2,762 3,413 11,699 Margin 3,221 3,055 3,138 3,937 13,351 Marketing 410 400 400 520 1,730 Payroll 1,590 1,590 1,590 1,590 6,360 Miscellaneous 0 0 0 0 0 Total_Expenses 2,000 1,990 1,990 2,110 8,090 Profit 1,221 1,065 1,148 1,827 5,261 Profit_% 20 19 19 25 21 Margin_% 53 53 53 54 53

Budget Product Market Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Sales 141,490 138,410 145,080 186,650 611,630 Cost_of_Goods_Sold 55,860 54,579 57,379 73,276 241,093 Margin 85,630 83,831 87,702 113,374 370,537 Marketing 10,555 10,680 10,780 13,915 45,930 Payroll 43,234 43,248 43,248 43,248 172,978 Miscellaneous 0 0 0 0 0 Total_Expenses 53,789 53,928 54,028 57,163 218,908 Profit 31,841 29,903 33,674 56,211 151,629 Profit_% 23 22 23 30 25 Margin_% 61 61 60 61 61

Use the following script to create Sample 5:

<PAGE (Scenario, Product, Market)
Budget
<ICHILDREN Product
<IDESCENDANTS Market
{ PAGEONDIMENSION Product }   // New page at each new Product
{ PAGEONDIMENSION Market }    // New page at each new Market
       <COLUMN (Year)
       <ICHILDREN Year

<ROW(Accounts)
<DESCENDANTS Accounts
    !

This report script, COMBO1.REP, is available in your\ESSBASE\APP\DEMO\BASIC directory.

Sample 6: Formatting Different Combinations of Data

This report uses the same layout and member selection as Sample 5, and adds more formatting in the report body. Note the use of line formatting.

Budget Audio New_York Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Sales 6,400 6,400 6,700 8,350 27,850 Cost_of_Goods_Sold 3,012 3,012 3,146 3,973 13,143 -------- -------- -------- -------- -------- Margin 3,388 3,388 3,554 4,377 14,707 Marketing 525 515 475 555 2,070 Payroll 1,950 1,950 1,950 1,950 7,800 Miscellaneous 0 0 0 0 0 -------- -------- -------- -------- -------- Total_Expenses 2,475 2,465 2,425 2,505 9,870 Profit 913 923 1,129 1,872 4,837 ======== ======== ======== ======== ======== Profit_% 14.27 14.42 16.85 22.42 17.37 Margin_% 52.94 52.94 53.04 52.42 52.81

Budget Audio Boston Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Sales 6,050 5,750 5,900 7,350 25,050 Cost_of_Goods_Sold 2,829 2,695 2,762 3,413 11,699 -------- -------- -------- -------- -------- Margin 3,221 3,055 3,138 3,937 13,351 Marketing 410 400 400 520 1,730 Payroll 1,590 1,590 1,590 1,590 6,360 Miscellaneous 0 0 0 0 0 -------- -------- -------- -------- -------- Total_Expenses 2,000 1,990 1,990 2,110 8,090 Profit 1,221 1,065 1,148 1,827 5,261 ======== ======== ======== ======== ======== Profit_% 20.18 18.52 19.46 24.86 21.00 Margin_% 53.24 53.13 53.19 53.56 53.30

Use the following script to create Sample 6:

<PAGE (Scenario, Product, Market)
{ PAGEONDIMENSION Product PAGEONDIMENSION Market }
Budget
<ICHILDREN Product
<IDESCENDANTS Market

      <COLUMN (Year)
      <ICHILDREN Year

<ROW(Accounts)

{ SUPBRACKETS DECIMAL 0 }
Sales 
Cost_of_Goods_Sold

{ UDATA "-" }      //line formatting command
Margin

{ SKIP }
Marketing
Payroll
Miscellaneous

{ UDATA "-" }      //line formatting command
Total_Expenses

{ SKIP }
Profit

{ UDATA DECIMAL 2 }    //line formatting command
Profit_%
Margin_%
    !

Format commands apply to members that follow the commands. The report begins each new page with the formats in place at the end of the previous report page. For example, if a report page ends with two decimal places, the following page begins with two decimal places. This report demonstrates the use of several important format commands:

• DECIMAL--The script for this report specifies the DECIMAL 0 format command before the Sales member.
• SUPBRACKETS--By default, negative numbers are enclosed in brackets, (). The SUPBRACKETS format command causes negative numbers to be output with a minus sign.
• UDATA--The UDATA command places underline characters under data columns. The character is specified within double quotes. The default is a double underline.

This report script, COMBO2.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 7: Using Aliases

This report outputs members in the middle of a page and uses aliases or alternate names. The default row member indentation is turned off.

Stereo Market Qtr4 Year Actual Budget Actual Budget ======== ======== ======== ======== 24,062 24,900 Sales 81,630 83,650 13,937 14,442 COGS 47,654 48,517 -------- -------- -------- -------- 10,125 10,458 Margin 33,976 35,133 1,438 1,600 Marketing 4,933 5,465 7,110 6,840 Payroll 28,440 27,360 -200 0 Misc. -143 0 -------- -------- -------- -------- 8,348 8,440 Total_Expenses 33,230 32,825 1,777 2,018 Profit 746 2,308 ======== ======== ======== ======== 7.39 8.10 Profit_% 0.91 2.76 42.08 42.00 Margin_% 41.62 42.00

Compact_Disc Market Qtr4 Period Actual Budget Actual Budget ======== ======== ======== ======== 29,039 29,250 Sales 101,664 100,300 10,830 11,115 COGS 38,120 38,114 -------- -------- -------- -------- 18,209 18,135 Margin 63,544 62,186 1,669 1,780 Marketing 6,067 5,975 5,721 5,415 Payroll 22,200 21,660 -226 0 Misc. 97 0 -------- -------- -------- -------- 7,164 7,195 Total_Expenses 28,364 27,635 11,045 10,940 Profit 35,180 34,551 ======== ======== ======== ======== 38.04 37.40 Profit_% 34.60 34.45 62.71 62.00 Margin_% 62.50 62.00

Use the following script to create Sample 7:

<PAGE (Product, Market)
{ PAGEONDIMENSION Product }
{ PAGEONDIMENSION Market }
<IDESCENDANTS Product
{ DECIMAL 0 }
<SYM

     <COLUMN (Year, Scenario)
     Qtr4 Year
     Actual Budget
<ROW(Accounts)
{ SUPBRACKETS OUTALTNAMES NOINDENTGEN ORDER 1,2,0,3,4 }
Sales Cost_of_Goods_Sold 
{ UDATA "-" }
Margin
{ SKIP }
Marketing Payroll Miscellaneous
{ UDATA "-" }
Total_Expenses
{ SKIP }
Profit
{ UDATA DECIMAL 2 }
Profit_%
Margin_%
    !

The SYM command forces the report to output symmetric column groups. The default is to display two columns: one for Qtr4 Actual and one for Year Budget. Because the report calls for Actual and Budget under both Qtr4 and Year, the SYM command is required. Alternatively, repeat the Actual and Budget names under Qtr4 and Year.

The OUTALTNAMES format command causes the report to use aliases or alternate names instead of member names.

The NOINDENTGEN format command causes row members to not be indented. By default, members are indented two spaces for each level.

The ORDER command moves specified output columns to new locations. The row name is considered column 0.

The FIXCOLUMNS format command restricts the number of output columns. Reports often require both ORDER and FIXCOLUMNS. You can use ORDER to remove unwanted columns, and FIXCOLUMNS to stop these columns from displaying after the report columns.

This report script, MIDDLE.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 8: Creating Custom Headings and % Characters

This report displays custom headings and percent sign (%) characters after each data value. This section shows a representative part of the output.

Prepared by: Admin The Electronics Club Page: 1 09/21/95 Profit_% Actual Stereo Jan Feb Mar Apr May Jun ======= ======= ======= ======= ======= ======= New_York 1.43% -10.00% -3.51% -2.22% 1.14% -6.18% Boston -0.34% -2.51% -4.44% -4.89% -7.02% -13.15% Chicago -0.65% -0.72% -2.28% -3.53% -6.33% -10.79% East 0.18% -4.47% -3.39% -3.41% -3.60% -9.70% San_Francisco 1.43% -1.87% 4.42% 2.15% -1.26% 0.66% Seattle 0.95% -5.66% 1.42% -6.82% -11.47% -12.34% Denver 3.03% -1.11% -5.88% -6.52% -5.17% -13.83% Los_Angeles -1.50% -3.94% -2.86% -3.29% 3.12% -2.51% West 0.98% -2.95% -0.13% -2.81% -2.62% -5.61% Dallas 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Houston 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Phoenix 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% South 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Market 0.65% -3.56% -1.44% -3.06% -3.03% -7.29%

Prepared by: Admin The Electronics Club Page: 2 09/21/95 Profit_% Actual Compact_Disc Jan Feb Mar Apr May Jun ======= ======= ======= ======= ======= ======= New_York 32.51% 29.95% 35.30% 32.70% 30.45% 31.73% Boston 33.42% 27.92% 33.98% 30.74% 27.45% 30.85% Chicago 34.29% 30.48% 26.33% 28.83% 28.11% 33.76% East 33.35% 29.50% 32.30% 30.92% 28.77% 32.09% San_Francisco 37.77% 35.02% 33.41% 33.23% 35.32% 37.95% Seattle 40.41% 38.33% 38.89% 37.06% 37.01% 38.29% Denver 31.93% 32.10% 34.82% 29.15% 32.71% 30.85% Los_Angeles 31.65% 30.22% 30.22% 31.45% 27.06% 33.20% West 35.51% 33.94% 34.21% 32.77% 33.16% 35.25% Dallas 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Houston 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Phoenix 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% South 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Market 34.60% 32.10% 33.41% 32.01% 31.35% 33.97%

Prepared by: Admin The Electronics Club Page: 3 09/21/95 Profit_% Actual Audio Jan Feb Mar Apr May Jun ======= ======= ======= ======= ======= ======= New_York 19.35% 13.64% 18.64% 16.55% 16.70% 14.65% Boston 18.34% 14.44% 18.94% 14.94% 12.14% 12.42% Chicago 18.50% 16.67% 13.18% 14.12% 12.70% 13.74% East 18.76% 14.88% 17.09% 15.32% 14.05% 13.68% San_Francisco 20.32% 17.38% 18.92% 18.03% 18.23% 20.57% Seattle 23.36% 21.40% 23.37% 20.17% 18.82% 19.04% Denver 18.36% 17.25% 18.88% 13.43% 15.84% 12.14% Los_Angeles 17.15% 14.76% 15.44% 15.76% 15.10% 17.07% West 19.75% 17.53% 19.00% 16.88% 17.01% 17.52% Dallas 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Houston 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Phoenix 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% South 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Market 19.34% 16.45% 18.21% 16.24% 15.78% 15.96%

Prepared by: Admin The Electronics Club Page: 8 09/21/95 Profit_% Actual Product Jan Feb Mar Apr May Jun ======= ======= ======= ======= ======= ======= New_York 22.71% 21.43% 13.11% 10.54% 9.73% 13.16% Boston 24.98% 23.25% 19.95% 18.00% 17.03% 18.62% Chicago 22.01% 17.94% 18.14% 15.45% 18.70% 16.01% East 23.19% 20.84% 16.89% 14.42% 14.94% 15.78% San_Francisco 23.71% 20.60% 21.93% 20.45% 21.44% 19.98% Seattle 21.06% 21.05% 21.24% 19.00% 21.72% 15.13% Denver 21.61% 16.01% 19.79% 14.81% 20.66% 13.89% Los_Angeles 17.54% 15.51% 17.03% 14.33% 17.59% 16.09% West 21.02% 18.35% 19.99% 17.26% 20.30% 16.61% Dallas 15.67% 16.50% 15.32% 13.93% 20.36% 15.49% Houston 20.01% 20.29% 20.62% 15.87% 23.60% 12.38% Phoenix 20.01% 16.12% 17.18% 16.50% 21.39% 15.22% South 18.39% 17.53% 17.59% 15.36% 21.66% 14.46% Market 21.37% 19.09% 18.46% 15.92% 18.67% 15.93%

Use the following script to create Sample 8:

<PAGE (Accounts, Scenario, Product)
{ PAGEONDIMENSION Product }   // New page when Product changes
Profit_%
Actual
<IDESCENDANTS Product

      <COLUMN (Year)
      Jan Feb Mar Apr May Jun

<ROW(Market)

{ STARTHEADING
TEXT   1 "Prepared by:"
      14 "*USERNAME"
       C "The Electronics Club"
      65 "*PAGESTRING"
TEXT  65 "*DATE"
SKIP
ENDHEADING }

{ Decimal 2 AFTER "%" SUPBRACKETS }   // Place % at end and 
    // suppress bracket 
<IDESCENDANTS Market
      !

Each data value in the report has a percent sign, %. This label is defined with the AFTER "%"format command. You can specify any character within quotation marks.

This report has custom headings at the top of each page. All format commands specified between the STARTHEADING and ENDHEADING format commands are displayed at the top of each report page.

TEXT format commands define text labels. The report generator provides dynamic text with *options. See the online Technical Reference in the DOCS directory for a full list of the available options. This report uses the following options:

• *USERNAME--which outputs the user name used when connecting to Hyperion Essbase
• *PAGESTRING--which outputs the current page number of the report
• C--which centers the report title

This report script, HEADING1.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 9: Creating Custom Page Headings

This report builds on Sample 8 by adding custom page headings. By default, page dimension members are output at the top center of a report page.

Prepared by :admin The Electronics Club Page: 1 Actual Profit by Product 12/12/95 Product: Stereo Jan Feb Mar Apr May Jun New York 1.43% -10.00% -3.51% -2.22% 1.14% -6.18% Boston -0.34% -2.51% -4.44% -4.89% -7.02% -13.15% Chicago -0.65% -0.72% -2.28% -3.53% -6.33% -10.79% San Francisco 1.43% -1.87% 4.42% 2.15% -1.26% 0.66% Seattle 0.95% -5.66% 1.42% -6.82% -11.47% -12.34% Denver 3.03% -1.11% -5.88% -6.52% -5.17% -13.83% Los Angeles -1.50% -3.94% -2.86% -3.29% 3.12% -2.51% Dallas #Missing #Missing #Missing #Missing #Missing #Missing Houston #Missing #Missing #Missing #Missing #Missing #Missing Phoenix #Missing #Missing #Missing #Missing #Missing #Missing East 0.18% -4.47% -3.39% -3.41% -3.60% -9.70% West 0.98% -2.95% -0.13% -2.81% -2.62% -5.61% South #Missing #Missing #Missing #Missing #Missing #Missing Market 0.65% -3.56% -1.44% -3.06% -3.03% -7.29%

Prepared by :admin The Electronics Club Page: 2 Actual Profit by Product 12/12/95 Pageduct:Compact Disc Jan Feb Mar Apr May Jun New York 32.51% 29.95% 35.30% 32.70% 30.45% 31.73% Boston 33.42% 27.92% 33.98% 30.74% 27.45% 30.85% Chicago 34.29% 30.48% 26.33% 28.83% 28.11% 33.76% San Francisco 37.77% 35.02% 33.41% 33.23% 35.32% 37.95% Seattle 40.41% 38.33% 38.89% 37.06% 37.01% 38.29% Denver 31.93% 32.10% 34.82% 29.15% 32.71% 30.85% Los Angeles 31.65% 30.22% 30.22% 31.45% 27.06% 33.20% Dallas #Missing #Missing #Missing #Missing #Missing #Missing Houston #Missing #Missing #Missing #Missing #Missing #Missing Phoenix #Missing #Missing #Missing #Missing #Missing #Missing East 33.35% 29.50% 32.30% 30.92% 28.77% 32.09% West 35.51% 33.94% 34.21% 32.77% 33.16% 35.25% South #Missing #Missing #Missing #Missing #Missing #Missing Market 34.60% 32.10% 33.41% 32.01% 31.35% 33.97%

Prepared by :admin The Electronics Club Page: 3 Actual Profit by Product 12/12/95 Product: Audio Jan Feb Mar Apr May Jun New York 19.35% 13.64% 18.64% 16.55% 16.70% 14.65% Boston 18.34% 14.44% 18.94% 14.94% 12.14% 12.42% Chicago 18.50% 16.67% 13.18% 14.12% 12.70% 13.74% San Francisco 20.32% 17.38% 18.92% 18.03% 18.23% 20.57% Seattle 23.36% 21.40% 23.37% 20.17% 18.82% 19.04% Denver 18.36% 17.25% 18.88% 13.43% 15.84% 12.14% Los Angeles 17.15% 14.76% 15.44% 15.76% 15.10% 17.07% Dallas #Missing #Missing #Missing #Missing #Missing #Missing Houston #Missing #Missing #Missing #Missing #Missing #Missing Phoenix #Missing #Missing #Missing #Missing #Missing #Missing East 18.76% 14.88% 17.09% 15.32% 14.05% 13.68% West 19.75% 17.53% 19.00% 16.88% 17.01% 17.52% South #Missing #Missing #Missing #Missing #Missing #Missing Market 19.34% 16.45% 18.21% 16.24% 15.78% 15.96%

Prepared by :admin The Electronics Club Page: 4 Actual Profit by Product 12/12/95 Product:Television Jan Feb Mar Apr May Jun New York 19.45% 28.80% 3.29% -4.14% -1.98% 7.73% Boston 34.53% 40.00% 24.92% 19.20% 15.00% 22.31% Chicago 20.61% 12.86% 15.29% 7.36% 19.23% 10.29% San Francisco 16.53% 12.59% 17.52% 10.74% 18.70% 7.43% Seattle 9.22% 12.29% 14.89% 18.39% 22.94% 7.87% Denver 17.69% 0.24% 16.39% 10.75% 21.05% 12.36% Los Angeles 10.41% 6.25% 10.48% 3.00% 14.52% 5.34% Dallas 2.24% 6.23% 5.89% 5.38% 15.48% 4.75% Houston 11.04% 8.65% 11.52% 0.54% 14.38% 2.19% Phoenix 11.09% 5.81% 8.58% 7.82% 12.47% 10.94% East 24.69% 26.95% 13.96% 6.89% 10.43% 13.14% West 13.41% 8.23% 14.57% 10.31% 18.95% 7.91% South 7.55% 6.73% 8.38% 4.90% 14.19% 5.97% Market 16.41% 14.54% 12.86% 7.86% 14.93% 9.28%

Prepared by :admin The Electronics Club Page: 5 Actual Profit by Product 12/12/95 Product:VCR Jan Feb Mar Apr May Jun New York 34.65% 32.38% 19.50% 20.96% 14.14% 21.57% Boston 31.44% 28.43% 22.33% 29.27% 25.86% 27.89% Chicago 31.97% 29.00% 30.95% 27.85% 28.81% 28.26% San Francisco 32.28% 31.47% 29.50% 31.81% 30.89% 26.42% Seattle 31.06% 33.42% 29.99% 26.21% 26.86% 16.40% Denver 31.18% 29.86% 30.39% 27.87% 29.66% 19.05% Los Angeles 29.14% 29.11% 28.16% 26.27% 27.31% 29.36% Dallas 32.17% 31.32% 27.11% 27.78% 27.55% 29.86% Houston 34.07% 35.53% 35.29% 31.06% 32.90% 20.63% Phoenix 32.91% 29.86% 30.39% 27.87% 29.66% 19.05% East 32.87% 29.99% 24.43% 25.95% 22.99% 25.79% West 30.88% 30.94% 29.42% 28.19% 28.76% 23.61% South 32.99% 32.22% 30.83% 28.84% 29.89% 23.71% Market 32.14% 30.99% 28.19% 27.62% 27.19% 24.37%

Prepared by :admin The Electronics Club Page: 6 Actual Profit by Product 12/12/95 Product:Camera Jan Feb Mar Apr May Jun New York 16.35% 12.30% 6.72% 4.26% 4.66% 6.67% Boston 10.55% 6.52% 10.66% 4.99% 17.87% 10.74% Chicago 17.57% 12.68% 13.67% 12.95% 16.71% 13.10% San Francisco 30.97% 25.36% 25.64% 24.43% 21.37% 25.96% Seattle 16.69% 11.96% 12.33% 7.91% 20.53% 13.56% Denver 22.36% 12.31% 12.19% 7.77% 20.53% 13.56% Los Angeles 14.96% 13.19% 15.65% 11.15% 14.95% 11.30% Dallas 11.68% 8.99% 11.41% 4.72% 17.62% 10.47% Houston 12.07% 10.99% 9.83% 11.79% 21.17% 13.15% Phoenix 14.36% 11.24% 10.13% 13.31% 22.56% 15.70% East 15.06% 10.77% 10.07% 7.41% 12.67% 9.99% West 22.31% 16.92% 17.63% 14.32% 19.47% 17.24% South 12.71% 10.38% 10.47% 9.94% 20.45% 13.11% Market 17.59% 13.28% 13.39% 11.04% 17.68% 13.92%

Prepared by :admin                The Electronics Club               Page: 7   
                                 Actual Profit by Product           12/12/95

 Product:Visual

                       Jan       Feb      Mar       Apr       May      Jun

  New York           24.40%    25.71%    9.65%     6.67%     5.23%    12.31%
  Boston             28.51%    28.19%   20.58%    19.75%    19.82%    21.89%
  Chicago            23.86%    18.63%   20.75%    16.17%    21.97%    17.24%
  San Francisco      25.96%    22.72%   24.00%    22.11%    23.63%    19.59%
  Seattle            19.41%    20.82%   19.77%    18.23%    23.54%    12.48%
  Denver             23.89%    15.13%   20.45%    15.73%    23.82%    15.05%
  Los Angeles        17.77%    15.97%   17.99%    13.38%    19.15%    15.46%
  Dallas             15.67%    16.50%   15.32%    13.93%    20.36%    15.49%
  Houston            20.01%    20.29%   20.62%    15.87%    23.60%    12.38%
  Phoenix            20.01%    16.12%   17.18%    16.50%    21.39%    15.22%
 East                25.49%    24.13%   16.77%    13.89%    15.46%    16.94%
 West                21.85%    18.89%   20.65%    17.52%    22.43%    16.00%
 South               18.39%    17.53%   17.59%    15.36%    21.66%    14.46%
Market               22.26%    20.27%   18.58%    15.76%    19.98%    15.91%

Prepared by :admin The Electronics Club Page: 8 Actual Profit by Product 12/12/95 Product:Product Jan Feb Mar Apr May Jun New York 22.71% 21.43% 13.11% 10.54% 9.73% 13.16% Boston 24.98% 23.25% 19.95% 18.00% 17.03% 18.62% Chicago 22.01% 17.94% 18.14% 15.45% 18.70% 16.01% San Francisco 23.71% 20.60% 21.93% 20.45% 21.44% 19.98% Seattle 21.06% 21.05% 21.24% 19.00% 21.72% 15.13% Denver 21.61% 16.01% 19.79% 14.81% 20.66% 13.89% Los Angeles 17.54% 15.51% 17.03% 14.33% 17.59% 16.09% Dallas 15.67% 16.50% 15.32% 13.93% 20.36% 15.49% Houston 20.01% 20.29% 20.62% 15.87% 23.60% 12.38% Phoenix 20.01% 16.12% 17.18% 16.50% 21.39% 15.22% East 23.19% 20.84% 16.89% 14.42% 14.94% 15.78% West 21.02% 18.35% 19.99% 17.26% 20.30% 16.61% South 18.39% 17.53% 17.59% 15.36% 21.66% 14.46% Market 21.37% 19.09% 18.46% 15.92% 18.67% 15.93%

Use the following script to create Sample 9:

<PAGE (Accounts, Scenario, Product)
<IDESCENDANTS Product
<SORTLEVEL
{ PAGEONDIMENSION Product }
{ STARTHEADING 
TEXT    1 "Prepared by:" 
       14 "*USERNAME" 
        C "The Electronics Club" 
       65 "*PAGESTRING"
SUPPAGEHEADING 
UNDERLINECHAR " "
TEXT    C "Actual Profit by Product" 
       65 "*DATE" 
TEXT    1 "Product:" 
       10 "*PAGEHDR 3" 
SKIP 
ENDHEADING }
Profit_%
Actual

      <COLUMN (Year)
      Jan Feb Mar Apr May Jun
<ROW(Market)

{ DECIMAL 2 AFTER "%" SUPBRACKETS UNDERSCORECHAR " " }
{ INDENTGEN 1 }
<IDESCENDANTS Market
     !

The SUPPAGEHEADING format command suppresses the default page headings from output.

The *PAGEHDR command customizes the location of page member labels. The Sample 9 script uses page heading number 3, Product, because this is the third page dimension.

You may have also noticed that member names do not have underscores. The UNDERSCORECHAR format command blanks out underscores.

Another difference is the underlining of column headings. The UNDERLINECHAR format command causes the underlining to character to change to the character in quotes.

The report rows are also sorted according to their levels in the database outline. Sort commands, such as SORTLEVEL, do not affect individual members selected in reports. Instead, these commands work in conjunction with member selection commands.

Sample 9 reverses the indentation of levels from previous reports. The INDENTGEN command indents members to the specified number of characters.

This report script, HEADING2.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 10: Using Formulas

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
    !

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, 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 your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 11: Placing Two Page Layouts on the Same Page

This sample report has two different page layouts on the same page.

Year Profit_% Actual East West South Market ========= ========= ========= ========= Stereo -0.52% 1.91% 0.00% 0.91% Compact_Disc 32.60% 36.00% 0.00% 34.60% Audio 17.86% 20.81% 0.00% 19.60% Television 20.40% 16.57% 13.50% 17.21% VCR 30.81% 32.43% 33.70% 32.24% Camera 16.66% 21.66% 17.83% 19.07% Visual 23.16% 23.56% 22.27% 23.09% Product 21.34% 22.50% 22.27% 22.04% Sales Actual Product Qtr1 Qtr2 Qtr3 Qtr4 Year ========= ======== ======== ======== ======== New_York $18,631 $17,681 $19,923 $24,403 $80,638 Boston $15,812 $15,050 $16,716 $19,159 $66,737 Chicago $16,536 $15,599 $17,411 $21,374 $70,920 East $50,979 $48,330 $54,050 $64,936 $218,295 San_Francisco $19,761 $19,019 $20,722 $24,807 $84,309 Seattle $13,766 $13,546 $14,204 $19,034 $60,550 Denver $13,800 $13,588 $13,838 $18,232 $59,458 Los_Angeles $17,866 $17,269 $17,208 $22,635 $74,978 West $65,193 $63,422 $65,972 $84,708 $279,295 Dallas $ 9,226 $ 9,175 $ 9,481 $12,700 $40,582 Houston $ 7,690 $ 7,363 $ 7,646 $10,785 $33,484 Phoenix $ 8,157 $ 7,903 $ 8,343 $11,843 $36,246 South $25,073 $24,441 $25,470 $35,328 $110,312 Market $141,245 $136,193 $145,492 $184,972 $607,902

Use the following script to create Sample 11:

<PAGE (Year, Accounts, Scenario)

      <COLUMN (Market)
      <ICHILDREN Market

<ROW(Product)
<IDESCENDANTS Product

Actual
{ DECIMAL 2 WIDTH 10 SUPBRACKETS AFTER "%" }
Profit_%
    !

<PAGE (Accounts, Scenario, Product)
Actual
Sales
Product

      <COLUMN(Year)
      <ICHILDREN Year 

<ROW(Market)
{ DECIMAL 0 After " " BEFORE "$" }
<IDESCENDANTS Market
    !

In a single report, you can select multiple dimension layouts and members. To define a multiple layout report, define reports as you normally would. Separate the commands with exclamation marks as shown above. Whenever the column, row, or page dimensions change between ! output commands, new headings are automatically generated to match the new layout.

The BEFORE format command places a character in front of data values. The AFTER format command turns off the percent signs from the first report layout.

This report script, 2LAYOUTS.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 12: Formatting for Data Export

This sample creates a report with a member name in each column. This format is required when you export Hyperion Essbase data to another product, such as an SQL database, with a flat file.

New York Stereo Sales 1000.0 950.0 New York Stereo Cost of Goods Sold 580.0 551.0 New York Stereo Margin 420.0 399.0 New York Stereo Marketing 80.0 80.0 New York Stereo Payroll 340.0 340.0 New York Stereo Miscellaneous 0.0 0.0 New York Stereo Total Expenses 420.0 420.0 New York Stereo Profit 0.0 -21.0 New York Stereo Profit % 0.0 -2.2 New York Stereo Margin % 42.0 42.0 New York Compact Disc Sales 1200.0 1150.0 New York Compact Disc Cost of Goods Sold 456.0 437.0 New York Compact Disc Margin 744.0 713.0 New York Compact Disc Marketing 95.0 95.0 New York Compact Disc Payroll 310.0 310.0 New York Compact Disc Miscellaneous 0.0 0.0 New York Compact Disc Total Expenses 405.0 405.0 New York Compact Disc Profit 339.0 308.0 New York Compact Disc Profit % 28.3 26.8 New York Compact Disc Margin % 62.0 62.0 New York Audio Sales 2200.0 2100.0 New York Audio Cost of Goods Sold 1036.0 988.0 New York Audio Margin 1164.0 1112.0 New York Audio Marketing 175.0 175.0 New York Audio Payroll 650.0 650.0 New York Audio Miscellaneous 0.0 0.0 New York Audio Total Expenses 825.0 825.0 New York Audio Profit 339.0 287.0 New York Audio Profit % 15.4 13.7 New York Audio Margin % 52.9 53.0 New York Television Sales 1800.0 1600.0

Use the following script to create Sample 12:

<PAGE(Scenario)

<COLUMN(Year)

<ROW (Market, Product, Accounts)
<CHILDREN East
<DESCENDANTS Product

{ DECIMAL 1
WIDTH 9
SUPBRACKETS
SUPCOMMA
MISSINGTEXT " "
UNDERSCORECHAR " "
SUPHEADING 
NOINDENTGEN 
SUPFEED 
ROWREPEAT

Budget
      Jan Feb

<DESCENDANTS Accounts
    !

The ROWREPEAT command produces rows of data that have the member names repeat for each row dimension.

The SUPFEED command suppresses page feeds. A page feed automatically occurs when the report output reaches the default page length of 66 rows, unless you enter the PAGELENGTH command to change this setting. When a large flat file is created, you can use this command to prevent page breaks (blank rows) from being displayed in the report every time output reaches a logical page length.

This report script, FLAT2SQL.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 13: Creating Asymmetric Columns

Asymmetric columns make up this report. Typically, a report contains symmetric columns. That is, when multiple dimensions are displayed across the page as column groups, each level of nested columns has the same number of members nested below. Because Actual has only one nested column, Jan, and Budget has three nested columns, this report is considered asymmetric.

Some rows in the report use names other than the member names from the database. In addition to allowing aliases, as in Sample 7, you can rename a row name in the reporter.

Product Market Actual Budget Budget Budget Jan Jan Feb Mar ======== ======== ======== ======== Revenue 49,896 49,950 45,770 45,770 Cost of Goods 20,827 19,755 18,058 18,047 Gross Margin 29,069 30,196 27,712 27,723 Marketing 3,560 3,515 3,525 3,515 Payroll 14,599 14,402 14,416 14,416 Miscellaneous 249 0 0 0 Total Expenses 18,408 17,917 17,941 17,931 Profit 10,661 12,279 9,771 9,792

Use the following script to create Sample 13:

<PAGE (Product, Market)

      <COLUMN (Scenario, Year)
      Actual   Budget Budget Budget
         Jan      Jan    Feb    Mar

<ROW (Accounts)

{ RENAME "Revenue" } Sales
{ RENAME "Cost of Goods" } Cost_of_Goods_Sold
{ RENAME "Gross Margin" } Margin

{ SKIP UNDERSCORECHAR " " }
<ICHILDREN Total_Expenses

{ SKIP }
Profit
    !

To create an asymmetric report, you must specify the member name of each column. Because the report output has two column groupings, Scenario and Year, you must specify a member from each dimension for each column. If you do not specify each column member, the resulting report format is symmetric.

The RENAME command redefines a member name when the report is output. This is useful when you do not want to use an aliases table.

This report script, ASYMM.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 14: Calculating Columns

This section contains two examples of CALCULATE COLUMN scripts and the reports they produce. CALCULATE COLUMN supports standard mathematical operations.

Sample 14-A: Basic Calculated Columns

East Actual Budget Var Jan Feb Mar Qtr1 Jan Feb Mar Q1 Q1 ====== ====== ====== ====== ====== ====== ====== ====== ======= 1,295 1,132 553 2,980 Tele~ Profit 1,240 950 950 3,140 (160) 25 27 14 66 Profit_% 26 22 22 70 (4) 56 62 59 177 Margin_% 60 60 60 180 (3) 1,417 1,120 898 3,435 VCR Profit 1,466 1,161 1,161 3,788 (353) 33 30 24 87 Profit_% 35 31 31 98 (10) 61 61 62 183 Margin_% 63 63 63 189 (6) 400 272 256 928 Cam~ Profit 528 360 360 1,247 (319) 15 11 10 36 Profit_% 19 13 13 45 (10) 70 70 70 211 Margin_% 71 71 71 213 (2) 3,112 2,524 1,707 7,343 Visu~ Profit 3,234 2,471 2,471 8,175 (832) 25 24 17 66 Profit_% 27 23 23 74 (7) 61 63 63 187 Margin_% 64 64 64 191 (4)

Use the following script to create Sample 14-A:

<PAGE(Market)
East
      <COLUMN (Scenario, Year)
      Actual  Budget
      Jan Feb Mar
{ CALCULATE COLUMN "Qtr1"  = 2 : 4 
  CALCULATE COLUMN "Q1" = 5 : 7 
  CALCULATE COLUMN "Var~Q1" = 8 - 9
  ORDER 2,3,4,8,0,1,5,6,7,9
  WIDTH 7 WIDTH 10 0 1
}
<ROW (Product, Accounts)
<ICHILDREN Visual
<CHILDREN Accounts
    !

This report script, COLCALC2.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 14-B: Asymmetric Columns

The following sample has two regular columns defined in asymmetric mode. For more information on asymmetric columns, see Sample 13: Creating Asymmetric Columns.

East Budget Actual Actual Jan Jan % Sales ======== ======== ======== 1,200 Television Payroll 1,236 25% 440 Marketing 365 9% 1,240 Profit 1,295 26% 4,800 Sales 5,244 100% 1,030 VCR Payroll 1,044 25% 150 Marketing 156 4% 1,466 Profit 1,417 35% 4,200 Sales 4,311 100% 1,195 Camera Payroll 1,167 42% 300 Marketing 288 11% 528 Profit 400 19% 2,850 Sales 2,656 100% 3,425 Visual Payroll 3,447 29% 890 Marketing 809 8% 3,234 Profit 3,112 27% 11,850 Sales 12,211 100%

Use the following script to create Sample 14-B:

<PAGE(Market)
East

     <COLUMN(Scenario, Year)
     Budget  Actual
     Jan     Jan

{ ORDER 2,0,1,3,4 WIDTH 12 0 1 NOINDENTGEN AFTER "%" 4 
  SKIPONDIMENSION Product LMARGIN 10 }

<ROW(Product, Accounts)

{ CALCULATE ROW "Sales" OFF }
{ CALCULATE COLUMN "Actual~% Sales" = 2 % "Sales" 2 }

<ICHILDREN Visual
{ SAVEROW } Sales
     Payroll
     Marketing
     Profit
<DUPLICATE Sales
    !

This report script, COLCALC3.REP, is available in your\ESSBASE\APP\DEMO\BASIC directory.

Sample 15: Calculating Rows

The sample reports in this section demonstrate CALCULATE ROW scripts and the reports they produce.

Sample 15-A: Basic Calculated Row

This sample report demonstrates the basic form of the CALCULATE ROW command.

Audio Actual Sales Jan Feb Mar ======== ======== ======== Boston 1,985 1,801 1,954 New_York 2,310 2,082 2,259 Chicago 2,043 1,884 1,814 Total Sales 6,338 5,767 6,027 Avg Sales 2,113 1,922 2,009

Use the following script to create Sample 15-A:

     Audio Actual Sales
     Jan Feb Mar

{ CALCULATE ROW "Total Sales" }    //create new calculated row

Boston
New_York
Chicago

{ SKIP
  CALCULATE ROW "Avg Sales" = "Total Sales" /3 
  PRINTROW "Total Sales"
  PRINTROW "Avg Sales" }
    !

This report script, ROWCALC1.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 15-B: Calculated Rows and Missing Relationships

This sample report is a simple summary of information in a North/South grouping, which is not part of the database outline. When relationships that you need for reporting are missing in the database outline, often the best solution is to use calculated rows (or columns).

Budget Payroll Jan Feb Mar ==== ==== ==== Northern Cities ================ New_York 1,940 1,930 1,930 Boston 1,610 1,610 1,610 Chicago 1,630 1,630 1,630 San_Francisco 1,815 1,815 1,815 Seattle 1,415 1,409 1,409 Southern Cities ================ Denver 1,499 1,499 1,499 Los_Angeles 1,757 1,787 1,787 Dallas 1,002 1,002 1,002 Phoenix 900 900 900 Houston 834 834 834 Total Northern 8,410 8,394 8,394 Total Southern 5,992 6,022 6,022

Use the following script to create Sample 15-B:

// Declare Calculated Rows to Sum Southern and Northern Cities
{ CALCULATE ROW "Total Southern" OFF

// initially, set operation to OFF
  CALCULATE ROW "Total Northern" OFF  }

<PAGE(Product,Scenario,Accounts)
{ RENAME "" } Product             // all products, so blank out
                                  // the Product Label
Budget
Payroll
     <COLUMN(Year)
     Jan  Feb  Mar

<ROW(Market)                      // Northern Cities

{ SETROWOP "Total Northern" +     // Accumulate for Northern

SKIP 3
IMMHEADING	 	 	 	 	 	 // Put out heading now so text
                                  // will go after it
Text 0 "Northern Cities" UCHARACTERS
}

New_York Boston  Chicago San_Francisco Seattle

//Southern Cities

{ SETROWOP "Total Southern" +   } // Accumulate for Southern
{ SETROWOP "Total Northern" OFF } // Stop Accumulation for Northern

{ SKIP Text 0 "Southern Cities" UCHARACTERS }

Denver  Los_Angeles Dallas  Phoenix Houston

{ SKIP
PRINTROW "Total Northern"         // output calculated rows
PRINTROW "Total Southern"
}
    !

This report script, ROWCALC2.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 15-C: Averaging Rows

This report 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 database outline. If this number changes, the report definition must be modified. If a count of regions is not computed, a hard-to-notice error could 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" }
    !

This report script, ROWAVG.REP, is available in your\ESSBASE\APP\DEMO\ BASIC directory.

Sample 16: Sorting by Top or Bottom Data Values

The following two reports demonstrate the use of TOP and BOTTOM conditional retrieval commands in a report script. For more information, see Developing Report Scripts.

Sample 16-A: Bottom Data Values

This sample report demonstrates the basic use of the BOTTOM command. The report is based on the Sample Basic database.

Measures Actual Budget Jan Dec Jan Dec ======== ======== ======== ======== East 200 158 233 280 340 300 184 277 240 210 Diet 181 213 200 240 West 100 378 223 830 530 300 755 971 830 950 400 454 434 470 370 South 200 480 496 520 390 Diet 355 404 490 430 300 188 213 270 240 Central 300 790 824 930 810 100 724 792 900 890 400 691 785 660 650 Market 200 2,141 2,302 2,710 2,810 300 1,917 2,285 2,270 2,210 400 1,611 1,720 1,730 1,600

Use the following script to create Sample 16-A:

<Sym
<Column (Scenario, Year)
Actual Budget
Jan Dec
<Row (Market, Product)
<ICHILDREN Market
<ICHILDREN Product
<Bottom (3, @DataColumn(3))
    !

The BOTTOM command specifies that only the three lowest data values are returned for each row grouping, based on the target data values specified in column three (Budget, Jan). Notice that no row dimension is selected here, so the report output defaults to the innermost row.

This report script, BOTTOM.REP, is available in your\ESSBASE\APP\SAMPLE\ BASIC directory.

Sample 16-B: Top Data Values

This sample report demonstrates the basic use of the TOP command. The report is based on the Sample Basic database.

Measures Actual Budget Jan Dec Jan Dec ======== ======== ======== ======== East Product 1,732 2,037 2,080 2,120 100 924 1,026 960 990 400 466 501 600 580 West Product 2,339 2,448 2,980 2,710 200 752 820 850 860 Diet 663 629 850 730 South Product 997 1,141 1,330 1,270 100 329 432 540 640 200 480 496 520 390 Central Product 2,956 3,154 3,550 3,570 Diet 1,080 1,064 1,340 1,300 200 751 753 1,060 1,220 Market Product 8,024 8,780 9,940 9,670 100 2,355 2,473 3,230 3,050 Diet 2,279 2,310 2,880 2,700

Use the following script to create Sample 16-B:

<Sym
<Column (Scenario, Year)
Actual Budget
Jan Dec
<Row (Market, Product)
<Ichildren Market
<Ichildren Product
<Top (10, @DataColumn(3))
    !

The TOP command specifies that only the three highest data values are returned for each row grouping, based on the target data values specified in column three (Budget, Jan). Notice that no row dimension is selected here, so the report output defaults to the innermost row.

This report script, TOP.REP, is available in your \ESSBASE\APP\SAMPLE\ BASIC directory.

Sample 17: Restricting Rows

The following report demonstrates the use of the RESTRICT conditional retrieval command in a report script. For more information, see Developing Report Scripts.

Measures Actual Budget Jan Dec Jan Dec ======== ======== ======== ======== East 200 158 233 280 340 300 184 277 240 210 Diet 181 213 200 240 South 300 188 213 270 240 400 #Missing #Missing #Missing #Missing

Use the following script to create Sample 17:

<Sym
<Column (Scenario, Year)
Actual Budget
Jan Dec
<Row (Market, Product)
<Ichildren Market
<Ichildren Product
<Restrict (@DataCol(3) < $300.00 )
    !

The RESTRICT command specifies that only data values that are less than $300.00 are returned for each row grouping, based on the target data values specified in column three (Budget, Jan). Notice that no row dimension is selected here, so the report output defaults to the innermost row.

This report script, RESTRICT.REP, is available in your\ESSBASE\APP\ SAMPLE\BASIC directory.

Sample 18: Ordering Data Values

The following report demonstrates the use of the ORDERBY conditional retrieval command in a report script. For more information, see Developing Report Scripts.

Sales Scenario Jan Feb Mar Apr ======== ======== ======== ======== New York 100-20 #Missing #Missing #Missing #Missing 100-30 #Missing #Missing #Missing #Missing 200-20 #Missing #Missing #Missing #Missing 200-30 #Missing #Missing #Missing #Missing 300-30 #Missing #Missing #Missing #Missing Diet #Missing #Missing #Missing #Missing 200-10 61 61 63 66 400-30 134 189 198 198 300-20 180 180 182 189 400-20 219 243 213 223 400-10 234 232 234 245 300-10 483 495 513 638 200-40 490 580 523 564 200 551 641 586 630 400 587 664 645 666 300 663 675 695 827 100-10 678 645 675 712 100 678 645 675 712 Product 2,479 2,625 2,601 2,835

Use the following script to create Sample 18:

<Page ("Measures")
<Column ("Scenario", "Year")
<Row ("Market", "Product")
"Sales"
"Scenario"
"Jan" "Feb" "Mar" "Apr"
"New York"

"Product" "100" "100-10" "100-20" "100-30" "200" "200-10"
"200-20" "200-30" "200-40" "300" "300-10" "300-20" "300-30" "400"
"400-10" "400-20" "400-30" "Diet" "100-20" "200-20" "300-30"

<ORDERBY ("Product", @DATACOL(1) ASC, @DATACOL(2) DESC, 
@DATACOL(3) ASC @DataCol (4) DESC)
     !

The ORDERBY command is based only on data in the data columns. If the SUPPRESSMISSING command is not used in the report, #MISSING is considered to be the lowest data value. ORDERBY compares data values in the following order:

• Two values in the same column (for example, in COL1, the value associated with 200-10 is compared with the 400-30 data value, as shown in the example below).
• Data values between two data columns (for example, the data value in COL1 is compared with the data value in COL2, as shown in the example next).

If two data value are the same, the sort proceeds to the next column to determine the order.

In this example, the data value 61 in COL1 and COL2 are identical; the data in COL1 should be in ascending order, the data in COL2 should be in descending order. The two values are compared, and as they are the same, COL2 and COL3 are compared. Therefore, even though COL2 is supposed to be in descending order, the comparison for the row 400-30 was determined by the values in COL3, which is in ascending order.

COL 1 COL 2 COL 3 COL 4 ===== ===== 200-10 61 61 63 66 400-30 134 189 198 198 300-20 180 180 182 189

This report script, ORDERBY.REP, is available in your\ESSBASE\APP\SAMPLE\ BASIC directory.

Sample 19: Narrowing Member Selection Criteria

The following report demonstrates the use of the LINK command to narrow the members returned in a selection in a report script. For more information, see Developing Report Scripts.

COL 1 COL 2 COL 3 COL 4 ===== ===== 200-10 61 61 63 66 400-30 134 189 198 198 300-20 180 180 182 189

Use the following script to create Sample 19:

<Page (Market)
<Column (Year)
Qtr1 Qtr2
<Row (Product)
<Link (<UDA (product, naturally-flavored) OR <LEV (product, 0))
    !

The LINK command uses the AND, OR, and NOT Boolean operators to refine your search. In the preceding example, the product with the "naturally-flavored" user-defined attribute, as well as all Level 0 products, are returned in the search.

Be careful how you group operators in the LINK expression. Hyperion Essbase evaluates operators from left to right. Use parentheses to group the expressions. For example, A OR B AND C is the same as ((A OR B) AND C). In the first expression, Hyperion Essbase evaluates the expression from left to right, evaluating A OR B before evaluating AND C. In the second expression, Hyperion Essbase evaluates the subexpression in parentheses (A OR B) before the whole expression, producing the same result. However, if you use (A OR (B AND C)), Hyperion Essbase evaluates the subexpression in parentheses (B AND C) before the whole expression, producing a different result.

This report script, LINK.REP, is available in your\ESSBASE\APP\SAMPLE\ BASIC directory.

Sample 20: Using Attributes in Member Selection

This sample report uses members of attribute dimensions to view data on base dimensions that are associated with those attribute dimensions.

Market Measures Scenario Qtr1 Qtr2 ======== ======== 100-10 5,096 5,892 100-20 1,359 1,534 100-30 593 446 200-10 1,697 1,734 200-20 2,963 3,079 200-30 1,153 1,231 200-40 908 986 300-10 2,544 3,231 300-20 690 815 300-30 2,695 2,723 400-10 2,838 2,998 400-20 2,283 2,522 400-30 (116) (84) 100-20 1,359 1,534 200-20 2,963 3,079 300-30 2,695 2,723 Product 24,703 27,107

Use the following script to create Sample 20:

{WIDTH 12}
<Page (Measures, Scenario, Caffeinated, Year, Market)
Profit
Actual
Caffeinated_True
Qtr1
East
<Column (Ounces)
<ICHILDREN Ounces
<Row ("Pkg Type")
<ICHILDREN "Pkg Type"
    !

The report output reflects data on Quarter 1 profits for caffeinated products by all their available sizes and package types. The data values indicate #MISSING when there is no data for a specific size in a specific package type. Because attributes are defined only on sparse dimensions, there are several #MISSING values in the sample report. You can represent missing values by suppressing the row or substituting a replacement text string, such as N/A. See Sample 2: Handling Missing Values for an example of substituting page breaks and labels for missing values.

This report script, ATTR.REP, is available in your\ESSBASE\APP\SAMPLE\ BASIC directory.

Sample 21: Using the WITHATTR Command in Member Selection

This sample report uses the WITHATTR command to view information based on the attributes of the members of a base dimension.

Profit Actual Caffeinated_True Qtr1 East Ounces_32 Ounces_20 Ounces_16 Ounces_12 Ounces =========== =========== =========== =========== =========== Bottle #Missing 488 240 (586) 142 Can #Missing #Missing #Missing 2,776 2,776 Pkg Type #Missing 488 240 2,190 2,918

Use the following script to create Sample 21:

{WIDTH 12}
<Page (Measures, Scenario, Year, Market)
Profit
Actual
Qtr1
East
<Column ("Pkg Type")
<ICHILDREN "Pkg Type"
<Row (Product)
<WITHATTR(Caffeinated,"<>",True) 
<IDESCENDANTS Product
!

The report output reflects data on Quarter 1 profits for caffeinated products by their package types. The data values indicate #MISSING when there is no data for a specific package type. Because attributes are defined only on sparse dimensions, there are several #MISSING values in the sample report.

This report script, WITHATTR.REP, is available in your\ESSBASE\APP\SAMPLE\ BASIC directory.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.