Dynamic Planning Analysis & Reporting

Basic Navigation Tips
For more learning resources, use Training under Get Help, and check Online Help and Community for additional references.

How to Create a View and Specify Parameters

Purpose: Create a view and set analysis parameters in SpotlightXL.

1. In SpotlightXL, choose Select Task → Analyze. 2) Go to Select Subtask → Data. 3) Use drop‑downs to pick page members. 4) To move members between page/row/column, press‑hold the drop‑down arrow and drag (e.g., drag Product Main to Actual). 5) Use Zoom In/Zoom Out to set hierarchy levels. 6) Click Get Data. 7) Go Data → Design View → Save. 8) To edit properties, click Properties, adjust, then Save.
   
   

How to Create a Report Based on a View

Purpose: Build a formatted report using an existing view (Design access required).

1. Analyze → Data. 2) Pick the model and view. 3) Use Actions to refine analysis and Save. 4) Go Data → Design With → Report (red carats on cells indicate captured intersections). 5) Format the report and Save. 6) Name the report and Save.
   

How to Use Variables in Views and Reports

Purpose: Use variables to minimize maintenance and make reports dynamic. Prerequisite: Variable exists in a formula used by a calculation.

Assign Variables to Views

1. Analyze → Design View. 2) Select model and view. 3) Actions → Properties. 4) Enter Variable Name (as used in the calculation). 5) Under Dimension, select the dimension the variable is built on. 6) Save.

Assign Variables to Reports

1. Reports → Design. 2) Select the report. 3) Actions → Properties. 4) Enter Variable Name (as used in the calculation). 5) Under Model, choose the model to map the variable to. 6) Under Dimension, pick the dimension the variable is built on. 7) Save.

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

Purpose: Analyze data in Analyze task while creating/updating views.

Drag and Drop

Move dimensions between page, row, and column.
Steps: Analyze → Data, select a dimension, press‑hold the drop‑down arrow (e.g., Product Main), drag to the target axis (e.g., Time).

Pivot command

Swap rows and columns.
Steps: Analyze → Data, select a dimension on row or column, click Pivot. Example: 2019 moves to columns and Scenario/Actual/Forecast to rows.

Zoom In

Show children of a selected hierarchy member (also via double‑click). Options: Children, Leaves, All Children, Data Leaves. Steps: Analyze → Data, select a cell, choose Zoom In option.

How to Create a Report Based Off Multiple Models

Purpose: Build one report from two models.

1. Analyze → Data. 2) Pick first model/view. 3) Data → Design With → Report (red carats indicate captured intersections). 4) Save and name the report. 5) Return to Analyze tab. 6) Pick the second model/view. 7) Actions → More → Capture Data. 8) Select desired cells. 9) Copy/paste into the saved report. 10) Format and save. 11) Design → Run, confirm Yes.

How to Filter Members Using Design Manager

Purpose: Filter members within a report using Design Manager.

1. Report → Design. 2) Select the entire sheet using the left Select All triangle. 3) Click Design Manager. 4) For the desired dimension, click Member Selector. 5) Choose Filter Member. 6) Update. (Note: You can apply this to individual cells or cell groups, not only entire reports.)

How to Preview Filter Member

1. Report → Design. 2) Select the entire sheet (Select All). 3) Design Manager. 4) Member Selector for the target dimension. 5) Choose Preview Filter Member. 6) Update.

How to Define Substitution Variables (and Use in a Report)

Purpose: Define substitution variables to replace dimension members anywhere in a report (cells, columns, ranges) and even use as calculation values.
Define variables:

1. Model → Model Administration → Substitution Variables. 2) Select the model. 3) Select the dimension the variable applies to (values load accordingly). 4) In Substitution Variable, enter a name. 5) In Value, pick a dimension member or type a value. 6) Save.
   Apply in a report:

2. Analyze → Data, open the model’s default view. 8) Use the Actions ribbon to shape data as needed. 9) Data → Design With → Report. 10) Select target cells (or one cell). 11) Design Manager, choose the member to apply the variable. 12) To the right of the chosen member, click Select Member, pick your substitution variable, then Select. 13) Update, Save and name the report. 14) To run with substitution variables: Report → Design → Run.
   

How to Perform Multi‑Tab Analysis (Display Multiple Tabs for Views)

Purpose: Open multiple views—each in a new sheet of the same workbook.

1. Analyze → Data. 2) Choose model/view. 3) Add a new sheet (+) and repeat to open other views.