Support for Structured Planning Cube Default Members for Dimensions in New Views, Formatted Reports, and Excel Reports

  • 3 minute(s) read
Prev Next

Article Summary

This article provides a complete, step-by-step guide to adding and using Structured Planning substitution variables and derived variables in Workforce models and reports. It explains how to generate models with variable support, use substitution variables in SpotlightXL, apply expressions, and update values at runtime, covers key settings like model setup, calculation refresh, design manager, and expression builder, and helps administrators efficiently integrate dynamic variables into Excel-based reporting, ensuring improved flexibility, easier maintenance, and consistent report results.

eddy_summary: Learn how to add and use Structured Planning substitution variables and derived variables in Workforce reports and models.
eddy_keywords: structured planning, substitution variables, derived variables, spotlightXL, workforce model, model refresh, expression builder, cube settings
eddy_intent: How-to
eddy_module: Dynamic Planning
eddy_synonyms:
substitution variables = dynamic variables, reporting variables
derived variables = calculated expressions, time-based variables
SpotlightXL = Excel interface, report design
eddy_questions:
- How do I add Structured Planning substitution variables to a Workforce model?
- What steps are required to generate or refresh a model with variable support?
- How can I use substitution variables in Excel reports using SpotlightXL?
- Can I modify substitution variable values during report execution?
- How do I insert derived variable expressions like FirstChild or Lag in Design Manager?
- What are the restrictions when using derived substitution variables at runtime?
- How do I change the current year or quarter variable for a report on the fly?
- What is the difference between substitution and derived variables in Structured Planning?

Dynamic Planning respects the Structured Planning default members according to workforce dimensions in new views, formatted reports, and Excel reports. For example, if 2024 is set as the default for the Time dimension, it will be reflected in Default and New Views, as well as in new cells in reports.

Note:

This functionality is applicable to models with Direct Access to Structured Planning, not Master models with traditional Structured Planning integration. It is supported in both Spotlight Web and SpotlightXL, but in Excel Reports, this feature is available only in SpotlightXL.

Creating Default Members

  1. Log in to Structured Planning.

  2. Go to Maintenance > Reports > Cube Settings > Default Members.

  3. Set the Reporting Area to Workforce.

  4. For each dimension, select a default member to be displayed in Structured Planning reports and new views in Dynamic Planning.

Using Default Members in a View on the Web

  1. Log in to Spotlight Web.

  2. Navigate to Dynamic Planning > Analyze.

  3. Select the workforce model and click Default view from the drop-down menu.

  4. In the Default view, the dimensions will display their default members, while other dimensions will show their root members.

Note:

Default members do not restrict the visibility of the entire dimension. Clicking on a dimension member will still allow access to the rest of the dimension.

Using Default Members in a Report

When creating a formatted report, users typically start with a view and then convert it into a report. In this process, all dimensions and members are selected in the view before conversion. However, the Report Design includes functionality to view the default members when creating a new cell, as described below.

  1. Log in to SpotlightXL.

  2. Go to Analyze > Data, and select a workforce model.

  3. Set up the view as needed and save it.

  4. Select Data > Design With > Report.
     
    This opens the Report tab with the view and comments on each cell.

  5. Click on any blank cell and then select Design Manager. The dimensions will have their default members selected by default for a Data cell.
     

    1. To change a member, select one of the dimensions on the row or column axis. The default member is automatically selected, but users can use the member select icon to choose another member. For example, if the Scenario is on the column axis, the 2025 Forecast is selected by default.
       

    2. Similarly, if POV is selected and a dimension is chosen, the default member will be selected. Users can use the member select icon to choose another member. For example, if CompensationItem is a POV dimension, All is selected by default.
       

Dimension Member Visibility

Default members do not restrict access to the rest of the dimension. Clicking on a dimension member allows access to other members of the dimension.