Creating and Redefining Worksheets

This chapter covers the following topics:

Introduction to Worksheets and Views

Within Oracle Demantra, you work almost entirely within worksheets. A worksheet is a customized working environment designed especially for your use. Here you can view and edit data. When you save changes back to the database, they become available to other users and to downstream operations.

A worksheet consists of one or more views, usually displayed as tabs within the worksheet. Each view retrieves a set of data that is aggregated in a specific way and that may also be filtered. The following shows an example:

the picture is described in the document text

Use this tree to select data at a level of aggregation. The view aggregates series data to the specified level. Here, views are shown as tabs within the worksheet. You can also display views as child windows of the worksheet.

Introduction to Levels

The first interesting feature of any worksheet view is the aggregation level or levels that it uses. For example, you might want to view data at the account level, as follows:

the picture is described in the document text

The worksheet might include a dropdown list instead of this tree control. For example:

the picture is described in the document text

In either case, you can view data for any account. For example, for the quarter that started on February 3, 2003, the Demand for the CVS account was 1, 571, 396 units, and the unit price was $9.99. You can edit any data that is shown in white, such as the price and market plan.

In generic terminology, the word member refers to a unit within a level. For example, CVS is a member of the account level.

Level Types

Levels can group and aggregate data in many different ways, and Oracle Demantra uses icons to indicate the type of level:

Item-type levels Item levels group and aggregate data according to characteristics of the items you sell.
Location type levels Location levels group and aggregate data according to characteristics of the locations where you sell. For example, location levels could describe geography or types of stores.
Combination (or matrix) levels Combination (or matrix) levels group and aggregate data according to characteristics of the item-location combinations. These are less common than item and location levels.
Time levels Time levels group and aggregate data by sales date. Normally you use a time level in place of the time axis.
Promotion levels Promotion levels group and aggregate data by sales promotions. Depending on how your system is implemented, you may have a hierarchy of promotional levels (to organize the promotions), and the higher levels might use different icons.
Unlike other kinds of levels, promotion levels can be displayed within a Gantt chart.
Settlement levels Settlement levels, which are used only by DSM. If you use a settlement level in a worksheet, you cannot use levels from any other hierarchy in that worksheet.
Check request levels Check request levels, which are used only by DSM.

In Oracle Demantra, the levels are completely configurable and are easily extended. The levels that you see should make sense to you. Your Oracle Demantra implementors defined levels as required for your organization, and an authorized user can add more.

Level Attributes

A level can have attributes. For example, a Ship To member might have the following attributes:

the picture is described in the document text

Apart from the Name attribute, the attributes shown in red are all parents of this member. They are shown in red because they are required.

The other attributes (such as Unit Cost) do not represent levels in the system but contain information used for reporting and other purposes.

Introduction to Series

Each worksheet shows series data associated with the combination that you have currently selected in the worksheet. These series were created by your implementors, for your organization's needs. A worksheet displays the series data in a table, or in a graph, or both. The following shows an example of a worksheet table:

the picture is described in the document text

The example here shows series at the lowest level, but you can view data for any given series at any aggregation level.

As you can see from this example, there are many possible variations of series:

Kinds of Series

Oracle Demantra supports the following kinds of series:

Series and Summary Rows

Each series has been configured to be summarized in a specific way, which you can see in the summary row of the worksheet table. The table has at least one summary row; depending on the layout, it may also include subtotal rows. The following shows an example:

the picture is described in the document text

As you can see, Oracle Demantra provides different summarizing options. In this example:

More complex options are also available. A given series can be summarized in different ways within a single worksheet table. Consult your implementors for details on how your series are summarized.

Visual Elements of Worksheet Views

In general, a worksheet view can contain the following common visual elements:

the picture is described in the document text

Axes of the View

Each worksheet view has an x-axis and a y-axis.

Members Browser or Combination-Selection Lists

As noted earlier, a worksheet view usually includes either a Members Browser or a set of dropdown menus, which you use to choose the data to display in the rest of the worksheet:

Members Browser

the picture is described in the document text
Selection lists

the picture is described in the document text

In each case, the selected combination is “Low fat products at the BJ account.”

In addition to providing a way to select the data to display, the Members Browser enables you to create, edit, and delete the level members. See Managing Items and Locations. Depending on your implementation, you may also be able to perform additional operations on the members; see Using Other Right-Click Menu Options.

In some cases, the worksheet does not include either of these controls. This means one of following is true:

Worksheet Table

A worksheet table shows series data for the item-location combination that you have currently selected in the view. Depending on how the layout is configured, this may appear as an ordinary table or it may appear as a crosstab; see Levels and Layout Variations.

By default, each row in the table corresponds to a point in time, and each column displays the data for a series. As noted earlier, the table also has a summary row. If the worksheet is in crosstab layout, the table also includes subtotal rows.

The table can show the same series as the graph or a completely different set of series, but usually some of the same series are displayed in both places. To specify where each series is displayed, see Defining the View Layout.

You can edit data in the white cells. The gray cells are computed and not editable. For details on editing data, see Editing Data.

For a given combination and date, the table may display symbols that indicate that a promotion or a note is associated with that combination and date. These indicator symbols are generally displayed for only some of the series, depending on how Oracle Demantra has been configured.

See Viewing Notes.

Graph

A worksheet graph displays data for the current selection. By default, the horizontal axis shows time, and the vertical axis shows one or more series.

Subtabs

A worksheet view can contain multiple subtabs.

See also Defining the View Layout.

Levels and Layout Variations

A worksheet can include multiple different levels or no levels at all (for a completely aggregated view).

When a level is included in a worksheet, that means you can see data associated with each member of that level.

Multiple Levels in a Worksheet View

A worksheet view can include multiple levels, for example:

the picture is described in the document text

The appearance is slightly different if the worksheet includes dropdown lists instead of the Members Browser. For example:

the picture is described in the document text

When a view includes multiple levels, you have a more detailed view of the data. In the earlier example, data was aggregated across all SKUs for a given account. Here, in contrast, you see data aggregated separately for each SKU.

Level Members Displayed in Cross-tab Format

It is also possible to lay out a worksheet so that the members of a level are all displayed at the same time. This format is called a cross-tab layout, and the table (also known as a pivot table) provides a cross tabulation of all the members.

The following figure shows a worksheet table in cross-tab layout, with a row for each SKU member within each time bucket:

the picture is described in the document text

Notice that the Members Browser does not include the SKU level, because all SKUs are displayed at the same time.

For another example, the worksheet could instead display the SKU members across the top of the table rather than down the side, as in the following example:

the picture is described in the document text

Cross-tab Definitions

There are ways that you can choose to display data in a table

You can position these entities on either the x- (columns) or y-axis (rows) of the table:

You can position the summary at the top, bottom, left, or right side of a worksheet

To change the position of the summary row or column:

You can turn off (hide) the display of summaries, for example, if you

You can hide:

The Worksheet Designer does not automatically hide any summaries. You evaluate the worksheet for meaningless and redundant summaries and hide them. It does, however, automatically hide repetitious summaries (summaries that summarize only one entity), for example, a promotion scenario that has only one promotion.

To turn off a summary:

You can freeze a worksheet summary row at the top. It then remains in position when you scroll.

To freeze the worksheet summary row, navigate to View > Freeze Overall Summary.

Introduction to the Worksheet Editor

To create a new worksheet

Click File > New. Or click the New button.

To edit an existing worksheet

  1. Click File > Open. Or click the Open button.

  2. Click a worksheet and click Open.

    The worksheet editor has a set of buttons on the left that you click to open different pages with different purposes:

    Button Purpose For details, see...
    Display Specify basic information about the worksheet; specify how to display the content of this worksheet in Collaborator Workbench. Configuring the Worksheet Basics
    Series Select series to include. Selecting Series on a Worksheet
    Time Specify time resolution of worksheet and span of time to consider. Specifying the Time Resolution
    Aggregation Levels Optionally specify aggregation levels to include. Specifying Aggregation Levels
    Filters Optionally filter the data in the worksheet. Filtering the Worksheet
    Exceptions Optionally apply exception filters to further filter the worksheet. Applying Exception Filters
    Layout Designer Define the layout of the worksheet and its views, including the layout of the worksheet tables, the location of each included series, and the graph format. Defining the View Layout

    Here you have the following options:

    • To move to another page, either click a button on the left side of the page or click Previous or Next.

    • To exit the worksheet editor and keep your changes, click OK.

    • To exit the worksheet editor and discard all changes, click Cancel.

Working with Lists

