This chapter covers the following topics:
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:
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.
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 worksheet might include a dropdown list instead of this tree control. For example:
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.
Levels can group and aggregate data in many different ways, and Oracle Demantra uses icons to indicate the type of level:
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.
A level can have attributes. For example, a Ship To member might have the following attributes:
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.
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 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:
Some series are editable (see Stat Frcst), some are editable only for specific dates (see Pseudo), and some are not editable at all. Generally, editable series are used as input to other series.
Some series are shown in different colors, depending on the data values. For example, Sales Fcst Bias is displayed in red for any values less than zero.
Some series are calculated from other series.
Some are generated by the Analytical Engine, if that is relevant in your Oracle Demantra solution.
Most series have time-varying values for each combination, but there are other kinds of series as well (discussed below). For example, the series Stat Frcst specifies whether a combination should be used in forecasting or not. As you can see, this series has the same value for all time buckets for a given combination.
Most series have numeric values, but some have string or date values. For some series, you choose a value from a dropdown list.
Oracle Demantra supports the following kinds of series:
Sales series contain time-dependent set of data for item-location combinations. That is, each data point in the series corresponds to a given item-location combination at a given point in time. This type of series is the most common type by far.
Combination series (also called matrix series) store time-independent data for each item-location combination. That is, each data point in the series corresponds to a given item-location combination. You use combination series to store and maintain information about item-location combinations, mainly flags for the Analytical Engine to use.
Promotion series store time-dependent data associated with each promotion, at each item-location combination.
Level series store data associated with a specific level. Each data point in the series corresponds to a given member of that level.
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:
As you can see, Oracle Demantra provides different summarizing options. In this example:
Price and Discount are summarized by averaging.
Revenue is summarized by adding all the values.
The Approved series is summarized by counting the number of approvals.
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.
In general, a worksheet view can contain the following common visual elements:
Each worksheet view has an x-axis and a y-axis.
In the graph, the x-axis is shown horizontally and the y-axis is shown vertically.
In the table, the x-axis is displayed vertically and the y-axis is displayed horizontally. (This way, the x-axis displays the same data in the table and in the graph.)
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 | |
Selection lists |
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:
The worksheet table is in crosstab format, which displays data for multiple items or multiple locations. See Levels and Layout Variations.
The data in the worksheet is completely aggregated. It may be filtered, however, so it is not necessarily aggregated across all items and locations.
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.
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.
A worksheet view can contain multiple subtabs.
The Notes/Attachments subtab displays notes and attachments related to the selected combination. See Viewing Notes.
The Activity Details subtab displays promotions and the promotion hierarchy. On the left, the Activity Browser displays an expandable tree view of the promotions associated with the currently selected combination. On the right, the Gantt chart displays the promotions associated with the currently selected combination. You can scroll backwards and forwards in time to see the start and end dates of each promotion, and you can zoom in and out in time.
Here you can create, edit, and delete promotions and higher levels in the promotion hierarchy. See the Oracle Demantra Trade Promotion Planning User's Guide.
A worksheet can include a subtab that contains a related worksheet. When you select a combination in the worksheet table, the related worksheet shows data associated with that combination. This related worksheet potentially includes different series than the rest of the worksheet and may also be filtered further.
See also Defining the View Layout.
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.
A worksheet view can include multiple levels, for example:
The appearance is slightly different if the worksheet includes dropdown lists instead of the Members Browser. For example:
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.
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:
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:
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:
Level
Time
Series: For example, if you want to compare two SKUs and their forecasts side by side, you can arrange the worksheet to display all series as rows and all levels as columns.
You can position the summary at the top, bottom, left, or right side of a worksheet
If there are many rows in a table, you can place the horizontal worksheet summary at the top of the worksheet and place the horizontal level summary at the top of the worksheet, The summary can be at top or bottom regardless of how many rows display.
If there are column summaries in a table, you can place the vertical worksheet summary at the left of the worksheet, and place the vertical level summaries at the left of the levels. When series display as rows, you can display the summary row either as the first (at the left) or the last column (at the right) in the table.
You can place a combination of horizontal and vertical summaries in the same worksheet.
You do not see a value on the worksheet in cells where horizontal and vertical summaries intersect.
To change the position of the summary row or column:
In the Worksheet Designer, open the Layout region.
Right-click on a level or series.
Select Left/Right, or Top/Bottom.
You can turn off (hide) the display of summaries, for example, if you
Want to use a separate view for rollups
Have data that does not summarize meaningfully
Want to save space on your screen
You can hide:
Level and worksheet summaries
Level summaries but display worksheet summaries
Worksheet summaries but display level summaries
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:
In the Worksheet Designer, open the View' region.
Right-click a level or series and deselect Show 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.
Click File > New. Or click the New button.
To edit an existing worksheet:
Click File > Open. Or click the Open button.
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 the Demantra Local Application. | 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.
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:
To move all elements from one list to the other, click one of the double arrow buttons, as appropriate.
To move a single element from one list to the other, click the element and then click one of the single arrow buttons, as appropriate. Or double-click the element.
To move several adjacent elements, click the first element, press Shift and click the last element. Then click one of the single arrow buttons, as appropriate.
To move several elements that are not adjacent, press Ctrl and click each element you want. Then click one of the single arrow buttons, as appropriate.
Click File > New. Or click the New button.
To edit an existing worksheet
Click File > Open. Or click the Open button.
Click a worksheet and click Open.
Click the Worksheet menu and select one of the menu items. Or click one of the worksheet buttons on the toolbar.
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.
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.
To configure basic information for a worksheet:
Click Worksheet > Display. Or click the Display button.
The system displays a page where you specify the following basic worksheet information:
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. See Sharing Worksheets and Content Panes.. |
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:
|
To display the content of this worksheet in the Demantra Local Application, check Content and then complete the following fields:
To specify how the worksheet table should appear, see Defining the View Layout.
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
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.
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.
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.
To change the order in which the series are displayed, see Defining the View Layout.
See also Introduction to Series.
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
Click the Sort button.
The Sort dialog box is displayed.
Drag the list name from the Available Columns to the Sort Columns. Or double-click the list name in the Available Columns list.
Click OK.
To filter a list of series
Click the Filter button.
The Filter page appears.
Click Add.
Click the arrow to the right of the operator box and select an operator from the dropdown list.
In the number box, enter the value by which to filter the list.
(Optional) You can filter further by using the AND relationship.
Click OK.
To find a series
Click the Find button.
The Find dialog box appears.
In the Find where box, select the name of the list to search.
In the Find what box, type name of the series.
Select Up, Down or All to determine the direction of the search.
(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).
Click Find Next to begin (or continue) searching.
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.
To specify time criteria
Click Worksheet > Time. Or click the Time button.
In the Time Scaling section, 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.
In the Time Window section, 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.
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. |
To control how dates are displayed in the worksheet, click the Advanced button, which brings up the following dialog box:
In the Format dropdown list, select a display format.
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.
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.
Related Topics
It is also possible to define additional date formats if required. For details see Adding a New Date Format in Oracle Demantra Implementaton Guide
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.
If you do not specify any aggregation levels in a worksheet, the data is completely aggregated across all items and locations.
If you use a settlement level in a worksheet, you cannot use levels from any other hierarchy in that worksheet.
To specify the aggregation levels in a worksheet
Click Worksheet > Aggregation Levels. Or click the Levels button.
The system displays the Available Levels and Selected Levels lists.
Move all aggregation levels that you want into the Selected Levels list, using any of the techniques in Working with Lists.
Remove any unwanted levels from the Selected Levels list.
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
By default, if a worksheet includes a promotion level, the worksheet includes all the following types of combinations:
Combinations that have both sales data and promotions
Combinations that have sales data, but no promotions
Combinations that have promotions, but no sales data
The worksheet displays placeholders for combinations that do not have promotions. For example:
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:
To exclude combinations with partial data
Click Worksheet > Aggregation Levels. Or click the Levels button.
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.
Click Advanced. Oracle displays a dialog box with additional options.
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. |
To exclude the combinations you do not want to see, select the check boxes as needed.
See also Specifying Aggregation Levels.
In addition to levels, series, and filtering, a worksheet has the following characteristics:
A single unit of measure. Typically, most series refer to this unit of measure, but there are exceptions such as percentage values. You can switch the unit of measure, and the displayed values are changed accordingly. The units in your system depend upon your implementation but probably include unit count and dollars.
For monetary units, you can also switch to a different index (such as the Consumer Price Index or CPI) or exchange rate, and the worksheet automatically multiplies all values accordingly.
An overall scale, which defaults to 1. If the displayed values are all large, it can be useful to re-scale the worksheet, for example, to display in amounts of 1000 or 1000000.
You can change either or both of these. When you make this change, the displayed values for most or all of the series in the worksheet are changed.
Note: This change affects only the series that are scaled. Not all series are scaled. For example, a series defined as a percentage is probably not scaled.
To change the overall scaling factor
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.
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
Click Worksheet > Aggregation Levels. Or click the Levels button on the toolbar.
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.
If the Index box is displayed, choose an index from the dropdown list.
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.
Filters control the combinations that you are able to see. Filtering can have multiple sources:
A given worksheet may be filtered. For example, worksheet X might show only Brand X, which means that the worksheet would show only combinations related to Brand X.
Your user ID may be filtered. For example, if you are an account manager, your user ID might give you access only to your accounts. At any level, you would not be able to see combinations associated with other accounts.
The data that you share with other users (called the component) might also be filtered. Components divide the data for different sets of users.
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
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.
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.
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.)
Continue to move members from the Available Members list into the Selected Members list, until the latter list includes all the members you want.
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:
Filter member search: You search for a specific member in the visible list and the specific member is highlighted.
Filter member filter: You apply another filter to the visible list to reduce it further.
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:
There is a system limit of 1000 members to display in the visible list
You have a worksheet with 10,000 members.
You apply a filter at the SKU level and 1000 of the members meet the filter criteria. The visible list displays the 1000 members; the visible list is complete.
You apply a filter member filter for which there are 200 matches in the worksheet using both filters.
This second filter searches the complete visible list that you retrieved with the first filter and displays the 200 matching members.
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:
There is a system limit of 1000 members to display in the visible list
You have a worksheet with 10,000 members.
You apply a filter at the SKU level and 2000 of the members meet the filter criteria. The visible list displays 1000 of the 2000 members because of the system limit; the visible list is incomplete.
You apply a filter member filter for which there are 200 matches in the worksheet using both filters. However the incomplete visible list only contains 37 members that would match in the worksheet using both filters.
If this second filter searched on the visible list, it would only display 37 matching members. Instead, it applies both filters to all the members in the full worksheet and displays the 200 matching members.
See also Applying Exception Filters.
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.
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:
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:
To apply an exception filter
Click Worksheet > Exceptions. Or click the Exceptions button.
The Exceptions Filter page appears.
Click Add.
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.
In the second box, select an operator from the dropdown list.
In the third box, type or choose a value.
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%.
(Optional) You can apply additional exceptions. Click the AND or the OR radio button to specify the relationship between the exceptions.
To delete an exception filter
Click the exception and then click Delete.
See also:
Filtering the Worksheet
Introduction to Series
To define the layout of a current view
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:
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.
To change the worksheet layout, drag the level or time axis icons to the appropriate areas. You cannot move the series icon.
To specify the type of graph to use, select a graph type from the Graph Type dropdown list.
To specify how to display series in this view:
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.
To hide a series in this view, click the None option in the dropdown list to the right of the series name.
Otherwise, to specify where to display the data for a series, select one of the following options: Table, Graph, Table and Graph.
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.
When you are done, click Close.
Click Save.
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.
Right-click within the Page area of the Layout Designer.
The system displays a menu like the following:
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
Click Worksheet > Layout Designer. Or click the Layout Designer button.
Click the tab corresponding to the worksheet view you want to reset.
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:
Visual Elements of Worksheet Views
Levels and Layout Variables
Specifying the Worksheet Elements in a View
Adding an Embedded Worksheet
Filtering a Worksheet View
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
Within the Layout Designer, click the Add Worksheet View button.
In the popup dialog box, type the name of the new view.
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:
Do not force synchronization between views
Synchronize data between views
To rename a worksheet view
Within the Layout Designer, click the Rename Worksheet View button.
In the popup dialog box, type the new name of the view.
Click OK.
To enable or disable a worksheet view
Within the Layout Designer, click the Hide/Display button.
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.
For the view interest, select the check box next to the name of the view.
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:
Click the tab that corresponds to the worksheet view. Then click the Delete Worksheet View button.
Click the Delete All Worksheet View button. Then, at the prompt, click Yes.
See also:
Defining the View Layout
Specifying the Worksheet Elements in a View
Adding an Embedded Worksheet
Filtering a Worksheet 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:
Click Worksheet > Layout Designer. Or click the Layout Designer button.
Click the tab corresponding to the worksheet view you want to modify.
Click Advanced... in the lower right.
Oracle Demantra displays the following screen:
For Combination Selector, click either Tree (to display a Members Browser) or Dropdowns (to display dropdown menus instead).
Select the check box next to each element you want to include in this view of the worksheet.
Click OK.
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.
For more information, see Level and Worksheet Association: Embedded Worksheets in Oracle Demantra Implementation Guide.
To add an embedded worksheets
Click Worksheet > Layout Designer. Or click the Layout Designer button.
Click the tab corresponding to the worksheet view to which you want to add the embedded worksheet.
Click Advanced... in the lower right.
Oracle Demantra displays the Advanced screen.
Click the Add WS As Subtabs tab.
Oracle Demantra displays a screen like the following:
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.
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.
For Selected Worksheets, select the worksheets that you want to display as embedded worksheets.
Click OK.
For each worksheet view, you can filter the view to show a subset of the data in the worksheet.
To filter a worksheet view
Click Worksheet > Layout Designer. Or click the Layout Designer button.
Click the tab corresponding to the worksheet view you want to filter.
Click Advanced... in the lower right.
Oracle Demantra displays the Advanced screen.
Click the Filter By Level Members tab.
Oracle Demantra displays a screen like the following:
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.
For Selected Members, select the level members whose data should be displayed in this worksheet view.
Click OK.
A worksheet or a content pane is either:
Private: Available only to the user who defined it.
Public: Available to the user who defined it and users in the same component as the user who defined it. For example, if you define a public worksheet or content pane in the DM component, all DM component users can view that worksheet or content pane.
In either case, the creator of a worksheet or content pane owns it and:
Anyone can change the displayed data and the layout. They can save those changes to a new worksheet or content pane (Save as... ).
Only the creator can save changes to the worksheet or content pane.
Oracle Demantra prevents users from seeing data for which they do not have permission. See Security chapter inOracle Demantra Implementation Guide..
For:
Worksheets: See also Configuring the Worksheet Basics.
Content panes: See also Configuring the Basics.
You can delete a worksheet if you are its owner.
To delete a worksheet
Open the worksheet.
Click File > Delete. Or click the Delete button.
Oracle Demantra prompts you to confirm the deletion.
Click Yes or No.
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:
Remove series if possible
Reduce the span of time
Apply filters
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:
If you need to edit data at one aggregation level and see easily how that affects higher aggregation levels.
If you need to display a large number of series without having to scroll to see each one.
To view the definition of a worksheet
Open the worksheet.
Click Data > Info. Or click the Info button.
Oracle Demantra displays a dialog box that summarizes the definition of a worksheet, for example: