Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

Effective updating of String data via PAfE Custom Report

  • 1.  Effective updating of String data via PAfE Custom Report

    Posted 01/26/24 11:11 AM

    This is not an unusual client request but one that I have not been able to ever solve elegantly whether in Perspectives or PAfE.

    A client has a custom report that contains Sales related data for example.
    The columns give them values for Actuals, Budget, Variance and another column for Commentary
    The rows have leaf levels and various rollups in a fixed structure - no dynamic reports, sets, VBA etc.

    The client want to enter comments in the Commentary column which is linked to the relevant Period, Scenario and Leaf or C levels for Product (keeping it simple).
    The Commentary field has a DBRW allowing any data type in to be sent and updated in the cube. Works as expected.

    In the use case, a sales consultant would be expected to put in commentary justifying the variance. They can enter their comments and all is great.
    The manager then refreshes the template, sees the commentary but needs to update/amend/add to what is there.
    This is where things start going South.

    In Perspectives, one could F2 to edit the cell, F9 to evaluate the DBRW and return the text, update/amend and then press enter.
    The text per the formula bar would then be sent back to the cube and everyone could see the update.

    In PAfE, attempting the same sequence leaves you with RECALC_0_0_
    Ctrl + C will copy the formula unless you then paste to notepad and then update there and paste back into Excel to send to TM1.
    Trying to have a combination of DBRW and DBSS on one sheet will probably implode.

    Are my only options to push users to TM1Web and/or PAW and a websheet embedded into a book, where they can edit comments simply?



    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------


  • 2.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/26/24 01:12 PM

    Hiyo George, my first question would be what is the current value on this setting?
    https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-refresh-data-writeback

    for the use case you describe i'd expect it to probably to be worksheet (or constrained calc) for something to feel 'good' in the Perspectives compare.



    ------------------------------
    Ted Phillips
    IBM
    ------------------------------



  • 3.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/26/24 02:33 PM

    Hi Ted,

    Thanks for the reply. I have tried None, Worksheet and Workbook.

    Data entered into the DBRW is fine, it is sent to TM1 and is accurate in the cube and refreshed in Excel.

    The issue is more around have a chunk of text in a string field i.e. some kind of narrative, which needs to be updated.

    Essentially we need to retrieve the text in the cell, be able to add or amend this and then write it back without copying it, pasting it elsewhere, amending then pasting as text in the original cell.
    In Perspectives, pressing F9 with the formula active would evaluate the DBRW and return the text. Now we end up with the RECALC message.

    I also tried setting "Refresh data on Excel recalculation Keys (F9, Shift F9) - Restart Excel after change", restarting Excel, logging in etc. but no luck.

    Hope that this is explaining the issue a bit better.



    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------

    Original Message


  • 4.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/26/24 03:41 PM

    I did catch that part of the description, but i know that PA for Excel improved handling of F9 while in edit-mode in late '22, so I figured that part was at least somewhat covered, perhaps there is a gap in there still.

    In the most basic incantation, hitting F9 on the formula or a selected argument in the formula, should reveal the computed value now (assuming no upstream invalidation/pending work in the worksheet construct). 

    Are you not seeing that? What version¿ that feature was done in the low SC80's.



    ------------------------------
    Ted Phillips
    IBM
    ------------------------------

    Original Message


  • 5.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/27/24 12:22 AM
    Edited by George Tonkin 01/27/24 12:24 AM

    HI Ted,

    PAfE is version 2.0.92.7

    In the above I am in Automatic calculation mode in Excel. I make some updates then F9 in the formula. RECALC_0_0 is returned.
    I then go to Manual calculation but F9 behaves the same in the formula bar. Was expecting either the old value or Peggy.

    Custom Report settings per below:

    Any other tips or ideas?



    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------

    Original Message


  • 6.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/31/24 03:24 AM

    Hi George,

    Have you tried replacing the DBRW formula with a DBR formula? Does this make any difference?

    Kind regards,

    Luc



    ------------------------------
    Luc Cusiel
    ------------------------------

    Original Message


  • 7.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/31/24 04:19 AM

    HI Luc, thanks for the suggestion.

    DBRW and DBR are identical in PAfE as far as I am aware. This was also confirmed previously on an AMA where the questions was asked in the context of dependencies and solve order. Same applies to DBSW/DBS.

    However, knowing this, I did try both options and they do behave in exactly the same way i.e. not fixing my issue.



    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------

    Original Message


  • 8.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/28/24 01:41 PM

    Hi George,

    You need to enable constrained calculation to reinforce that behavior in PAfE, if it isn't already enabled. You may do so by adding the below line in your tm1features.json file:

    { "r56_EnableManualConstrainedCalc": true }

    Once you do that, you will notice an additional option (Constrained Calc Scope) showing under "Refresh data on writeback". Select that and you should be able to use F2+F9 to update the values in PAfE. 

    Regards,



    ------------------------------
    Amin Mohammed
    ------------------------------

    Original Message


  • 9.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/29/24 03:37 AM

    HI Amin,

    Thanks for the above. I added the setting and could see the Constrained Calc Scope option in my PAfE.
    The F2, F9 now works.

    What is curious is I can now remove that setting, set it to false and leave the Refresh data on writeback to None and F2, F9 does the evaluation, just like I needed it to.

    On a colleague's machine, he did not need to make any changes, F2, F9 worked for him.
    Main difference is likely that 2.0.92 is the first version of PAfE ever "installed" on his machine.

    Will need to test further with the client who has come through 2.0.48 and is now on 2.0.92



    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------

    Original Message


  • 10.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/30/24 08:55 AM

    Seems like my luck has run out. Today back to square one.

    I have enabled the constrained calculation, set the option in PAfE options but what worked yesterday no longer works today, not for myself or my colleague.

    The recording above shows that the option is set.
    Updates typed in work but on entering the formula bar and pressing F9 no longer evaluates the function and returns the comment.
    @Ted Phillips - Any ideas you could share please? Anyone else?

    Thank you.


    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------

    Original Message


  • 11.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/30/24 09:57 AM
    Edited by Walter Coffen 01/30/24 09:58 AM

    George,

    How about a Universal Report? There are no DBRWs, just numbers or strings. You just have to remember to hit Commit... and be on the latest versions for the best experience.



    ------------------------------
    Walter Coffen
    Technology Manager
    QueBIT Consulting, LLC
    ------------------------------

    Original Message


  • 12.  RE: Effective updating of String data via PAfE Custom Report

    Posted 01/30/24 10:02 AM

    Thanks Walter - I think will be going down that avenue soon. 

    Just apprehensive due to some of the quirks in the current format and references to attributes and parents on the rows.
    On a positive note, will definitely get some experience and potentially learn the limitations and benefits of URs.



    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------

    Original Message


Related Content