5 Workspaces
When you use an RPASCE solution, you can interact with the solution through a personal data repository called a workspace. A workspace contains a segment of the data (and metadata) from the domain, and its scope is constrained by the access rights available to you. Workspaces are stored on the RPASCE server and can be built using an online wizard process or using an automatic batch process.
Pivot Table
When you are working with a workspace, the pivot table is the main area that displays the data in rows and columns.
Figure 5-1 Pivot Table
Sorting and Filtering
Sorting and finding data is essential when working with workspaces that contain thousands of items and hundreds of locations across calendar periods. Being able to put this data in a logical order or find a specific piece of information is what makes planning possible.
Sorting
You can sort positions in a level by using the arrows that appear on column headers or by right-clicking on a cell to open the context menu. The positions are sorted based on the values of a measure's slice for that level.
The sorting occurs along a single measure, using only a single level in the sort. The sorting is limited to the current view, so you can see the same data sorted differently in different views. Sorting is only available in the pivot table, not the graph view.
You can also sort by right-clicking the required column header and selecting the option Sort Ascending or Sort Descending from the contextual menu.
Note:
A slice is valid if it involves only one measure and if it has a unique value for each position along the level being sorted (that is, one position along all other dimensions in the measure's intersection has been selected). Performing a dimension move or swap resets the sort.
To sort by the column heading, use the mouse pointer to hover over a column header of the desired valid slice of measure data to enable the sort arrow and click the arrow.
Figure 5-2 Sort by Column Heading
Once you click the sort arrow, the selected positions are sorted according to the measure's values in the selected slice. The arrow sorts in ascending order the first time you click it, then in descending order on the next click; it switches back and forth for each new click. The arrow displayed in the column header shows the current sort order.
An ascending sort orders the data so that the lowest number appears at the top of the list and the highest number appears at the bottom of the list.
Figure 5-3 Sort by Ascending Order
A descending sort orders the data so that the highest number appears at the top of the list and the lowest number appears at the bottom of the list.
Figure 5-4 Sort by Descending Order
Clear Sort
Right-click any column header and select Clear Sort from the contextual menu. Clear Sort removes the simple sort in effect, which reverts all the columns to the attribute/label/default position sort ordering, as defined using Edit View.
Figure 5-5 Before Clear Sort
Figure 5-6 After Clear Sort
Selecting a Position
In the z axis, you can use the Select Position button to find a position instead of scrolling to the desired position.
Click the dimension tile you want to be active for the Select Position action. In this example, you can choose from Location or Product. Whichever dimension is selected, Select Position will open for that dimension.
Figure 5-7 Active Dimension (Product)
Using the Z-axis
The z-axis appears at the top of the View area and displays the dimensions on the z-axis according to its current position in the dimension. You can scroll through the positions on the z- axis to move through the data you are viewing in the x and y-axes in the pivot table. If more than one dimension is on the z-axis, you can select the active dimension to scroll through.
In this example, Product and Location are on the z-axis, and Location is the active dimension, so the current Location position is displayed.
Figure 5-8 Location (Active Dimension) on Z-axis
Select the dimension that you want to make active and scroll through that dimension only. In the example shown in Figure 5-9, the Product dimension is selected to make it active; the current product position is displayed. Click Next to advance to the next product; the location position will not change.
Figure 5-9 Select Product on Z-axis
When you open a workspace, the All Dimensions button is selected by default, as shown in Figure 5-10.
Figure 5-10 All Dimensions Active
If you select multiple levels of the dimension, all the selected levels for all the dimensions on the z-axis will be visible, as shown in Figure 5-11. You can differentiate the lowest level tile for each dimension.
Figure 5-11 All Dimensions Active: Multiple Levels Selected
If you select any other dimension of the z-axis, the All Dimensions button becomes unavailable. If there are no dimensions or only one dimension on the z-axis, then the All Dimensions button is not visible, as shown in Figure 5-12.
Figure 5-12 No Multiple Levels on Z-axis: All Dimensions Button is Unavailable
![No Dimensions on Z Axis: All Dimensions Button Not Visible No Dimensions on Z Axis: All Dimensions Button Not Visible](img/Figure5-12ButtonNotVisible.png)
If you move between steps in the workspace, the All Dimension selection for that view will persist for the current session. If you select All Dimension in one view when Synchronize Z-axis is on, then it will be selected in all the views in the workspace.
Using Find
Use the find feature to search for words, partial words, or phrases within the row headers and column headers of the visible views containing pivot tables. The search does not include the data cells within the view. The search does not include positions or measures on the z-axis.
The find feature locates the phrase you are looking for, and the color of the phrase changes to pale blue. If the matched position is not visible because it is hidden under a scroll bar, the view is automatically scrolled to reveal it. The find does not match collapsed or hidden positions.
The find feature can be accessible in the following ways:
-
The Find option in the Edit sub-menu of the Quick Access Toolbar menu
-
The shortcut Ctrl + F from a selected pivot table cell
-
The Find button on the Quick Access Toolbar
Using Find with the Quick Access Toolbar Menu
To use Find with the Quick Access Toolbar menu:
-
Click Menu to slide in the Main Menu.
-
Click Edit.
-
Click Find.
Figure 5-13 Using Find with the Quick Access Toolbar Menu
-
The Find panel replaces the content area of the Main Menu.
Figure 5-14 Find Panel
-
Enter the following criteria:
-
Enter the characters to search for in the Find input field.
-
If more than one view is visible, select a specific view in which to perform the search. By default, the most recently selected view is chosen, but an All Open Views option is available from the View list. If All open Views is chosen, the views are searched from upper left to lower right.
-
Narrow the search to a particular dimension using the Dimension list. The default is to search all dimensions. Only dimensions on the x-axis or y-axis are listed.
-
You can search attributes to find the relevant positions by using the Attribute List. The attributes drop-down list has the list attributes related to the dimensions selected in the dimension drop-down list.
-
Select Match Case to make the search case sensitive. Leave it clear if you want the search to find all text that matches your text, regardless of case.
-
Select Whole Word to search for and find the text in a whole word rather than a partial one. For example, if you are searching for the letter R and you select the Whole Word option, then the search will find the Ly Net Sales R measure, but it will not find the letter R within the Ly GMROI% measure.
-
-
When finished, click Find.
-
Any matching results are displayed in a scrolling list below the Find input field. Results are grouped by view, with the upper-most and left-most view results first. Each result includes the measure or position label that was matched as well as the view and dimension of the match. If the attributes are selected, then the result includes the Attribute labels as well.
Find results are capped at 250 matches. If there are more matches, you may need to modify your search criteria to narrow your search.
-
Click one of the results.
Figure 5-15 Find Results
-
The matching header scrolls to show the result in the appropriate view.
Attribute Search
You can find dimension position in workspace by searching the respective attribute related to it. The position label may not have all the relevant information whereas attributes are the additional details provided for each position. The Attribute Search makes it quick and easy to locate for positions in pivot table.
The Attribute Search is enabled only when the attributes are shown on the pivot table. You may display these attributes using the the Edit View and Show/Hide Attribute selection. The Attribute drop-down list has all of the attributes related to dimension. Based on the dimension selection in the Dimension drop-down list. The Attribute drop-down list shows the related attributes list. The attribute drop-down list shows the attributes for the dimension selected in the Dimension drop-down list.
The attribute of the matching search result is shown in the result description with prefix Attr. When the measure dimension is selected, Attribute drop-down list is set to None. Figure 5-16 shows an example of attribute search where product dimension is searched by attribute fabric: cotton.
Figure 5-16 Attribute Search
Modifying Find Criteria
To return to the Find panel, click Main Menu. If the Find panel was the most recently selected menu item, the Find panel will be displayed with any previously found results. If not, you can navigate using the Edit menu item. Click in the Find input field and the search criteria are displayed. Modify any find criteria and click Find; the new results are displayed.
Click Reset to return the Find panel criteria to the default state. Click Cancel to close the Find panel. Clicking outside the Find Panel causes it to close.
Note that when the focus moves from the Find panel to the grid after a reset, the Find pop-up will close but the Find panel is still displayed on the left.
Find Limitations
Find does not consider z-axis positions. However, clicking Position Tile on a z-axis position launches a pop-up of all positions and measures that can be selected, so you can find a particular position if the dimension and level are known.
Pivot Table Context Menu
The Pivot Table Context menu gives you the list of the actions that can be performed on pivot table cell, column, or row header. An organized context menu gives you ease of selecting actions from a long list. In context menu, the actions are divided into sections based on the function groups. These grouped functions are shown in a sub-context menu. For example the actions related to Attributes are combined together.
Figure 5-17 Context Menu Attribute Options
Measures
Measures represent the events or measurements that are recorded; the positions in the dimensions provide a context for the measurement. Measures are defined based on the business rules set in the application. The dimensionality of a measure is configured through the definition of its base intersection, which is the collection of levels (one per appropriate dimension) defining the lowest level at which the information is stored for the measure. Measure names are completely configurable and typically named using a convention that identifies each component and the meaning of the measure.
Measures that appear in the view are based on the configuration, and only measures configured for a view are visible in the view. You can show, hide, or reorder the measures using Edit Measure. See Editing Views.
Measure Information
You can right-click a measure and select Measure Information from the context menu. This provides more information on the selected measure that can help you understand its use and context in the open workspace.
Figure 5-18 Open Measure Information
A Measure Information window opens as shown below that displays Measure Name, Data Type, Default Spread Type, Intersection, Lower and Upper Bound, Default Aggregation Type and Description.
Figure 5-19 Measure Information
A tool tip displays the measure description for the measure header. When you hover over the measure header, an i icon appears. Hover over the i to display the measure description. This minimizes your need to refer to documentation. The following figure shows an example of the tool tip with the measure description.
Figure 5-20 Measure Description Tool Tip
Hiding Selected Measures
Using the context menu shortcut, Hide Selected Measures, you can hide the metrics you do not want to work with currently. This saves you from opening Measure Edit View and selecting the measures to hide from the list.
Figure 5-21 Hide Selected Measures
You can see all the measures in the view by selecting the measure profile from the Measure Edit View once you have completed the work.
Figure 5-22 Unhide Selected Measures
Switching Between Measure Profiles
Measure profiles are customized groups of measures that you can create and use in views. See "Measure Profiles" for details about measure profiles. You can see the current measure profile on the Measure dimension tile on the pivot table, as shown in Figure 5-23.
Figure 5-23 Current Measure Profile
Figure 5-24 Selected Measure Profile Displayed on Measure Tile
![Selected Measure Profile Displayed on Measure Tile Selected Measure Profile Displayed on Measure Tile](img/measure-change.jpg)
To switch between measure profiles, select the required measure profile from the list of Measure dimension tile, as shown in Figure 5-25. When you select a measure profile from the list, the view refreshes to show the measures of the selected measure profile. The selected measure profile is shown on the tile as well highlighted in the list.
Figure 5-25 Select Measure Profile
Multi-Item Measure
You can add multi-item measure to see a concatenated list of positions for a dimension. You can see the alerts or offers related to a position in single cell. This is a read-only measure that can be configured from Config Tools using the single hierarchy select measure property. This measure displays the list of items related to the position along with the total count of the items. Figure 5-26 shows that position 10000011 Leather Loafer has five dates and a badge on the right top corner of cell showing count of the items. If the count of item displayed in cell exceeds 99, then the badge displays 99+. For more details on how to configure the multi list measure, refer to the Oracle Retail Predictive Application Server Cloud Edition Configuration Guide.
Figure 5-26 Multi-Item Measure
![Multi-Item Measure Multi-Item Measure](img/f5x-mi-measure.jpg)
Selecting Rows and Columns
Selecting an entire row or column or multiple rows or columns is required for using several features such as Hide Selected Measures, Lock and Unlock, and so on.
Selecting a Single Row or Column
To select a single row or column, click the row or column header. When selected, the row or column becomes shaded.
Figure 5-27 Select Single Row
Selecting a Group of Contiguous Rows or Columns
To select a group of contiguous rows or columns, do one of the following:
-
Double click the first row or column header to be selected and then drag the mouse pointer along the row or column headers to select the remaining rows or columns.
-
Select a row or column header and then use Shift Key + Down/Up Arrow Key to select multiple contiguous rows or columns.
The selected row or columns become shaded.
Figure 5-28 Select a Group of Contiguous Rows or Columns
Selecting a Group of Non-Contiguous Rows or Columns
To select a group of non-contiguous rows or columns:
-
Click the first row or column header you want to select. The selected row or column becomes shaded.
-
Hold down the Ctrl key and click the other row or column headers you want to select.
All the selected rows or columns become shaded.
Figure 5-29 Select a Group of Non-Contiguous Rows or Columns
Resizing Rows and Columns
You can quickly resize multiple columns or rows to view the relevant information on the column or row header. You can resize by adjusting the height or width of headers for multiple cells. You can select one or more column or row headers and take contextual action to resize the width/height.
Figure 5-30 Select Rows to Resize
Table 5-1 Resizing Rows and Columns
Value | Width | Height |
---|---|---|
Minimum Value |
13 px |
11 px |
Maximum Value |
512 px |
364 px |
If the values entered for width or height are outside these thresholds, then an error message is displayed and the OK button is unavailable. You must enter the correct value or leave it blank (the default value) and then click OK.
Figure 5-31 Default Height and Width
![Default Height and Width Default Height and Width](img/fnew-DefaultHeightandWidth.png)
Figure 5-32 Resize Height or Width
![Resize Height or Width Resize Height or Width](img/f31-ResizeRowsandColumns_ResizeHeightorWidth.png)
You can restore the selected row or column headers to the default size by clicking the Restore Default option. After you change the default height or width value, the Restore Default link is available. Click Restore Default to revert back to default size values. You can resize the height and width of all columns or rows by selecting the check boxes; Apply to All Columns or Apply to All Rows.
Figure 5-33 Resizing Results
Expanding and Collapsing Levels in Rows and Columns
Collapse functionality allows a planner to group the child level to view data at summary level. Expand functionality allows a planner to drill-down by level to see the data at the lower levels. Quick Collapse and Expand of the levels in the pivot table helps you to see the data in a way that is meaningful to you, and is easy to navigate.
You can collapse or expand a dimension using context menu (right-click) Collapse or Expand function. Collapse functionality groups the child positions of the selected parent position. Expand functionality expands all the child positions belonging to a selected parent. You can select more than one column or row dimension level and right-click to Expand or Collapse lower levels for that position only.
For example: In a calendar dimension that has a levels week beneath the month of January and February and a planner collapses the month of January, then all the week levels beneath the month of January will collapse.
Collapse All and Expand All
The same functionality of collapse has been extended to Collapse All which collapses child levels of all the positions of the selected dimension in the view. Similarly, the expand functionality has been extended to Expand All which expands child levels of all the position of selected dimension in the view. The selected dimension level is specified next to the Collapse All or Expand All in context menu. With Collapse All or Expand All you can quickly view data as desired without navigating to edit view.
For example, there are 10 sub-classes with three style colors each, when a planner applies Collapse All on one subclass, it collapses style colors across the 10 subclasses. The data is shown at sub class level in the view.
Note:
Collapse All or expand all functionality does not collapse every level in the dimension hierarchy. It collapses or expands only the selected position. Assume, there are two department with three subclasses and five styles in each subclass. The Collapse all function applied at department level will collapse the sub class beneath it but does not collapse the child under subclass.
Note:
Expand or Collapse of levels done in the Edit View Overlay will not change the expanded or collapsed levels in the pivot table.
Showing and Hiding Attributes
You can show or hide the attributes in the pivot table in the row or column. Right-click on any position in a row or column header and select Show Attributes from the contextual menu. This displays the Attribute Name: Attribute Value list against the position in a single cell.
Figure 5-34 Show Attributes
Figure 5-35 Attribute List Visible in a Single Cell
To show the attributes in separate columns, you can right-click on position and from the Attribute sub –menu, select Show Attribute in Separate Column.
Figure 5-36 Context Menu - Show Attributes in Separate Columns
Figure 5-37 Attributes in Separate Columns
You can also sort the positions by sorting attributes in ascending or descending order when you right-click on the attribute header. The context menu sort on the attribute is in sync with the edit view sorting functionality. To remove sorting from attribute, you can right-click on the attribute header and select Remove Sorting.
To show the attributes again in single column again, you can right-click the position and select the context menu option, Show Attributes in Single Column.
Note- If you bring high number of attributes in the separate column, you may not see the pivot table x-axis dimension columns. To bring back the x-axis dimension columns, just resize the attribute columns to a smaller size.
Figure 5-38 Context Menu - Show Attributes in Single Column
To hide the attributes list, use Hide Attributes from the contextual menu.
Figure 5-39 Hide Attributes
Hide Selected Position
You can hide selected positions by using the context menu option, Hide Selected Position. This saves you from opening the Edit view and selecting positions to hide.
You can hide one or multiple positions using hide position function in the view. Once your work is complete, you can view all the positions by selecting the hidden positions from edit view.
Figure 5-40 Hide Selected Positions
Displaying Attributes in the Z-axis
You can show and hide attributes in the z-axis. The show and hide attribute feature in the z-axis works bit differently than the other axis. The display of attributes in z-axis helps you to plan with attributes.
This feature also continually displays the primary attribute in the z-axis. Perform the following steps to use this feature.
-
Right-click on the Z-axis to display the menu for Show Attributes and Manage Attributes.
Figure 5-41 Show Attributes and Manage Attributes Menu for the Z-axis
-
To show attributes for the Z-axis, right-click the z-axis and select Show Attributes from the menu to display the first attribute. This action only displays the first attribute in the attribute list with a pipe delimiter (|).
Figure 5-42 Displaying the Attribute for the Z-axis
To view all of the selected attributes that belong to the specific dimension, either:
-
Hover over the Z-axis. Each attribute is separated with a pipe delimiter (|).
Figure 5-43 Hover to View All of the Selected Attributes in the Z-axis
-
Click the Edit icon on the Z-axis to view the list of selected attributes.
Figure 5-44 Using Edit to Display the List of Attributes in the Z-axis
-
-
To manage attributes for the Z-axis, right-click the z-axis and select Manage Attributes from the menu to open the standard edit view, You can select the attributes for the dimension from this view.
Figure 5-45 Edit View for the Z-axis
-
This action only displays the first attribute in the attribute list.
Figure 5-46 First Attribute Message
To view all of the selected attributes, hover over the Z-axis dimension.
As shown in Figure 5-47, Product Type is the first attribute in the attribute list. You can drag and drop the attributes to change the display order the list
Figure 5-47 Display Order of Attribute
-
When the attribute is already displayed in z-axis, you can right-click the z-axis and select Hide Attributes from the menu to hide the attribute
Figure 5-48 Hide Attributes Menu
Cells
Your ability to edit multiple workbook cells at once and to move chunks of data in and out of the workbook is essential to using RPASCE efficiently and effectively. This section describes how to select and edit cells as well as how to cut, copy, and paste information into cells. It also provides details about the various tasks you can perform with the data in cells.
Selecting and Manipulating Cells
Cells or groups of cells must be selected in the pivot table before certain operations can be performed on them. Operations such as cutting and copying data, filling or clearing data cells, and displaying data in chart form are typically performed on a subset of cells that you must select before invoking the menu commands.
Note:
Certain cells are read-only to prevent them from being edited. By default, read-only cells are indicated by a gray background. Cells are specified as read-only during configuration. This cannot be changed by the user.
Selecting a Single Cell
To select a single cell, click inside the cell or use the cursor keys or tab key to move from one cell to another. When selected, the cell becomes shaded and has a black outline.
Selecting a Group of Contiguous Cells
To select a group of contiguous cells, do one of the following:
-
Click a starting cell and draw adjacent cells to select them. You can drag within one row or column or across rows and columns to create a block of selected cells.
-
Click a cell to select it. Hold the Shift key and use the cursor keys to select adjacent cells.
The selected cells become shaded, while the first cell selected is also outlined in black.
Figure 5-49 Select a Group of Contiguous Cells
Selecting a Group of Non-Contiguous Cells
To select a group of non-contiguous cells,
-
Click the first cell you want to select. The selected cell becomes shaded.
-
Hold down the Ctrl key and click the other cells you want to select. All selected cells become shaded.
Figure 5-50 Select a Group of Non-Contiguous Cells
Navigation Shortcuts for Editing Cells
When you are editing cells in a pivot view, you can use the navigation options listed in Table 5-2 to move to the next cell.
Table 5-2 Navigation Options
Action | Effect |
---|---|
Tab or Keyboard right arrow |
Move to next editable cell to right |
Shift + Tab or Keyboard left arrow |
Move to next editable cell to left |
Enter or Keyboard down arrow |
Move to next editable cell below |
Keyboard Up arrow |
Move to next editable cell above |
When you use these options, the cell you navigate to opens in editable mode (unless the cell is read-only). To exit editable mode, use the Escape key.
When you navigate to read-only cells or move to and from cells that are not in editable mode, you can use the cursor keys.
Note:
Use the Escape key to exit Editable mode and restore the previous value.
Entering or Changing Values in a Cell
This section provides descriptions of actions you can take to change individual values in the pivot table.
Note:
The type of data that cells can accept is predefined. If you try to enter another type of data into the cell, you will see an error message.
Numbers
You can enter or overwrite a numeric value. Some cells may have constraints on the values that can be entered. If you exceed this limit, you will see an error message.
Alphanumeric Values or Plain Text
You can enter or overwrite an alphanumeric value. Text may be entered up to a maximum value of 4096 characters. Any text string that exceeds this length will be truncated to this value.
List Items
You can select the desired option from the list. Two types of lists are available.
-
Use a standard list by clicking the arrow to expand the list and select an item.
-
A list that lists positions from a hierarchy is often quite long.This type of list provides a search field at the top of list. The search field filters the long list to list items matching the search text. To make a selection, click the arrow to expand the list and search for or select an item. To modify a selection, click the arrow to expand the list, then click X to clear the search field in order to enter new search criteria.
Check Box (Toggle) Items
You can select the check box to change the status of the item (Yes or No; On or Off). This may be called a Boolean measure and provides capability for an active or a non-active user defined response.
Date and Time Items
You can select the desired date and time. Some measures may be formatted to display only the date. You can only set the time when the date measure is formatted to display time.
Click within the cell to display the Select Date and Time pop-up. Click the appropriate arrow keys to change the year, month, day, hour, minute, second, and AM/PM. (The AM/PM option buttons are available only if the measure has been configured to use the 12-hour format.)
You cannot enter dates or times outside of the lower and upper bounds for the measure.
Undefined Cells
Cells with undefined values cannot be edited and are represented with symbol -. Hovering over these cells displays the message cells value undefined. This may indicate that the display is at the wrong intersection to display the cell value. You may adjust the display to reflect the correct product, location, or calendar intersection.
Figure 5-51 Cell Value Undefined
![Cell Value Undefined Cell Value Undefined](img/Figure5-52CellValueUndefined.png)
Modify Data with Cell Formulas (Smart Edits)
In the view, you can make changes to the data cells. You can make the edits by directly typing or updating a value in the cell, copying and pasting, or using fill. You can also lock a cell value to ensure that any calculation performed during the cell edits do not affect the locked cell values.
After you enter or change the value in the cell, you can navigate to any other cell by double-clicking on that cell or using the following keyboard keys to navigate:
-
Enter to scroll down
-
Shift + Enter to scroll up
-
Tab to scroll right
-
Shift + Tab to scroll left
To learn how to modify data with math formulas, see "Modifying Data with Cell Formulas".
Modifying Data with Cell Formulas
You can use cell formulas to modify the value of a data cell in the pivot table by applying an operator (+, -, *, or /) to that value. With this functionality, you can make changes to data values without having to manually calculate the result. To perform this function, click the data cell and type the operator that you want to add, subtract, multiply, or divide by.
For example, suppose that a particular data cell contains the value 10.
-
Add: If you enter +10 in the cell, the value becomes 20.
-
Subtract: If you enter + -10 in the cell, the value becomes 0.
-
Multiply: If you enter *10 in the cell, the value becomes 100.
-
Divide: If you enter /10 in the cell, the value becomes 1.
-
Percentages: If you want to increase the value of a cell by 10 percent, multiply the value by 1.1 (enter *1.1)
Cell formulas have many applications for modifying data. Cell formulas can only be applied to one cell at a time, but changes made to aggregate level cells are spread down to lower-level cells along dimension lines. Similarly, any changes made to lower level cells are reflected in the aggregates of that data.
In addition to the basic math operations, you can also extend the math operations. For example, entering +30/2 in a cell with a value 70 will add 30 to the existing value and then divide the result by 2.
Using Math Formulas
In addition to the basic math operations, you can enter formulae in the cells. For example, entering 10+30/2 in a cell will update the cell with a value 20. Note that this formula does not follow operator precedence logic, but evaluates from left to right.
Entering Measure Data Using a Scaling Factor
Use the scaling factor feature to enter measure data to be scaled or factored to an internal value that is recognized by the server in data calculations. When you enter a value for a measure that has a scaling factor, the value that you enter is multiplied by the scaling factor to arrive at this internal value. The display of the data and the ease of data entry can be greatly enhanced by use of a scaling factor.
For example, suppose that you want to enter data in thousands of units. You might find it tedious to enter 1000, 2000, 6000, and so on. A more sensible approach is to enter the values 1, 2, and 6, and have the system apply a scaling factor (in this case 1000) to the data that has been entered. The internal values of the three affected cells are 1000, 2000, and 6000, and these internal values are used in required data calculations. Removing the zeros from the display results in a cleaner, less cumbersome view appearance. Scaling factors can be set in the RPASCE Configuration Tools or through the formatting options in RPASCE. For more information about setting scaling factors in the Configuration Tools, see the Oracle Retail Predictive Application Server Cloud Edition documentation.
To set scaling factors in RPASCE, complete the following steps:
-
Right-click the measure header to invoke the contextual menu and select Format.
Figure 5-52 Formatting Measure Data
-
The Format window is displayed.
-
In Figure 5-52, the selected measure Wp Sales R is already selected and all the views in the current tab are selected.
Figure 5-53 Format for Measure Wp Sales R
-
Enter a value in the Scale field. For example, if you enter 1000, the cell value 35 will equal 35,000. You can also add a k suffix to help remind you that this is a scaled cell.
-
Click OK to apply the change and exit the window. Note that the values are now scaled by the specified scale factor.
Figure 5-54 Scale Factor Values
You can use the scale factor for percentages as well. Enter a scale of 0.01, if you want to see values displayed as percentages, so that you see 19% rather than 0.19.
Using the Fill Function in a View
You can populate many cells of a writable measure at a time with the Fill function. This enables you to edit multiple workbook cells with the same values easily and quickly. Fill is available as a menu item in the right-click context menu.
Perform the following steps to fill cells with the same value:
-
Select a source cell whose value you want to repeat in the other cells or block of cells. The source cell is always the top left cell of the first selection.
A source cell can be the top left cell or any cell that is first selected out of multiple blocks.
-
Select the block of cells where you want to fill the value of the source cell. You can select either select consecutive or non-consecutive cells or block of cells per your requirements.
-
Right-click one of the selected cells and select Fill from the context menu.
-
The value of the source cell is copied to all the other selected cells or block of cells.
Figure 5-55 Fill Cell
Figure 5-56 Selected Cells Filled with Source Cell Value
Note:
The locked, read-only, protected and invalid data types cells will be skipped during fill.
Aggregation and Spread Methods
Fundamental to planning is the ability to review and plan at both a high-level and detailed level. You are able to easily do this by showing or hiding levels of any hierarchy (see Editing Views). When multiple hierarchy levels are visible you are able to see both the high-level (parent), and low-level (child) data.
The parent data represents an aggregation of its children. This means that a change to the data at the child level is reflected in the parent (after you calculate). The way in which data is aggregated from child to parent is called the Aggregation Method. There are multiple aggregation methods which are used to produce values at parent level.
Spreading is the opposite of aggregation and works to distribute parent-level modifications to the children.
Various aggregation and spreading methods are provided such as total, average, minimum, maximum. A complete list of aggregation and spreading methods are explained in these sections; Aggregation Methods and Spread Methods.
Note:
A few aggregation
methods only consider cells that are populated for calculation. These
methods are annotated by _pop
. For example, the total_pop
means that the aggregation method total is applied
only on the cells which are populated.
The user can find aggregation and spread methods under measure information of each measures. for measure information navigation, refer to Measures.
In Table 5-3, assume the following hierarchy and measure data for understanding the aggregation and spread methods, here six measures and five weeks (Week1, Week2, Week3, Week4, Week5) are shown for one product.
Table 5-3 Hierarchy and Measure Data
Measure | Sales R | Sales U | Sales AUR | Selling Week | BOP | EOP |
---|---|---|---|---|---|---|
Month A | ||||||
Week 1 |
3000 |
190 |
15.8 |
X |
350 |
310 |
Week 2 |
1550 |
120 |
12.9 |
310 |
250 |
|
Week 3 |
1850 |
170 |
10.9 |
X |
250 |
170 |
Week 4 |
1370 |
120 |
11.4 |
X |
170 |
95 |
Week 5 |
2450 |
195 |
12.6 |
X |
95 |
50 |
Aggregation Methods
Aggregation methods are explained in the following sections.
Total
The measure is aggregated by taking the total (numeric sum) of the values of all child cells at the base intersection.
Example: If Agg method for measure Sales R is total, then the value for Sales R at month A will aggregate as Week1 + Week2 + Week3 + Week4 + Week5
Measure | Sales R |
---|---|
Month A |
10220 |
Week 1 |
3000 |
Week 2 |
1550 |
Week 3 |
1850 |
Week 4 |
1370 |
Week 5 |
2450 |
Average
The measure is aggregated by taking the numeric average of the values of all the child cells at the base intersection.
Example: Assume the agg method for measure Sales R is average, then the value of Sales R at month A will be aggregated as (Week1 + Week2 + Week3 + Week4 + Week5) / 5
Measure | Sales R |
---|---|
Month A |
2044 |
Week 1 |
3000 |
Week 2 |
1550 |
Week 3 |
1850 |
Week 4 |
1370 |
Week 5 |
2450 |
Mode
Picks the most frequently occurring cell value from the base intersection to represent the cell value of the parent level.
Example: Assume the agg method applied for measure Sales U is mode, then result at month A for Sales U will be 120
Measure | Sales U |
---|---|
Month A |
120 |
Week 1 |
190 |
Week 2 |
120 |
Week 3 |
170 |
Week 4 |
120 |
Week 5 |
195 |
Median
The measure is aggregated as the median value (the middle value when sorted from lowest to highest) of the values of all child cells.
Example: If agg method for measure Sales R is median then the value for Sales R at month A will be 1850
Measure | Sales R |
---|---|
Month A |
1850 |
Week 1 |
3000 |
Week 2 |
1550 |
Week 3 |
1850 |
Week 4 |
1370 |
Week 5 |
2450 |
Max
The measure is aggregated by taking the maximum of the values of all child cells at the base intersection.
Example: Assume agg method applied for Sales R is max then the value for sales R at month A will be 3000
Measure | Sales R |
---|---|
Month A |
3000 |
Week 1 |
3000 |
Week 2 |
1550 |
Week 3 |
1850 |
Week 4 |
1370 |
Week 5 |
2450 |
Min
The measure is aggregated by taking the minimum of the values of all child cells at the base intersection.
Note:
For most purposes,
the min_pop
is most appropriate because the minimum
value of all child values is typically the NA value, which is usually
zero.
Example: Assume agg method applied for Sales U is min then the value for Sales U at month A will be 120
Measure | Sales U |
---|---|
Month A |
120 |
Week 1 |
190 |
Week 2 |
120 |
Week 3 |
170 |
Week 4 |
120 |
Week 5 |
195 |
recalc
The measure is not aggregated, but is recalculated at all aggregated levels. The equation used to calculate a parent value will be unique to the data being calculated.
Example: Assume the recalc method is applied to Sales AUR, the month A value for sales AUR will be defined by the same equation used for calculating the base intersection of Sales AUR. For each week level Sales AUR = Sales R / Sales U. Hence, at month A level Sales AUR = Sales R at month A / Sales U at month A that is, Sales AUR at month level = 10220/795= 12.86
Measure | Sales R | Sales U | Sales AUR |
---|---|---|---|
Month A |
10220 |
795 |
12.86 |
Week 1 |
3000 |
190 |
15.8 |
Week 2 |
1550 |
120 |
12.9 |
Week 3 |
1850 |
170 |
10.9 |
Week 4 |
1370 |
120 |
11.4 |
Week 5 |
2450 |
195 |
12.6 |
ambig
The measure is aggregated by considering the values of all child cells. If all child cells have the same value, the parent value is the same as the child cells. Otherwise, the children are different therefore the parent value is ambiguous. Ambig value is annotated as a question mark (?) symbol.
Example: Assume agg method applied for Sales R is ambig, then at month A of Sales R will be ?.
Measure | Sales R |
---|---|
Month A |
? |
Week 1 |
3000 |
Week 2 |
1550 |
Week 3 |
1850 |
Week 4 |
1370 |
Week 5 |
2450 |
popcount
The measure is aggregated by counting the number of child cells that are populated (meaning that they have a value different from the NA value for the measure). This is frequently used for Alert (hit count) measures.
Example: Assume agg method for measure Selling week is popcount, then the value at month A will be four (count of selected check box).
Measure | Selling Week |
---|---|
Month A |
4 |
Week 1 |
X |
Week 2 |
|
Week 3 |
X |
Week 4 |
X |
Week 5 |
X |
pst (Period Start Total)
The measure is aggregated by selecting the first child value in the Calendar hierarchy and by taking the total of all child values in all other hierarchies.
For example: if aggregating from week to month the value in the first week of the month becomes the month-level value. Similarly, to aggregate from Month to Quarter will take the value from the first month of the quarter and place it in the Quarter.
Example: Assume agg method for measure BOP is pst, the value of BOP at month A will be same as first week value.
Measure | BOP |
---|---|
Month A |
350 |
Week 1 |
350 |
Week 2 |
310 |
Week 3 |
250 |
Week 4 |
170 |
Week 5 |
95 |
pet (Period End Total)
The measure is aggregated by selecting the last child value in the Calendar hierarchy, and by taking the total of all child values for all other hierarchies.
For example: if aggregating from week to month the value in the last week of the month becomes the month-level value. Similarly, to aggregate from Month to Quarter will take the value from the last month of the quarter and place it in the Quarter.
Example: Assume agg method for measure EOP is pet, the value for EOP at month A will be the same as last week value.
Measure | EOP |
---|---|
Month A |
50 |
Week 1 |
310 |
Week 2 |
250 |
Week 3 |
170 |
Week 4 |
95 |
Week 5 |
50 |
and
The measure is aggregated by performing a Boolean And operation on the values of all child cells. This means that if all children are True then the parent is True. Otherwise, the parent is False.
(child 1=True, and child 2=True, and…and child n=True) then True.
Example: Assume the agg method for selling week is and, then value for selling week at month A is unchecked (false) considering all the child cells are not checked.
Measure | Selling Week |
---|---|
Month A |
|
Week 1 |
X |
Week 2 |
|
Week 3 |
X |
Week 4 |
X |
Week 5 |
X |
or
The measure is aggregated by performing a Boolean Or operation on the values of all child cells. This means that if any of the children are True then the parent is True. Otherwise the parent is False.
(child 1=True, or child 2=True, or…or child n=True) then True.
Example: Assume the agg method for selling week is or then the value for selling week at month A is checked.
Measure | Selling Week |
---|---|
Month A |
X |
Week 1 |
X |
Week 2 |
|
Week 3 |
X |
Week 4 |
X |
Week 5 |
X |
Spread Methods
Spread methods are explained in the following sections.
none
Values cannot logically be spread to the child.
Example: Here the Sales AUR is example for spread type none, since this cannot be logically spread.
repl
Replicate the value to each child.
Example: Assume spread method for Sales U is repl, then the value for Sales U at Month A will be replicated to all the Weeks intersection.
Measure | Sales U |
---|---|
Month A |
500 |
Week 1 |
500 |
Week 2 |
500 |
Week 3 |
500 |
Week 4 |
500 |
Week 5 |
500 |
prop
Spreads values evenly.This means the parent value is distributed to all child cells evenly as shown in the following example.
Example: Assume spread method for Sales U is prop, the value of the sales U at month A will be spread proportionally to all the weeks. If Sales U at month A is updated from 795 to 1000 then:
Measure | Sales U | Updated To | Measure | Sales U |
---|---|---|---|---|
Month A |
795 |
Month A |
1000 |
|
Week 1 |
190 |
Week 1 |
239 |
|
Week 2 |
120 |
Week 2 |
151 |
|
Week 3 |
170 |
Week 3 |
214 |
|
Week 4 |
120 |
Week 4 |
151 |
|
Week 5 |
195 |
Week 5 |
245 |
even
Spreads values evenly. This means the parent value is distributed to all child cells evenly as shown in the following example.
Example: Assume spread method for Sales R is even, then the value of Sales R at Month A will be spread evenly to all the weeks. If Sales R at Month A = 10,000 then:
Measure | Sales U |
---|---|
Month A |
10000 |
Week 1 |
2000 |
Week 2 |
2000 |
Week 3 |
2000 |
Week 4 |
2000 |
Week 5 |
2000 |
delta
Increments or decrements each cell evenly. Effectively evens the spreading of the change (delta).
Example: Assume the spread method of Sales R is delta, then the increment or decrement delta value of Sales R is spread evenly at all weeks. If Sales R is increased by 250 from 10220 then 50 is spread to each week (250 / 5 weeks = 50):
Measure | Sales R |
---|---|
Month A |
10470 |
Week 1 |
3050 |
Week 2 |
1600 |
Week 3 |
1900 |
Week 4 |
1420 |
Week 5 |
2500 |
Overriding Spread Methods
When you edit a parent value you can change how data will be spread by entering a symbol indicating the preferred spread method. Type the new value followed by r, e, p, or d. The spread method is changed for that specific data edit and is not permanently changed.
Note:
The spread methods of r,e,p, and d must be typed as lower case letters.
The alternate spread methods are listed in the following table.
Symbol | Symbol Name | Description |
---|---|---|
r |
Replicate |
Replicate copies the entered value to all child cells below the aggregate level parent. This method can be used for measures that have an aggregation method of Total or Recalc. |
e |
Even |
Even divides the entered value evenly to all child cells below the aggregate level. This method can be used for measures that have an aggregation method of Total or Recalc. |
p |
Proportional |
Proportional spreads the difference between the original and entered value to all child cells below the aggregate level, based on that cell's percent contribution to the original value in the edited cell. This method can be used for measures that have an aggregation method of Total. |
d |
Delta |
Delta spreads the difference between the original and entered value evenly to all child cells below the aggregate level. This method can be used for measures that have an aggregation method of Total. |
Locking and Unlocking
When you change a value in a one cell, it can impact other cells, measures, or positions. For example, increasing the value of week 1 Regular Sales in January will also increase the value of the Month Total for January, but will not impact the other weeks in January. In addition to read-only workspaces and measures, RPASCE also provides a locking function in order to protect information. The locking function can be used on cells, measures, and positions. To continue with the above example, if you lock the Month Total for January and then increase the value of week 1 Regular Sales, the January Month Total will not change because it is locked, but the other weeks of January will be decreased.
Cell locking is available for any editable cell and invokes protection processing.
Measure locking is available for any measure and invokes protection processing. When a measure is locked, all cells for that measure are locked.
Position locking is available for non-calendar dimensions and does not invoke protection processing.
Note:
Locks are not recognized by operations such as custom menus and refresh. Locks are only recognized when a workspace calculation is done.
Cell Locking
Use the cell locking feature to lock one or more editable cells in the pivot table. When a table cell is locked, calculations performed as a result of data manipulations do not affect the locked data values. This functionality allows you to examine various What-if scenarios to determine the best course of action.
RPASCE iterates through the selected cells by measure, then by column, then by row. Locked cell information is immediately transferred to the RPASCE server. The locked cell information is saved with the workspace, and locked cells continue to be locked when the workspace is reopened.
The locked status of a cell is indicated by the presence of a picture of a lock on the left side of the cell. After an eligible cell is locked, the system determines whether the remaining table cells are eligible or ineligible for locking. Any read/write cells that become ineligible for locking are made read-only.
You may choose to lock a data cell at any time to protect that cell from forced recalculations as a result of data manipulation elsewhere in the workspace.
For example, you may want to change a sales value at an aggregate level (such as month) and spread the results to only three of the four weeks that comprise that month. In this case, you can effectively hold the second week's sales value constant while spreading the aggregate-level increase among the remaining three weeks.
Cell Locking Example
The goal, when locking a cell, is to make it remain constant while you adjust other cells. In this example, the month level cell is locked, and then one of the week level cells is adjusted. This forces the unlocked weeks to adjust while keeping the month total.
-
To lock the month level cell, right-click the cell and click Lock Cells.
Figure 5-57 Lock Month Level Cell
-
Note the lock icon in the locked month level cell.
Figure 5-58 Lock Cell Lock Icon
-
Increase the first week of the month.
Figure 5-59 Increase the First Week
-
Click Calculate. Note the decrease to the other weeks of the month, while the month total did not change.
Figure 5-60 Calculation Results
Unlocking Cells
Unlocking the cells allows them to become editable again. To unlock a cell, right-click the locked cell and click Unlock Cells.
To unlock more than one cell simultaneously, either:
-
Right-click the selected locked cells and click Unlock All Cells
-
Select the locked cells and unlock them from the global menu.
Measure Locking
Use the measure locking feature to simultaneously lock all of the cells that are associated with a given measure in a view. A measure can be locked or unlocked when the header cell of the measure dimension is selected. As with individual cell locking, the locked status of each cell in the measure is indicated by the lock picture on the left side of each cell.
Locked measure information is immediately transferred to the RPASCE server. The locked measure information is saved with the workspace, so locking measures enables the save features of the workspace. The locked measure information is saved with the workspace, and locked measures continue to be locked when the workspace is reopened.
If multiple measures are selected, they are locked or unlocked in row or column order. A measure may be locked even if it is already protected by protection processing.
Note:
You can only make a selection at one level in the headers of a multidimensional header. Locking and unlocking apply to the selected measure only. Locked measures are designated by a lock icon in the header text of the measure and in its cells.
Measure Locking Example
The goal, in measure locking, is to lock an entire measure. If this measure is included in a calculation with three other measures, and this measure is locked, any change to the other measures will only affect the other three measures; this measure will remain constant.
-
To lock an entire measure, right-click the measure header and click Lock Selected Rows.
Figure 5-61 Lock Entire Measure
-
The entire measure is now locked, and the lock icon appears in the header and each cell. If any other measures are changed, the value of this measure will not change, even if this measure is impacted by a calculation involving the other adjusted measures.
Figure 5-62 Entire Measure Locked
Unlocking Measures
Unlocking the measures allows them to become editable again. To unlock a measure, right-click the locked measure and click Unlock Selected Row. To unlock more than one measure simultaneously, either:
-
Right-click the locked measures and click Unlock All Measures
-
Select the locked measures and unlock them from the global menu.
Figure 5-63 Unlocking All Measures
Position Locking
Use position locking to lock all measures in all displayed views along one or more positions of non-calendar dimensions. Cells along unlocked positions are still editable and can also change as a result of calculations. Locked positions are designated by a lock icon in front of the position name. The cells of the locked position are shaded as read-only.
Figure 5-64 Locked Positions
Protection processing does not run against cells locked by a position lock. Unlike cell locks, a parent position becomes locked if all its children are locked. A parent position becomes unlocked if any of its children are unlocked. Hidden children are considered when deciding if a parent position becomes locked. Unlocking or locking the parent unlocks or locks all the children. Hidden child positions are treated in the same way as visible children. Unlike a measure lock, the lock indicators do not show up in each of the cells, only in the header cells, even though the cells are displayed as read-only.
Locked position information is immediately transferred to the RPASCE server. The locked position information is saved with the workspace, so locking positions enable the save features of the workspace. The locked position information is saved with the workspace and locked positions continue to be locked when the workspace is reopened.
A position cannot be locked when locking it affects an edited or locked cell. A warning modal dialog is displayed and asks you to revert the affected edits and calculate the workspace or cancel the position locks. You are warned if a cell lock is affected and given the choice of canceling the position lock or unlocking the affected cell locks and continuing. If both edits and cell locks are affected, then you see both windows, with the edit window appearing first. If you cancel the position lock from either window, then no action is taken against either locked or edited cells.
Figure 5-65 Locked Subclass
Position Locking Example
The goal, in position locking, is to lock a position so that it remains constant while you adjust other positions. In this example, if one position at the Style/Color level is locked, then the subclass total is adjusted at the higher level. This forces the unlocked Style/Color level positions to adjust while keeping the locked subclass unchanged.
-
To lock one of the Style/Color level positions, access the right-click context menu on the header of the position.
-
Click Lock/Unlock, then select either the Lock Selected Columns or Lock Selected Rows option.
-
The entire measure or position is now locked and the Lock icon appears in the header of the locked position, but not each cell.
Figure 5-66 Entire Product Position Locked
-
Increase the subclass level value.
Figure 5-67 Increase the Subclass Level Value
-
Click Calculate. Note that the unlocked departments increased, while the locked department did not change.
Figure 5-68 Calculation Result
Unlocking Positions
Unlocking a locked position allows it to become editable again. To unlock a position, right-click the locked position and click Unlock Selected Row. To unlock more than one position simultaneously, either:
-
Right-click the locked positions and click Unlock All Positions
-
Select the locked positions and unlock them from the global menu.
Figure 5-69 Unlocking All Positions
Unlock All
You can unlock all cells, measures and positions by using Unlock All. This function unlocks all the cells, measures, and positions simultaneously across the view.
Access Unlock All from either the right-click context menu or the global menu.
Figure 5-70 Unlocking All Cells, Measures, and Positions
Protection Processing
Protection processing is the process that makes some cells within a workbook read-only to ensure that, during edits, no conflicts occur within the RPASCE engine during a calculation cycle. There are two types of protection processing.
-
Measure Protection Processing: Locks cells in all the displayed views based on the measures that have been edited.
-
Dimension Protection Processing: Locks cells based on the dimension intersections that have been edited.
Protection processing runs each time when a workbook with any locked cell or measure is opened, a cell is edited, a cell or measure is locked, and after each cell revert action. It runs only once when a group of cells is updated in one action. Protected cells or measures appear highlighted in a different color in the view. This is a configurable feature.
Measure Protection Processing
In measure protection processing, cells become read-only when you make changes to enough measures. This ensures that no other possible changes exist that may cause conflicts.
For example, consider six measures (A, B, C, D, and E) set up with the following two rules:
-
Rule 1 - A = B + C
-
Rule 2 - B = D + E
In this scenario, both A and B are read-only before any edits are applied. Although B appears to be editable, since there are no reciprocal expressions for B's relation to D and E, it is not editable. Measures C, D, and E, however, are editable.
Typically, rule definitions are set up to include all equivalent derivations of any expression. This ensures that you can edit all of the measures contained in any expression in the rule.
Considering the previous example, Rules 1 and 2 can be configured as:
-
Rule 1 A=B+C, B=A-C, C=A-B
-
Rule 2 B=D+E, D=B-E, E=B-D
In this case, all measures are editable before you make any changes, and the measures remain editable based on the edits you make.
Measure protection processing locks all instances of a measure when any position for the other measures in the rule are edited.
For example, consider the Rule 1 in a typical Product, Location, Calendar dimension.
When you edit the measure B for product 1, location 1, and week 1 and measure C for product 1, location 1, and week 2, the measure A becomes read-only for all products at all locations in every week
Dimension Protection Processing
Changes to cells at the aggregated levels occur during a spread action that changes values down to the base intersection of a measure. Dimension protection processing protects the intersections (combination of levels) to ensure that all changes made during the spread do not affect such a spreading path.
Considering the typical retail dimensions, the process applies at product:color-location:store-calendar:week and product:style-location:region-calendar:month. These two intersections are on the same path from the root to leaf. If the location dimension has roots for both region/state and store volume, any edit to a cell in the volume group dimension causes all cells included in an intersection with a company/region/state/city to become read-only.
Figure 5-71 Location Dimension
![Location Dimension Location Dimension](img/location-dim.jpg)
Dimension protection processing changes to the intersection of dimension and level are processed, and edits are allowed to cells as long as the edits are on one path from the root to the leaf level.
Using Cut, Copy, and Paste
The cut, copy, and paste features provide flexibility to edit the workbook according to the business needs and transfer data within RPASCE as well as transferring data to and from RPASCE and external application (such as, Notepad, Excel, Google Sheets, and so on).
Table 5-4 Cut, Copy and Paste Functionality
Function | Description | Shortcut |
---|---|---|
Cut |
Allow users to copy data and reverts the cell values to NA values from the selected view and then moves it to another location within RPASCE or an external application. |
Ctrl+X |
Copy |
Allows users to copy data from a selected view and move it to another location within RPASCE or an external application. It differs from the cut operation as it does not clear the selected data from original location. |
Ctrl+C |
Paste |
Allows users to paste the cut or copied data to the selected destination location either within RPASCE or external application. |
Ctrl+V |
Using Cut, Copy and Paste Functionality for Cells
Key features of cut, copy, and paste functionality for cells include:
-
Cut, copy, and paste functionality is supported beyond what is viewable on your screen.
-
Only unformatted data can be pasted to a destination area.
-
Data in non-editable, read-only, protected, or locked measures or cells cannot be cut, it can only be copied.
-
The shortcut commands Ctrl+X, Ctrl+C, and Ctrl+V allows you to cut, copy, paste data respectively within RPAS as well as between RPASCE and an external application. This allows you to copy and paste data for cells at selected level of cells.
-
You can copy and paste the cells data within RPASCE at the base intersection level by using the keyboard shortcut command Ctrl+Atl+D and Ctrl+Atl+B. For more details on using copy and paste at the base intersection level, refer to Copying Data and Pasting Data.
-
You can use the right-click context menu copy Paste feature to copy paste data between cells. It allows you to cut,copy, and paste data within RPASCE.
The context menu Copy Paste can also be used in mobile devices like tablets where keyboards are unavailable.
-
Using cut, copy and paste functionality for non-contiguous (non-adjacent) cells is not recommended, as the paste result may differ from your expected result.
-
For optimum performance, the maximum number of cells that can be copied at one time is 10,000. If more data needs to be copied it is recommended to copy and paste multiple smaller groups of cells.
Using Shortcut Command Functionality for Cells
Perform the following steps to use the shortcut commands; cut, copy, and paste for cells.
-
Select all data cells that you need to either cut or copy.
-
Either cut or copy the data to the clipboard.
-
Cut the data using Ctrl+X .
-
Copy the data using Ctrl+C. When cells are copied, only textual content is transferred.
Figure 5-72 Selecting the Cell to Copy or Cut with a Shortcut
-
-
To paste the data from the clipboard, select the destination cell (either within RPASCE or external application) and use Ctrl+V for Paste.
Note:
Cut or Copied data will display in italicized font until calculated.
Figure 5-73 Select the Destination Cell and use the Paste Shortcut
Figure 5-74 The Paste Result
Using Right-click Cut, Copy, and Paste Functionality for Cells
You can also use right-click context menu option to Cut and Copy Paste data between cells. This feature is also used in mobile devices like tablets.
Perform the following steps to use the right-click functions; cut, copy, and paste for cells.
-
Select all the data cells that you need to either cut or copy.
-
Either cut or copy the data to the clipboard.
-
Cut the data using right-click functionality
-
Copy the data using right-click functionality. When cells are copied, only textual content is transferred.
Figure 5-75 Selecting Cells to Copy or Cut with Right-click Functionality
-
-
To paste the data, select the destination cell and use right-click functionality to Paste.
Figure 5-76 Select the Destination Cell and use the Right-click Functionality
Note:
Cut or copied data displays in an italicized font until calculated.
Figure 5-77 The Paste Result
Using Copy and Paste Functionality for Columns and Rows
RPASCE provides two ways to copy and paste column or row headers:
-
Context menu (right-click to access)
-
Keyboard shortcuts
Both of these functionalities have the distinct purpose of copy and paste.
Using the Context Menu to Copy and Paste
Access the context menu through right-click functionality. The copy and paste column or row header functionality allows you to transfer data at the lowest intersection level while viewing the data at aggregated level within the pivot table. This functionality is known as copy and paste special from earlier versions of RPASCE.
You can copy and paste data at the base level without editing the position hierarchy to child level. This is useful, for example, when a planner wants to copy and paste LY sales data to WP sales data at the style/color level while viewing the data sub class product level by using the context menu for copy and paste.
WARNING:
The context menu copy and paste options, Copy Column and Copy Row perform a permanent change that cannot be undone since the data is copied to server and not to clipboard.
Copying Data
Both the context menu and keyboard shortcuts allow you to copy data at two levels:
-
Copy at Selected Level—Allows you to copy at the level which is displayed in the pivot table. This level can be either base level or an aggregated (parent) level which is rolled up in hierarchy.
-
The keyboard shortcut for copying at selected level is Ctrl+Alt+C.
-
-
Copy at Base Intersection Level—Allows you to copy at the base intersection level while viewing the data at an aggregate level / parent level. If multiple levels are present on the pivot table, it copies data from the lowest level intersection.
-
The keyboard shortcut for copying at base intersection level is Ctrl+Alt+D.
-
This option is available for copy functionality:
Always copy at this level—This option enables you to choose one level at which you need to copy data. When you select the option, you will not see the copy window appearing. You can make changes to the selection from User Preferences. For more details on User Preference, refer to the section, User Preference within the Special Features chapter.
Figure 5-78 Selecting Data Level to Copy
Pasting Data
Both the context menu and keyboard shortcuts allow you to paste data at two levels:
-
Paste at Selected Level—Allows you to paste at the level which is displayed in the pivot table. This level can be either base level or an aggregated (parent) level which is rolled up in hierarchy.
-
The keyboard shortcut for pasting at the selected level is Ctrl+Alt+V.
-
-
Paste at Base Intersection Level—Allows you to paste at base intersection level while viewing the data at an aggregate level / parent level. If multiple levels are present on the pivot table, it copies data from the lowest level intersection.
-
The keyboard shortcut for pasting at base intersection level is Ctrl+Alt+B.
-
These options are available for paste functionality:
-
Do not Paste NA values—Allows you to skip missing values while pasting the data. Missing values are represented by the symbol NA (Not Available).
-
Always Paste at this level—This option enables you to choose one level at which you need to paste data. When you select the option, you will not see the copy window appearing. You can make change to the selection from User Preferences functionality.
For more details on User Preference, refer to the section, User Preference within the Special Features chapter.
-
Automatically accept column/row paste without confirmation—This option enables you to accept the confirmation on permanent changes done by column or row pasting. Selecting this option prevents the confirmation message, Changes made by pasting an entire column or row are permanent and cannot be undone, from displaying for the rest of the session. To have the confirmation display again, you can make changes in the User Preference Functionality.
For more details on User Preference, refer to the section, User Preference within the Special Features chapter.
Figure 5-79 Selecting Data Level to Paste
Key Points when Using Copy and Paste from the Context Menu
Follow these points when using the context menu for copy and paste:
-
Copy and paste of a column or row header is designed to enable the functionality within RPASCE.
-
Allows copy and paste functionality of a column or row header on mobile devices like tablets where keyboard shortcuts are unavailable.
-
Context menu selections for Copy Column and Copy Row perform a permanent change that cannot be undone since the data is copied to server and not to clipboard.
-
RPASCE allows multiple column or rows to be copied and pasted within the application.
The number of columns and rows selected for copy should be same as the number of columns and rows selected for paste. Copy and paste will not work when the column to column or row to row numbers are different.
-
Restricted cell data is skipped for column and row copy and paste.
-
Restricted cell data includes data for read only cells, locked cells, protected cells, and format mismatch
-
-
Copy and paste for a non–contiguous selection of multiple rows and/or columns is not recommended. The paste result may differ from your expected result. For more details about non- contiguous rows or columns, refer to Selecting a Group of Non-Contiguous Cells.
If the Data is Copied at the... And Pasted at the... It Results in... Parent level
Base level
Pasting the same values for all of the child levels.
The parent value at the destination is derived from aggregation method.
Parent level
Parent level in destination
The pasted value is spread to all the child level using the spread method.
Base level
Parent level
An error since the base level has a higher number of rows or columns than the destination rows or columns.
Base level
Base level in the destination
A correct paste result only occurs when the intersection of measure is same for both source and destination.
Planner Example to Copy and Paste Column and Row Headers
The following steps detail how a Planner would copy LY sales data to WP sales at the child level while viewing the data aggregated at all product levels, all locations and all calendars.
-
Click the header to select the entire column for LY Sales U.
Figure 5-80 Select the Column Header
-
Right-click to open the context menu and select Copy Column.
Figure 5-81 Context Menu Copy Column
-
Select the copy option, Base Intersection Level.
Figure 5-82 Copy at the Base Intersection Level
-
Select the destination column, WP Sales U, to paste the data
Figure 5-83 Select the Column Header to Paste
-
Right-click to open the context menu and select Paste Column.
Figure 5-84 Context Menu Paste Column
Figure 5-85 Paste at the Base Intersection Level
-
Select the paste option, Base Intersection Level.
-
The result is that the base intersection values of LY Sales U is copied to the base intersection level of WP Sales U.
Figure 5-86 Paste Result at the Aggregated Level
Figure 5-87 Result at the Base Intersection Level
The following result shows the base level of product at all Calendar and all Location.
Using Copy and Paste between RPASCE and an External System
Follow these points when using copy and paste between RPASCE and an external system:
-
Use keyboard shortcuts to copy (Ctrl+C) and paste (Ctrl+V) column or row data between RPASCE and an external application.
-
When you copy an entire row or column from RPASCE to an external application, only the grid data is copied. The row or column header text is skipped. To copy column or row headers from RPASCE to an external application, you can use the context menu option Copy Label and then use Ctrl+V to paste.
To copy and paste the entire row and/or column into RPASCE from external application, make sure to remove the header of the row and/or column, the header cell (text) is copied in RPASCE grid when pasting the data.
-
Multiple rows or columns can be copied and pasted between RPASCE and an external application.
-
Restricted cell data is skipped for column and row copy and paste.
Restricted cell data includes data for read only cells, locked cells, protected cells, and format mismatch
-
You can copy and paste the z-axis label from RPASCE to an external application by using the context menu option Copy Label. Select the label which you need to copy, then use the right-click context menu option Copy Label and use Ctrl+V to paste the label in an external application.
-
You can copy and paste multiple non-contiguous columns or rows from RPASCE to an external device. When you copy paste two or more non-contiguous columns or rows to external device, the paste result will appear in contiguous columns or rows For more details about non- contiguous rows or columns, refer to Selecting a Group of Non-Contiguous Cells.
-
Copy and paste for a non–contiguous selection of multiple rows and/or columns from and external device to RPASCE is not recommended. The paste result may differ from your expected result.
Figure 5-88 Copy Label for Column or Row Header
![Copy Label for Column or Row Header Copy Label for Column or Row Header](img/f5-89-clfcrh.jpg)
Figure 5-89 Copy Z-axis Label
![Copy Z-axis Label Copy Z-axis Label](img/f5-90-czal.jpg)
Figure 5-90 Copy Non-contiguous Columns from RPASCE
![Copy Non-contiguous Columns from RPASCE Copy Non-contiguous Columns from RPASCE](img/f5-CopynoncontiguouscolumnsfromRPASCE.png)
Figure 5-91 Paste Result on External Application
![Paste Result on External Application Paste Result on External Application](img/f5-PasteResultonExternalapplication.png)
Data Type Results between RPASCE and External Applications
When copying and pasting between RPASCE and external applications, the results differ based on the type of data that is copied and pasted. It also differs depending on if the copy or paste function is performed from or to the RPASCE and the external application as shown in Table 5-5.
Table 5-5 Data Type Results for Copy and Paste Functionality
Data Type | From RPASCE to External Application | From External Application to RPASCE |
---|---|---|
Boolean |
Results are True and False. |
To receive the desired result of True, copy the value 1 to RPASCE. To receive the desired result of False, copy the value 0 to RPASCE. |
SHS (Single Hierarchy Select Text Measure) |
Only values are pasted to external applications. For example, the copied cell value of Fall, FY2005 is pasted as s2_2005. |
Ensure that the data is copied and pasted as a value, if a label is pasted instead of the value, it will cause an error in the result. |
Date |
Copied dates lose their format and paste as a YYYY-MM-DD format. For example, the copied date 11/18/2020 pastes as 2020-11-18. |
Copied dates lose their format and paste as a MM/DD/YYYY format. For example, the copied date 18-11-2020 pastes as 11/18/2020. |
Real Number |
Unformatted values are pasted. Verify that the results are as expected. |
Formatting is applied to the pasted value. |
Picklist |
Labels are pasted to external application, not the value. For example, the copied cell has a label of one and a value of a, the paste result is one. |
Labels gets copied from external application and gets pasted to RPASCE. |
Integer |
Only integer values are pasted to external applications. |
Formatting is applied to the pasted integer value. |
Workspace Operations
The majority of the work you perform within the application occurs within a personal workspace built around a segment. These workspaces are constructed by creating a copy of the subset of the applications data described by the segment and are, therefore, to a degree, independent from the domain and its data.
There are two reasons for the creation of workspaces within the application. First, by limiting the workspace to the subset of the application's data defined within a segment, it is possible to improve the overall performance of operations. Second, the workspace serves as a sandbox where you can experiment with the data without being concerned about the effects of those experiments on the main application data.
However, the use of workspaces by the application introduces the need to manage the flow of data between the domain and the workspace through a number of workspace operations. This section describes the operations you can perform on workspaces.
Opening Workspaces
In order to continue working within a segment workspace, either select the segment from the Select Segment dialog reached from the task module or, if you have recently worked within that workspace, from the Recent Plans list.
Figure 5-92 Opening a Workspace
In Figure 5-92, one segment has been defined for the Create Merch Plan task. The segment 2023 H2_Women’s Formal Wear_MPT is selected within the dialog with an option to open it in current application browser tab or a separate browser tab.
Figure 5-93 Recent Plans
Because the 2023 Merch Plan workspace has been recently used, it is also present within the Recent Plans section of the dashboard. Click the segment name to open the segment workspace in the current browser tab. Click Open Tab (highlighted in red) to open the segment workspace in separate browser tab.
The workspace opens in a new application tab so that you may begin working with it.
Building Workspaces
Whenever you define a segment, a workspace will automatically be created based upon that segment. When you create a segment using the Segment wizard, a workspace is built after you click Finish. This serves as the initial workspace you may use to work with. Additionally, it is common practice to create a regular schedule that the system uses to automatically rebuild workspaces (usually in response to the loading of new data to the application). However, under certain circumstances, such as duplicating a segment, it may be possible that a segment you have defined will not have a workspace built and available when you wish to work in it.
Note:
To view the updates or changes in the wizardless workbooks, you should remove the existing segment and re-build the workbook again.
Figure 5-94 Building Workspaces
In Figure 5-94, the segment 2022 H2_Location Target Plan has a workspace ready for use and can be opened. The segment Location Target Plan_2022 cannot be worked in until a workspace has been built.
When this occurs, selecting a segment with no available workspace causes the system to build that workspace in an on-demand fashion. This on-demand workspace build operates like the initial workspace creation when the segment was defined and results in a notification when the workspace is ready to be opened. To build the Location Target Plan_2022 segment, select it and click Build.
Calculating Workspaces
Edits made to the cells in a view do not automatically result in updates to values affected by those edits. Instead, the propagation of changes to the workspace is deferred until an explicit action called a calculation is performed. The reasons for this are two-fold.
First, due to the large number of relationships between the measures in a workspace, a single edit might result in changes to many values. In order to prevent the application from becoming unresponsive after an edit, these resulting changes are not immediately applied until a calculation is performed.
Second, the business logic defining how values in the workspace will update based upon an edit is sophisticated enough to be able to choose between multiple ways of updating the workspace data for a set of edits or combination of edits and cell locking, depending on which measures have been edited or locked.
For example, consider the relationship between total sales (the summation of regular, markdown, and clearance sales), markdown sales, and markdown percentage. An edit to total sales results in a change to markdown sales so that the markdown percentage remains fixed. However, if both total sales and markdown sales are edited (or if one is edited and the other locked), then the markdown percentage will instead be updated based upon the edit.
In order to allow these more sophisticated methods for propagating changes to the data, the system allows several edits to be entered before their effects are evaluated in a calculation.
Once all edits desired have been entered, a calculation can be initiated by selecting the Calculate item from the Edit menu.
Figure 5-95 Calculate Workspace
Click Calculate from either the menu bar above the open views or the action tray below it or use the Calculate accelerator hotkey (F9).
When you perform a long running calculate operation, the partial refresh of the pivot table during the operation allows you to continue to see the grid data during the calculate processing, so that you can continue your analysis. However, you cannot interact with any of the menus or buttons, edit data, or scroll through the grid while the calculate operation is in progress. You can switch workspace tabs to continue working on other workspaces and you can access the left sidebar Menu (Tasks, Notifications, Reports).
As seen in Figure 5-96, you cannot interact with the area indicated by the red box. Outside of it, you can continue with your work.
Figure 5-96 Partial Refresh During Calculation
Refreshing Workspaces
When a segment is built, the workspace for that segment is initialized with the most current data in the application. However, after a segment has been built, its workspace does not automatically update to reflect changes to the application's data such as the loading of updated sales information. In order to incorporate the most recent changes to the application's data into a workspace, it is necessary to perform an operation called refreshing the workspace.
When a workspace is refreshed, a rule group known as a refresh rule group is executed in order to perform the refresh and update the data in the workspace. This rule group defines which measures in the workspace must be updated to reflect changes in the application and in conjunction with the calculation group ensure that all measures derived from refreshed measures (such as variances and other Key Performance Indicators) are updated in response to the changing data.
Note that some workspaces, mainly those associated with some administrative activities, do not define a refresh rule group and, therefore, cannot be refreshed.
Figure 5-97 Refresh Workspaces
Committing Workspaces
Once a workspace has been built for a segment, all work performed is saved within that workspace. All edits, calculations, and actions are saved within the workspace sandbox but are reflected outside of the workspace. In order for the changes made within the workspace to be available to be an input to subsequent steps in the planning process or to be exported for use outside of the application, the changes within the workspace must be applied to the domain. This process is called committing the workspace. When a workspace is committed, the values contained within it are written back to the domain in accordance to the rules defined within the commit rule group.
Because the work performed within a segment workspace is saved only within that workspace until the commit, that work can be lost if the segment workspace is recycled and rebuilt without committing the workspace. It is a common practice to set up a schedule for performing segment rebuilds (usually to coincide with the loading of new data into the domain on a regular basis), so it is important for you to know the schedule for your organization and to plan committing workspace segments around this schedule to prevent the loss of your work. In order to commit a workbook, select Commit from the Action menu.
Click Commit from the action tray below the view or Commit accelerator hotkey F8.
When you click Commit, the Commit Confirmation Message appears that allows you to accept or reject the commit as shown in Figure 5-98. To prevent the confirmation message appearing every time you commit, check the option: Always perform this action without confirmation. To have the confirmation display again, you can make changes in User Preferences.
Figure 5-98 Commit Confirmation Message
Figure 5-99 Commit
Once the workspace commit has been initiated, the system makes a copy of the current data within the workspace and prepares to commit that data back to the domain. In order to prevent data inconsistency, the system only commits a single workspace at a time. As a result, when multiple users are interacting with the application and committing segment workspaces, a delay can occur between the initiation of a workspace commit and its conclusion.
Once the workspace commit has completed, the system creates a notification to inform you that the data has been processed by the system. In the interim, you may continue to perform additional work within the workspace or you may move to another segment workspace. Additionally, a snack bar notification displays for the success or failure of the commit operation.
You can see the time of the Last Committed displayed at the bottom of workspace in the date and time format. This provides information when you have committed your data on the workbook. Post commit the data if you make edits to the data and perform a Calculate action where you are notified for uncommitted data. An Uncommitted alert displays at the bottom of the workspace next to the Last Committed time. The following figure shows an example of the last commit time and uncommitted alert.
Note:
When a custom menu linked with a commit rule is run, the uncommitted alert is only dismissed when the commit rule is successfully run.
Figure 5-100 shows an example of the notification received at the conclusion of a segment workspace commit.
Figure 5-100 Commit Notifications
![Commit Notifications Commit Notifications](img/Figure_5-100_Commit_Notifications.png)
Figure 5-101 shows the snack bar notification you receive once the commit operation is successful.
Figure 5-101 Snack Bar Notification
![Snack Bar Notification Snack Bar Notification](img/f4_sbnfco.jpg)
Figure 5-102 Last Commit Status and Uncommitted Alert
Closing Workspaces
When you are finished working in a segment workspace, you may close it by selecting the close icon on the application tab for the workspace. You do not need to save your work prior to closing the workspace, as all operations performed in the workspace (refreshes, calculations, commits, and so on) cause the workspace to be automatically saved.
Figure 5-103 Close the Workspace
Figure 5-103 shows the application tab for the open 2023 H2_Women’s Formal Wear_MPT segment workspace Click the X icon on the tab to save any outstanding edits and close the workspace.
Note:
If the browser in which the application is running is closed directly, edits that have not been calculated can be lost. Be sure to close all open segment workspaces and log out of the application prior to closing the browser window.
Closing the workspace does not commit the data to the database; it only saves the data to the specific workspace. To apply data to the database, it is required that users must click Commit.