As you create or edit worksheets, you will often use pages that present two lists of elements, where you specify your selections. To do so, you move elements from the left list to the right list. The left list always presents the available elements (such as the available series) and the right list always shows your selections.

You can move elements from one list to the other in many equivalent ways, summarized here:

Creating or Editing a Worksheet

To create a new worksheet

Click File > New. Or click the New button.

To edit an existing worksheet

  1. Click File > Open. Or click the Open button.

  2. Click a worksheet and click Open.

  3. Click the Worksheet menu and select one of the menu items. Or click one of the worksheet buttons on the toolbar.

  4. Save your changes to the worksheet definition:

    • To save the new definition, click the Save button. Or click File > Save

      Note: In contrast, the Data > Update option saves the data and notes in the worksheet, not the worksheet definition.

    • To save the worksheet with a new name, click the Save As button.

  5. Rerun the worksheet to see your changes (unless you have changed only the layout, which is refreshed automatically). To do so, click Data > Rerun. Or click the Run button.

Configuring the Worksheet Basics

To configure basic information for a worksheet

  1. Click Worksheet > Display. Or click the Display button.

    The system displays a page where you specify the following basic worksheet information:

    the picture is described in the document text

    Name The title to use in the My Worksheets module and in the worksheet window title.
    Description Provides optional information to display in My Worksheets. This is especially useful in public worksheets for explaining the purpose of the worksheet to other users.
    Access Select Private or Public. Private is for your use only. Public worksheets are available to all users in the group.
    Enable Extra Filters This option adds an extra, user-controlled filter to the worksheet. If you select the Enable Extra Filters check box, that means that when you open the worksheet, you can prefilter the data before seeing the worksheet results; this is especially useful for users who use Oracle Demantra Anywhere.
    Normally when you specify aggregation levels to include in the worksheet (as described in Specifying Aggregation Levels), all members of those levels are available in the worksheet.
    If you select the Enable Extra Filters check box, that means that when you run the worksheet, Oracle Demantra first prompts you to select the members to display for each level. For an example, see Viewing Data.
    This option has no effect on content panes.
    Cache Worksheet Data Specifies whether Oracle Demantra should cache the data for this worksheet, for each user who works with it. If you cache a worksheet, it will run more quickly in general, but the cache will need to be refreshed periodically. Choose one of the following refresh options:
    - Manual: You manually refresh the cache. See To refresh your local worksheet cache.
    - Automatic: Oracle Demantra will automatically refresh the cache, as specified by your implementors.
    In either case, Oracle Demantra automatically detects when a cache is out of date and behaves appropriately. Oracle Demantra also automatically refreshes the cache when you make certain structural changes to the worksheet.
    Open With Context Specifies how this worksheet should be filtered when a user opens it through the Open or Open With menu options (on the right-click menu; see Opening an Item or Location in Another Worksheet).
    Choose one of the following options:
    - Selected Member (this will filter the worksheet to the object from which it was launched, aggregating across all combinations associated with that object)
    - Selected Combination (this will filter the worksheet to the combination from which it was launched)

  2. To display the content of this worksheet in Collaborator Workbench, check Content and then complete the following fields:

    Display Format Specifies the display format to use. Not all display formats may be possible, depending on the number of levels and series you include.
    Currently, Collaborator Workbench chooses the color to use for each series, when displaying series in the graph-type content panes.
    Location Select Wide Pane or Narrow Pane to determine where the content pane will be displayed in Collaborator Workbench.
    Top/Bottom Filter (Only for bar chart content panes.) Specifies how to filter the members or combinations for display in the bar chart (to save space, not all members are shown).
    - Use the dropdown menu to specify whether the filter should apply to the top-ranked or to bottom-ranked members.
    - In the input field, specify the number of members to be included.
    - For Criteria Series, select the series that Collaborator Workbench should use to rank the members. (This does not control the order in which they are displayed in the chart.)
    Note: If multiple items have identical values, all of them are displayed, and they collectively count as 1 towards the total. For example, suppose top values were 200, 150, 150, 100, and 50. If you specified Top/Bottom filter as three, you would see a total of four items: both the 150 items, in addition to the 200 and 100 items.
    This option does not affect the worksheet.

  3. To specify how the worksheet table should appear, see Defining the View Layout.

Selecting Series on a Worksheet

Every worksheet must include at least one series.

