Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

PAfE and "trace" functionality

  • 1.  PAfE and "trace" functionality

    Posted Wed April 29, 2026 02:00 AM

    Sometimes you just take things for granted.

    At our company, many users were still relying on the "TM1 Trace" functionality from the legacy/community-driven Excel add-in "TM1 Tools."
    Since this feature is not only the most popular one, but also more or less the only functionality that still works with PAfE, we decided to replace it by porting its functionality into our own Excel add-in.

    Only now do I find myself wondering:
    Why isn't a "TM1 Trace" functionality included in PAfE?

    I mean, who can easily read a formula like this?

    =DBRW($A$1;$B$2;C$10;$B$7;$B$3;$A11;$B$4;$B$5;$B$8;$B$6)

    Why isn't a proper "trace" feature part of PAfE?

    trace

    Curious to hear if others feel the same, or if I'm missing something obvious here.

    Best regards,



    ------------------------------
    Herman Teeuwen
    Canon Production Printing
    ------------------------------


  • 2.  RE: PAfE and "trace" functionality

    Posted Wed April 29, 2026 05:10 AM
    Hi Herman,
    Perhaps I have misunderstood what you are hoping to get from a Trace function so forgive me if I have over simplified the problem.
    Have you come across the Excel functionality where when you go into the function editor to edit the DBRW formula and it highlights each of the elements on the sheet in a colour coded way. Then you can combine this with the Trace Precedents and Trace Dependents functions in Excel to trace where each of the elements derive from or feed into.
    Hope this helps
    Shaun


    Shaun Richardson

    Financial Systems Analyst (Planning and Forecasting)

    Corporate Processes and Systems Programme

     

    Uni of Leeds

     

    E-Mail: s.t.richardson@leeds.ac.uk

    E-Meet: Microsoft Teams (Preferred)

     

    Please note I am working from home and can be reached via email and Microsoft Teams.

     

     

     






  • 3.  RE: PAfE and "trace" functionality

    Posted Wed April 29, 2026 01:09 PM

    Hey!

    Good question. We will be working on adding trace functionality to Planning Analytics Universal reports that'll let you see where your cell data is coming from.  Think of it like cell details, and it will include the return of the classic trace. 

    This will work in websheets too, so you'll be able to trace Excel formula-derived values alongside your TM1 data.

    We don't have a delivery date yet, but it's on our to-do list. 



    ------------------------------
    Xavier Osorio
    Planning Analytics
    Senior Product Manager
    IBM
    ------------------------------

    Original Message


  • 4.  RE: PAfE and "trace" functionality

    Posted Thu April 30, 2026 08:42 AM

    Hello,

    I obviously welcome new functionality in any area. But for this one, it could already help if you click on the fx to the left of the formula bar in Excel. This works for any formula in Excel.



    ------------------------------
    Wim Gielis
    Senior Consultant
    Aexis International
    Sint-Stevens-Woluwe
    +32496225001
    ------------------------------

    Original Message


  • 5.  RE: PAfE and "trace" functionality

    Posted Thu April 30, 2026 09:39 AM

    Correct Wim, my comment is focused on Universal Reports. Since these don't include a formula and it does get harder to "trace" the value. 



    ------------------------------
    Xavier Osorio
    Planning Analytics
    Senior Product Manager
    IBM
    ------------------------------

    Original Message


  • 6.  RE: PAfE and "trace" functionality

    Posted Thu April 30, 2026 09:15 AM

    You can use this Excel function to parse DBRWs (instead of the formula bar). You'll need Excel 365. E2 is the cell with a DBRW

    =MAP(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(E2),"("),")"),","),LAMBDA(cell,INDIRECT(cell)))

    For semicolon separators:

    =MAP(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(E2),"("),")"),";"),LAMBDA(cell,INDIRECT(cell)))



    ------------------------------
    Ivan C
    ------------------------------



  • 7.  RE: PAfE and "trace" functionality

    Posted Fri May 01, 2026 03:34 AM

    Nice one Ivan, thanks for sharing.

    These, using semicolon for a separator, worked for me:

    Horizontal display:

    =MAP(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(E2);"(");")");";");LAMBDA(CELL;INDIRECT(CELL)))

    Vertical display:

    =MAP(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(E2);"(");")");;";");LAMBDA(CELL;INDIRECT(CELL)))

    The cool thing about the trace in "TM1 Tools" is that it automatically shows the dimensions of a cube in the correct order.

    This make "debugging" formulas errors quite easy:

    trace compare

     



    ------------------------------
    Herman Teeuwen
    Canon Production Printing
    ------------------------------

    Original Message


  • 8.  RE: PAfE and "trace" functionality

    Posted Fri May 01, 2026 04:26 PM

    Dear All,

    This is very interesting, and I truly appreciate all the responses shared here. I would like to add a perspective based on my own experience.

    When I first started working with TM1 nearly 20 years ago, we used a third‑party tool called Calumo. This add‑in integrated directly with Excel alongside the TM1 toolbar and provided additional functionality that TM1 itself did not offer at the time.

    Please see the screenshot from one of the training slides. As shown, Calumo included a Formula Trace feature that was very intuitive and extremely helpful for users to quickly identify and fix errors within formulas. While I recognize that users are increasingly moving towards formula‑less worksheets, I think we can all agree that DBRW formulas remain the most robust and stable way to connect to the database. In comparison, Quick Reports can occasionally become corrupted, requiring reports to be rebuilt from scratch.

    I would also strongly welcome IBM adding similar formula tracing functionality to PAfE, as this would be an invaluable enhancement for finance users in their day‑to‑day work.

    Another highly useful feature was Clear Data, especially for non‑admin users. This functionality is particularly beneficial during budgeting and forecasting cycles, allowing users to clear previously loaded data and submit revised figures. I sincerely hope IBM will consider adding this capability as well. 😊

    Calumo Toolbar for TM1



    Thank you.



    ------------------------------
    Vincent George
    ------------------------------

    Original Message


  • 9.  RE: PAfE and "trace" functionality

    Posted Fri May 01, 2026 06:26 PM

    Regarding: "as this would be an invaluable enhancement for finance users in their day‑to‑day work."

    Exactly, that's the whole point Vincent. This helps our users to fix their own issues, instead of escalating to our TM1 team.
    Best, Herman


    ------------------------------
    Herman Teeuwen
    Canon Production Printing
    ------------------------------

    Original Message


  • 10.  RE: PAfE and "trace" functionality

    Posted Sat May 02, 2026 02:57 PM
    Edited by Ivan C Mon May 04, 2026 11:09 AM

    Herman,

    Try this:

    =IFERROR(LET(DimCount,10,servercube,TM1PRIMARYDBNAME()&":Revenue",dbrwcell,K42,
    MAP(SEQUENCE(DimCount),LAMBDA(d,TABDIM(servercube,d)&": "))&MAP(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(dbrwcell),","),")"),,","),LAMBDA(dbrwcell,INDIRECT(dbrwcell)))),"")

    DimCount = number greater than the number of dimensions of the cube

    servercube = cube name

    dbrwcell = target cell with the dbrw

    This will not work for DBRW cells with IF statements or other logic inside. The formula is expecting cell references.

    Edit: This version calculates the server & cube name. Keep the DimCount a # larger than the cube then you only have to change the dbrwcell

    =IFERROR(LET(DimCount,10,dbrwcell,K55,servercube,TEXTBEFORE(INDIRECT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(dbrwcell),"("),",")),":")&":"&INDEX(TEXTSPLIT(INDIRECT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(dbrwcell),"("),",")),":"),2),
    MAP(SEQUENCE(DimCount),LAMBDA(d,TABDIM(servercube,d)&": "))&MAP(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(dbrwcell),","),")"),,","),LAMBDA(dbrwcell,INDIRECT(dbrwcell)))),"")



    ------------------------------
    Ivan C
    Revelwood
    ------------------------------

    Original Message


  • 11.  RE: PAfE and "trace" functionality

    Posted Mon May 04, 2026 05:11 PM

    Hi Ivan,

    Thank you for sharing the formula. Unfortunately, it did not work for me, so I may be applying it incorrectly.
    Could you please substitute my parameters into the formula so I can see where I might have gone wrong and understand how to get it working correctly?
    DimCount = 21
    servercube = Server Instance REGION_AM, Cube name FINANCE_AM
    dbrwcell = target cell with the dbrw G4

    Thank you.
    Vincent





    ------------------------------
    Vincent George
    ------------------------------

    Original Message


  • 12.  RE: PAfE and "trace" functionality

    Posted Mon May 04, 2026 05:24 PM
    Edited by Ivan C Mon May 04, 2026 06:33 PM

    This is a more legible* version, with your parameters:

    =IFERROR(
    LET(DimCount,21,
    dbrwcell,G4,
    server,TEXTBEFORE(INDIRECT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(dbrwcell),"("),",")),":"),
    cube,INDEX(TEXTSPLIT(INDIRECT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(dbrwcell),"("),",")),":"),2),
    servercube,server&":"&cube,
    dims,MAP(SEQUENCE(DimCount),LAMBDA(d,TABDIM(servercube,d))),
    elements,MAP(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(FORMULATEXT(dbrwcell),","),")"),,","),LAMBDA(dbrwcell,INDIRECT(dbrwcell))),
    dims&": "&elements),
    "")

    Regards,

    Ivan

    *'more legible' still under debate

    ------------------------------
    Ivan C
    Revelwood
    ------------------------------

    Original Message


  • 13.  RE: PAfE and "trace" functionality

    Posted Tue May 12, 2026 03:06 PM

    Hi Ivan,

    Thank you for providing the revised formula. Unfortunately, it didn't work for me; it gave the following error.
    It's OK, as the other two fomulas you gave are working. That's a grate help! Thank you.

    Error message when uding Trace Formula


    ------------------------------
    Vincent George
    ------------------------------

    Original Message


Related Content