Update quick report PAW "host" link in PAx reports using TM1Py

[vishwesh999]

Hi

I am looking to find out if there is a function within TM1py v1.5 to update quick report PAW "host" link in PAx reports using TM1Py. If this functionality currently doesnt exist and if it is possible via REST API, can this be added to enhancements list for next releases. Currently when we move between environments, only way to change the host link is to go into each excel file and change the host link for all quick reports in PAX.

Happy to provide more context into the use case if required.

Thanks
Vishu

[rclapp]

Pax reporting functionality is not available via the tm1 server api. Those types of things can be handled via the Pax vba api, and you should look to build something using that. Ryan Clapp Sr. Manager AWS FinTech

…

Sent from my mobile device On Jul 16, 2020 1:32 PM, vishwesh999 <notifications@github.com> wrote: Hi I am looking to find out if there is a function within TM1py v1.5 to update quick report PAW "host" link in PAx reports using TM1Py. If this functionality currently doesnt exist and if it is possible via REST API, can this be added to enhancements list for next releases. Currently when we move between environments, only way to change the host link is to go into each excel file and change the host link for all quick reports in PAX. Happy to provide more context into the use case if required. Thanks Vishu - You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<#289>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AEK7GZQ3WSQQQ5ZN7BBWZVTR35PJXANCNFSM4O44LHEA>.

[vishwesh999]

Got it, thanks for quick response - will look into the PAx VBA API.

Regards
Vishu

[MariusWirtz]

Hi @vishwesh999, Hi @rclapp,

I don't think this approach necessarily leads to a dead end.
I took a closer look at the xlsx files of the quick reports and found that the relevant information (server) is stored inside a JSON in a named range.
The key is tm1\\_0_H.
With a python excel library like openpyxl we can manipulate this value (without actually opening the file with excel).
Below you find a sample.

Obviously, with TM1py we can retrieve any applications as an xlsx file - and send it back to TM1.
More analysis is needed in order to assess the feasibility, but I will re-open this issue for now.

from openpyxl import load_workbook

wb = load_workbook('excel.xlsx')
defined_name = wb.defined_names[r"tm1\\_0_H"]

del wb.defined_names[r"tm1\\_0_H"]

defined_name.attr_text = defined_name.attr_text.replace(
    "http://rb-paw-x.prod.cubewise.com/",
    "http://rb-paw-x.dev.cubewise.com/")

wb.defined_names.append(defined_name)

wb.save("excel2.xlsx")