Note: If you use a settlement level in a worksheet, all series in the worksheet must refer to tables used by the settlement hierarchy.

To specify the series on a worksheet

  1. Click Worksheet > Series. Or click the Series button.

    The system displays the Available Series and Selected Series lists. Each list is a collapsible list of series groups and the series in them.

    You can instruct Worksheet Manager to display the series in a series group either:

    • In alphabetical order

    • By the display order that you set in Business Modeler

    In Business Modeler, set parameter Sort Series by Display Order. See Oracle Demantra Implementation Guide.

    the picture is described in the document text

  2. Move all series that you want into the Selected Series list. To do so, either double-click each series or drag and drop it. You can also move an entire series group from one list to the other in the same way.

  3. Remove any series from the Selected Series list that you do not want to include on the worksheet.

    Note: You cannot remove a series from a worksheet if that worksheet uses it as the Criteria Series for bar chart content. See Viewing Pending Changes to Data.

  4. To change the order in which the series are displayed, see Defining the View Layout.

    See also Introduction to Series.

Managing the Series Lists

You may have a very large number of series, and it can be useful to sort and filter these lists so that you can readily find what you need. The system also provides a search mechanism.

Note: This section applies only to the series page of the worksheet editor (Worksheet > Series). To sort the series as displayed in the worksheet, see To define the layout of a current view.

To sort a list of series

  1. Click the Sort button.

    The Sort dialog box is displayed.

  2. Drag the list name from the Available Columns to the Sort Columns. Or double-click the list name in the Available Columns list.

  3. Click OK.

To filter a list of series

  1. Click the Filter button.

    The Filter page appears.

  2. Click Add.

  3. Click the arrow to the right of the operator box and select an operator from the dropdown list.

  4. In the number box, enter the value by which to filter the list.

  5. (Optional) You can filter further by using the AND relationship.

  6. Click OK.

To find a series

  1. Click the Find button.

    The Find dialog box appears.

  2. In the Find where box, select the name of the list to search.

  3. In the Find what box, type name of the series.

  4. Select Up, Down or All to determine the direction of the search.

  5. (Optional) Select one or more of the check boxes:

    • Whole Word: Search for the exact match of a word.

    • Match Case: Search for the exact match of a word (case sensitive).

  6. Click Find Next to begin (or continue) searching.

Specifying the Time Resolution and Time Span

Each worksheet selects data for a specified span of time and optionally aggregates it in time. You use the Time dialog box to specify the time resolution of the worksheet results and to decide the span of time to which the worksheet applies.

the picture is described in the document text

To specify time criteria

  1. Click Worksheet > Time. Or click the Time button.

  2. In the Time Scale box, specify the time resolution of the worksheet results. The data in the worksheet is aggregated to this time resolution. That is, this option specifies the period of time that each data point in the line graph represents.

  3. In the Time Filter box, specify the time period to which the worksheet applies:

    • Fixed if you always want the worksheet to show a specific time range, regardless of the current date.

    • Relative to Today if you always want the worksheet to show a time range relative to today.

    • Relative to Last Sales Date if you always want the worksheet to show a time range relative to the last sales date in the loaded data.

  4. In the From Date and To Date boxes, enter values depending on the time filter you have chosen, as follows:

    Time Filter Box Action
    Relative From Date/ To Date Specify periods in both From and To with the current (computer) date as the reference point.
    For example: If the Time Scale is Month, and you want to see results starting from six months before today, enter -6 in From Date.
    Fixed From Date Enter a specific date as a starting point of the worksheet results. To enter a date, click the calendar button and select a date.
    Fixed To Date Specify the number of periods you want to include, starting from the From date.

  5. To control how dates are displayed in the worksheet, click the Advanced button, which brings up the following dialog box:

    the picture is described in the document text

    1. In the Format dropdown list, select a display format.

    2. To offset the displayed dates, optionally specify values for Month Offset or Year Offset. For example, to add one month to each displayed date, specify 1 for Month Offset.

      The Preview field shows what the first time bucket in the worksheet would look like with this format and offset.

    3. Click OK.

    Note

    If you change the time scale, the worksheet might not show exactly the same aggregate numbers, because the cutoff points for the worksheet would not necessarily be the same. For example, suppose your worksheet is weekly and displays 48 weeks of data. Then supposed you change the worksheet to display quarterly data. A quarter is 13 weeks, and the original span (48 weeks) is not an integer multiple of 13. So the worksheet selects a different amount of data and shows different overall results.

