Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

Perspectives V11 / TM1 formulas return NA

  • 1.  Perspectives V11 / TM1 formulas return NA

    Posted Tue November 10, 2020 11:17 AM
    Hello Community,
    We've got a bunch of users who are complaining because it seems that when they use their "old" Excel workbooks (the ones they use to connect to the V10.2.2) with Perspectives V11, quite often the TM1 formulas break and return NA results.
    Once it has started to return NA results, the only way to get it work again is to enter and validate each individual cell or do a global replace of DBRW with DBRW...
    Users do not understand why it doesn't work as before...
    Anyone facing the same kind of issue ?  
    Thanks


    ------------------------------
    Bob
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Tue November 10, 2020 11:31 AM
    Hey Bob,

    I have found this to be linked to the use of Manual calculation mode, the preferred mode in perspectives. PAfE is designed to work in automatic calculation mode. I also recommend that you find the setting to enable "F9 recalc" since it makes the behavior a bit more familiar.
    Thanks!
    Ryan




    Original Message


  • 3.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Tue November 10, 2020 12:01 PM
    Hi Ryan,
    Thanks for answering so quick. I got a user who tested it right now and still the same issue.
    She copy a cell with a DBRW and as soon as she paste the value in an empty cell, all the TM1 formulas are gone :-(
    It's quite frustrating because I can't share anything with the support as our Workbooks contain sensitive data and we're not able to reproduce on the SData database.
    Regards,



    ------------------------------
    Bob
    ------------------------------

    Original Message


  • 4.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Tue November 10, 2020 06:12 PM
    Bob:

    I first used Perspectives in 1996 (!) and after DBRW() formulas were released to speed up report refreshes over a slow network (originally W stood for "WAN" - Wide Area Network - not for "Write" as many people think today), this was a common problem. The issue was that because DBRW packaged data requests together, Excel did not understand the evaluation order, and so if there was a dependency in which one DBRW referenced another, you would see something like this. Back in the day, the solution (unless you had a slow network) was to replace all the DBRW() formulas with DBR() formulas.

    BUT I take it that this is NOT your problem, since you first started seeing this issue with Perspectives 11 (Planning Analytics). I still work with Perspectives 11 and have not seen this, but I have seen it with the new "Planning Analytics for Excel" add-in, also known as PAfE or PAx. 

    So beyond Ryan's suggestion I am not sure I have an answer for you, but I do want to make you aware of a FREE tool you can download on the IBM Accelerator Catalog called QueBIT Print Reports: https://community.ibm.com/accelerators/catalog/content/QueBIT-Print-Reports-for-PAfE

    Besides the Print Reports feature, it includes an Audit Report feature that automatically reads an Excel workbook with TM1 formulas and highlights potential issues. We developed it because as our clients started moving away from Perspectives to the new PAfE add-in, they frequently had problems with their legacy reports. It turns out that Perspectives was very forgiving when it came to broken formulas - for example you could have broken references in VIEW() formulas and they would still work. PAfE is less forgiving! With the Audit Report feature in the free Print Reports tool, you can easily track all these down. Even if it does not solve your NA problem, it will potentially help you eliminate other problems efficiently!

    You can also find it at https://quebit.com/printreports

    Regards,
    AG



    ------------------------------
    Ann-Grete Tan
    Chief Strategy & Marketing Officer
    QueBIT Consulting

    Analyze. Plan. Achieve.
    https://quebit.com
    ------------------------------

    Original Message


  • 5.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed November 11, 2020 09:24 AM
    I would also double check that you are using the correct capitalization on your server and dimension references.  I don't believe this is an issue on the version of PAfE that I'm running, but I recall something like this causing an issue in an older version when I first went through an upgrade from 10.2. 

    For example, if you have a hardcoded value for your scenario of "actual", but the element name is really "Actual". 

    See if that helps.

    - Jason

    ------------------------------
    Jason Dawson
    ------------------------------

    Original Message


  • 6.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed November 11, 2020 10:14 AM
    Jason:

    Planning Analytics (and TM1 before it) *always* ignored CASE *and* SPACES in dimension element (member) references. In any Excel, Rules or TurboIntegrator function that references dimension elements, these are (by design) the same:

    "actual"
    "ACTUAL"
    "aCtUaL"
    "A  ctual"
    "A c t u a l "

    Now if you insert a period, a dash or an underscore, then it is no longer the same. For example "actual" is NOT treated as THE SAME element as "actual." or "_actual".

    If you have observed something different, then that is very concerning because MANY TM1 models would have stopped working. Is it possible that the situation you experienced was caused by another issue, for example the addition of a period, or a broken VIEW() formula? 

    Another possibility is that PAfE is more picky (which I didn't know). This would imply that most legacy TM1 users have not adopted PAfE yet. 

    Regards,
    AG

    ------------------------------
    Ann-Grete Tan
    Chief Strategy & Marketing Officer
    QueBIT Consulting

    Analyze. Plan. Achieve.
    https://quebit.com
    ------------------------------

    Original Message


  • 7.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed November 11, 2020 09:28 AM
    Bob:

    Just to one up Ann-Grete I have been using TM1 since before Perspectives existed (1989!).  I am not sure what that means other than I am getting old!

    I am not sure I understand exactly what your end-users are experiencing.  In your first post you mentioned "TM1 formulas break and return NA results." but in your second post you stated, "she paste the value in an empty cell, all the TM1 formulas are gone".

    Is the user seeing:
    #N/A?
    #NAME?
    Empty cells?
    All the above?

    Other questions:
    - in the upgrade to Perspectives 11 did you also upgrade Excel (i.e.  Were the original workbooks created in a previous version of Excel?).

    - do these workbooks uses VIEW() functions?  I have seen many cases where VIEW() function exists but are "broken" in that they do not generate a valid view to the cube they reference.  There have been some versions of TM1in the past where the calculation of the workbook would attempt to create the invalid view and disconnect the user from the TM1 Server.  VIEW() functions can be useful in speeding up some Excel reports, but most users do not really understand them that well and in most cases, they cause more problems than they help.  Unless they are well maintained and the user understands how to use them, I usually ask users to remove VIEW functions replacing with the cube reference.

    - are these workbooks just straight DBR(), DBRW) or are they Active Form - RPTVIEW, RPTROW(), etc.

    Dan Bernatchez
    Clear Apex LLC​​​

    ------------------------------
    Daniel Bernatchez
    Clear Apex LLC
    ------------------------------

    Original Message


  • 8.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed November 11, 2020 10:19 AM
    Dan:

    Happy to yield to you on seniority! :-) 

    Here's a walk down memory lane:
    - Spreadsheet Connectors 3.0 and 4,0 on Novell Netware
    - The original TM1 spreadsheet itself - with formulas (???) - I saw this once but never worked with it
    - Processing worksheets (genius idea!)
    - Renaming elements in .XDI files 
    - Building dialog boxes using Lotus 123 macros
    - DBR2, DBR3, etc formulas

    Sigh. The good old days :-)

    AG

    ------------------------------
    Ann-Grete Tan
    Chief Strategy & Marketing Officer
    QueBIT Consulting

    Analyze. Plan. Achieve.
    https://quebit.com
    ------------------------------

    Original Message


  • 9.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed November 11, 2020 05:22 PM
    Bob,

    I have run into this problem in the past when the version of PAFe was not compatible with the PA version installed on the server.  I would confirm that the release you are using is within 3 minor versions of the PA version.

    If you are using PAfe and not perspectives I would also check your PAW version for compatibility issues.   It could be unrelated but might be worth a look.

    Regards,
    Tim

    ------------------------------
    Timothy Corrigan
    Director, Services Delivery
    QueBIT Consulting
    ------------------------------

    Original Message


  • 10.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed November 11, 2020 06:57 PM
      |   view attached
    I would like to add to @Timothy Corrigan's reply that I have seen the compatibility issue between PAfE (Planning Analytics for Excel) and PA too.
    In my case, the PA version was too old and I also received a warning message like the one in the attachment.

    Regards,
    Kong​

    ------------------------------
    Kong Intrasinghathong
    Master Consultant
    QueBIT Consulting

    Analyze. Plan. Achieve.
    https://quebit.com
    ------------------------------

    Original Message


  • 11.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Thu November 12, 2020 10:10 AM

    lot's of expertise in the thread here, so i'll simply add that in PA for Excel, #N/A indicates a disconnected refresh response.

    I'm unclear why such a response would occur in Perspectives, however, since it doesn't generally produce spreadsheet error codes but prefers blank/proprietary error strings.

    in this state, does a ctrl-shift-alt-F9 help?



    ------------------------------
    Ted Phillips
    ------------------------------

    Original Message


  • 12.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Fri November 27, 2020 03:58 AM
    Hello community,
    Thanks for all the replies ! It's a topic which a created a lot of activity !
    Unfortunately, at this step, no solution has been found to help our business users except to do a global replace of DBRW by DBRW.
    One good point is that we've been able to replicate on SData server so we've made a video (see below) which has been transmitted to our support provider and IBM. We're expecting their feedback.
    This issue occurs on Perspectives as on Planning Analytics for Microsoft Excel as well.
    Regards,




    ------------------------------
    Bob
    ------------------------------

    Original Message


  • 13.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Mon November 30, 2020 01:51 AM
    Hi Bob

    This generally occurs due to a corrupt reference in your sheet. A good culprit is the hidden view formula - make sure that only the correct context fields are being used here, and not other dimensions. This typically breaks when users adjust an existing report. They don't update the view formula.

    Cheers,
    Dale

    #PlanningAnalyticswithWatson


  • 14.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed December 02, 2020 08:24 AM
    Hi Dale,
    Thanks for your answer. The worksheet has been created by creating a slice from a brand new view in Cube Explorer (Perspectives) and no modification has been applied to the worksheet after except the copy/paste...
    Regards

    ------------------------------
    Bob
    ------------------------------

    Original Message


  • 15.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed December 02, 2020 11:10 AM
    Edited by System Admin Fri January 20, 2023 04:40 PM
    Hi Robert,

    Had a quick look on my SData sample to create the same view as you.

    Where did JANUARY_MTD come from?  I do not see that in my model.
    Sure it exists in the month dimension?
    Principal name or Alias?

    ------------------------------
    George Tonkin
    ------------------------------

    Original Message


  • 16.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Fri December 04, 2020 09:13 AM

    Hello George,
    You're right that the SData database wasn't a genuine one, so I've resetted it and asked my favorite business user to create a new version of the video and here we are !
    Regards,
    Robert



    ------------------------------
    Bob
    ------------------------------

    Original Message


  • 17.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Fri December 04, 2020 01:30 PM
    Robert:
    Thanks for detailed video.

    I have not been able to replicate your issue.

    Question:
    -  You had previously mentioned testing both PAfE and Perspectives.  If both add-ns are loading they may be conflicting with each other.  Have you confirmed all the PAfE Excel and COM add-ins are disabled and not loading?

    - Does it happen if you leave calc method as manual?  

    Other than that, I am running out of ideas.  Do we need to start looking at versions of the components?
    - Excel
    - TM1 Perspectives
    - PA Server


    ------------------------------
    Daniel Bernatchez
    Clear Apex LLC
    ------------------------------

    Original Message


  • 18.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Sat December 05, 2020 08:50 AM
    HI Robert,

    I am also battling to replicate, just like Daniel.
    If I have the Perspectives Excel add-in and the Office Reporting TM1 add-in both active, I get mixed results - #N/A
    etc.  I have left to COM add-in off as this will load the ribbon etc.

    May be worth checking if there are any add-ins loaded but not visible.  Try the code below and let us know what you have:
    ​
    Sub Addins()
    Dim aiXL As AddIn
    For Each aiXL In Application.AddIns2
    Debug.Print aiXL.Name, aiXL.Installed, aiXL.IsOpen
    Next
    End Sub

    ------------------------------
    George Tonkin
    ------------------------------

    Original Message


  • 19.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Mon December 14, 2020 11:03 PM
    Hi, Robert

    I have a similar issue in PAfE but showing "#Values". As long as I do a global replace of DBRW, it also works. Have you had any clue on it yet? Thanks.​

    ------------------------------
    mvp morgan
    ------------------------------

    Original Message


  • 20.  RE: Perspectives V11 / TM1 formulas return NA

    Posted Wed December 16, 2020 09:37 AM
    Hello,
    Thanks to @frédéric Mallaval we've got a solution which works pretty well : Disable "Enable Live Preview"

    Excel option
    We noticed also that our Excel version has some upgrades which were not applied and which could also help.

    Let us know if it change something for you.
    Regards,
    ​

    ------------------------------
    Bob
    ------------------------------

    Original Message