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 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 repeated 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 the \ARBORPATH\App\Demo\Basic directory.