Specifying Aggregation Levels

A worksheet usually includes aggregation levels. When you use the worksheet, you can examine data for the item-location combinations associated with those levels.

Based on the levels included in a worksheet, Oracle Demantra automatically determines which item-location combinations the worksheet should include. Depending on which combination you select, the worksheet displays series data associated with that combination. For example, if you select one location level (city) and one item level (SKU), the worksheet will contain series data associated with each city-SKU combination. On the other hand, if you select one location level (city) and you do not specify an item level, the worksheet aggregates data for all items. That is, the worksheet will contain series data associated with each city, aggregated for all products.

To specify the aggregation levels in a worksheet

  1. Click Worksheet > Aggregation Levels. Or click the Levels button.

    The system displays the Available Levels and Selected Levels lists.

  2. Move all aggregation levels that you want into the Selected Levels list, using any of the techniques in Working with Lists.

  3. Remove any unwanted levels from the Selected Levels list.

    the picture is described in the document text

    The selected levels will now be used on all views of this worksheet, unless you configure the views otherwise; see To specify which levels to use in a worksheet view.

    The layout of the worksheet view controls the order in which the levels are used; see Defining the View Layout.

    See also:

    • Using the Advanced Selection Options

    • Introduction to Levels

    • Changing the Overall Scale or Unit of Measure

Using the Advanced Selection Options

By default, if a worksheet includes a promotion level, the worksheet includes all the following types of combinations:

The worksheet displays placeholders for combinations that do not have promotions. For example:

the picture is described in the document text

If you move the promotion level to the worksheet axis (see Defining the View Layout), the table will display a similar placeholder.

You can exclude some of these combinations. For example, you might want the worksheet to include only the combinations that have both sales and promotions, as follows:

the picture is described in the document text

To exclude combinations with partial data

  1. Click Worksheet > Aggregation Levels. Or click the Levels button.

  2. Make sure that the worksheet includes at least two levels, one of which should be a promotional level.

    When you do so, the screen displays an Advanced button in the lower right.

    the picture is described in the document text

  3. Click Advanced. Oracle displays a dialog box with additional options.

    the picture is described in the document text

    Include combinations that exist only in Promotion This option selects combinations that have associated promotions, even if they do not have sales data.
    Include combinations that exist only in SALES_DATA This option selects combinations that have sales data, even if they do not have any associated promotions.

  4. To exclude the combinations you do not want to see, select the check boxes as needed.

    See also Specifying Aggregation Levels.

Changing the Overall Scale or Unit of Measure

In addition to levels, series, and filtering, a worksheet has the following characteristics:

To change the overall scaling factor

  1. Click Worksheet > Aggregation Levels. Or click the Levels button.

    The Levels page includes a section where you specify the overall scale of the worksheet, as well as its units of measure.

    the picture is described in the document text

  2. In the Scale Units by box, specify the factor by which all numbers in the worksheet are to be divided (for display purposes).

    For example, if you specify a factor of 1000, the displayed data will divided by 1000. So the number 96,000 will be displayed as 96. The vertical axis of the graph is updated to show the factor in parentheses. For example, if the vertical axis was formerly labeled “units”, it will be updated to say “units (1000)” instead.

To change the unit of measure

  1. Click Worksheet > Aggregation Levels. Or click the Levels button on the toolbar.

  2. In the Unit Type box, select the unit of measure to display in the worksheet results.

    For example, our items are bottles, and suppose that a case that contains six bottles. If you display the worksheet with cases instead, the system will display the number of bottles divided by six.

  3. If the Index box is displayed, choose an index from the dropdown list.

    the picture is described in the document text

    The Index menu lists all the time-dependent indexes and exchange rates that are associated with this unit. Each index or exchange rate is a time-varying factor that the worksheet can use. When you select an index, the worksheet will automatically multiply all monetary series by the factor for each date. For example, if you choose Consumer Price Index (CPI) as the index, the system will calculate all monetary quantities with relation to the CPI.

    Note: These indexes and exchange rates are generally imported from other systems. The set available to you depends upon your implementation.

    See also Specifying Aggregation Levels.

Filtering the Worksheet

Filters control the combinations that you are able to see. Filtering can have multiple sources:

Oracle Demantra automatically combines all the filters. In the preceding example, if the component is not filtered, if you use worksheet X, you can see only data for Brand X at your accounts.

In contrast to an exception filter (Applying Exception Filters), this type of filter is static and behaves the same no matter how the data changes.

To apply a filter to a worksheet

  1. Click Worksheet > Filters. Or click the Filters button.

    The system displays the Available Filter Levels and Selected Filter Levels lists.

    You can instruct Worksheet Manager to display the level members on the filter screen either by:

    • Code

    • Description

    In Business Modeler, set parameter Sort Member by Code in Filter. See Oracle Demantra Implementation Guide.

  2. Find the aggregation level at which you want to filter data and move it from the Available Filter Levels list into the Selected Filter Levels list, using any of the techniques in Working with Lists.

    Note: This level does not have to be the same as any of the aggregation levels you display in the worksheet. In fact, typically you filter using a different level than you use to display.

  3. In the Available Members list, find a member that you want to include in the worksheet and move it into the Selected Members list, using any of the techniques in Working with Lists.

    At this stage, the worksheet includes only data for this member. (Before you applied a filter at this level, the worksheet could theoretically include any member of this level.)

  4. Continue to move members from the Available Members list into the Selected Members list, until the latter list includes all the members you want.

    the picture is described in the document text

To filter data further

Once you have applied a filter, the content pane contains only those members that meet the filter criteria that you specified. To avoid performance issues and database errors, there are system limits on the number of members that the each filter can select and that it can display in the Available Filter Level List. Therefore, the visible list may not include all the members that meet the filter criteria that you specified.

You can further filter the data by using either of these techniques:

When you apply a filter member filter to the results of the first filter, it behaves differently depending on whether the visible list is complete or incomplete.

If the visible list from the first filter is complete, it shows all the members that meet the filter criteria that you specified in the initial filter. The second filter member filter searches on the members in the visible list. For example:

If the visible list from the first filter is incomplete, it does not show all the members that meet the filter criteria that you specified in the initial filter. It was subject to system limits. When you apply a further filter to the visible list, the list may not include all the data that you want to filter to. Therefore, this second filter searches on all the members in the full worksheet. For example:

See also Applying Exception Filters.

Managing the List of Members

Depending on how your system has been configured, it might contain a very large number of members. If so, you might want to sort or filter the list or search it. For information, see Managing the Series Lists.

Applying Exception Filters

If you attach an exception filter to a worksheet, Oracle Demantra checks the values of the worksheet data and displays only the combinations that meet the exception criteria. In contrast to an explicit filter (Filtering the Worksheet), this type of filter is dynamic and can behave differently as the data changes.

Specifically, you define an exception condition that consists of a series, a comparison operator, and a value, for example:

the picture is described in the document text

When you open the worksheet, Oracle Demantra checks each combination in the worksheet. For each combination, if the condition is met for any time in the worksheet date range, Oracle Demantra displays that combination. For example, the worksheet shows combinations that have Sales Plan values greater than or equal to 150000, within the time range included in the worksheet.

Note: Exception filters do not cause filtering in time. If a combination meets the exception criteria, Oracle Demantra displays data for that combination for all time buckets within the time range of the worksheet.

If the condition is not met at any time for any of the worksheet combinations, Oracle Demantra shows the worksheet as empty. That is, if all values in the Sales series are less than 150000 for all combinations, the worksheet comes up empty.

Note: If the worksheet includes a promotion level or a promotion series, the behavior is slightly different. In this case, the Members Browser or dropdown list does initially show all combinations. When you click a combination to display it, the worksheet then checks for exceptions.

You can apply multiple exceptions to a worksheet. When you apply multiple exceptions, you can relate them to each other by using logical AND or logical OR relationships. For example:

the picture is described in the document text

To apply an exception filter

  1. Click Worksheet > Exceptions. Or click the Exceptions button.

    The Exceptions Filter page appears.

  2. Click Add.

  3. In the first box in the new row, select a series from the dropdown list.

    Note: Typically only some series are available for exceptions. If you do not see a series you need, contact your Oracle Demantra administrator or your implementors.

  4. In the second box, select an operator from the dropdown list.

  5. In the third box, type or choose a value.

    the picture is described in the document text

    • For a numeric series, type a number.

    • For a dropdown series, choose one of the allowed values of this series.

    • For a string-type series, type any string. You can use the percent character (%) as a wildcard.

    • For a date-type series, type a date or use the calendar control to choose a date.

    • For a percentage, use decimals. For example, substitute 1.2 for 120% or 0.35 for 35%.

  6. (Optional) You can apply additional exceptions. Click the AND or the OR radio button to specify the relationship between the exceptions.

    the picture is described in the document text

