One frequent requirement of budgeting and planning applications is to send worksheets to various functional areas of an organization. After the worksheets have been distributed, the recipients can review the contents, make modifications, and send updates back to the distributor. Using the Essbase Cascade feature, you can create multiple worksheet files based on a single database view. You can specify at what level of detail you want to replicate the worksheets to tailor the information to each recipient’s needs.
The Sample Basic database contains data for beverage products sold in different states across the U.S. For example, assume that you want all product managers to review and respond to a proposed budget and to return their changes to the finance department. You must create a worksheet for each combination of budget and P&L data to distribute to the product managers for their respective products.
➤ To create this set of worksheets:
1 Select File, then Open.
2 From HYPERION_HOME/products/Essbase/EssbaseClient/client/sample, open P&l.xls.
This file contains the data that you need to replicate for each worksheet.
3 Select Essbase, then Retrieve.
Notice that the retrieval uses the Use Aliases option, which is already set for this file in the Essbase Options dialog box. In this example, product 200 changes to Root Beer, which is its preassigned alias.
4 Select Central (in cell B1) and Root Beer (in cell B2) as the members to be represented in the resulting worksheets.
5 Select Essbase, then Cascade.
The Essbase Cascade Options dialog box is displayed.
6 Select Cascade Information.
The Cascade Information page contains the list of members that you selected and the options for specifying the level at which the selected members are retrieved into the cascaded worksheets. For more information, see Oracle Essbase Spreadsheet Add-in Online Help.
7 In Member, select Central.
8 In Choose Level for Selected Member, select Same level.
9 In Member, select Root Beer, and select Next level (the default setting) (see Figure 158).
Figure 158 Cascade Information Page
The replicated, or cascaded, spreadsheet reports now provide data for members at the same level as Central (East, West, and South) and for members at the level below Root Beer (Old Fashioned, Diet Root Beer, Sarsaparilla, and Birch Beer).
10 Select Destination Options.
11 In Destination Directory, enter C:/temp as the name of the directory in which to store the cascaded worksheets.
Click Browse to select a destination directory from the Browse dialog box.
12 In Destination Types, select Separate Workbooks (the default setting), to create separate spreadsheet files for each cascaded worksheet.
You can also choose to create only one workbook with separate worksheets for each cascaded report, or you can choose to send the cascaded reports to the printer.
You can also select the Open Created Files option to open each cascaded file in the spreadsheet as it is created.
Caution!
Depending on the number of replicated worksheets that you want to create, the Cascade command can create more worksheets than can be stored in the memory of your computer. Therefore, the Open Created Files option should not be used when you are replicating large numbers of worksheets.
14 In Naming Information , for Prefix, enter BUD.
The completed Destination Options page should match Figure 112.
Figure 159 Destination Options Page
When you assign a prefix or suffix in the Naming Information group box, the worksheet files that are generated as a result of executing the Cascade command are named with the prefix or suffix that you specify. The default is to generate worksheet names that are numbered 1 through n, where n is the total number of worksheets created.
The syntax for the file names is PrefixnSuffix.xls for Excel. If you do not specify a prefix or suffix, Essbase creates the worksheets 1.xls, 2.xls, and so on. If you are creating a single workbook, the same naming convention is used for the worksheet names within the workbook.
Caution!
Do not specify a prefix and suffix combination that leaves no characters free for Essbase to create unique file names. If file names are duplicated, Essbase overwrites the duplicate file name with the last cascaded worksheet.
15 Click Format Options(see Figure 165).
Figure 160 Format Options Page
16 Select Copy Formatting to copy the formatting of the source worksheet into each cascaded worksheet. Copy formatting copies only the visual cues set using Essbase and the cell formatting that you set using the worksheet. It does not copy formulas, column formatting, worksheet formatting, or graphs.
17 In Header and Footer, specify a header or footer name to be used for all of the cascaded worksheets.
18 In Sheet Formatting, select Suppress Missing Rows so that rows containing only #Missing values are not replicated.
19 In Table of Contents, select Include Table of Contents .
This creates a Table of Contents text file that lists all replicated worksheets, their creation dates, and their member content. By default, Essbase names the Table of Contents file with the extension .lst.
20 Click OK to create the cascaded worksheets.
Essbase rapidly creates the cascaded worksheets. As each worksheet is created, it is automatically saved, closed, and logged in the Table of Contents. Each individual file is saved in the directory that you specified, named Bud1.xls through Bud10.xls. When the Cascade is completed, Essbase returns you to the original worksheet view (that is, the source file).
21 Using a text editing application, open the Table of Contents file from the destination directory that you specified earlier. This file is named BUD0.LST and contains a list of all cascaded worksheets (see Figure 161).
Figure 161 Table of Contents File for Cascaded Worksheets
22 Select File, then Close to close the worksheet. You do not need to save the worksheet.
➤ To create multiple worksheet files based on the attributes of a product:
1 Enter the attribute names in the top row of the worksheet.
2 Select the attribute names and select Essbase, then Cascade. Proceed as previously described.