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 [email protected] 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 GitHubhttps://github.com/cubewise-code/tm1py/issues/289, or unsubscribehttps://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")