To delete an exception filter

Click the exception and then click Delete.

See also:

Defining the View Layout

To define the layout of a current view

  1. Click Worksheet > Layout Designer. Or click the Layout Designer button.

    The system displays a page where you specify the layout. This page displays the following areas:

    the picture is described in the document text

    In addition, this screen displays the following icons:

    • An icon for each aggregation level that you have included in the worksheet. By default, these levels are included in the Members Browser or selector lists.

    • An icon that represents the time axis. By default, time is shown on the x-axis.

    • An icon that represents the series data. Series are shown on the y-axis.

  2. To change the worksheet layout, drag the level or time axis icons to the appropriate areas. You cannot move the series icon.

  3. To specify the type of graph to use, select a graph type from the Graph Type dropdown list.

  4. To specify how to display series in this view:

    1. Click the Sort button.

      The Layout Designer displays a page that shows the order in which this view currently displays the series. The default order of the series is the order that you added the series.

      the picture is described in the document text

    2. To hide a series in this view, click the None option in the dropdown list to the right of the series name.

    3. Otherwise, to specify where to display the data for a series, select one of the following options: Table, Graph, Table and Graph.

    4. To move a series up or down in this list, click the series name and drag it up or down. If you want to restore the series to their original order, click the Reset layout button.

    5. When you are done, click Close.

  5. Click Save.

  6. Rerun the worksheet to see your changes. To do so, click Data > Rerun.

To specify a crosstab layout

Drag one or more level icons from the Page Item area to x-axis or y-axis areas.

To specify which levels to use in a worksheet view

By default, all levels you include in a worksheet are used on all views of the worksheet. Within a multi-view worksheet, you often hide some of the levels in some views, so that each view is aggregated differently.

  1. Right-click within the Page area of the Layout Designer.

    The system displays a menu like the following:

    the picture is described in the document text

  2. Click Hide level and then click the name of the level to hide.

    When you hide a level, the worksheet automatically aggregates data across members of that level.

    Note: Do not use this option to hide the time axis.

To revert to the default layout of a worksheet view

  1. Click Worksheet > Layout Designer. Or click the Layout Designer button.

  2. Click the tab corresponding to the worksheet view you want to reset.

  3. Click the Reset button.

    In the default layout, all selected levels are visible and are on the X axis. Also, all series are displayed in the graph and table according to their default definitions.

See also:

Adding and Managing Worksheet Views

A worksheet can include multiple views, each of which can have a different set of series and a different layout.

Note: If you display the worksheet views as tabs, note that Oracle Demantra does not provide a way to change the order in which the tabs are displayed. The tabs are displayed in the order in which you create them.

To add a worksheet view

  1. Within the Layout Designer, click the Add Worksheet View button.

  2. In the popup dialog box, type the name of the new view.

  3. Click OK.

To control synchronization between the views

The views of a worksheet may or may not be synchronized with each other. If they are synchronized, when you edit in one view, that change automatically appears in the other views. Because this can affect performance, sometimes it is best to switch off this synchronization.

Within the Layout Designer, click one of the following buttons, whichever is currently displayed:

To rename a worksheet view

  1. Within the Layout Designer, click the Rename Worksheet View button.

  2. In the popup dialog box, type the new name of the view.

  3. Click OK.

To enable or disable a worksheet view

  1. Within the Layout Designer, click the Hide/Display button.

  2. The Layout Designer displays a popup list of all the views associated with this worksheet. A check mark is displayed next to each view that can currently be displayed.

    the picture is described in the document text

  3. For the view interest, select the check box next to the name of the view.

  4. Click elsewhere on the screen to close the list of views.

To delete a worksheet view

Within the Layout Designer, do one of the following:

See also:

Specifying the Worksheet Elements in a View

For each worksheet view, you can specify which of the basic worksheet elements are included: the table, the graph, and so on.

