Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  Scripting PAX API with Excel application hidden

    Posted Fri February 25, 2022 10:30 AM
    Hi,

    Does anybody know if it's possible to script through PAX api with Excel Application hidden?

    When executed with Excel visible everything works just fine, but with Excel hidden, script hangs and Excel has to be killed. I'm getting errors saying that remote procedure call failed.

    Best regards,
    Herman

    ------------------------------
    Herman Teeuwen
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: Scripting PAX API with Excel application hidden

    Posted Mon February 28, 2022 10:01 AM
    In general, I would say it should work. Perhaps you can share additional details?

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

    Original Message


  • 3.  RE: Scripting PAX API with Excel application hidden

    Posted Tue March 01, 2022 03:24 AM

    Sure I can.

    I was testing report bursting via an app and noticed that when executing the bat file that triggers the app directly on the TM1 server, logged on with the service account, everything worked just fine (Excel running in interactive mode).
    When kicking of the same bat file via TI process, Excel runs hidden on the TM1 server and hangs. I have to kill the task manually and after that I get a message saying that Remote procedure call failed.
    On the TM1 server I applied all the suggested fixes that circulate on the internet like adding Desktop folder, DCOM settings etc., as suggested for example here.

    I first thought it was maybe related to the stack I'm using (Python & xlwings), but I was able to replicate the same behavior on my workstation with vbs and Excel visible vs. hidden.
    Through vbs I'm opening an xlsm with the VBA posted here on IBM page. This two-step logon is needed in case of security mode 5/CAM authentication.
    I then open a PAfE report and call the VBA to logon and refresh data. This works just fine in interactive mode with Excel visible, but Excel hangs when executed hidden. After I kill Excel, I get the same message saying that Remote procedure call failed.

    After that I did some further testing and I think it's related to the two logon statements.
    I read on this forum about a trick to automatically logon to PAfE by integrating a hidden quick report and VBA to trigger a refresh of this quick report. When I use this and comment out the logon statements from VBA macro everything works fine whether Excel is visible or not. This is not a real word option though because service account is not a TM1 admin.



    ------------------------------
    Herman Teeuwen
    ------------------------------

    Original Message


  • 4.  RE: Scripting PAX API with Excel application hidden

    Posted Tue March 01, 2022 10:18 AM

    in the negative outcome, are you running the program in windows session 0? similarly/if that's unclear, is TM1 server running 'as a windows service'?

    if yes, would it vary if you ran the server as a desktop app? (s instead of sd)

    i can imagine a number of challenges if you're looking to run Office completely headless, and for your local test outcome that may be a distinct set of issues as well.  There are some options regarding log in (leveraging a 'sticky' report is one), but I'd like to make sure your circumstances are good and you wouldn't just run into other issues as you proceed.

    Can you email me a sample of your vbs?



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

    Original Message


  • 5.  RE: Scripting PAX API with Excel application hidden

    Posted Wed March 02, 2022 10:25 AM
    Herman,

    You mentioned that the issue is only occurring when the authentication code is included. This could be a side effect of the way PAX stores cookies. As I understand, a browser is launched during authentication in order for the cookies to be retrieved for future API calls. Since the authentication window is a child process of the Excel Application, a headless (not visible) instance could possibly cause issues with a browser launch and cookie retrieval.

    I noticed that you're attempting to automate PAX as a way to burst out reports. QueBIT has a free product called Print Reports which is an Excel Addin specifically designed to burst out PAFE reports. https://quebit.com/printreports There is also a paid version which integrates with TI Scripts and allows for scheduling. https://quebit.com/reportworq

    Thanks,

    ------------------------------
    Matt Hopkins
    Senior Software Engineer
    QueBIT Consulting
    ------------------------------

    Original Message


  • 6.  RE: Scripting PAX API with Excel application hidden

    Posted Thu March 03, 2022 07:12 AM
    Hi Matt,

    OK, I sent vbs sample to Ted/IBM Support and I'm waiting for their feedback.

    I am aware of both products, QueBIT demoed ReportWORQ to us a bit more than year ago. Impressive but unfortunately no budget available here...

    Thanks,

    Herman

    ------------------------------
    Herman Teeuwen
    ------------------------------

    Original Message