Spreadsheet Body Functionalities

There are several features and functions available within the body of the spreadsheet, as described below.

Dimension Drag and Drop

To reposition dimensions within the axes of your analysis (e.g., page, row, or column), drag and drop the dimension. Dimensions are interchangeable between any axes, though at least one dimension must be present on both the row and column axes. No dimensions are required on the page axis.

Drag and drop:

No page dimensions, 3 column dimensions, and 2 row dimensions:

Copy/Paste (Ctrl+C/Ctrl+V)

You can copy and paste cells, including underlying data or dimension members, within the spreadsheet. For example, copy and paste the Scenario cell (cell C4) into cell D4. Then, select a scenario from the list box for each cell.

Direct Cell Entry

To add dimension data directly into a cell, follow these steps: If you have scenarios such as Actual and 2024 Budget, and you want to add a Variance scenario in the column next to the 2024 Budget, select the appropriate cell, enter "Variance," and then click "Refresh" to update the spreadsheet with the variance data.

Data Entry

In a view or report, you can modify displayed data. When you input a numeric value and press Enter, the cell background turns yellow, signaling that the change remains unsaved. The rules for entering numeric values align with Excel's guidelines. For instance, when inputting a value exceeding 1000, enter only the digits without using the thousands separator. If you're viewing currency figures like the Euro, Dynamic Planning recognizes and respects the thousands separator (') and decimal separator (,) as needed.

To save your data modifications, use the Save Data option. Note that only changes made to leaf-level data will be preserved. Any alterations in parent cells won't be saved when you click Save Data.

Dynamic Planning provides a warning if you try to navigate while there are unsaved changes on the screen. Additionally, if you select an option that alters the displayed data, Dynamic Planning will prompt you to save any pending modifications.

See also: Save Data.

Retain Member Selection in Alternative Hierarchies

In the Analyze task, if you have common members in both your Product Main Hierarchy and Alternative Hierarchy, and you click 'Get Data' after selecting the shared member from the Alternative Hierarchy, the Search Account member drop-down menu will display the Alternative Hierarchy member as the selected choice.

For instance, if you have 'Net Income' as a common member in both your Product Main Hierarchy and Alternative Hierarchy, and you select 'Net Income' from the Alternative Hierarchy, upon clicking Get Data, the Search Account member drop-down menu will show 'Net Income' selected from the Alternative Hierarchy. The image below illustrates the details.

List Box Search

Use the Search list box to find specific data quickly. In the example below, the Product Main dimension list displays members. When entering the search criteria, up and down arrows appear. Click these arrows to navigate the search results. In the image below, 6 members start with 'B0', with the first occurrence highlighted in grey.

The search functionality utilizes wildcard criteria. For instance, when searching for a member named '10', the results will include '10' as well as related entries like '108', '100067', and similar.

Secondary-Click Menu

In SpotlightXL, while using or designing views, you can access a popup menu by right-clicking (for right-handed users) or left-clicking (for left-handed users) on your mouse. This menu allows you to efficiently arrange and position dimensions and data cells according to your preferences. 

Within a View, selecting a dimension member cell and pressing the secondary mouse button triggers the appearance of the regular Excel popup menu, featuring a new SpotlightXL item at the top. Clicking on it reveals a submenu that includes options such as Zoom In, Zoom Out, Keep Only, Remove Only, and Pivot. Further expansion of Zoom In and Zoom Out reveals the same submenu items available on the main menu ribbon.

Use Drag and Drop, Zoom, and Pivot to Analyze Data

In this example, the drag and drop feature is used along with Zoom In and Pivot actions.

1. Select the Product dimension and drag and drop it onto Scenario.
   
   
   
2. Select the Product cell and click the Zoom In action.
   
   
   
3. Click Pivot.

Zooming In to Children, Leaves, and All Children

When navigating data within Views, Zooming In lets you view additional details for the parent member selected.

Zooming In one level to Children is the default.

Additionally, if you want to Zoom In and specify how and where to place the parent members, click More Options. From the Zoom Options box, you can combine options to lay out the view precisely.

Retaining or Not Retaining the Parent with Zoom In

Zoom In offers the ability to keep the parent members as part of the view or remove the parent members from the view. Under Zoom In, More Options, the second section lets you remove the parent members with None.

Moving the Dimension Parent to the Bottom of the View

Zoom-In offers the ability to keep the parent members above their children or move the parent members below the children. Placing parent members below the children offers the ability to have subtotals and totals below the details in your views and subsequent reports.

Drilling Through a View Based on Multiple External Source Models

When performing a drill-through on a view that pulls data from multiple External Source Models (ESM), the information shown in the drill-through report is determined by the dimensions that match the aggregated cell-level data in the dimensions and mappings of the source ESM model.

During the drill-through process, different scenarios might occur:

Situation 1: In cases where multiple source models share matching criteria, the system will dynamically select one source model at runtime. The displayed data on the drill-through screen may or may not correspond with the aggregated data observed in the original view.

Situation 2: If the user conducting the drill-through lacks access to any of the ESM models providing data for the intersecting data point, the user will view other relevant ESM models they have access to, matching the criteria. For example: 

• User A possesses permission to access an Analytic Model (named Model A) and ESM Models (named E1, E2, and E3). 
• User B has permission to access the Analytic Model (named Model A) and ESM Models (E1 and E2, but not E3).

If user A loads data into Analytic Model A from ESM models E1, E2, and E3, and user B attempts a drill-through operation on the data sourced from ESM model E3, user B will observe data from either E1 or E2 that satisfies the dimension intersection criteria.