To specify the elements to include in a worksheet view:

  1. Click Worksheet > Layout Designer. Or click the Layout Designer button.

  2. Click the tab corresponding to the worksheet view you want to modify.

  3. Click Advanced... in the lower right.

    Oracle Demantra displays the following screen:

    the picture is described in the document text

  4. For Combination Selector, click either Tree (to display a Members Browser) or Dropdowns (to display dropdown menus instead).

  5. Select the check box next to each element you want to include in this view of the worksheet.

  6. Click OK.

Adding an Embedded Worksheet

You can display one or more associated worksheets embedded within a subtab. When you select a member in the view, the embedded worksheet is filtered to show just that member. You typically use an embedded worksheet to drill into further detail.

Note: If you use the keyboard to move through the worksheet table, the subtabs are not automatically refreshed, for performance reasons.

To add an embedded worksheets

  1. Click Worksheet > Layout Designer. Or click the Layout Designer button.

  2. Click the tab corresponding to the worksheet view to which you want to add the embedded worksheet.

  3. Click Advanced... in the lower right.

    Oracle Demantra displays the Advanced screen.

  4. Click the Add WS As Subtabs tab.

    Oracle Demantra displays a screen like the following:

    the picture is described in the document text

    Depending on the level that you select, the bottom part of the screen shows different worksheets that you can add as an embedded worksheet to this worksheet view.

  5. For Selected Levels, select the level that is associated with the worksheet you want. In general, a worksheet is associated with the levels where it makes sense to use it; this is controlled by your system configuration. You can choose any of the levels that are used in this worksheet.

  6. For Selected Worksheets, select the worksheets that you want to display as embedded worksheets.

  7. Click OK.

Filtering a Worksheet View

For each worksheet view, you can filter the view to show a subset of the data in the worksheet.

To filter a worksheet view

  1. Click Worksheet > Layout Designer. Or click the Layout Designer button.

  2. Click the tab corresponding to the worksheet view you want to filter.

  3. Click Advanced... in the lower right.

    Oracle Demantra displays the Advanced screen.

  4. Click the Filter By Level Members tab.

    Oracle Demantra displays a screen like the following:

    the picture is described in the document text

  5. For Selected Filter Levels, select the level by which you want to filter this worksheet view. You can choose any of the levels that are used in this worksheet.

  6. For Selected Members, select the level members whose data should be displayed in this worksheet view.

  7. Click OK.

Sharing Worksheets

In general, any worksheet is one of the following:

In either case, the original creator of a worksheet owns it and only that person can change it.

When you do share worksheets, however, you should consider data security. Oracle Demantra automatically prevents any user from seeing data for which he or she does not have permissions. If you build a worksheet with data that other users do not have permissions to view, then those users will see an empty worksheet. Similarly, if a user has partial permissions for the data, then the worksheet will open with only those results that are permitted.

See also Configuring the Worksheet Basics.

Deleting Worksheets

You can delete a worksheet if you are its owner.

To delete a worksheet

  1. Open the worksheet.

  2. Click File > Delete. Or click the Delete button.

    Oracle Demantra prompts you to confirm the deletion.

  3. Click Yes or No.

General Tips on Worksheet Design

For performance reasons, don't select too much data to view, unless there is no other choice.

If you receive a message saying “out of memory,” try the following techniques to reduce the amount of memory that your worksheet selects:

Also see the Oracle Demantra Implementation Guide.

If you do need to select a large amount of data, use the levels to your advantage. Specifically, use the levels in the Members Browser or selector lists rather than moving them to a worksheet axis. If levels are in the Members Browser or selector lists, each combination in the worksheet is relatively smaller and will load more quickly.

If you do not plan on working with the Activity Browser, you can switch off the Auto Sync option on the toolbar, and you can also hide the Activity Browser and Gantt chart.

Remember that you can filter the worksheet by any level, including levels that are not shown in the worksheet. For example, you might want to see data at the region level, but exclude any data that does not apply to the Acme territory. To do this, you would filter the worksheet to include only the Acme member of the Territory level, but you would select data at the Region level.

A multi-view worksheet is useful in the following cases:

Viewing the Definition of a Worksheet

To view the definition of a worksheet

  1. Open the worksheet.

  2. Click Data > Info. Or click the Info button.

    Oracle Demantra displays a dialog box that summarizes the definition of a worksheet, for example:

    the picture is described in the document text