- Getting Started
- Dynamic Planning
- Dynamic Planning Overview
- Write Back
- Aggregation Process
- User Security
- Direct Access to PCR Model
- Frequently Asked Questions
- Download & Installation
- SpotlightXL
- Analyze
- Report
- Excel Report
- Spotlight for Office
- Model
- Manage
- Token Management
- Write Back
- Dynamic Planning How to Load an External Data Source into a Master Model (Legacy)
- Spotlight Web
- Spotlight for Google Sheets
- Breakback
- Dynamic Planning API Library
- Base URL
- List of APIs
- Append Attributes (Master and Analytic Models)
- Sample Payload for Creating or Appending Attributes and Specifying the Member-Attribute Mapping
- Sample Payload for Updating the Member-Attribute Mapping for Previously Saved Attributes
- Response
- Parameters
- 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)
- 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)
- Create Users and Groups (User Management)
- List Users and Groups (User Management)
- Dynamic Planning: Group Access API
- How-Tos
- Dynamic Planning: How to Create a Report Based Off Multiple Models
- Dynamic Planning: How to Create a Report Based Off an Existing Report
- Dynamic Planning: How to Configure the Number of Invalid Login Attempts
- How to Open a Second Instance of SpotlightXL
- Dynamic Planning: How to Update a Dimension Hierarchy Without Clearing a Model
- Dynamic Planning How to Use Variables in Views and Reports
- Dynamic Planning How to Use the Metadata Viewer
- Dynamic Planning How to Use Excel Based Formulas
- Dynamic Planning How to Use Drag, Drop, Pivot, and Zoom Actions to Analyze Data
- Dynamic Planning How to Use Currency Exchange Rates
- Dynamic Planning How to Unlock a Locked User
- Dynamic Planning How to Use Change Data Tracking
- Dynamic Planning How to Specify Match Criteria
- Dynamic Planning How to Share a POV Dimension Across Models in a Report
- Dynamic Planning How to Set up a Power User with Access to Everything
- Dynamic Planning How to Select Data and Member Design Manager Options to Populate Report Values
- Dynamic Planning How to Save Data on Attributes for Views and Reports
- Dynamic Planning How to Restore a Model
- Dynamic Planning How to Perform Multi-Tab Analysis
- Dynamic Planning How to Lock Members in a View or Report to Prevent Modification
- Dynamic Planning How to Lock and Unlock Scaling
- Dynamic Planning How to Lock a Model to Prevent Modification or Clearing
- Dynamic Planning How to Lock a User and Prevent Sign On
- Dynamic Planning How to Generate a Model
- Dynamic Planning How to Filter Members Using Design Manager
- Dynamic Planning How to Download Data from the Structured Planning, Consolidation, and Reporting Applications into Dynamic Planning
- Dynamic Planning How to Delete a Model
- Dynamic Planning How to Define Multiple Number Formats for Dimension Members in Views
- Dynamic Planning How to Define Dimension Security
- Dynamic Planning How to Create Dimensions as Attributes
- Dynamic Planning How to Create and Load External Source Models
- Dynamic Planning How to Create a View and Specify Properties
- Dynamic Planning How to Create a Report off a Model and a View
- Dynamic Planning How to Create a Map
- Dynamic Planning: How to Clear a Model
- Dynamic Planning How to Cascade a Report
- Dynamic Planning How to Apply Scope to a Model
- Dynamic Planning How to Add or Delete Dimensions from a Model
- Dynamic Planning How to Add a Leaf Level Member to a Hierarchy
- Dynamic Planning How to Add a Chart to a Report
- Dynamic Planning How to Work with Model, View, and Report Folder and Organization Functionality
- Dynamic Planning How to Lock Dimension Members
- Dynamic Planning How to Find Data Cells Quickly
- Dynamic Planning How to Define Substitution Variables
- Dynamic Planning How to Backup a Model
- Dynamic Planning How to Add a Group and User Group
- Refrences
- Ref1
- Dynamic Planning Publishing and Opening Excel Reports, Word Reports, and PowerPoint Reports
- Purpose of Breakback
- Breakback Types
- Example Substitution Variable Expressions
- Prerequisites
- Install Planful Spotlight Add-on for Google Sheets
- Log in to Planful Spotlight Add-on
- Single Sign-On (SSO) for Planful Spotlight on Google Sheets
- Reports
- Using Breakback Summary
- Breakback and Data Locking
- Limitations
- Running Breakback from a Calculation
- Account Details
- Dynamic Planning User Groups
- Dynamic Planning Model Validation
- Dynamic Planning: Calculation Scheduler Option in Spotlight Web
- Spotlight User Summary
- Dynamic Planning Spotlight User Guide Supported Chart Types and Formatting on the Web
- Dynamic Planning Spotlight User Guide Report Task
- Dynamic Planning Spotlight User Guide Model Task
- Dynamic Planning Spotlight User Guide Analyze Task
- Creating and Formatting a Chart in PowerPoint or Word with Spotlight for Office
- Copying Spotlight Formulas from an Excel View or Report to PowerPoint
- Username and Password Security
- To launch Spotlight independently from a web browser:
- To launch Spotlight from the Planful application
- Supported Browsers
- Model
- Report
- Analyze
- Overview
- Dynamic Planning: Group Access
- About Dynamic Planning
- Spotlight versus SpotlightXL
- User Roles
- User Role Permission, Access, and Security
- User Types
- Unified Tenant
- Quick Summary of How to Assign or Restrict Access
- User Roles, Access, and Security
- Introduction to SpotlightXL
- Username and Password Security
- Accessing SpotlightXL
- Manage
- Model
- Report
- Analyze
- Overview
- Dynamic Planning Analyze Task
- SpotlightXL User Summary
- Dynamic Planning: SpotlightXL and Spotlight for Office Installation
- Using Dynamic Commentary in Excel Reports
- Dynamic Planning Global Settings for View and Report Properties
- Dynamic Planning SpotlightXL User Guide Function Support
- Dynamic Planning View and Report Permissions
- Dynamic Planning Using Substitution Variables and Expressions with Excel Reports
- Dynamic Planning SpotlightXL User Guide Chart Formatting and Types
- Dynamic Planning Sharing Excel Reports with People without a Dynamic Planning License Using Snapshots
- Dynamic Planning Setting Up Excel Reporting Security
- Dynamic Planning Setting Up Excel Reporting
- Dynamic Planning Setting Up an Assumptions Worksheet
- Dynamic Planning Report Design Using Substitution Variables and Expressions
- Dynamic Planning Repeat Headers Across Multiple Pages
- Dynamic Planning Refreshing and Formatting Excel Reports
- Dynamic Planning Overview of Excel Reporting
- Dynamic Planning Negative Amounts in Reports
- Dynamic Planning Navigation Access
- Dynamic Planning Multi-Tab Reporting
- Dynamic Planning Model Task in Excel
- Dynamic Planning Report Task
- Dynamic Planning Cascade Reporting
- Dynamic Planning Application Administration
- Creating Formulas in the External Source Model using Model Manager
- Creating Fields in the External Source Model using Model Manager
- Creating an External Source Model using Model Manager
- External Source Model Process
- List of Key External Source Model Features
- External Source Model Overview
- External Source Model Benefits
- Guard Rails
- 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
- Calculations in SpotlightXL Honor Group Access for ESMs
- Group Access to ESM Views
- Model Permissions for ESM Models
- Managing Group Access
- External Source Model DLR- Calculations tab Introduced
- Arabic and Chinese character support for ESM Data Load
- Examples of Include in Clear Data Loads
- Example of Creating a Data Load Rule for an External Source Model
- Using External Source Models in the Data Load Rule Definition Screens
- Best Practice Recommendations
- Details and Limitations
- Quick Summary
- Loading Data into the External Source Model
- External Source Model Field Types, Expressions, and Formulas
- Dynamic Planning External Source Model
- Dynamic Planning Export Data
- Transferring Data from One External Source Model to Another External Source Model
- Transferring Data from Analytical Model to ESM Model
- External Source Model Best Practices
- Backup and Restore of External Source Models
- Using Views to Input Future Opportunities into the Source Model
- Viewing the Data in the Master Model
- Using a Calculation to Load Data into the Master Model
- Defining a Map from Source Model to Master Model
- Creating a Master Model
- Loading Data into the Opportunity Data External Source Model
- Explanation of the DaysFactor Function
- Explanation of the Lookup Function
- Defining the External Source Model
- Viewing the Source Data
- Online Help, Support, Community, and Solution Hub
- Supported Browsers for SpotlightXL and Spotlight
- Automatic Signout
- Unique URLs for Artifacts
- 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
- Troubleshooting Tips
- ClickOnce Flexible Deployment
- Installation Best Practices
- User Roles
- View Formula in External Source Model
- Loading Data into External Source Models using PCR Data Load Rules
- Access to ESMs
- Step-by-Step Use Case for Forecasting Revenue Spread Over Time
- Dynamic Planning Data Locking
- Dynamic Planning Model Substitution Variables
- Dynamic Planning Model Lookup
- Dynamic Planning Groups
- Accessing Spotlight
- Dynamic Planning: Spotlight for Office User Guide
- 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: New Improved Aggregation Process
- Using Drill-Through
- Views and Reports
- 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
- Overview
- Assign User Group Access to Models, Reports, and Views
- Quick Summary of How to Assign or Restrict Access
- Set up a Contributor User with Access to Specific Models
- 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
- 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: Subject Matter Guides
- Report Collection for Dynamic Planning
- Dynamic Planning: Best Practices for Model Building, Model Refresh, and Model Restructure
- ESM (External Source Models) Canvas
- Analyze
- Dynamic Planning: Using Direct Access (aka Direct Connect) to PCR Financial Reporting
- 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
- Viewing PCR Substitution Variables and Derived Variables in SpotlightXL
- Using PCR Substitution Variables and Derived Variables in Reports at Runtime
- Adding Substitution Variables and Derived Variables to Existing Direct Access Models
- Creating a Direct Access Model with PCR Substitution Variables and Derived Variables
- 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: Predict Signals
- Dynamic Planning: How To Tasks
- Dynamic Planning How to Add a User
- Dynamic Planning Manage Task
- Ref1
- Structured Planning
- Tasks to Configure for the Planning Module
- Planning Control Panel Intro
- Template Input and View Modes
- Entity Workflow
- Dynamic Commentary in Planning Template
- Working with Reports
- Workforce Planning - Classic UI
- 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
- Capital Planning
- Initiative Planning
- Workforce Planning
- References
- 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
- Financial Hierarchy Overview
- Setup Finance Hierarchies
- Understanding Calculated Members
- Calculated Member Example - Creating a Member and Including it in a Dynamic Report
- 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
- Global Fields
- Refreshing Actuals (Closed Period Data)
- Mapping Templates to Scenarios
- Securing Scenarios
- Why Secure Scenarios
- When to Secure Scenarios
- How 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 Provide Scenario Access to Multiple Users at Once
- How to Set Up Scenario Security for Reporting Purposes
- Clear Data
- 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
- Reference Cube
- Setting Up and Applying Global Fields to Templates (Optional)
- Apply Global Fields to Template Lines Accessed from the Planning Control Panel
- What are Global Fields and Why are They Used
- Add a Global Field
- Map the Global Field to Another Scenario and Budget Entities
- Mass Upload Global Fields
- Map Global Fields to Templates for Budget Entity and Scenario
- Global Fields in the Planning Control Panel
- Input Global Field Data
- Setting Up and Using Spreads in Templates (Optional)
- Attributes
- Template Setup Examples
- What To Do Once Template Setup is Complete
- 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
- 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
- Initiative Workflow
- Adding Assets to Initiatives
- Add an Initiative to the Initiative Template and Start Budgeting for that Initiative
- Open the Template in the Planning Control Panel
- Map the Initiative Template to a Scenario(s)
- Map Attributes to Initiatives
- Add Attributes
- Set up Attributes to Map to Initiatives
- Setup/Design the Initiative Template
- Set Up Categories
- References
- Workforce Planning Admin Guide
- Workforce Planning
- Define Compensation Items for Workforce Planning
- Description of Fields on the Compensation Item Page
- Compensation Basis Examples
- Add Compensation Items Overview
- Description of Fields on the Add Compensation Item Page
- 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 Complete Pays Setup - Workforce Planning?
- Employees Application Page - Workforce Planning
- Personalize the Employee list page - Workforce Planning
- Description of Fields on Employee Add Page
- Automated Employee Processing
- How to Extract Allocated Employee Information
- Security Administration
- Workforce Defaults
- Add Employees & Details
- Templates
- Template Mapping
- User Access
- Reporting
- Employee Type
- Advance Search in Workforce Planning
- Workforce Planning
- Workforce Planning - Enhanced UI
- How-Tos
- Subject Matter Guides
- Workforce Planning Admin Guide
- Set Up Approval Roles for the New Template
- Add an Initiative Template
- Overview of Steps to Set Up Initiative Planning
- Inputting Data via the Planning Control Panel
- User Security
- Scenario and Entity Mapping
- Adding Capital Templates
- Setting Up Finance Mode Templates and Mapping Capital Budget Lines to Your General Ledger
- Adding Finance Modes
- Adding Assets
- Adding Asset Categories
- Configuring Time Sets
- Configuring Capital Planning Attributes
- Configuring Time Sets
- Reporting
- Structured Planning User Guide
- Snapshot introduced in Planning Control Panel
- Performing Template Input
- Template View Mode
- Template Notifications
- View User History of Any Template
- Global Templates
- Block Templates
- Workforce Planning Templates
- Capital Templates
- Initiative Templates
- Line Item Template
- Allocation Template
- Approvers
- Workforce Planning UI
- Snapshot Restore Guidelines
- Structured Planning Admin Guide
- Consolidation
- Prerequisites
- Consolidation Control Panel
- How-tos?
- How to Configure Retained Earnings Roll Forward?
- How to Setup for Enhanced 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
- Consolidation Accounts Setup Explained
- Intro to Consolidation
- Standard Journals
- Reporting
- Recurring Journals
- Dynamic Journals
- Non Controlling Interest
- Reclassifications
- Eliminations
- Validations
- Performing Entry Actions on Multiple Entries
- Consolidation Security
- Interim Currency
- How to Add Members to Sibling Hierarchies
- How to Create a Sibling Hierarchy Using a Sibling Hierarchy as the Source
- How to Create a Sibling Hierarchy Using the Main Company Hierarchy as the Source
- Source Hierarchies Used to Create Sibling Hierarchies
- What are Sibling Hierarchies and Why Do I Need to Create Them
- How to Provide Users with Access to Hierarchies
- What is the Hierarchy Usage Report
- Disallowed Strings for Attributes and Dimensions
- Dimension Locking
- Auditing Hierarchies
- Entity Currency
- Security and Accounts
- Configuration Tasks
- When Consolidation Is Needed
- Security Administration
- User Management
- Non Controlling Interest
- Centralized Security
- Cloud Scheduler for Consolidation Admin
- 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
- How to Delist Currency Types
- Currency Exchange Rate
- Hyperinflationary Economy
- Calculation Exceptions
- CTA
- Reporting Currency
- Entity Currency
- Load Data to Planful
- What Are Currency Exceptions and Do I Need to Set Them Up
- How to Setup and View Currency Exceptions
- Consolidation - Income Statement Retained Earnings Account & Retained Earnings Roll Forward
- Journals
- Reclassifications
- Standard Reports
- Security Overview
- Eliminations
- Validation
- Setting Up Hierarchies
- Report Categories
- Hierarchy - Reporting, Entity, & Interim Currency
- What is Org by Period, How to Access Org by Period, and How to Configure It
- How to Create an Alternate Hierarchy
- Dated Hierarchies FAQs
- Org By Period Report Generation
- How to Add a Dated Hierarchy
- How to Create an Alternate Hierarchy Using the Copy Functionality
- Calculated Member Example - Creating a Member and Including it in a Dynamic Report
- What are Calculated Members and Why Do I Need to Create Them
- Setup for Finance Type Hierarchies
- Overview
- What are Reporting Members and Why Do I Need to Create Them
- 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
- Scenario Setup
- Overview
- Description of Fields on the Scenario List Page
- How to Provide Scenario Access to Multiple Users at Once
- 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
- Processing Consolidations - What Happens During the Consolidation Process
- Override Workflow Actions for the Locking Period
- Review Consolidation Setup
- Consolidation Process Summary
- Copying Journal Entry Data
- What Is Validation Functionality and How to Enable It for Users
- How to Delete a Template from a Locked Scenario
- How to Add a Report Category
- 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
- 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
- Reports
- Reports Prerequisites
- Reports Configurations
- Reports Home Page
- Report Folder
- Report Document
- Report Sets
- Report Collection
- Financial Package
- Dynamic Reports
- Working with Dynamic Reports
- Build and Run a Dynamic Report
- Example of Building a Dynamic Report
- Enhanced Drill Through in Dynamic Reports
- Template Notes in Dynamic Reports
- Dynamic Commentary
- Currency Display in Dynamic Reports
- Automatic Data Refresh
- Print Dynamic Report
- Exporting Dynamic Reports Output in Unprotected View
- View History from Reports
- Dynamic Reports Use Case
- Best Practices
- Dynamic Report Limitations
- Workforce Reporting
- Reports - References
- Dynamic Reports
- Overview
- Composition of a Dynamic Report
- Modify the Look and Feel of Dynamic Report Output
- 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
- Template Notes in Dynamic Reports
- How to Post a Comment
- Dynamic Reports Mapped to Planning Templates
- Dynamic Reports Mapped to Planning Templates Overview
- Copying Dynamic Reports
- How to Create and Modify Dynamic Reports
- Setting Up and Using Substitution Variables
- Limitations
- Overview
- How to Set Up Substitution Variables
- How to Use Substitution Variables in Dynamic Reports
- Dynamic Reports Options
- Best Practices and Tips
- References - Report Set
- Report Collection
- Financial Package
- Build and Run a Financial Package
- Reports User Guide
- File Cabinet
- 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
- Dynamic Reports
- 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
- View History from Dashboards
- Best Practices
- 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
- Planful AI
- Predict
- Budget Manager Experience
- Set Up Budget Managers
- Getting Started with Budget Manager Experience
- Sharing Templates with Budget Managers
- Manage Account Settings
- Track the Shared Templates
- Share Reports Via Link
- Predict in Budget Manager Experience
- Dashboards in Budget Manager Experience
- Workforce Planning Templates in Budget Manager Experience
- Maintenance
- Administration
- User & Role Management
- Scenario Setup
- Security Administration
- Configuration Tasks
- Workdays Setup
- Cloud Scheduler
- Customize Branding
- MyPlan Configuration
- Comments Manager
- Multi-Currency
- Hierarchy Management Overview
- Hierarchy Management Interface Overview
- Creating and Managing Additional Finance Hierarchies
- Adding Report Categories
- How-Tos
- Setting Up Attributes for Attribute Hierarchies
- Understanding Best Practices - Finance Hierarchies
- Understanding Reporting Members and Calculated Members
- Setting Up an Attribute Hierarchy
- Configuring OLAP Cube
- Understanding Example of How to Use Attributes
- Adding Another Level to Your Attribute Hierarchy
- Reflecting New Attribute Levels in Reports
- Setting Up An Attribute Used in Reporting and Enabling Drill Down to Dimension Member
- Mapping and Unmapping Workforce Planning Default Measures for Reporting Purposes
- Understanding Best Practices - Attribute Hierarchies
- Setting Up an Alternate Hierarchy
- Setting Up a Dated Hierarchy
- Entity Hierarchy Overview
- Sibling Hierarchies - Multiple Set of Books Overview
- Versioned Hierarchy Management in Dynamic Planning
- Exploring Additional Hierarchy Topics
- How-Tos
- Exploring the Frequently Asked Questions (FAQs) for Hierarchies
- FAQs for Hierarchies
- Planning Templates
- Global Fields
- Attributes
- Line Item Category
- Template Setup
- Template List
- Add Template
- Attribute Columns
- Deleting Attribute Columns
- Avoiding Mismatched Lines Between Sub and Main Templates
- Initiative Template Setup
- Time Sets
- Template Types
- How to Add a Global Template - Single Copy Template Type?
- How to Add a Global Template- Entity Copy?
- How to Add a Block Template Type?
- How to Add HR - Workforce Planning Template?
- How to Add an HR Template Type?
- How to Add an Allocation Template Type?
- How to Add a Capital Template Type?
- How to Add a Line Item Template Type?
- How to Add an Initiative Template?
- Template Examples
- Add Template
- Template Setup
- Mappings
- Spreads Set Up
- What To Do Once Template Setup is Complete?
- Template List
- Additional Template Information
- References
- What Are Time Sets?
- How to Configure Time Sets
- How to Create an Approval Role and Provide Users with Approval Role Access
- How to Provide Users with Scenario Access
- Attributes
- Applying Spreads to a Template Accessed via the Planning Control Panel
- Rules for Template RC Lines
- What are Spreads and How are They Used
- Initiative Template Setup
- Capital Template Setup
- How to Set Up a Line Item Template
- How to Set Up Line Item Categories
- How to Define a Time Set
- How to Set the Default Time Set
- Understanding the Template Setup Application Page
- What is Template Setup
- Template Column Types
- How to Map a Template to a Scenario
- How to Map a Template to an Entity
- What are Templates and Why are They Needed
- Template Types
- Description of Fields on the Template List Page
- Line Item Template Setup
- Spreads
- Template Setup Examples
- How to Access the Template List Page
- Opt In Features
- Publishing Templates
- Workforce
- Workforce Planning Setup
- Employees
- Adding Employees
- Add a Profile Picture to Employee Profile
- Reports Option for Employees
- Mass Update
- Using Customize Roster to Add Compensation Items
- Changing Budget Raise Percentage
- Employee Positions Outside of the Current Budget Cycle
- Automated Employee Processing
- Performance Statistics - Workforce Planning Employee Page
- Calculating Budget Review Date and Current Review Date
- Pop-up Message Introduced across all the windows in Workforce Planning Setup
- Create a Duplicate Employee
- Copy Employees
- Use Cases on How to Update Several Employees at Once
- How-Tos
- Compensation Groups
- Compensation Items
- Compensation Basis
- Marginal Benefit Calculation Setup Examples
- Situations When You Can't Edit a Compensation Item
- Understanding Pre-built Calculation Basis
- Linking to Global Fields
- How-Tos
- How to View Compensation Items in a Workforce Planning Budget Report?
- How to Set up 401k Match?
- How to Copy all Compensation Items and Rates from One Scenario to Another?
- How to Input Data Using the Direct Data Input Compensation Item?
- How to Lock Compensation Items to Prevent Modification?
- How Changes to Compensation Items Affect Running Scenarios?
- How to Update Compensation Items Loaded via Data Load Rules?
- How to Make the Bonus Expense Calculation Variable?
- How to Select Compensation Items for Calculating Wages?
- Employee Types
- Pay Plans
- Workforce Attributes
- User Access
- Employees
- Export Employee Data
- References
- Workforce Planning Admin Guide
- Configuring Workforce Planning
- Security Administration
- Workforce Defaults
- Add Employees & Details
- Templates
- Template Mapping
- User Access
- Reporting
- Workforce Planning Admin Options
- Understanding Tiers on Employee Attribute - Value in Hours per Period with Annual Cap
- Understanding Tiers on Employee Attribute - Value in Hours per Period
- Understanding Tiers on Employee Attribute - Value in Wages with Varying Annual Cap
- Understanding Rate x Hour
- Workforce Planning Admin Guide
- Workforce Planning Setup
- Capital Planning
- References
- Capital Planning Admin Guide
- Reporting
- Adding Assets
- Adding Finance Modes
- Adding Capital Templates
- Setting Up Finance Mode Templates and Mapping Capital Budget Lines to Your General Ledger
- User Security
- Configuring Time Sets
- Scenario and Entity Mapping
- Adding Asset Categories
- Configuring Capital Planning Attributes
- Inputting Data via the Planning Control Panel
- Capital Planning Admin Guide
- References
- Rules
- Data Integration
- Actual Data Templates
- Translations Setup
- Export Data
- Transaction Details
- Data Load Rules
- Integration Services
- Clear Data
- Data Calculation Exceptions
- Additional Topics
- API Library
- Introduction
- Data Import
- Data Export
- Structured Planning REST APIs
- Workiva Integration
- Objects Overview
- API Compliance
- Client Application Platforms
- API Access Keys
- API Calls - Overview
- Core API Calls
- Utility Calls
- Objects
- API Methods
- Clear_Data API
- Load_Data API
- Transfer_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
- Validate Source Segment API
- Transaction Data Extract APIs
- Planful API Library
- Source Segment API
- Report Administration
- Application Audit
- Application Audit Details - Audit Area
- References
- User & Role Management
- How to Access the User and Role Management Application Page
- Navigation Role and Access
- Manually Add a User to Planful
- Exporting Navigation Role Summary and Detailed Reports
- Locking Users and Preventing Sign On to Planful
- Copy An Existing User
- Approval Roles
- Consolidation Security
- User Groups
- Scenario Access
- Add-In Security (Optional)
- Data Integration Security
- Dimension Security Configuration
- Workforce Reporting Security
- Understanding the User Management Interface - Approval Role
- Understanding the User Management Interface - User Add Page
- Understanding the User Management Interface
- Understanding the User Management Interface - Navigation Role
- Purpose of User and Role Management
- Scenario Setup
- Security Administration
- Configuration Tasks
- Workdays Setup
- Auditing the Application
- Rules - References
- 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 - References
- Data Load Rules
- Web Services / Boomi
- Actual Data Templates
- Integration Services
- Working with Custom Parameters for Integration Service Processes
- Adding an Integration Service Process
- Completing the Integration Services Configuration Task
- Providing Navigation Access to the Integration Services Application Page
- Editing, Deleting and Copying Integration Service Processes
- Introduction
- Cloud Services
- Transaction Details
- Google Drive
- NetSuite Integration
- FTP/SFTP Connector
- Cloud Services
- Data Integration Admin Guide
- Admin Guide to Loading Data to Planful
- Cloud Scheduler Ref.
- What is Cloud Scheduler
- How to Provide Users with Access to Cloud Scheduler
- How to Add A Process Flow
- Understanding Process Flow and Job Manager
- Description of Fields on the Process Flow Page
- 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
- Currency-Ref
- Difference Between Common Currency, Local Currency, and Interim Currency
- Currency Use Case - Common Currency vs. Local Currency
- Decision Hub Summary
- Interim Currency
- Currency Setup
- Currency Type Setup
- Currency Type and Currency Type Exceptions
- Mass Loading Currency Exceptions
- Loading Exchange Rates Using Data Load Rules
- Cube Settings
- Report Administration Guide
- MyPlan Admin Guide
- User & Role Management
- 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
- 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
- Spring 17 Release Notes
- 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
- 25 Minutes to read
- Print
- DarkLight
- PDF
Transferring Data from One External Source Model to Another External Source Model
- 25 Minutes to read
- Print
- DarkLight
- PDF
Overview
Modeling supports copying data from one External Source Model to another External Source Model. Just as you can copy data from an External Source Model into a Master or Analytic model using an ESM map, you can use the same method to copy data from one ESM to another. You must first define both the Source and the Target ESMs using the Source Model page, then you can define the Source Map to transfer the data. When the map is run, all data in the Target ESM is deleted before the data transfer from the Source Model is done.
A common use case for this feature would be to store your data load inputs in one ESM and your calculated/formula fields in a second ESM. The formula fields in the second ESM depend on data from the first ESM, so you can copy the requisite fields to the second ESM, which automatically calculates the formula fields. After verifying the data load, then load the data from the second ESM into your Master model.
Example Source ESM
In the examples in this section, the Source Model is Budget. The Source Model has 8 fields.
Here is the data in the Source Model.
Example Target ESM
In the examples in this section, the Target ESM is Revenue. The Target ESM also has 8 fields but some fields are different than the Source Model.
Defining a Map from a Source ESM to a Target ESM
The Source Model is a two-dimensional table of data organized into fields. The Target ESM must contain at least as many data-input fields as the Source Model. You will map the fields from the Source Model to the fields in the Target ESM. You must provide a mapping for all the data-input fields in the Target ESM; data-input fields are those marked as Yes for Include in Data Load.
Go to Model > External Source Model > Source Map.
Enter a name for the map.
From the Model drop-down list box, select or specify the name of the Target ESM you want to map the Source ESM to. In the drop-down list box, the ESMs are listed separately under a sub-heading called ESM.
Select the Source Model from the Source Model drop-down.
Select Data for Type.
Set Append Missing Dimension Members to No. This option applies only to mapping to Master or analytic models.
The Filter Field is optional. When filters are defined in an ESM to ESM map, only the filtered data is moved from the source ESM to the target ESM. For example,let's say a filter is defined on a Department Segment field and Dept1 is the value. When this map is executed from a Calculation, the map will only move the Dept1 data from source ESM to target ESM. Additionally, the Dept1 data only in the target ESM will be cleared before the data is moved from source to target
Now specify the fields from the Source Model that map to fields in the target ESM. Not all fields in the Source must be mapped, but you must provide a mapping for all the fields in the Target ESM that are marked as Yes for Include in Data Load.
For fields in the Target ESM but not in the Source Model, specify Field: None, Maps To: DimensionFilter, the field name in the Target ESM under Target Field, and the text, number, or date to be placed into the target model under Default Value.
Save the map.
Using a Calculation to Load Data into the Target ESM
To copy data from the Source Model to the Target ESM, you must use a calculation to run the External Source Map. When the map is run, all data in the Target ESM is deleted before the data transfer from the Source Model is done, if there are no filters defined in the map. If there are filters defined in the map, the data in the target ESM will be deleted based on the filters and the source data will also be filtered based on the filters defined.
Calculations are stored within a Master or analytic model. We suggest that you create the calculation inside the Master model that the ESM data will eventually be loaded into.
Go to Model > Calculation.
Select the Master model associated with your ESMs from the Model drop-down.
Give the calculation a name.
Create the first step of the calculation by selecting ExternalSourceMap from the Type drop-down.
Select the name of the map you created in the previous task from the name drop-down.
Save the calculation.
Run the calculation.
When using this calculation to execute the ExternalSourceMap, the existing records in the Target ESM are deleted, and then records from the Source Model are copied to the Target ESM. To verify the data in the Target ESM:
Go to Model > External Source Model > Source Data.
Select the Target ESM from the drop-down and click Refresh.
If there were any formula fields in the Target ESM, they are calculated and displayed on the screen.
Using Filter Fields to Limit the Data Load
If you want to load only a subset of data in the Source model, use the Filter Field section of the Source Map to indicate which rows of data to load. In the following example, the map indicates that only rows with Department Aerospace should be loaded from Budget to Revenue.
Here is the resulting data.
Formula Field added to Filter Fields in Maps
Formula fields are also available as filter fields in the ESM Maps. You can apply the Formula fields as filters while moving data from one External Source Model to another External Source Model, Master, or Analytic Model.
For example, if you want to load only a subset of data based on a specific calculation in the Source model, use the Filter Field section of the Source Map to indicate which rows of data to load. In the following example, the map indicates that only rows with Department Aerospace that have the Project End as 02/01/2020 should be loaded from Revenue to Budget. In this example, Department is a Text field, and Project End is a Formula field.
Once you run this Map, the data corresponding to the Aerospace Department with Project End as 02/01/2020 is segregated in the output.
Here is the resulting data.
To move data from the source ESM to target ESM you must create a calculation and then run the External Source Map. For more information on creating a calculation, see Run Calculation with a Map.
When a map is run, Reverse Filtering is applied to preserve all existing data in the target ESM that does not satisfy the filter condition. So, when you select formula filters in the map, the data that meets the filter condition will be appended to the existing data in the output.
For example, if you want to load data only for the rows of the Aerospace Department that have the Project End calculated as “04/01/2020”, you have to update the filter field in the Map as shown in the image below.
In the following example, you can see that only rows with the Department as Aerospace and that have the Project End calculated as "04/01/2020" are loaded from Revenue to Budget and appended to the data existing in the output.
Business Example
Below is an example of data mapping from an ESM to an Analytical Model. Here, you can load the rows with Gateway Cycle as the Customer Full Name field value. The data can be loaded from Purchase Information to Outline Purchase Information.
Here, Customer Full Name is the formula field that should be mapped to the Customer field in the Target dimension. The formula condition being:
CONCATENATE ([Customer First Name], “ “, [Customer Last Name])
To move data from Purchase Information to Outline Purchase Information, you must create a calculation and run the Purchase Map. For more information on creating a calculation, see Run Calculation with a Map.
Based on the Calculation, rows with the Customer Full Name as Gateway Cycle are loaded from the Purchase Information to Outline Purchase Information and appended to the data existing in the output.
So, when you run the calculation and select the Scenario Dimension as 13, the uploaded data corresponding to the Gateway Cycle as the Customer’s Full Name Product will be updated in the output.
Business Value
With formula filters in the maps, you have the flexibility to transfer Formula field-specific data from one ESM to another instead of copying all the existing data.
It significantly reduces the effort and time previously involved to segregate data based on Formula fields and enhances the existing filtering with more options to group data.
In Practice: To use Formula Field in Maps
In SpotlightXL, select the Model task and the Map subtask.
Select the name of the Source Model defined on the Source Model page.
Enter a name for this map and add the description.
Select the type as Data for loading external source data.
Select or specify the name of the ESM you want to map the Source ESM to.
For Append Missing Dimension Members , select No when mapping to another external source model.
For the Filter field, specify the name of the field in the Source ESM to filter the data you want to load. Under the Value column, you can specify the value name.
Specify the name of the formula under the Filter field and the value.
For the Fields column, specify field names from the Source Model that will be mapped to fields in another model.
Select any of the following in the Maps To column:
DimensionFilter : This filter indicates that there is no field present in the Source ESM to map to the Target ESM. You will have to specify a value in the Default Value Column.
Value : indicates that this field in the external source is a value to be copied as is to the specified field in the target ESM.
In the Target Field column, select the name of the field in the target ESM.
In the Default Value column, specify the text, date, or number to be updated into this field.
For information on the steps to create an ESM to ESM map and run a Calculation, see the Transferring Data from One External Source Model to Another External Source Model topic.
Filtering Data with ESM Filters
You can filter data with ESM filters in the application.
You must set the 'Enable Filters in ESM Source Data' to yes to filter data.
When you enable the flag, you can see the 'Include in Filters' column in the source Model screen. To provide value for the filter, you can either copy-paste or type the value. Also, you can only enable the Include in filters column for a maximum of 5 fields.
In Practice: Enabling the Filter Flag
1. In SpotlightXL, select the Manage screen.
2. In the Application settings drop-down, select Application Administration and then click on Application Settings.
3. Scroll down to enable the 'Enable Filters in ESM Source Data' flag. The value for this flag is set to ‘No’ by default.
In Practice: Filtering data in Source Data load
1. Select the Model task, and from the Source Model drop-down, select Source Model.
2. Define Source Model Name in the Source Model page.
3. Define Field and type in the Source Model page and click Save.
4. You will see the 'Include in Filters' column available.
5. You can set this column value to ‘Yes’ for the specified fields.
In Practice: Loading data in the Model
1. Select the Model task, and from the Source Model drop-down, select Source Data.
2. You can either copy-paste the values or enter them manually to load the data.
3. For the Filter field, specify the name of the field in the Source ESM to filter the data you want to load. Under the Value column, you can specify the value name.
4. After you refresh the page, you will see the filtered data based on the specified field.
Multi-ESM Use Case
The following example shows how you can use multiple external source models to generate valuable forecasting data for sales and consulting revenue.
Data inputs about the prospective customer, sales rep, potential revenue amount (ARR), and potential implementation consulting revenue are loaded into an ESM called Revenue Opportunities.
Sales revenue is forecast monthly based on a project start date and a term. To keep the ESMs organized and of a manageable size, there is one ESM for each year to store the monthly forecast.
Consulting revenue is forecast quarterly based on a project start date and four quarters of payments. To keep the ESMs organized and of a manageable size, there is one ESM for each year to store the consulting forecast.
Revenue Opportunities identifies 8 data input fields that you need to load.
Here is the data load file for Revenue Opportunities.
The Revenue ARR amount is spread over 2018 and 2019 in this example. An ESM for each year is created. Only 4 fields from Revenue Opportunities need to be mapped to the ARR Spread models to generate the monthly forecast.
Here are the maps to copy the four input fields from Revenue Opportunities to the Spread models.
Here is the calculation to run the two ESM maps.
The resulting ARR spread data is as follows. For details on how the DaysFactor function works, see Explanation of the DaysFactor Function.
Similar to ARR, the Revenue Implementation Consulting amount is spread over 2018 and 2019 in the following example. To show a different use case, we assume that consulting will be paid in 4 equal quarterly payments and will last no longer than 4 quarters, even if the Term of the project is more than 12 months. An ESM for each year is created. Only 3 fields from Revenue Opportunities need to be mapped to the Consulting Spread models to generate the monthly forecast.
Explanation of the Quarterly Formulas
For syntax of the ESM functions, see External Source Model Field Types, Expressions, and Formulas.
We have a Service Start date and need to calculate the dates of the following three quarters.
Formulas are created which calculate what the month and year is 3 months later, 6 months later, and 9 months later.
If the Service Start date is late in the year, then adding 3, 6, or 9 months will push into the next calendar year. For example, 11/1/18 plus 3 months is 2/1/19.
If you add 3 to the Service Start date month (11 + 3 = 14), and the result is greater than 12, then you need to subtract 12 to get the correct month (14 - 12 = 2).
IF(MONTH([Service Start])+3<13, MONTH([Service Start])+3, MONTH([Service Start])+3-12)
Similarly with the year, if you add 3 to the Service Start date month (11 + 3 = 14), and the result is greater than 12, then you are into the next year, so you need to add 1 to get the correct year (2018 + 1 = 2019).
IF(MONTH([Service Start])+3>12, YEAR([Service Start])+1, YEAR([Service Start]))
Once you have fields defined that store the month and year of the following three quarters, you can put it all together and create fields to store the Consulting Q2, Q3, and Q4 dates.
- Date([Service Start Q2 Month],[Service Start Day],[Service Start Q2 Year])
To create the monthly forecast of consulting revenue, we want 25% of the Implementation Consulting numeric value to appear in each of the four months of the Consulting Q2, Q3, and Q4 dates.
For Nov-2018, if the Service Start date, Consulting Q2 date, Consulting Q3 date, or Consulting Q4 date has a Month=11 and a Year=2018, then calculate Implementation Consulting]/4, otherwise 0.
In other words, if any of the following conditions are True, then the OR condition is True and will result in [Implementation Consulting]/4.
AND(MONTH([Service Start])=11, YEAR([Service Start])=2018)
AND(MONTH([Consulting Q2])=11, YEAR([Consulting Q2])=2018)
AND(MONTH([Consulting Q3])=11, YEAR([Consulting Q3])=2018)
AND(MONTH([Consulting Q4])=11, YEAR([Consulting Q4])=2018)
IF(OR(AND(MONTH([Service Start])=11, YEAR([Service Start])=2018), AND(MONTH([Consulting Q2])=11, YEAR([Consulting Q2])=2018), AND(MONTH([Consulting Q3])=11, YEAR([Consulting Q3])=2018), AND(MONTH([Consulting Q4])=11, YEAR([Consulting Q4])=2018)), [Implementation Consulting]/4,0)
Here are the maps to copy the three input fields from Revenue Opportunities to the Spread models.
Here is the calculation to run the two ESM maps.
The resulting Consulting Revenue spread data is as follows.
Using Aggregate Functions in Maps
The Aggregate functions are now available in the ESM Maps. You can use these functions directly while moving the data from one ESM to another ESM through maps. The Aggregate Function column has been added to the Maps.
The following Aggregate functions have been included in the maps:
COUNT
SUM
MIN
MAX
AVG (average)
The following table provides a quick overview of the functions.
Function Name | Description |
---|---|
COUNT | Returns the count of rows of the source ESM column where the field value is not empty/null/undefined. This is applicable to Text, Numeric, and Date field types on source ESM. The mapped target ESM field has to be of type Numeric. |
SUM | Returns the sum of all values from a list of numbers in the model. This is applicable to Numeric source fields. |
MIN | Returns the minimum value from a list of values in the model. This is applicable to Numeric and Date source fields. |
MAX | Returns the maximum value from a list of values in the model. This is applicable to Numeric and Date source fields. |
AVG | Calculates the average for a specified range of values. This is applicable to Numeric source fields. |
The Aggregate functions were not available in the Maps.
You can use the Aggregate functions, when you have a model with a lot of data and want to compile and organize the raw data into a more consumable and summarized format.
Business Example
If you have a model containing data about Sales opportunities. The model can contain raw data about leads, different sales owners, sales segments, annual recurring revenue (ARR), and so on.
Suppose you want to aggregate data and find specific data related to a sales segment or sales owners. In that case, you can use the aggregate functions available in the map and pull only relevant data to another ESM. The functions provide the flexibility to organize data and identify different key values such as maximum value, minimum value, total count, average, etc.You can create a different model with only relevant fields that are required. The following image shows the model created with only relevant fields.
You can create a map with your actual ESM as the Source Model and the new model as the Target model. The following image shows the map containing different fields such as Sales Segment, Opportunity Owner, Lead Source, ARR.
You can group the data as per your requirement and configure the fields accordingly in the Maps. You will have the Aggregate Function column available in the map to select the required function for getting the output. In the example above, the data is grouped by Sales Segment, Opportunity Owner, Lead Source, while the ARR fields are used to find the minimum, maximum, count, average, etc.So, when you run the calculation, you get summarized data that only provides information about the relevant fields.
If you want to filter and segregate data only for a particular field, you can easily do it. For example, you can easily find and track the count, sum, average of opportunities generated by a specific Opportunity Owner.
You can directly find the key point values such as sum, count, maximum, minimum, and average while moving the data from one ESM to another. The functions significantly reduce the effort and time required to organize the data and find key values. You have the flexibility to customize the fields in the map based on your requirement and see different outputs with just a click of a button. You can map a single source field to multiple target fields as per your requirement. For example, ARR was mapped to multiple target fields like sum, count, etc. It is not mandatory to map all the source fields in the map. For example, if you do not want to track data for Lead Source in the example above, then you can just update the Default value for that field to NA and update the Maps to Column value to DimensionFilter. The calculation will aggregate the data based on the updated values and you will see data only based on the Segment and Sales Rep.
- The aggregate functions are applicable only for ESM to ESM maps.
- The aggregate functions consider the data as case insensitive. For example, if you have data as East, east, EAST, then the aggregate functions can use any of them for grouping and the result would be random.
In Practice: To use the Aggregate functions in Maps
In SpotlightXL, select the Model task and the Map subtask.
Select the name of the Source Model defined on the Source Model page.
Enter a name for this map.
Add the required description.
Select the type Data for loading external source data.
Select or specify the name of the ESM you want to map the Source ESM to.
For Append Missing Dimension Members , select No when mapping to another external source model.
Optional, specify the name(s) of one or more fields in the Source ESM to use for filtering the data you want to load. Under the Value column, you can specify a leaf-level field.
In the Fields column, specify fields from the Source Model that will be mapped to fields in another External Source Model.
Select any of the following in the Maps To column:
DimensionFilter : indicates that there is no field in the Source ESM to map to the field in the Target ESM. If you select DimensionFilter, you must specify the Default Value column.
Value : indicates that this field in the external source is a value to be copied as is to the specified field in the target ESM.
In the Target Field column, select the name of the field in the target ESM to correspond with this field in the source ESM
In the Default Value column, specify the text, date, or number that should be placed into this field for all rows in the target ESM if Maps To is DimensionFilter. Or, leave this column blank if Maps To is Value.
In the Aggregate Function column, select the required function such as COUNT, MIN, MAX, etc.
Business Use Cases
Let’s understand using the Aggregate functions with some examples.If you have a model containing raw data about leads, different sales owners, sales segments, annual recurring revenue (ARR).
If you want to track your Sales opportunities based on different parameters, for example, you can know the maximum ARR based on Sales Segments, you can know what the opportunities are based on a specific lead source, and many more.
You must perform the following:
Create a target model with the required fields.
Create a Map to move data for relevant fields from your Source model to the Target model.
Select the required Aggregate function.
Run the calculation to view the output.
Creating a Target Model with Relevant Fields
You must create a Target Model to view and track the data for relevant fields based on your requirement. In this case, as you want to track Sales Opportunities based on different parameters, you can create your Target Model to have fields like Segment, Sales Rep, Lead Source, Minimum ARR, Maximum ARR, and so on.
To do that, navigate to SpotlightXL and create an External Source Model. For example “Opportunity Analytics”
Add the required fields to the Field column of your “Opportunity Analytics” model. If you want to segregate data based on specific fields, then include only those fields to your model. For example, Segment, Sales Rep, Lead Source, Minimum ARR, Maximum ARR, etc.
Once you add fields, specify the type in the Type column. For example, Text, Numeric, Date, and so on. Based on the data in your Source ESM, set the type as Text or Numeric. For example, Segment, Sales Rep, Lead Source are of Type - Text, and the different ARR fields are of Type - Numeric.
In the Include in Data Load column, specify Yes as the value is coming directly from the data source.
Once you have created the Target model and set all the required fields, navigate to creating a Map.
Creating and Configuring a Map
You will be creating a Map to pull the data from your source model to your target model. You must define the Source Fields and then map them to the respective Target Fields. Once you define the source and target fields based on your requirement, you can select the Aggregate functions for the fields. By selecting the Aggregate functions in the Map, you can Aggregate the data as per your requirement while simultaneously moving the data from Source Fields to Target fields. You have the flexibility to configure only the fields that you want to see in the target output.
Let’s understand, configuring a map for different use cases.
Scenario 1
For your Sales Opportunity data, if you want to aggregate data based on Segment, Sales Rep, and Lead Source, and track the Minimum ARR, Maximum ARR, Average ARR, Total ARR, and Count of Opportunities for a Sales Rep in a specific Segment or if you want to know the Lead Source for a Sales Rep’s generated Opportunities.
In SpotlightXL, select the Model task and the Map subtask.
Select the “Opportunity Data” as the Source Model.
Enter a name for this map. For example, “Summarize Opportunity”.
Select the type Data for loading external source data.
Specify the Target Model name that you created. For example, “Opportunity Analytics”
In the Field column, specify the fields that you want to move from your Source Model - Opportunity Data. In this case add Sales Segment, Opportunity Owner, Lead Source, and ARR.
Select Value in the Maps To column for the fields that you want to be copied. In this case, specify Value for Sales Segment, Opportunity Owner, Lead Source, and ARR.
In the Target Field column, select the name of the fields defined in the target model. In this case, specify Segment, Sales Rep, Lead Source, Min ARR, Max ARR, Avg ARR, Count of Opportunities, Total ARR.
Leave the Default Value column blank for all fields.
In the Aggregate Function column, select the following for the respective ARR target fields:
MIN for the Min ARR field
MAX for the Max ARR field
AVG for the Avg ARR field
COUNT for Count of Opportunities field
SUM for Total ARR
The following image shows the Map configuration for the above use case.
Run Calculation with a Map
You must create a calculation for the above Map. The calculation will run the map.
In the Calculation, select the target model name. In this case, select “Opportunity Analytics”.
Select ExternalSourceMap as the type in the Type column.
Specify the name of the map created. In this case, use “Summarize Opportunity”.
When you run the calculation, you will see that the complete Opportunity data is segregated as per the Map configuration and displayed in the Target Model.
Reviewing Output in the Target Model
Once you run the calculation, you will see that the complete Opportunity data is segregated based on the Sales Segments, Opportunity Owners, Lead Source. Enterprise and SMB. So, based on the fields configured in the Map you would see the data segregated in the output. In this case, as you have mapped the Sales Segment, Opportunity Owner, Lead Source, and ARR source fields with their respective Target fields, you see the values grouped accordingly. You can see the Minimum, Maximum, Average ARR, Total ARR, and Count of Opportunities for all Sales Reps for their Respective Segments. You can also see the Count of Opportunities generated by individual Sales Rep in a specific segment. You can see the lead sources used by a Sale Rep to generate the Opportunity.
Once the data is displayed in columns, you can easily filter data for a specific Sales Rep and track all the details as shown in the above image. For example, if you want to know data for the Sales Rep - Mary, you can just select Mary from the Sales Rep column. Once you select the Sales Rep, you can see the Segments where the Sales Rep has generated the opportunities. In this case, Mary has generated the opportunities in the Enterprise segment, with the total Count of Opportunities generated by her is 8. Out of the total, 7 were generated from the Lead Source “Partner” and 1 was generated from the Lead Source “Partner”. You can also check the Total ARR for Mary along with the other values as Min ARR, MAX ARR, and Average ARR. You can similarly view and track details for any Sales Rep.
Scenario 2
For your Sales Opportunity data, if you want to aggregate data based on Segment, and track the Minimum ARR, Maximum ARR, Average ARR, Total ARR, and Count of Opportunities.
In SpotlightXL, select the Model task and the Map subtask.
Select the “Opportunity Data” as the Source Model.
Enter a name for this map. For example, “Summarize Opportunity”.
Select the type Data for loading external source data.
Specify the Target Model name that you created. For example, “Opportunity Analytics”
In the Field column, specify the fields that you want to move from your Source Model - Opportunity Data. In this case add Sales Segment and ARR. As you previously had Opportunity Owner and Lead Source also there in your Map, now update these fields in the Map and specify “None” in the Field column.
Select Value in the Maps To column for the fields that you want to be copied. In this case, specify Value for Sales Segment and ARR, while specify Dimension Filter in the Maps To column for the fields with “None”.
In the Target Field column, select the name of the fields defined in the target model. In this case, specify Segment, Min ARR, Max ARR, Avg ARR, Count of Opportunities, Total ARR.
In the Default Value column, specify “NA” for the Fields with “None”, and leave this column blank for Sales Segment and ARR fields.
In the Aggregate Function column, select the following for the respective ARR target fields:
MIN for the Min ARR field
MAX for the Max ARR field
AVG for the Avg ARR field
COUNT for Count of Opportunities field
SUM for Total ARR
The following image shows the Map configuration for the above use case.
Run Calculation with a Map
You must create and run the calculation for the above Map configuration. For information on how to create and run a calculation, see Run Calculation with a Map.
Reviewing Output in the Target Model
When you run the calculation for the above configuration, you will see that the complete data is segregated based on the segments Enterprise and SMB.
The Segment column only shows values whereas the Sales Rep and Lead Source columns don’t show any value as they were set to “NA”.
So, based on the fields configured in the Map you would see the data segregated in the output. In this case, as you have only mapped the Sales Segment source field with the Segment target field, you see the values grouped by Segment.
You can see the Minimum, Maximum, Average ARR, Total ARR, and Count of Opportunities for the Enterprise and SMB segment.
The aggregate functions help to compile and summarize huge data from a source system into a consumable output that can easily be used for accurate analysis and tracking.
Table of contents
- Overview
- Example Source ESM
- Example Target ESM
- Defining a Map from a Source ESM to a Target ESM
- Using a Calculation to Load Data into the Target ESM
- Using Filter Fields to Limit the Data Load
- Formula Field added to Filter Fields in Maps
- Filtering Data with ESM Filters
- Multi-ESM Use Case
- Using Aggregate Functions in Maps