Using SpotlightXL-Data Subtask

Overview

Within the Data subtask, users can perform various actions such as selecting models and associated views and exploring the data:

• Model: It represents a multi-dimensional structure containing both data and metadata. The Model list box displays all accessible models.
• View: Views are linked to models and serve as starting points for analysis. Default views are automatically generated upon model creation, facilitating navigation through dimensions and data layout. Custom views can be created to preserve specific dimension layouts, member selections, and data arrangements.

To access the Data subtask, navigate to the Analyze task and select the Data subtask. Then, select the View: Default list box to pick both a Model and a View. Once you have selected a model and view, features and functionalities become accessible within the spreadsheet. Actions related to data manipulation in the spreadsheet prompt additional options in the ribbon interface.

See also: How to Work with Model, View, and Report Folder and Organization Functionality.

The following features are available on the Data subtask screen:

Default Views

Default views cannot be deleted, renamed, or moved. For every model, a default layout is automatically generated, listing all dimensions. In the folder drop-down, any additional view appears under the Default view. Default views remain read-only. Data modifications using the Default view are not possible. Configure the view layout as desired, then switch to Design View and Save the view to activate operations like Save Data and Breakback.

See Create a View and Specify View Properties.

View Navigation Actions

When performing actions such as Zoom In, Zoom Out, Pivot, and Keep Only, the view changes according to the action performed.

Suppose your manager requires you to view a specific set of data. Your manager might start with a default view, manipulate the view by executing actions, save the view as a Custom view, and permit you to access it. Once you select the model, your manager's view will be available in the View list box, along with any other views you have access to. Views are particularly useful in this situation, as you don't need to replicate the same actions your manager performed to analyze the data in the required manner. Several actions can be performed on a model and a view using the options in the ribbon.

Zoom In

Under the Zoom-In action, there are specific actions, which define how the Zoom-In displays the data.

Zoom In - Double-Click

This action displays the children of the selected member within a dimension hierarchy. For example, in the image below, Zoom In is applied to the Scenario dimension. As observed, all available scenarios listed in the box are visible in the spreadsheet. Alternatively, you can choose a specific scenario from the list box if you prefer not to display all of them.

Before Zoom In:

Scenarios Available:

After Zoom In:

Zoom In - Children

This is the default action whether you simply click Zoom In or double-click.

Select a dimension and then click Zoom In, and select Children. When Zoom In and Children are applied to the Time dimension, only the highest-level parent for each year is displayed.

Zoom In - Leaves

Select a dimension and then click Zoom In, and select Leaves. When Zoom In, Leaves is selected, leaf-level members associated with the selected dimension member are displayed, and all parent members in the dimension are removed from the view. For example, if you select Scenario and click Zoom In, Leaves, the children are displayed and the parent member is removed.

Original:

Zoom In, Leaves:

Zoom In - All Children

Select a dimension and then click Zoom In, and select All Children. When Zoom In, All Children are selected for the Time dimension, and yearly, quarterly, and monthly data for all years are displayed.

When Zoom In, All Children is selected for the Time dimension, yearly, quarterly, and monthly data for 2023 and 2024 is displayed as shown below.

Zoom In - Data Leaves

Select a dimension on the row axis and then click Zoom In, and select Data Leaves. When Zoom In, Data Leaves is selected, leaf-level members associated with all the dimensions on the row axis are displayed, but only if they contain data. All parent members in the dimension are removed from the view.

For more information, see Dynamic Planning: How to Find Data Cells Quickly.

Zoom In - More Options

To perform a Zoom In action with a combination of options, first, select how far you want to zoom in from the upper section of the Zoom Options box. Then, determine what to do with the parent member in the lower section of the Zoom Options box.

You can keep the parent members above their children, move the parent members below the children, or remove the parent. Placing parent members below the children allows for the inclusion of subtotals and totals beneath the details in your views and subsequent reports.

Zoom Out

Zoom Out/Double-Click

Zoom Out to the next highest level within a dimensional hierarchy. 

Zoom In:

Zoom Out:

Zoom Out - Parent

This is the default action if you just click Zoom Out.

Select a dimension member and then click Zoom Out, and select Parent. When Zoom Out, Parent is selected for a month in the Time dimension, only the next-highest-level parent for the month is displayed.

Before Zoom Out, Parent:

After Zoom Out, Parent:

Zoom Out - Top Level

Select a dimension member, click Zoom Out, and select Top Level. When Zoom Out, the Top Level is selected for a month in the Time dimension, and the view changes to go back to the Top Level (the top or root of the dimension, or the highest member in the dimension that you have access to).

Before Zoom Out, Top Level:

After Zoom Out, Top Level:

Pivot

Swap row and column dimensions. For example, in the model below Period is column header and Account Main is a row header. Click Pivot and Account Main becomes a column header and Period becomes row headers.

Before clicking Pivot:

After clicking Pivot:

Keep Only

Keep only certain data within a view. This action works the opposite of the Remove Only action. Select a cell containing members from a dimension you want to keep and click Keep Only. All other dimension members will be removed and only the data and metadata within the selected intersection will be displayed. Data that is not kept is not permanently removed or deleted. It is just removed from the view.

Remove Only

Remove data and metadata from a view. For example, there are two scenarios (Actual and Forecast) for 2024 and you want to view the Actual scenario only. Select the Forecast cell and click Remove Only. Remove Only does not permanently remove or delete data. It just removes the intersections from the view.

Suppress Rows/Columns

