- Getting Started
- Structured Planning
- Structured Planning User Guide
- Planning Control Panel Intro
- Snapshot introduced in Planning Control Panel
- Scheduling Template Snapshot Creation Using Cloud Scheduler
- Template Input and View Modes
- Performing Template Input
- Template View Mode
- Template Notifications
- View User History of Any Template
- Different Template Types With Examples
- Global Templates
- Block Templates
- Workforce Planning Templates
- Structural Changes to Template
- Workforce Planning - Classic UI
- Workforce Planning - Enhanced UI
- Overview
- Field Descriptions
- Add Employees to a Workforce Template and Process
- Automated Employee Processing
- Copy Employees from One Scenario to Another
- Update Employee Data
- Search for an Employee
- View Employee Allocations in Graphical Format
- Create a Duplicate Employee
- Delete an Employee
- Delete an Employee Position
- Add a Profile Picture to Employee Profile
- Personalize the Employees list page
- How-Tos
- How to Generate Pay Plans Exception Report?
- How to Select Compensation Items for Calculating Wages?
- How to Add a Profile Picture?
- How to Add Employees?
- How to Copy Employees from One Scenario to Another?
- How to Update Compensation Items Loaded via Data Load Rules?
- How to Add Employees To a Workforce Template and Process?
- How to Generate Workforce Planning Budget Report?
- How to Remove a Value from the Value Set in Workforce Attributes?
- Capital Templates
- Initiative Templates
- Line Item Template
- Allocation Template
- Template and Entity Workflow
- Working with Reports
- Approvers
- Template Examples
- Workforce Planning UI
- Dynamic Commentary in Planning Template
- Snapshot Restore Guidelines
- Structured Planning Admin Guide
- Configuration
- Security Administration
- Currency
- Hierarchies and Entities
- Overview
- Why Hierarchies Are Needed
- Types of Hierarchies You Can Create
- Accessing the Hierarchy Management Interface
- Working in the Hierarchy Management Interface
- Setup Entity Type Hierarchies
- Budget Hierarchy and Entity Setup Examples
- How to Create a New Department as a Budget Entity
- Financial Hierarchy Overview
- Setup Finance Hierarchies
- Understanding Calculated Members
- Adding a Calculated Member to an Account Dimension
- Calculated Member Example - Creating a Member and Including it in a Dynamic Report
- Attributes and Attribute Hierarchies
- Overview
- Example of How to Use Attributes
- Adding an Attribute Hierarchy
- Defining Attributes
- Setting Up An Attribute Used in Reporting and Enabling Drill Down to Dimension Member
- Making an Attribute Available for Dynamic Reporting
- Reflecting New Attribute Levels in Reports
- Adding Another Level to Your Attribute Hierarchy
- Alternate Hierarchies
- Report Categories
- Additional Hierarchy Topics
- Load Data to Planful
- Scenario Setup
- Overview
- Description of Fields on the Scenario List Page
- Performing Scenario / Template Maintenance
- Scenario Types
- Creating a Budget Scenario
- Creating a Forecast Scenario
- Creating a Plan Scenario
- Creating a Preloaded Budget Scenario
- How to Delete a Scenario
- Alternate Approach to Data Seeding
- Global Fields
- Refreshing Actuals
- Mapping Templates to Scenarios
- Securing Scenarios
- How to Provide a User with Edit and View Access to Scenarios
- How to Provide Scenario Access to Multiple Users at Once
- Why Secure Scenarios
- How to Automatically Have Access to all Future Scenarios You Create
- When to Secure Scenarios
- How to Set Up Scenario Security for Reporting Purposes
- Clear Data
- Workforce Actuals
- How to Secure Scenarios
- Additional Scenario Topics
- Compare Scenarios
- Intelligent Data Seeding with the Plan Scenario
- Data Seeding Without the Plan Scenario
- Reporting Area Admin
- Dimension Security
- Standard Reports
- Planning Templates
- What are Templates and Why are They Needed
- Template Types
- Template List Application Page
- Time Sets - Everything You Need to Know
- How to Add a Block Template Type
- How to Add a Global Template - Single Copy Template Type
- How to Add a Global Template - Entity Copy Template Type
- How to Add an Allocation Template Type
- How to Add a Capital Template Type
- How to Add an Workforce Planning Template Type
- How to Add an HR Template Type
- How to Add a Line Item Template Type
- How to Add an Initiative Template
- Template Setup
- Understanding Template Setup
- Understanding the Template Setup Application Page
- Template Line Types and Column Types
- Understanding Line Item Template Setup
- Understanding Capital Template Setup
- Understanding Initiative Template Setup
- Template Start and End Dates
- Avoiding Line Discrepancies Between Sub and Main Templates
- Wildcard Support for Destination and Reference Account Lines
- Reference Cube
- Setting Up and Applying Global Fields to Templates (Optional)
- What are Global Fields and Why are They Used
- Map Global Fields to Templates for Budget Entity and Scenario
- Global Fields in the Planning Control Panel
- Apply Global Fields to Template Lines Accessed from the Planning Control Panel
- Map the Global Field to Another Scenario and Budget Entities
- Add a Global Field
- Mass Upload Global Fields
- Input Global Field Data
- Setting Up and Using Spreads in Templates (Optional)
- Attributes
- Template Setup Examples
- What To Do Once Template Setup is Complete
- Additional Template Information
- Simulation Engine
- User Management
- Loading Users to Planful
- Security Cheat Sheet
- User Groups
- Navigation Role and Access
- Two Step Verification
- Web Service Access
- SSO Users
- Locking Users Out of Planful
- Copying Users
- Approval Roles
- Approval Roles - What Are They and How Are They Used
- Example of a Typical Approval Role Configuration
- Approval Role Setup
- How to Add and Edit Approval Roles
- How to Bulk Export Approval Roles and Budget Entity Permissions for a Single User/All Users
- How to Bulk Import Approval Roles
- Understanding Operating Budget Template Actions for Approval Roles
- Understanding Initiative Budgeting Approval Actions
- Scenario Access
- Security for Add-Ins
- Data Integration Security
- Cloud Scheduler
- What is Cloud Scheduler and How Does It Benefit Me?
- Providing Users with Access to Cloud Scheduler
- Understanding Process Flow and Job Manager
- Description of Fields on the Process Flow Page
- How to Add A Process Flow
- Auditing Process Flows
- Overriding Substitution Variables for Report Collections and Financial Package When Running a Process Flow
- Description of Fields on the Job Manager Page
- Scheduling Template Snapshot Creation Using Cloud Scheduler
- MyPlan Guides
- MyPlan User Guide
- Accessing MyPlan
- Dissecting the MyPlan Interface
- Retained In-Memory Actions
- How Actions Are Calculated
- Adjusting, Updating, Adding, and Editing Accounts in MyPlan
- How to Add New Headcount
- How to Edit a Headcount
- How to Add Multiple Employees at Once
- How to Edit Multiple New Hires at Once
- How to Add a Headcount Based on an Existing Headcount and Edit Details
- How to Add an Asset
- How to Delete a Headcount
- How to Add an Asset Not Yet Budgeted
- How to Add An Asset for a Budget Entity When the Asset Definition Does Not Exist
- How to Edit an Asset
- How to Update an Account
- Working with Templates in MyPlan
- Activity Manager
- How to Submit a Budget/Forecast
- Template Locking
- Limitations
- MyPlan User Options
- MyPlan Admin Guide
- MyPlan User Guide
- Subject Matter Guides
- Capital Planning Admin Guide
- Reporting
- Configuring Time Sets
- Configuring Capital Planning Attributes
- Configuring Time Sets
- Adding Asset Categories
- Adding Assets
- Adding Finance Modes
- Setting Up Finance Mode Templates and Mapping Capital Budget Lines to Your General Ledger
- Adding Capital Templates
- Scenario and Entity Mapping
- User Security
- Inputting Data via the Planning Control Panel
- Initiative Planning Admin Guide
- Add an Initiative to the Initiative Template and Start Budgeting for that Initiative
- Map Attributes to Initiatives
- Open the Template in the Planning Control Panel
- Overview of Steps to Set Up Initiative Planning
- Map the Initiative Template to a Scenario(s)
- Initiative Workflow
- Set up Attributes to Map to Initiatives
- Set Up Categories
- Setup/Design the Initiative Template
- Set Up Approval Roles for the New Template
- Add an Initiative Template
- Add Attributes
- Adding Assets to Initiatives
- Workforce Planning Admin Guide
- Workforce Planning
- Define Compensation Items for Workforce Planning
- Description of Fields on the Compensation Item Page
- Understanding Pre-built Calculation Basis
- How to Set up 401k Match
- How to Update Compensation Items Loaded via Data Load Rules
- How to Copy all Compensation Items and Rates from One Scenario to Another
- How to View Compensation Items in a Report
- How to Make an Workforce Attribute Available as a Value Source
- Marginal Benefit Calculation Setup Examples
- How to Make the Bonus Expense Calculation Variable
- How Changes to Compensation Items Affect Running Scenarios
- Situations When You Can't Edit a Compensation Item
- Compensation Basis Examples
- Add Compensation Items Overview
- Description of Fields on the Add Compensation Item Page
- Understanding of Wages
- Understanding of Wages with Annual Cap
- Understanding Fixed Amount
- Understanding Fixed Amount based on Employee Attribute
- Understanding Fixed Amount per FTE
- Understanding Rate x Hour
- Understanding Tiers on Employee Attribute - Value in Wages
- Understanding Tiers on Employee Attribute - Value in Wages with Varying Annual Cap
- Understanding Tiers on Employee Attribute - Value in Hours per Period
- Understanding Tiers on Employee Attribute - Value in Hours per Period with Annual Cap
- How to Input Data Using the Direct Data Input Compensation Item
- How to Lock Compensation Items to Prevent Modification
- Linking to Global Fields
- How to Define Compensation Groups for Workforce Planning
- How to Define Workforce Attributes for Workforce Planning
- How to Define Employee Types for Workforce Planning
- Overview of Pay Plans - Workforce Planning
- How to Set Up Pay Plans - Workforce Planning
- What is Pays Setup and What is the Purpose of It
- How to Use Pays Setup to Calculate Salary Differently for Salaried Versus Hourly Employees?
- How to Map a Pay Plan at the Employee or Compensation Item Level?
- How to Complete Pays Setup - Workforce Planning?
- Employees Application Page - Workforce Planning
- Personalize the Employee list page - Workforce Planning
- Calculating Budget Review Date and Current Review Date
- Description of Fields on Employee Add Page
- Automated Employee Processing
- Employee Positions Outside of the Current Budget Cycle
- Performance Statistics - Workforce Planning Employee Page
- How to Extract Allocated Employee Information
- How to Move Employees from One Cost Center to Another
- What is Mass Update and How to Apply a Mass Update to Employees - Workforce Planning
- Pop-up Message Introduced across all the windows in Workforce Planning Setup
- Configuring Workforce Planning
- Security Administration
- Workforce Defaults
- Add Employees & Details
- Templates
- Template Mapping
- User Access
- Reporting
- Employee Type
- Budget Entities Explained
- Advance Search in Workforce Planning
- Managing Compensation Items Access and Visibility with the Workforce User Access Tab
- Workforce Planning
- Capital Planning Admin Guide
- Structured Planning User Guide
- Dynamic Planning
- Dynamic Planning Basics
- About Dynamic Planning
- Spotlight versus SpotlightXL
- User Roles
- User Role Permission, Access, and Security
- User Types
- Unified Tenant
- Guard Rails
- Online Help, Support, Community, and Solution Hub
- Supported Browsers for SpotlightXL and Spotlight
- Automatic Signout
- Frequently Asked Questions
- Unique URLs for Artifacts
- Dynamic Planning: SpotlightXL and Spotlight for Office Installation
- Overview
- Where to Download SpotlightXL and Version Information
- What Version of SpotlightXL Am I Using?
- Incompatible SpotlightXL Error
- System Requirements
- Installing SpotlightXL and Spotlight for Office
- How to Use the ClickOnce Installer for Automatic Upgrades
- How to Use the InstallShield MSI Installer
- Upgrading SpotlightXL and Spotlight for Office
- ClickOnce Flexible Deployment
- Troubleshooting Tips
- Installation Best Practices
- Dynamic Planning: SpotlightXL User Guide
- User Roles
- Dynamic Planning Analyze Task
- Overview
- Analyze
- Report
- Excel Report
- Model
- Manage
- Accessing SpotlightXL
- Username and Password Security
- Introduction to SpotlightXL
- User Roles, Access, and Security
- Quick Summary of How to Assign or Restrict Access
- Dynamic Planning: SpotlightXL User Guide: Summary
- Dynamic Planning Analyze, Data
- Dynamic Planning Analyze, Design View
- Dynamic Planning Analyze, Design With, Report
- Dynamic Planning Application Administration
- Dynamic Planning Attributes
- Dynamic Planning Cascade Reporting
- Dynamic Planning Data Locking
- Dynamic Planning Export Data
- Dynamic Planning External Source Model
- Dynamic Planning External Source Model Functions
- Dynamic Planning Groups
- Dynamic Planning How to Add a Group and User Group
- Dynamic Planning How to Add a User
- Dynamic Planning How to Backup a Model
- Dynamic Planning How to Define Substitution Variables
- Dynamic Planning How to Find Data Cells Quickly
- Dynamic Planning How to Load an External Data Source into a Master Model (Legacy)
- Dynamic Planning How to Lock Dimension Members
- Dynamic Planning How to Work with Model, View, and Report Folder and Organization Functionality
- Dynamic Planning Import and Export Data
- Dynamic Planning Manage Task
- Dynamic Planning Manage, Application Settings
- Dynamic Planning Manage, Audit Log
- Dynamic Planning Manage, Metadata Download
- Dynamic Planning Manage, Model Backup and Restore
- Dynamic Planning Manage, Model Permissions
- Dynamic Planning Manage, Process Management
- Dynamic Planning Manage, Request Status
- Dynamic Planning Model Administration
- Dynamic Planning Model Calculations
- Dynamic Planning Model Data
- Dynamic Planning Model Dimensions
- Dynamic Planning Model Formulas
- Dynamic Planning Model Lookup
- Dynamic Planning Model Maps
- Dynamic Planning Model Scopes
- Dynamic Planning Model Setup
- Dynamic Planning Model Substitution Variables
- Dynamic Planning Model Task in Excel
- Dynamic Planning Model Validation
- Dynamic Planning Multi-Tab Reporting
- Dynamic Planning Navigation Access
- Dynamic Planning Negative Amounts in Reports
- Dynamic Planning Office Reports
- Dynamic Planning Overview of Excel Reporting
- Dynamic Planning Refreshing and Formatting Excel Reports
- Dynamic Planning Repeat Headers Across Multiple Pages
- Dynamic Planning Report Design Using Substitution Variables and Expressions
- Dynamic Planning Report Task
- Dynamic Planning Report, Design
- Dynamic Planning Report, Run
- Dynamic Planning Schedule Management
- Dynamic Planning Setting Up an Assumptions Worksheet
- Dynamic Planning Setting Up Excel Reporting
- Dynamic Planning Setting Up Excel Reporting Security
- Dynamic Planning Sharing Excel Reports with People without a Dynamic Planning License Using Snapshots
- Dynamic Planning Spotlight User Guide Supported Chart Types and Formatting on the Web
- Dynamic Planning SpotlightXL User Guide Chart Formatting and Types
- Dynamic Planning Token Management
- Dynamic Planning User Groups
- Dynamic Planning User Management
- Dynamic Planning Using Substitution Variables and Expressions with Excel Reports
- Dynamic Planning View and Report Permissions
- Example Substitution Variable Expressions
- Dynamic Planning SpotlightXL User Guide Function Support
- Dynamic Planning Global Settings for View and Report Properties
- Dynamic Planning How to Add a Chart to a Report
- Dynamic Planning How to Add a Leaf Level Member to a Hierarchy
- Dynamic Planning How to Add or Delete Dimensions from a Model
- Dynamic Planning How to Apply Scope to a Model
- Dynamic Planning How to Cascade a Report
- Dynamic Planning: How to Clear a Model
- Dynamic Planning How to Create a Map
- Dynamic Planning How to Create a Report off a Model and a View
- Dynamic Planning How to Create a View and Specify Properties
- Dynamic Planning How to Create and Load External Source Models
- Dynamic Planning How to Create Dimensions as Attributes
- Dynamic Planning How to Define Dimension Security
- Dynamic Planning How to Define Multiple Number Formats for Dimension Members in Views
- Dynamic Planning How to Delete a Model
- Dynamic Planning How to Download Data from the Structured Planning, Consolidation, and Reporting Applications into Dynamic Planning
- Dynamic Planning How to Filter Members Using Design Manager
- Dynamic Planning How to Generate a Model
- Dynamic Planning How to Lock a User and Prevent Sign On
- Dynamic Planning How to Lock a Model to Prevent Modification or Clearing
- Dynamic Planning How to Lock and Unlock Scaling
- Dynamic Planning How to Lock Members in a View or Report to Prevent Modification
- Dynamic Planning How to Perform Multi-Tab Analysis
- Dynamic Planning How to Restore a Model
- Dynamic Planning How to Save Data on Attributes for Views and Reports
- Dynamic Planning How to Select Data and Member Design Manager Options to Populate Report Values
- Dynamic Planning How to Set up a Power User with Access to Everything
- Dynamic Planning How to Share a POV Dimension Across Models in a Report
- Dynamic Planning How to Specify Match Criteria
- Dynamic Planning How to Use Change Data Tracking
- Dynamic Planning How to Unlock a Locked User
- Dynamic Planning How to Use Currency Exchange Rates
- Dynamic Planning How to Use Drag, Drop, Pivot, and Zoom Actions to Analyze Data
- Dynamic Planning How to Use Excel Based Formulas
- Dynamic Planning How to Use the Metadata Viewer
- Dynamic Planning How to Use Variables in Views and Reports
- Dynamic Planning Publishing and Opening Excel Reports, Word Reports, and PowerPoint Reports
- Transferring Data from One External Source Model to Another External Source Model
- Dynamic Planning: How to Update a Dimension Hierarchy Without Clearing a Model
- Using Dynamic Commentary in Excel Reports
- Dynamic Planning: Manage, Application Statistics
- Dynamic Planning: Best Practices for Model Building, Model Refresh, and Model Restructure
- Dynamic Planning: Group Access
- How to Open a Second Instance of SpotlightXL
- Dynamic Planning: How to Configure the Number of Invalid Login Attempts
- Dynamic Planning: How to Create a Report Based Off an Existing Report
- Dynamic Planning: How to Create a Report Based Off Multiple Models
- Dynamic Planning: SpotlightXL and Spotlight for Office Installation
- SpotlightXL User Summary
- Spotlight for Google Sheets
- Dynamic Planning: Spotlight User Guide
- Overview
- Analyze
- Report
- Model
- Supported Browsers
- Accessing Spotlight
- Username and Password Security
- Dynamic Planning Spotlight User Guide Analyze Task
- Dynamic Planning Spotlight User Guide Model Task
- Dynamic Planning Spotlight User Guide Report Task
- Spotlight User Summary
- Dynamic Planning: Calculation Scheduler Option in Spotlight Web
- Dynamic Planning: Spotlight for Office User Guide
- Purpose of Spotlight for Office
- Installation Options for Spotlight for Office
- Embedding Excel Workbooks in PowerPoint and Word
- One Spotlight Login for all Office Products
- Copying Spotlight Formulas from an Excel View or Report to PowerPoint
- Creating and Formatting a Chart in PowerPoint or Word with Spotlight for Office
- Dynamic Planning: Using Excel Reporting
- Dynamic Planning: Admin Guides
- Dynamic Planning: Integration Admin Guide
- Overview
- Definitions
- Model Design - Connecting to the Structured Planning, Consolidation, and Reporting Applications
- Model Design - Setting up the Source Model in Dynamic Planning
- Model Design - Validation Report for Duplicate Members
- Model Design - Setting up the Target/Master Model using a Map
- Model Design - Target Model Design
- Populating the Source Model - Data Download
- Clearing the Source Model
- Populating the Target Model
- Viewing the Default Calculation
- Model Statistics
- Maintaining and Refreshing the Model
- Dynamic Planning: Improved Aggregation Process
- Using Drill-Through
- Views and Reports
- Writing Back Data
- Ability to Automatically Add Members When Writing Data Back From Dynamic Planning to Structured Planning, Consolidation, and Reporting
- Integration Admin Options
- Dynamic Planning: User Setup and Security Guide
- Quick Summary of How to Assign or Restrict Access
- Overview
- Assign User Group Access to Models, Reports, and Views
- Definition of Security Levels
- Set up a Reviewer User with Access to Specific Models, Views, Reports, and Dimension Members
- Set up a Power User with Access to Everything
- Set up a Contributor User with Access to Specific Models
- Dynamic Planning: Model Manager, Setup in Planful Applications
- Dynamic Planning: Best Practices for Model Building, Model Refresh, and Model Restructure
- Dynamic Planning: Integration Admin Guide
- Dynamic Planning API Library
- Base URL
- List of APIs
- Append Attributes (Master and Analytic Models)
- Sample Payload for Creating or Appending Attributes (Without Display Labels, Without Specifying the Member-Attribute Mapping)
- Sample Payload for Creating or Appending Attributes With Display Labels (Without Specifying the Member-Attribute Mapping)
- Response
- Sample Payload for Updating the Member-Attribute Mapping for Previously Saved Attributes
- Parameters
- Sample Payload for Creating or Appending Attributes and Specifying the Member-Attribute Mapping
- Append Dimension Members (Master and Analytic Models)
- Calculation Run (Master and Analytic Models)
- Calculation Status (Master and Analytic Models)
- Clear Data (Master and Analytic Models)
- Clear Leaf Data (Master and Analytic Models)
- Export Dimension / Attribute Hierarchy (Master and Analytic Models)
- Export Leaf-Level Data (Scoped) (Master and Analytic Models)
- Load Data (Master and Analytic Models)
- Clear Data (External Source Models)
- Clear Dimension Members (Metadata) (External Source Models)
- Export Leaf-Level Data (Scoped) (External Source Models)
- Load Data (External Source Models)
- Load Dimension Members (Metadata) (External Source Models)
- Append Dimension Members (Master and Analytic Models)
- Calculation Run (Master and Analytic Models)
- Clear Data (Master and Analytic Models)
- Export Dimension / Attribute Hierarchy (Master and Analytic Models)
- Export Leaf-Level Data (Scoped) (Master and Analytic Models)
- Load Data (Master and Analytic Models)
- Clear Data (External Source Models)
- Clear Dimension Members (Metadata) (External Source Models)
- Export Leaf-Level Data (Scoped) (External Source Models)
- Load Data (External Source Models)
- Load Dimension Members (Metadata) (External Source Models)
- Create Users and Groups (User Management)
- List Users and Groups (User Management)
- Dynamic Planning: Group Access API
- Dynamic Planning: Subject Matter Guides
- Dynamic Planning: Using Breakback
- Dynamic Planning: Using Excel Reporting
- Dynamic Planning: Using External Source Models
- ESM Formatting in SpotlightXL
- External Source Model Overview
- External Source Model Benefits
- List of Key External Source Model Features
- External Source Model Process
- Creating an External Source Model using Model Manager
- Creating Fields in the External Source Model using Model Manager
- Creating Formulas in the External Source Model using Model Manager
- View Formula in External Source Model
- External Source Model Field Types, Expressions, and Formulas
- Loading Data into the External Source Model
- Loading Data into External Source Models using PCR Data Load Rules
- Transferring Data from One External Source Model to Another External Source Model
- Access to ESMs
- Model Permissions for ESM Models
- Group Access to ESM Views
- Calculations in SpotlightXL Honor Group Access for ESMs
- Model Manager in Spotlight Honors User Group Security for all ESM Artifacts
- Drill Through in Views Accessed Via SpotlightXL or Spotlight Honor Group Access for ESMs
- Managing Group Access
- Step-by-Step Use Case for Forecasting Revenue Spread Over Time
- Creating a Master Model
- Viewing the Source Data
- Using a Calculation to Load Data into the Master Model
- Defining the External Source Model
- Loading Data into the Opportunity Data External Source Model
- Viewing the Data in the Master Model
- Using Views to Input Future Opportunities into the Source Model
- Defining a Map from Source Model to Master Model
- Explanation of the Lookup Function
- Explanation of the DaysFactor Function
- Backup and Restore of External Source Models
- External Source Model Best Practices
- Dynamic Planning: Using Direct Access (aka Direct Connect) to PCR Financial Reporting
- Purpose of Direct Access to PCR
- Key Benefits of Direct Access Models
- Setting Up the Connection to PCR
- Creating Users
- Creating a Direct Access to PCR Model
- Metadata Dimension Security for the Scenario Dimension
- Support for PCR Attributes, Attribute Hierarchies, and Alternate Hierarchies
- Adding Attributes, Attribute Hierarchies, or Alternate Hierarchies to Existing Direct Access Models
- Creating a Direct Access Model with Attributes, Attribute Hierarchies, or Alternate Hierarchies
- Viewing Attributes, Attribute Hierarchies, or Alternate Hierarchies in Spotlight Model Manager
- Viewing Attributes, Attribute Hierarchies, or Alternate Hierarchies in SpotlightXL Model Setup
- Support for _Attribute_Default in Direct Access to PCR Models
- Support for PCR Substitution Variables and Derived Variables
- Adding Substitution Variables and Derived Variables to Existing Direct Access Models
- Viewing PCR Substitution Variables and Derived Variables in SpotlightXL
- Creating a Direct Access Model with PCR Substitution Variables and Derived Variables
- Using PCR Substitution Variables and Derived Variables in Reports at Runtime
- Adding PCR Substitution Variables, Derived Variables, and Expressions to Reports
- Reporting and Analysis Primer
- Multiple POV Selections in SpotlightXL and Spotlight for Direct Connect Models
- Reporting Dimension Custom Labels Displayed in Direct Connect Models
- Support for PCR Cube Default Members for System-Defined Dimensions in New Views, Formatted Reports, and Excel Reports
- Support for Drill Through in Direct Connect Models in Both SpotlightXL and Spotlight
- Maintenance Options
- Information for Experienced Power and Contributor Users
- Converting a Master Model into a Direct Access to PCR Model
- Dynamic Planning Converting a Master Model into a Direct Access to PCR Model
- Using Direct Access (aka Direct Connect) to PCR Financial Reporting
- Dynamic Planning: Using Model Manager
- Overview
- Accessing the Model Manager
- Purpose of the Model Manager
- When to Use the Model Manager
- Dynamic Planning Model Manager Layouts
- Dynamic Planning Model Manager Security
- Dynamic Planning Model Manager, Creating and Editing Models
- Dynamic Planning Model Manager, Using the Model Artifacts Browser
- Modeling Model Manager, Global Model Actions with the More Menu
- Using Model Manager Summary
- Dynamic Planning: How To Tasks
- Report Collection for Dynamic Planning
- Dynamic Planning Basics
- Consolidation
- Configuration Checklist
- Prerequisites
- Intro to Consolidation
- Standard Journals
- Recurring Journals
- Dynamic Journals
- Non Controlling Interest
- Reclassifications
- Eliminations
- Validations
- Formula Validation
- Copying Journal Entry Data
- Performing Entry Actions on Multiple Entries
- Rules
- Reporting
- Consolidation Status
- Consolidation Functionality Changes
- Consolidation User Options
- Consolidation Control Panel
- When Consolidation Is Needed
- Configuration Tasks
- Security Administration
- Set Up Currency
- Difference Between Common Currency, Local Currency, and Interim Currency
- Currency Use Case - Common Currency vs. Local Currency
- How to Set Up Currency and Currency Type
- What are Currency and Currency Type Exceptions and When Do I Need Them
- Currency Exchange Rate
- Interim Currency
- Currency Exception
- Hyperinflationary Economy
- Calculation Exceptions
- CTA
- Reporting Currency
- Entity Currency
- Currency Setup Summary
- Standard Reports
- Setting Up Hierarchies
- Finance Hierarchy
- Reporting Members
- Calculated Members
- Alternate Hierarchies
- Dated Hierarchies
- Sibling Hierarchies - Multiple Set of Books
- Source Hierarchies Used to Create Sibling Hierarchies
- How to Create a Sibling Hierarchy Using a Sibling Hierarchy as the Source
- What are Sibling Hierarchies and Why Do I Need to Create Them
- How to Create a Sibling Hierarchy Using the Main Company Hierarchy as the Source
- How to Add Members to Sibling Hierarchies
- Report Categories
- Hierarchy - Reporting, Entity, & Interim Currency
- Additional Hierarchy Topics
- Best Practices
- Why Hierarchies Are Needed
- Types of Hierarchies Explained
- Accessing the Hierarchy Management Interface
- Exploring the Hierarchy Management Interface Panes
- Exploring the Top Ribbon
- Exploring the Right Pane - Member Properties
- Exploring the Right Pane - Default Properties
- Exploring the Right Pane - Segment Properties
- Exploring the Right Pane - Interim Currencies
- Exploring the Right Pane - User-Defined Attributes
- Setting Up Hierarchies Summary
- User Management
- Administration
- Journals
- Non Controlling Interest
- Reclassifications
- Eliminations
- Validation
- Consolidation Process
- Cloud Scheduler for Consolidation Admin
- What is Cloud Scheduler and How Does It Work?
- Providing Users with Access to Cloud Scheduler
- Understanding Process Flow and Job Manager
- Cloud Scheduler for Consolidation Admin Summary
- Cloud Scheduler for Standard Journals
- Cloud Scheduler for Recurring Journals
- Cloud Scheduler for Reclassification Journals
- Consolidation - Income Statement Retained Earnings Account & Retained Earnings Roll Forward
- Security Overview
- Centralized Security
- What are Alternate Hierarchies and How are They Used
- What are Dated Hierarchies and How are They Used
- How to Add a Calculated Member to an Account Dimension
- How to Use Cloud Processing for Consolidation Hierarchies
- Load Data to Planful
- Actual Data Templates
- Translations Setup
- Export Data / Export Data Setup
- Transaction Details
- Data Calculation Exceptions
- Additional Data Load Topics
- Verify Data
- How to Verify Data was Loaded to Planful
- Description of Fields on the Verify Data Page
- Data Load History
- How to Load Zeros Through Data Load Rules to Reflect in a Template
- Best Practices for Loading Data
- Data Load Missing Segment Member - Opt In Feature
- Auditing Data Integration
- Limitations when Loading Data
- Overview and Cheat Sheet
- Loading Data to Planful Summary
- Scenario Setup
- Overview
- Description of Fields on the Scenario List Page
- Performing Scenario / Template Maintenance
- Why do I Need to Lock a Scenario
- How to Lock a Scenario
- How to Unlock a Scenario
- Scenario Types
- How, Why and When to Refresh Actuals (Closed Period Data)
- How, Why, and When to Refresh Preloaded Data for Preloaded Scenarios
- How, Why, and When to Secure Scenarios
- How to Provide a User with Edit and View Access to Scenarios
- How to Automatically Have Access to all Future Scenarios You Create
- How to Set Up Scenario Security for Reporting Purposes
- How to Create a Scenario Based on Two Existing Scenarios
- Data Seeding
- Scenario Setup Summary
- How to Provide Scenario Access to Multiple Users at Once
- How to Delete a Template from a Locked Scenario
- How to Add a Report Category
- What is Two Step Verification, How Does It Work, and How Do I Enable It
- How to Add a User Group
- What Is Elimination Functionality
- How to Set Up an Elimination Company
- Loading Data with An Elimination Company
- How-tos?
- How to Setup for Enhanced Retained Earnings Roll Forward?
- How to Configure Retained Earnings Roll Forward?
- How to Close with Consolidation?
- How to Process Consolidation With Configurable Steps?
- How to Unlock Multiple Periods in Consolidation Process?
- How to Lock Multiple Periods in Consolidation Process?
- How to Provide Security for Centralized Consolidation Users?
- How to Provide Security for Decentralized Consolidation Users?
- How to Disable Display of Security Option from the Consolidation Control Panel?
- References
- Reports
- Reports User Guide
- File Cabinet
- Report Sets
- Dynamic Reports
- Overview
- Composition of a Dynamic Report
- Dynamic Report Setup Functionality
- Formatting a Dynamic Report - Overview
- How to Access the Format Pane
- An Explanation of Standard Formatting Functionality
- Cell Level Formatting Explained
- How to Reset Formatting Selections
- Situations When Formatting is Disabled or Can't be Applied
- How to Resize and Save Groups of Columns in a Dynamic Report
- Currency Display in Dynamic Reports
- Automatic Data Refresh
- Template Notes in Dynamic Reports
- Overview
- How to Display Template Notes in a Dynamic Report
- When Templates Notes Will NOT Display
- Template Notes in the Operational Planning Tab of a Dynamic Report Drill Through Report
- Dynamic Report Output Functionality
- How to Export to Excel All Members in a Rollup in a Dynamic Report
- Template Notes in DR
- Dynamic Commentary
- How to Post a Comment
- Dynamic Reports Mapped to Planning Templates
- Dynamic Reports Mapped to Planning Templates Overview
- Copying Dynamic Reports
- Print Dynamic Report
- How to Create and Modify Dynamic Reports
- Build and Run a Dynamic Report
- Enhanced Drill Through in Dynamic Reports
- Template Notes in Dynamic Reports
- How to Display Template Notes in a Dynamic Report
- Exporting Dynamic Reports Output in Unprotected View
- How to Modify a Dynamic Report from the Template Interface Accessed from the Planning Control Panel
- Integrating Dynamic Reports with Financial Package Publisher
- Drilling Capabilities
- How to Create and Modify Dynamic Reports Summary
- Dynamic Reports Use Case
- Setting Up and Using Substitution Variables
- Best Practices
- Limitations
- Overview
- How to Use Substitution Variables in Dynamic Reports
- How to Set Up Substitution Variables
- Modify the Look and Feel of Dynamic Report Output
- Best Practices and Tips
- Dynamic Reports Options
- Report Collection
- Financial Package
- Spotlight for Office
- Overview
- How to Log Into Spotlight for Office
- Copying Spotlight Formulas from an Excel View or Report to PowerPoint
- Copying Spotlight Formulas from an Excel View or Report to Word
- Copying Spotlight Formulas to PowerPoint using Copy Formulas
- Copying Spotlight Formulas to PowerPoint using Design PowerPoint Report
- Using Spotlight for Office
- Reports Administration Guide
- Configuration
- Security Administration
- Currency
- Hierarchies
- Load Data to Planful
- Setting Up Scenarios
- Reporting Roles
- File Cabinet
- Dimension Security
- Point of View
- Substitution Variables
- Lock Data
- Standard Reports
- Reporting Scenario Setup
- Cube Settings
- Workforce Reporting
- Workforce Reporting Enhanced Notifications
- Process Reporting Area
- Spotlight for Office
- Limitations
- Reports User Guide
- Dashboards
- Dashboards User Guide
- What are Dashboards
- Why Use Dashboards
- When to Use Dashboards
- How to Use Dashboards
- Dashboard Designer
- Using Consider Absolute Value Denominator Option for KPI Chart
- Using Dynamic Planning Data in a Dashboard
- Dynamic Report Chart
- Dashboard Read
- Saving Dashboards
- Refreshing Dashboards
- Using Filters
- Custom Color Schemes
- How to Build a Workforce Dashboard
- Setting Dashboards as Your Homepage
- Best Practices
- View History from Dashboards
- Dashboards User Options
- Dashboards Administration Guide
- Providing Users with Navigation Access to Dashboards
- Dashboards Reporting Area
- Use Dynamic Planning Data in Dashboards
- Dashboards Dimension Security
- Dashboards Scenario Security
- Dashboards Budget Entity Security
- Substitution Variables
- Pre-Defined Custom Members/Formulas
- Dashboard Sharing
- Edit Versus View Access to Dashboards
- Dashboards Versus Dynamic Reports
- Dashboard Updates
- Recommended Dashboards Screen Resolution
- Auditing Dashboards
- Best Practices
- Dashboards Administration
- Dashboards User Guide
- Predict
- Predict Signals
- Business Value
- Enabling Predict Signals
- Model Training
- Pre-computation on Scenarios
- Accessing Predict: Signals
- Predict: Signals in Dimension based Dynamic Reports
- Predict: Signals on Templates
- Predict: How Signal Generation Works
- Signals Overview
- Signals Donut Charts
- Signals Context Pop-up
- Limitations for Signals Generation
- Feedback and Suggestions
- Predict Projections
- Predict Signals
- Budget Manager Experience
- Maintenance
- Administration
- User & Role Management Admin Guide
- Purpose of User and Role Management
- How to Access the User and Role Management Application Page
- Loading Users to Planful
- User Access/Security Overview
- Navigation Role and Access
- Exporting Navigation Role Summary and Detailed Reports
- Manually Add a User to Planful
- Locking Users and Preventing Sign On to Planful
- Copy An Existing User
- Approval Roles
- Scenario Access
- Consolidation Security
- User Groups
- Add-In Security (Optional)
- Data Integration Security
- Report Access
- Dimension Security Configuration
- Workforce Reporting Security
- Understanding the User Management Interface - Navigation Role
- Understanding the User Management Interface - Approval Role
- Understanding the User Management Interface - User Add Page
- Understanding the User Management Interface
- Offline Planning
- Understanding Scenario Setup
- Global Fields
- Copying Actual Data from Closed Periods
- Plan Scenarios
- Refreshing Actuals / Closed Period Data
- Refreshing Preloaded Data for Preloaded Scenarios
- Creating One Scenario Based on Two Existing Scenarios
- Deleting and Reloading All Employees in the Default Scenario
- Intelligent Data Seeding with the Plan Scenario
- Scenario Workflow
- Data Seeding
- Mapping Templates to Scenarios
- Securing Scenarios
- Compare Scenarios
- Budget Scenarios
- Simulation Engine
- Forecast Scenarios
- Security Administration
- Understanding Configuration Tasks
- Ivy Configuration
- How to Access the Configuration Tasks Page
- General Financial Configuration - Required
- Define Capital Budgeting Criteria - Optional
- Define Workforce Planning Budgeting Criteria - Optional
- Data Integration Configuration
- Configuring Planful Connector for Power BI
- Define Login Restrictions - Optional
- Collaboration Configuration - Optional
- Define Reporting Areas
- Consolidation
- Workflow Configuration
- Workdays Setup
- Cloud Scheduler
- What is Cloud Scheduler
- How to Provide Users with Access to Cloud Scheduler
- Understanding Process Flow and Job Manager
- Description of Fields on the Process Flow Page
- How to Add A Process Flow
- Auditing Process Flows
- Overriding Substitution Variables for Report Collections and Financial Package When Running a Process Flow
- Description of Fields on the Job Manager Page
- Customize Branding
- Comments Manager
- MyPlan Admin Guide
- User & Role Management Admin Guide
- Multi-Currency
- Currency Admin Guide
- Difference Between Common Currency, Local Currency, and Interim Currency
- Currency Setup
- Currency Type Setup
- Currency Type and Currency Type Exceptions
- Currency Exchange Rates
- Profile - Decision Hub
- Mapping - Decision Hub
- Decision Hub Summary
- Loading Exchange Rates Using Data Load Rules
- Viewing Exchange Rates on a Single Excel Sheet
- Currency Exception Setup
- Hyperinflationary Setup
- Currency Calculation Exception Setup
- Mass Loading Currency Exceptions
- Mass Load Exceptions - Detail View Page & Excel Error File
- Currency Use Case - Common Currency vs. Local Currency
- Interim Currency
- CTA Sets
- Reporting Currency
- Entity Currency
- Currency Admin Guide
- Hierarchy Management
- Hierarchy Management Admin Guide
- Why Hierarchies Are Needed
- Types of Hierarchies
- Hierarchy Management UI
- Entity Hierarchies
- Financial Hierarchies
- Attributes & Attribute Hierarchies
- Alternate Hierarchies
- Dated Hierarchies - Org by Period
- Sibling Hierarchies
- Report Categories
- Currencies
- OLAP Cube Configuration
- Auditing Hierarchies
- Dimension Locking
- Updating Reports with Changes Made to Accounts
- Hierarchies Management for Dynamic Planning
- Versioned Hierarchy Management in Dynamic Planning
- Disallowed Strings
- User Access to Hierarchies
- Usage Report
- Best Practices
- Hierarchy Management Admin Guide
- Planning Templates
- Planning Templates Guide
- What are Templates and Why are They Needed
- Template Types
- How to Access the Template List Page
- Description of Fields on the Template List Page
- Time Sets
- What Are Time Sets?
- How to Configure Time Sets
- How to Define a Time Set
- How to Set the Default Time Set
- How to Copy a Template and Add Time Set Functionality to the Template
- How to Copy Template Formula Changes to Different Scenarios
- How to Make a Copied Template with a New Time Set Available for Budgeting
- Data Allocation with Time Sets Recommendations
- Templates Based on Template Type
- Block
- How to Add a Global Template - Single Copy Template Type?
- How to Add a Global Template- Entity Copy?
- How to Add an Allocation Template Type?
- How to Add a Capital Template Type?
- Workforce Planning
- How to Add an HR Template Type?
- How to Add a Line Item Template Type?
- How to Add an Initiative Template?
- Template Setup
- Line Item Template Setup
- Capital Template Setup
- Initiative Template Setup
- Template Start and End Dates
- Avoiding Mismatched Lines Between Sub and Main Templates
- What is Reference Cube
- Rules for Template RC Lines
- Global Fields
- How to Add a Global Field
- How to Input Global Field Data
- How to Map a Global Field to Another Scenario and Budget Entities
- Working with Global Fields in the Planning Control Panel
- How to Apply Global Fields to Template Lines Accessed from the Planning Control Panel
- When and How to Map Global Fields to Templates for Budget Entity and Scenario
- Spreads
- Attributes
- Template Setup Examples
- What To Do Once Template Setup is Complete
- How to Provide Users with Scenario Access
- How to Create an Approval Role and Provide Users with Approval Role Access
- Displaying Currency Property Changes In Hierarchy Management In Templates
- Planning Templates Guide
- Workforce
- Capital Planning
- Capital Planning Admin Guide
- Scenario and Entity Mapping
- Adding Asset Categories
- Adding Finance Modes
- Reporting
- User Security
- Configuring Time Sets
- Adding Assets
- Adding Capital Templates
- Setting Up Finance Mode Templates and Mapping Capital Budget Lines to Your General Ledger
- Configuring Capital Planning Attributes
- Inputting Data via the Planning Control Panel
- Capital Planning Admin Guide
- Rules
- Simulation Engine Guide
- What is Simulation Engine?
- When to Use Simulation Engine
- Why Simulation Engine is Needed
- How to Create a Simulation Engine Process
- How to Add Template - Entity Combinations to a Simulation Engine Process
- How to Schedule a Simulation Engine Process via Cloud Scheduler
- Copying Simulation Engine Processes From the Default Scenario to Other Scenarios
- Additional Features Available for Simulation Engine
- Simulation Engine Guide Summary
- FAQs
- Simulation Engine Guide
- Data Integration
- Admin Guide to Loading Data to Planful
- Data Integration Admin Guide
- Web Services / Boomi
- Integration Services
- Editing, Deleting and Copying Integration Service Processes
- Adding an Integration Service Process
- Working with Custom Parameters for Integration Service Processes
- Providing Navigation Access to the Integration Services Application Page
- Completing the Integration Services Configuration Task
- Introduction
- Cloud Services
- Google Drive
- NetSuite Integration
- Data Load Rules
- Clear Data
- DLR Locking
- Transaction Details
- Actual Data Templates
- Translations Setup
- Actual Data Template Setup
- Add Actual Data Templates
- Best Practices for Loading Data
- API Library
- Introduction
- Data Import
- Data Export
- Workiva Integration
- Objects Overview
- API Compliance
- Client Application Platforms
- API Access Keys
- API Calls - Overview
- Core API Calls
- Utility Calls
- Objects
- API Methods
- Load_Data API
- Transfer_Data API
- Clear_Data API
- Segment APIs
- GLData_Retrieve API
- Login API
- GetSessionId API
- GetVersion API
- GetAllCurrencies API
- GetAllCurrencyTypes API
- GetAllSegments API
- Logout API
- Employee APIs
- Approval Role Setup APIs
- Scenario Access APIs
- Dimension Member Name Update API
- Bulk User and User Group Extract API
- Dimension Security APIs
- Dimension Security Update API
- MapEntity API
- Refresh Preloaded Data API
- Best Practices and Tips
- Enumerations
- Transfer Data REST API
- Transaction Extract Data APIs
- Planful API Library
- Source Segment API
- Report Administration
- Report Administration Guide
- Point of View
- Setup Reporting Scenario
- Lock Data
- Process Reporting Area
- Dimension Security
- Cube Settings
- Standard Reports
- Description of Each Standard Report
- How to Run User Security Report?
- How to Run the CTA Detailed Report?
- How to Execute Pay Plans Report?
- How to Run a Capital Budget Analysis Report via Standard Reports?
- Standard Reports
- Deploying Standard Reports for End-Users
- Report Groups
- Understanding Repeat Labels
- Data Sources
- Verify Data
- Report Administration Guide
- Application Audit
- Administration
- Release Updates
- Release Notes
- 2025 Release Notes
- 2024 Release Notes
- 2023 Release Notes
- 2022 Release Notes
- 2021 Release Notes
- 2020 Release Notes
- 2019 Release Notes
- 2018 Release Notes
- 2017 Release Notes
- December 17 Maintenance Release
- 2.3.1 Modeling Release Notes, December
- Fall 17 Release Notes
- 2.3 Modeling Release Notes, November
- October 17 Maintenance Release
- 2.2.2 Modeling Release Notes, October
- September 17 Maintenance Release
- Spring 17 Release Notes
- Summer 17 Release Notes
- 2.1.2.2 Modeling Release Notes, July/August
- July 17 Release Notes
- 2.1.2 Modeling Release Notes, July
- June 17 Release Notes
- 2.1.1 Modeling Release Notes, June
- 2.1 Modeling Release Notes, May
- April 17 Release Notes
- 2.0.2 Modeling Release Notes, April
- March 17 Release Notes
- 2.0.1 Modeling Release Notes, March
- Winter 17 Release Notes
- 2.0 Modeling Release Notes, February
- January 17 Release Notes
- 1.9.2 Modeling Release Notes, January
- 2016 Release Notes
- Planning/Consolidation/Reporting December 16 Release Notes
- 1.9.1 Modeling Release Notes, December
- Planning/Consolidation/Reporting Fall 16 Release Notes
- 1.9 Modeling Release Notes, November
- Planning/Consolidation/Reporting October 16 Release Notes
- 1.8.2 Modeling Release Notes, October
- Planning/Consolidation/Reporting September 16 Release Notes
- 1.8.1 Modeling Release Notes, September
- Planning/Consolidation/Reporting Summer 16 Release Notes
- 1.8 Modeling Release Notes, August
- Planning/Consolidation/Reporting July 16 Maintenance Release
- 1.7.2 Modeling Release Notes, July
- Planning/Consolidation/Reporting June 16 Maintenance Release
- 1.7.1 Modeling Release Notes, June
- Planning/Consolidation/Reporting Spring 16 Feature Release
- 1.7 Modeling Release Notes, May
- Planning/Consolidation/Reporting April 16 Maintenance Release
- 1.6.2 Modeling Release Notes, April
- Planning/Consolidation/Reporting March 16 Maintenance Release
- 1.6.1 Modeling Release Notes, March
- Planning/Consolidation/Reporting Winter16 Release
- 1.6 Modeling Release Notes, February
- Planning/Consolidation/Reporting January 16 Maintenance Release
- Enhancements to Application Behavior
- 1.5.2 Modeling Release Notes, January
- Release Notes
- Additional Resources
- 14 Minutes to read
- Print
- DarkLight
- PDF
Substitution Variables
- 14 Minutes to read
- Print
- DarkLight
- PDF
Overview
Substitution variables are used in the creation of Dynamic Reports and Report Sets. A substitution variable replaces a complex multi-dimensional expression (Rule) with a specified value. Substitution variables must be added to Report Sets as lines to be reflected in reports. Planful provides over 40 system-defined substitution variables.
The purpose of using a substitution variable is to:
Improve report performance
Improve Report Set maintainability
Dynamically display report column/row headers
To access the Substitution Variable page, navigate to Maintenance > Reports > Cube Settings and click the Substitution Variable tab.
Description of Fields
Add (Substitution Variable)
To add a substitution variable to use in a reporting formula:
- Enter an identifier in the Code field.
- Select the reporting area where you want the substitution variable defined.
- Select the dimension where you want the substitution variable defined.
- Select the Substitution Value by opening the hierarchy dialog page. Use the hierarchy or filter functions to search for, and select, a specific value.
For information on system-defined substitution variables, click here.
Derived Variables
Click the Derived Variable button on the Substitution Variables page.
Derived Variables contain a formula that references a Substitution Variable and derives an appropriate value by evaluating the formula.
In the example below @CURYR@ is the substitution variable, which displays the current year for the Workforce reporting area. Derivatives of @CURYR@ are:
@CURYR+1@ = pulls data for the year following the current year
@CURYR+2@ = pulls data for 2 years from the current year
For information on system-defined derived variables, click here.
Grid Fields
Code | The name of the substitution variable as used in reporting formulas. |
Reporting Area | The reporting area where the substitution variable is defined. |
Dimension | The dimension where the substitution variable is defined. |
Value | The value a substitution variable points to. |
Derived Variables | The number of derivatives for the selected variable. |
Understanding Substitution Variables
A substitution variable contains a Name and Value pair. For example:
Substitution Variable Name: @CURMNTH@
Substitution Variable Value: Jan-2018
The Substitution Variable Name is replaced by its Value when the report is executed, thereby making reporting flexible. In the example above, you may create a report column using a the @CURMNTH@ Substitution Variable. When the report is executed the Name is substituted by its Value (i.e. Jan-2018).
Let's say you want to create a Rule for the following:
Prior Year Quarter 1
Prior Year Quarter 2
Prior Year Quarter 3
Prior Year Quarter 4
This is how the Rule would look:
Q1 Prior year – Openingperiod(Ancestor(Parallelperiod(year,1,Time.CurrentMember),year))
Q2 Prior year - Openingperiod(Quarter,Ancestor(Parallelperiod(year,1,Time.CurrentMember),year)).lead(1)
Q3 Prior year - Openingperiod(Quarter,Ancestor(Parallelperiod(year,1,Time.CurrentMember),year)).lead(2)
Q4 Prior year - Openingperiod(Quarter,Ancestor(Parallelperiod(year,1,Time.CurrentMember),year)).lead(3)
You can also create prior year quarters as derived variables on the @CURQTR@ substitution variable:
@PRYRQTR1@ - Openingperiod(Quarter,Ancestor(Parallelperiod(year,1,@CURQTR@),year))
@PRYRQTR2@- Openingperiod(Quarter,Ancestor(Parallelperiod(year,1,@CURQTR@),year)).lead(1)
@PRYRQTR3@- Openingperiod(Quarter,Ancestor(Parallelperiod(year,1,@CURQTR@),year)).lead(2)
@PRYRQTR4@- Openingperiod(Quarter,Ancestor(Parallelperiod(year,1,@CURQTR@),year)).lead(3)
Attributes and attribute hierarchies mapped on the Attribute Settings page are available in the Financial Reporting Area. Attributes mapped to properties are not available.
Types of Substitution Variables
- Standard Substitution Variables: Standard Substitution Variables point to a value, which can be updated by a Reporting Administrator using the Substitution Variable page located under the Maintenance menu. Substitution Variables can be established for any of the dimensions or attributes available in any of the reporting areas.
Example: Variable @CURYR@ contains the year value 2018, which internally points to the appropriate Time dimension member such as [Time].&[5045]. The value is used on column headers and the dimension member is used in expressions.
- Derived Variables: Derived Variables contain a formula which references a Substitution Variable and derives an appropriate value by evaluating the formula.
A Derived Variable named ‘@CURMTH -1@’ can:
Reference the following Substitution Variable: @CURMTH@
Contains a formula, such as: @CURMTH.Lag(1).
Will result in the month preceding the Current Month based on setup.
At run time @CURMTH.Lag(1) is evaluated based on the value of @CURMTH@. Generally, the value is used for column headers and the dimension member is used in expressions.
How to Set Up Substitution Variables
- Navigate to Maintenance > Reports > Cube Settings and click the Substitution Variables tab.
- Review the list of substitution variables defined in the application.
- Click the Add button to setup a new substitution variable on the Add Substitution Variable page.
- Complete the fields on the Add Substitution Variable page. Note that substitution variables are defined for a reporting area and dimension.
- Select the newly added substitution variable on the list page and click the Derived Variables button to view the corresponding derived variables.
- Add new derived variables by clicking the Add button on the Derived Variables list page.
- The Add Derived Variable page is opened. Complete the fields on this page.
How to Use Substitution Variables
Substitution variables are used in the creation of Dynamic Reports. A substitution variable replaces a complex multi-dimensional expression (Rule) with a specified value. Substitution variables must be added to Report Sets as lines to be reflected in reports. Planful provides over 40 system-defined substitution variables.
Here is a list of places where you can use substitution variables:
In rules and on row or column labels and headers.
To dynamically display report set lines.
As an efficient way to replace formula exceptions: For example, display the current period in the column header of a report by replacing the column set’s line name with a substitution variable, such as @CURMTH@. When the report is run, @CURMTH@ will be replaced with the actual month name Mar-19.
Combined with static text: For example, YTD~ @CURMTH@ will display YTD~ Jan-18 and @CURYR@Actual will display 2018 Actual.
To improve report performance by avoiding the use of calculated or measured dimensions when building column sets: For example, the following Report Set is commonly used.
YTD
MTD
Actual
Budget
Variance
Actual
Budget
Variance
Setting up this Report Set requires the usage of complex Rules that typically require a column set on a Calculated or Measured dimension and utilizes the Reporting scenario for the Budget column. The following table displays commonly used formulas to deliver report columns.
Line Name | Formula Without Substitution Variables |
---|---|
YTD~Actual | ([Measures].[YTD],[Scenario].&[1],[Calculated].&[1]) |
YTD~Budget | ([Measures].[YTD],StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[Calculated].&[1]) |
YTD~Variance | Custom Formula |
MTD~Actual | ([Measures].[MTD],[Scenario].&[1] ,[Calculated].&[1]) |
MTD~Budget | ([Measures].[MTD],StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[Calculated].&[1]) |
MTD~Variance | Custom Formula |
Line Name | Formula Without Substitution Variables |
YTD~Actual | ([Measures].[YTD],[Scenario].&[1],[Calculated].&[1]) |
YTD~Budget | ([Measures].[YTD],StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[Calculated].&[1]) |
YTD~Variance | Custom Formula |
MTD~Actual | ([Measures].[MTD],[Scenario].&[1] ,[Calculated].&[1]) |
MTD~Budget | ([Measures].[MTD],StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[Calculated].&[1]) |
MTD~Variance | Custom Formula |
While the rules above deliver the expected result, they are complex. This complexity adversely impacts report performance. The same report can be set up more efficiently using Substitution Variables. The table below displays how Substitution Variables compare to the formulas in the table above.
Line Name | Formula with Substitution Variables |
---|---|
YTD~Actual | ([Scenario].&[1] ,[Measures].[YTD]) |
YTD~Budget | (@CURBGT@,[Measures].[YTD]) |
YTD~Variance | Custom Formula |
MTD~Actual | ([Scenario].&[1],[Measures].[MTD]) |
MTD~Budget | (@CURBGT@,[Measures].[MTD]) |
MTD~Variance | Custom Formula |
Performance for the Report Set depicted in the table above can be improved by moving the repeated member on the Page axis. The Report Set above uses [Measures].[MTD] and [Measures].[YTD] frequently. Move one of these members to the page level. For example, use the member [Measures].[YTD] on the Page axis rather than repeating [Measures].[YTD] on the first and second lines as shown in the table below.
Line Name | Formula with Substitution Variables |
---|---|
YTD~Actual | [Scenario].&[1] |
YTD~Budget | @CURBGT@ |
YTD~Variance | Custom Formula |
MTD~Actual | ([Scenario].&[1],[Measures].[MTD]) |
MTD~Budget | (@CURBGT@,[Measures].[MTD]) |
MTD~Variance | Custom Formula |
- To improve report performance instead of reporting scenario functionality: For example, when using reporting scenario functionality to retrieve the budget and forecast for a selected year, you might use the following rule: StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]"))
While the rule above delivers the expected result, it slows report performance. A performance improvement is to use Substitution Variables as described below.
Line Name | Previous Approach to Formula | Formula with Substitution Variables |
---|---|---|
Current Budget | StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")) | @CURBGT@ |
Current Forecast | StrToMember ("Scenario.&["+Time.Currentmember.properties("forecast_Id")+"]")) | @CURFCST@ |
Current Month Budget | (Time.Currentmember,StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[CalculatedDim].&[1]) | (@CURMTH,@CURBGT) |
Prior Year YTD | (Parallelperiod(Year,1,Time.currentmember),[Measures].[YTD]) | (@PREVYEAR@,[Measures].[YTD]) |
- To improve efficiency in building report sets. Substitution Variables aide in the setup and maintenance of time-based Report Sets, such as Trailing 12 months, along with Budget and Forecast columns. The following example displays a Trailing 12-month Report Set:
Line Name | Previous Approach to Rule | Rule with Substitution Variables |
---|---|---|
Current Month | (Time.Currentmember,[CalculatedDim].&[1]) | @CURMTH@ |
Current Month - 1 | (Time.Currentmember.Lag(1),[CalculatedDim].&[1]) | @CURMTH-1@ |
Current Month - 2 | (Time.Currentmember.Lag(2),[CalculatedDim].&[1]) | @CURMTH-2@ |
Current Month - 3 | (Time.Currentmember.Lag(3),[CalculatedDim].&[1]) | @CURMTH-3@ |
Current Month - 4 | (Time.Currentmember.Lag(4),[CalculatedDim].&[1]) | @CURMTH-4@ |
Current Month - 5 | (Time.Currentmember.Lag(5),[CalculatedDim].&[1]) | @CURMTH-5@ |
Current Month - 6 | (Time.Currentmember.Lag(6),[CalculatedDim].&[1]) | @CURMTH-6@ |
Current Month - 7 | (Time.Currentmember.Lag(7),[CalculatedDim].&[1]) | @CURMTH-7@ |
Current Month - 8 | (Time.Currentmember.Lag(8),[CalculatedDim].&[1]) | @CURMTH-8@ |
Current Month - 9 | (Time.Currentmember.Lag(9),[CalculatedDim].&[1]) | @CURMTH-9@ |
Current Month - 10 | (Time.Currentmember.Lag(10),[CalculatedDim].&[1]) | @CURMTH-10@ |
Current Month - 11 | (Time.Currentmember.Lag(11),[CalculatedDim].&[1]) | @CURMTH-11@ |
Current Month - 12 | (Time.Currentmember.Lag(12),[CalculatedDim].&[1]) | @CURMTH-12@ |
Current Budget | (Ancestor(Time.Currentmember,1,Year), StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[CalculatedDim].&[1]) | (@CURYR@,@CURBGT) |
Current Forecast | (Ancestor(Time.Currentmember,1,Year), StrToMember ("Scenario.&["+Time.Currentmember.properties("forecast_Id")+"]")),[CalculatedDim].&[1]) | (@CURYR@,@CURFCST) |
- To overcome Rule Limitations. You cannot use the same dimension on two axes simultaneously. For example, a report that requires a Report Set on the Time dimension, will not allow selection of Time dimension members at the Page level. To set up a trailing 12-month report, define the column set on either the Calculated or Measure dimension. Time should be on the Page axis. This setup does not use substitution variables so performance will not be optimal. With Substitution Variables, the report column set can be set up on the Time dimension directly and the required selections can be made using Substitution Variables, which will result in improved performance.
How to Transition Existing Reports to Use Substitution Variables
Transition an existing report to utilize Substitution Variables to take advantage of the following benefits:
Improve Financial report performance.
Improve the ease and maintenance of Report Sets.
Display the report column/row headers dynamically.
Provide you with additional flexibility in changing report parameters.
Substitution Variables and their derivatives may be found by navigating to Maintenance > Reports > Cube Settings > Substitution Variables. This list displays system-defined variables, as well as any custom variables you create.
In Practice
Complete the following steps to transition your existing reports to Substitution Variables:
Review the report and corresponding row and column set(s) in order to determine how you want to proceed. For example, if the column set is built on the measures dimension or calculated dimensions, consider how the report may be modified to use time or scenario on the column. Also consider using Substitution Variables in column headers and row titles.
Make a copy of the existing report prior to beginning the transition. This copy will be modified during the transition, but you may compare the end result with the original report/report set(s) to validate data.
If you plan to change the dimension upon which the report set is built, you must create a new report set as it is not possible to change an existing report set to run on a different dimension. If you plan to use the same dimension for your new report set, you may copy the original report set. This copy will be modified during the transition and you can then compare the end result with the original report/report sets to validate data.
Modify the formulas on the Advanced Rule page from the current string to include the appropriate Substitution Variables. For example; In a rolling twelve month column set, you can remove the expression “Time.CurrentMember.lag(1)” and replace it with “@CURMTH-1@” for the column pulling the last month’s data.
Modify the column headers/row titles as necessary. A column previously titled using Formula Exceptions to display the appropriate month or hardcoded to fixed text can be replaced with a Substitution Variable in the column header within the column set. This header may include the Substitution Variable alone, or included with static text. For example; If the month is May, the column header within the column set may be “@CURMTH-2@” to display “March.” Or, it may be “YTD @CURMTH-2@” to display “YTD March.”
Save the copied and modified report set. Adjust the dimensions on the copied report if you created new report sets to accommodate the Substitution Variables. When you have the report highlighted within the Reports file cabinet, select “Setup” to view the Report Details and Report Settings. For example, you may need to move one dimension to the Page (such as Measures) and another to the Column (such as time) to use the new column set.
Run the newly transitioned report. Compare the results to the original report/report set(s) and make adjustments if necessary. You may change the values of the Substitution Variables at run time, however, these changes will not be saved with the report.
In addition, moving highly repeated members of Rules to the page level will improve report performance. If “*Measures+.*MTD+” is used in many of the Rule within a report set, you may move Measures to the page and include the measures dimension in the Rule only in fields where you do not want MTD.
Delete or rename the original report, which is no longer in use. Rename the transitioned report accordingly.
Substitution Variables and Dynamic Reports
Substitution variables displayed are filtered based on Reporting Area. For example, @CURBDGT@ may be defined under the Financial Reporting Area and therefore can only be used if you select Financial as the Reporting Area while creating a Dynamic Report.
In the example below, you can see a list of substitution variables available under the Financial Reporting Area for the Time dimension.
When you select a substitution variable to use in a Dynamic Report, you can also select one of the following Options:
Selected - The report returns the members selected.
Children - The report returns the first level children of the selected members.
All Children - The report returns all of the descendants of the selected members.
Sel+Children - The report returns the selected members and the first level children of the selected members.
Sel+AllChildren - The report returns the selected members and all of the descendants of the selected members.
Leaves - The report returns the posting levels below the selected members.
Sel+Leaves - The report returns the selected members and the posting levels below the selected members.
Sel+Parents - The report returns the selected members and the summary levels above the selected members.
The option selected (i.e. Leaves, Children, etc.) also applies to substitution variables used. For example, if you select the @CURYR@ substitution variable as the member for a Time dimension on the column or a Dynamic Report and then you select the Leaves option, the Dynamic Report output will display with all current month output. This is because the @CURYR@ substitution variable has current month leaf members.
System-Defined Substitution Variables
The following Substitution Variables are available in your application by default.
Variable Name | Variable Type | Purpose | |
---|---|---|---|
1 | @CURMTH@ | Substitution Variable | Current Month |
2 | @CURMTH+1@ | Derived Variable | Next Month |
3 | @CURMTH+2@ | Derived Variable | 2 Months after Current Month |
4 | @CURMTH+3@ | Derived Variable | 3 Months after Current Month |
5 | @CURMTH+4@ | Derived Variable | 4 Months after Current Month |
6 | @CURMTH+5@ | Derived Variable | 5 Months after Current Month |
7 | @CURMTH+6@ | Derived Variable | 6 Months after Current Month |
8 | @CURMTH+7@ | Derived Variable | 7 Months after Current Month |
9 | @CURMTH+8@ | Derived Variable | 8 Months after Current Month |
10 | @CURMTH+9@ | Derived Variable | 9 Months after Current Month |
11 | @CURMTH+10@ | Derived Variable | 10 Months after Current Month |
12 | @CURMTH+11@ | Derived Variable | 11 Months after Current Month |
13 | @CURMTH+12@ | Derived Variable | 12 Months after Current Month |
14 | @CURMTH-1@ | Derived Variable | Previous Month |
15 | @CURMTH-2@ | Derived Variable | 2 Months prior to Current Month |
16 | @CURMTH-3@ | Derived Variable | 3 Months prior to Current Month |
17 | @CURMTH-4@ | Derived Variable | 4 Months prior to Current Month |
18 | @CURMTH-5@ | Derived Variable | 5 Months prior to Current Month |
19 | @CURMTH-6@ | Derived Variable | 6 Months prior to Current Month |
20 | @CURMTH-7@ | Derived Variable | 7 Months prior to Current Month |
21 | @CURMTH-8@ | Derived Variable | 8 Months prior to Current Month |
22 | @CURMTH-9@ | Derived Variable | 9 Months prior to Current Month |
23 | @CURMTH-10@ | Derived Variable | 10 Months prior to Current Month |
24 | @CURMTH-11@ | Derived Variable | 11 Months prior to Current Month |
25 | @CURMTH-12@ | Derived Variable | 12 Months prior to Current Month |
26 | @CURBDGT@ | Substitution Variable | Current Budget |
27 | @CURFCST@ | Substitution Variable | Current Forecast |
28 | @CURQTR@ | Substitution Variable | Current Quarter |
29 | @CURQTR+1@ | Derived Variable | Next Quarter |
30 | @CURQTR+2@ | Derived Variable | 2 Quarters after Current Quarter |
31 | @CURQTR+3@ | Derived Variable | 3 Quarters after Current Quarter |
32 | @CURQTR+4@ | Derived Variable | 4 Quarters after Current Quarter |
33 | @CURQTR-1@ | Derived Variable | Prior Quarter |
34 | @CURQTR-2@ | Derived Variable | 2 Quarters prior to Current Month |
35 | @CURQTR-3@ | Derived Variable | 3 Quarters prior to Current Month |
36 | @CURQTR-4@ | Derived Variable | 4 Quarters prior to Current Month |
37 | @CURYR@ | Substitution Variable | Current Year |
38 | @CURYR+1@ | Derived Variable | Next Year |
39 | @CURYR+2@ | Derived Variable | 2 years after Current Year |
40 | @CURYR-1@ | Derived Variable | Previous Year |
41 | @CURYR-2@ | Derived Variable | 2 Years prior to Current Year |
Derived Variables
Derived variables contain a formula that references a Substitution Variable and derives an appropriate value by evaluating the formula.
In Practice
Let's say you need to set up a derived variable which will result in the month preceding the Current Month. To do so, use a Derived Variable named @CURMTH -1@, which references a Substitution Variable @CURMTH@ and contains the formula; such as @CURMTH.Lag(1).
If you need to set up a derived variable off of substitution variable @CURMTH@ (current month) to list all the days in the month. Your hierarchy is set up where Jan 2017 has 1/1/2017 through 1/31/2017. To do so, use the following options:
@CurMth@ = Jan-17
@CurMth.Day01@ = @CurMth@.FirstChild
@CurMth.Day02@ = @CurMth@.FirstChild.lead(1)
@CurMth.Day03@ = @CurMth@.FirstChild.lead(2)
Because the Time dimension is not symmetrical, meaning every month does not have the same # of days, you could change @CurMth@ to Feb-17, but since Feb only has 28 days, the last 3 derived variables would get the 1st 3 days in March.
Now, let's say you need to reflect data for 3 years back and you are using the @CURYR@ substitution variable. To do so, define a derived variable on the current year substitution variable as @CURYR-3@.
System-Defined Derived Variables
There are 49 derived variables for the @CURYR@ substitution variable on the Time dimension. These derived variables are available for Financial and Workforce reporting areas.
You can access the derived variables by navigating to Maintenance > Reports > Cube Settings > Substitution Variables > @CURYR@> Derived Variables.
The followings derived variables are available.
Variable Name | Variable Type | Purpose | |
---|---|---|---|
1 | @CURYRQ1@ | Derived Variable | Quarter 1 |
2 | @CURYRQ2@ | Derived Variable | Quarter 2 |
3 | @CURYRQ3@ | Derived Variable | Quarter 3 |
4 | @CURYRQ4@ | Derived Variable | Quarter 4 |
5 | @NYRQ1@ | Derived Variable | Next year Quarter 1 |
6 | @NYRQ2@ | Derived Variable | Next year Quarter 2 |
7 | @NYRQ3@ | Derived Variable | Next year Quarter 3 |
8 | @NYRQ4@ | Derived Variable | Next year Quarter 4 |
9 | @PRYRQ1@ | Derived Variable | Previous year Quarter 1 |
10 | @PRYRQ2@ | Derived Variable | Previous year Quarter 2 |
11 | @PRYRQ3@ | Derived Variable | Previous year Quarter 3 |
12 | @PRYRQ4@ | Derived Variable | Previous year Quarter 4 |
13 | @CURM01@ | Derived Variable | 1st month of the year |
14 | @CURM02@ | Derived Variable | 2nd month of the year |
15 | @CURM03@ | Derived Variable | 3rd month of the year |
16 | @CURM04@ | Derived Variable | 4th month of the year |
17 | @CURM05@ | Derived Variable | 5th month of the year |
18 | @CURM06@ | Derived Variable | 6th month of the year |
19 | @CURM07@ | Derived Variable | 7th month of the year |
20 | @CURM08@ | Derived Variable | 8th month of the year |
21 | @CURM09@ | Derived Variable | 9th month of the year |
22 | @CURM10@ | Derived Variable | 10th month of the year |
23 | @CURM11@ | Derived Variable | 11th month of the year |
24 | @CURM12@ | Derived Variable | 12th month of the year |
25 | @NYRM01@ | Derived Variable | 1st month of the next year |
26 | @NYRM02@ | Derived Variable | 2nd month of the next year |
27 | @NYRM03@ | Derived Variable | 3rd month of the next year |
28 | @NYRM04@ | Derived Variable | 4th month of the next year |
29 | @NYRM05@ | Derived Variable | 5th month of the next year |
30 | @NYRM06@ | Derived Variable | 6th month of the next year |
31 | @NYRM07@ | Derived Variable | 7th month of the next year |
32 | @NYRM08@ | Derived Variable | 8th month of the next year |
33 | @NYRM09@ | Derived Variable | 9th month of the next year |
34 | @NYRM10@ | Derived Variable | 10th month of the next year |
35 | @NYRM11@ | Derived Variable | 11th month of the next year |
36 | @NYRM12@ | Derived Variable | 12th month of the next year |
37 | @PRYRM01@ | Derived Variable | 1st month of the previous year |
38 | @PRYRM02@ | Derived Variable | 2nd month of the previous year |
39 | @PRYRM03@ | Derived Variable | 3rd month of the previous year |
40 | @PRYRM04@ | Derived Variable | 4th month of the previous year |
41 | @PRYRM05@ | Derived Variable | 5th month of the previous year |
42 | @PRYRM06@ | Derived Variable | 6th month of the previous year |
43 | @PRYRM07@ | Derived Variable | 7th month of the previous year |
44 | @PRYRM08@ | Derived Variable | 8th month of the previous year |
45 | @PRYRM09@ | Derived Variable | 9th month of the previous year |
46 | @PRYRM10@ | Derived Variable | 10th month of the previous year |
47 | @PRYRM11@ | Derived Variable | 11th month of the previous year |
48 | @PRYRM12@ | Derived Variable | 12th month of the previous year |
49 | @2YRPriorM12@ | Derived Variable | 12th month of 2nd year prior to the current year |