• Skip to primary navigation
  • Skip to main content
  • Skip to footer
Revelwood

Revelwood

  • Who We Are
    • About Us
      • Our Company
      • Our Team
      • Partners
    • Careers
      • Join Our Team
  • What We Do
    • SOLUTIONS
      • Workday Adaptive Planning
      • IBM Planning Analytics
      • BlackLine
    • SERVICES
      • Implementation Services
      • Advisory Services
      • Customer Care
        • Help Desk
        • System Admin
        • Performance Tune-Up
      • Training
        • Workday Adaptive Planning Training
        • IBM Planning Analytics / TM1 Training
    • PRODUCTS
      • DataMaestro
      • Lightspeed Planning & Reporting
      • IBM Planning Analytics Utilities
  • How We Help
    • Workday Adaptive Planning Use Cases
    • IBM Planning Analytics Use Cases
    • Client Success Stories
  • How We Think
    • Knowledge Center
    • Events
    • News
    • User Groups
  • Contact Us

IBM Planning Analytics Tips & Tricks: Adding a New Entry to Index Cube via Dynamic Report

IBM Planning Analytics Tips & Tricks

IBM Planning Analytics Tips & Tricks: Adding a New Entry to Index Cube via Dynamic Report

 

Tips & Tricks

May 10, 2022

By Revelwood

If your IBM Planning Analytics model contains a cube that uses an index or slot dimension, you may encounter a situation where you’ll need to create a template that will allow your users to add data to an unused index.

Below is an example of a staffing cube that has slots for open positions in the Employee dimension. This example is a dynamic report that’s zero suppressed to show only indexes that have value.

IBM Planning Analytics Tips & Tricks: Adding a New Entry to Index Cube via Dynamic Report

If users want to update the first three open positions in the report, then there isn’t an issue. However, if they want to add a new open position, how will they be able to do so since position O-004 isn’t displayed due to zero suppression?

The straightforward answer is to unsuppress zero. But what if your index dimension contains hundreds or thousands of elements? One such example is a CapEx model in which each asset is represented by a different index. Unsuppressing the report could include hundreds or thousands of blank rows.

Since we’re working with a dynamic report, we can use MDX to create a dynamic subset that will limit the number of rows displayed when used in conjunction with unsuppressing zeroes. The MDX formula will then be reference by the TM1RPTROW function. We’ll use the HEAD MDX function which has the following syntax: 

HEAD( {set}, Count). 

In our example, it can be written as such to return a total of 5 elements:

HEAD(DESCENDANTS([bpmStaff_Employee].[Total Open]),5)

IBM Planning Analytics Tips & Tricks: Adding a New Entry to Index Cube via Dynamic Report

In most index cubes, there will be a Count measure to let you keep track of how many indexes are used. Use this Count measure to dynamically determine the Count parameter of the HEAD MDX function. In our example, our cube has a Headcount measure since we’re in the Staff cube. This will serve as our Count measure.

If we rewrite our MDX to be the one below, it’ll return six total rows:  three for the existing open headcount and three extra which we added in the formula. Our report contains the Total element, so it only shows 2 extra indexes at the end.

=”HEAD(DESCENDANTS([bpmStaff_Employee].[Total Open]),”&DBRW(…,”Headcount”)+3&”)”

IBM Planning Analytics Tips & Tricks: Adding a New Entry to Index Cube via Dynamic Report

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Using Charts and Data Series with Dynamic Reports

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Process Editor Function Help

IBM Planning Analytics Tips & Tricks: Refresh PAW Visualizations Automatically

    • Categories

      • Awards & Recognition
      • FP&A Done Right
      • IBM Planning Analytics Tips & Tricks
      • Modern Accounting
      • News & Events
      • Success Stories
      • Tech Bulletins
      • Workday Adaptive Planning Insights
      • Workday Adaptive Planning Tips & Tricks

    Popular Posts

    • Workday Adaptive Planning Tips & Tricks: The Formula Assistant – How To, Where & Why
    • IBM Planning Analytics Tips & Tricks: Troubleshooting Planning Analytics for Excel
    • Workday Adaptive Planning Tips & Tricks: Templates
    • Workday Adaptive Planning Named a Leader in Gartner Magic Quadrant for Cloud Financial Planning and Analysis Solutions 2020
    • Workday Adaptive Planning Tips & Tricks: Excel Reporting Using a Report Template

    Sign up for our newsletter

    Connect

    Authors

    • John Pra Sisto
    • Revelwood
    • Adam Riskin
    • Brian Combs
    • Dave Miersch
    • Dillon Rossman
    • Gary Leiffer
    • Ken Wolf
    • Koury Reid
    • Lee Lazarow
    • Lisa Minneci
    • Marc Assenza
    • Mary Luchs
    • Michelle Song
    • Nina Inverso
    • Summer Jeter
    • Tara Byrnes
    • Thanh Chau
    • Wesley DeMarco

Footer

Revelwood Overview

Revelwood helps finance organizations close, consolidate, plan, monitor and analyze business performance. As experts in solutions for the Office of Finance, we partner with best-in-breed software companies by applying best practices guidance and our pre-configured applications to help businesses achieve their full potential.

EXPERTISE

  • Workday Adaptive Planning
  • IBM Planning Analytics
  • BlackLine

ABOUT

  • Who We Are
  • What We Do
  • How We Help
  • How We Think
  • Privacy

CONNECT

Contact:
25B Vreeland Road, Suite 111
Florham Park, NJ 07932

201.984.3030
info@revelwood.com

© Revelwood Inc. All rights reserved. Revelwood® and the Revelwood logo are registered marks of Revelwood Inc. 

FP&A Done Right

FP&A Done Right: ESG Reporting Tool

FP&A Done Right

Choosing the Right Accounting Software for your CFO Tech Stack

Modern Accounting: Easier Intercompany T

Modern Accounting