Suppress rows/columns (None ), suppress rows/columns that have a zero value (Zero ), suppress rows/columns that have no data (Blank ), or suppress rows/columns that have a zero value and no data (Both - Zero and Blank ) for a displayed view.

Drill Through

This action displays detailed data for Master models loaded to Dynamic Planning through Planful Structured Planning, Consolidation, and Reporting applications or from an external data source. All users, regardless of access privileges, can drill through detailed data exposing read-only detailed information. Drill-Through is available from View and Reports. It works only on Master models created from HACPM_Financial or external data sources.

Drill-Through provides visibility into rolled up data in two ways:

• From parents whose children are present in the model.

• From parents whose children are absent in the model.

Parents with Children Present

When viewing rollup data in Dynamic Planning, if you want to see the child data items aggregated to the parent without having to zoom in and out or pivot, simply select the parent member and click 'Drill Through' for a clearer view

If the child members were not visible, you could select the data cell containing 1100 and then click Drill Through instead to see the child members.

Dynamic Planning opens another tab called DrillThrough. The tab shows the source model, the number of data items that were aggregated, the intersection details, and the list of specific data items. Click Go Back to return to the view.

Parents with Children Absent

If the model map was designed to include parent members in the target model but not include the children, Drill-Through will show the data from the child members in HACPM_Financial.

How to Use Drill-Through in SpotlightXL?

1. In SpotlightXL, select the Analyze task and the Data subtask.
2. Select a model and a view.
3. Select one or more data cells for which you want to view detail-level information.
   
4. Click Drill Through. A new tab is created in Excel called DrillThrough and detailed data is displayed.
5. Click Go Back to return to the view displayed before clicking Drill Through.

Get Data

This action updates data in the spreadsheet after selections are made. If multiple views are open, select the Get Data for All Open Views option.

Save Data

This action saves modified data to the view, provided that Enable Save is set to Yes in the view Properties. Users can save changes only to leaf-level data. If a user tries to save data changes to rollup members in the view, a message appears informing them that the changes have not been saved. The modification is visible on the screen but hasn't been saved to the model..

When the rollup notification appears, if the Save action was set to also run a calculation, the calculation will not be executed.

When the view comprises a combination of leaf-level and rollup-level cells, the validation process is not executed. Leaf-level cells will be saved, while rollup-level cells will not be saved.

Breakback

This action opens the Breakback box where you can select a spreading method and allocate changes in numeric data from the top down. If you do not see Breakback on the menu, then the view does not have Save enabled. See Enabling Menu Items in Views and Reports: Save Data and Breakback.

For details on Breakback refer to Breakback from View or Report.

More

More - Calculation

The Calculation option provides a quick way to adjust underlying Calculations and associated Formulas to achieve desired results whether in Report/View Design or Run mode. For example, let's say you have a View where you forecast units and multiply the price to result in sales. Now, you can quickly change a Calculation and associated Formula to recalculate forecasted sales to provide another sales scenario.

More - Capture Data

Select Capture Data to capture data intersection details to use when creating a formatted report from a view. Once captured, red marks are displayed in the upper right-hand corner of the cells. These red marks indicate that the coordinates of the data are recorded or "captured". The metadata intersections will be used in the report layout.

More - Capture Data as SpotlightXL Formulas

Select Capture Data as SpotlightXL Formulas to capture data intersection details to use when creating an Excel Report from a view. Once selected, all the data items show as #REFRESH and cell contents consist of SpotlightMetadata or SpotlightData formulas.

For detailed information on how to use this feature, see Creating Excel Reports.

More - Copy Formulas

Users who have SpotlightXL views and reports can copy all or part of the formulas to PowerPoint. For information on how to copy formulas, click here.

Display

Open a view and select More 

Select the Code or Label from the Display drop-down menu.

This functionality provides the flexibility to both analyze and report the data based on Code or based on Display Labels for the dimensions in the Model.

For models sourced from Structured Planning, Consolidation, and Reporting applications, the Display Label defaults to the dimension member code name. For example, if a dimension member is named Executive and the code is Exec, the Display Label would be Exec - Executive. If no name exists for the dimension member, the dimension member code is displayed.

You can set the Display Label for each report or view.

See Also: Customizing the Display Label

Number Format

Change the number format for numeric values in the displayed view to Currency or Percentage. Numeric is the default.

Currency – display all numbers in the view in currency format ($).

Percent – display all numbers in the view in percentage format (%).

Numeric – display all numbers in the view in numeric format. Numeric is the default.

See: How to Define Multiple Number Formats for Dimension Members in Views

Indent Row Members

Indent dimension members on the rows for the displayed view. Select Yes to indent or No. No is the default.

Indented Example

Width

The width of the column in the spreadsheet view is defined in pixel width. This setting applies to all columns in the view. A default setting 'Autofit' will adjust columns based on the data displayed in the columns.

See Also: How to work with Model, View, and Report Folder and Organization Functionality

Spreadsheet Body Functionality

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.

Understanding Excel Formulas in Views

For a model view, the values that result from an Excel formula are saved, although, the formula itself is not saved. For instance, in the example below, cell B6 is 13,367.

Now, an Excel formula is added to this cell as shown below.

Once saved, the resulting value is displayed, which is 36,776. The formula (=C6+C7) is not.

To save values that came from Excel formulas, View Properties (accessed by selecting the Analyze task and the Design View subtask and then clicking the Properties action) must have Enable Save set to Yes as shown below.