Get Report SQL

[DanDavies1989]

Hey Can we have the ability to retrieve the SQL view of a report object Looks like this functionality is possible using the REST API side of thins but not currently in the Python offering Apologies if I have missed it however

[urszulajaczewska]

Hi @DanDavies1989, we will include this functionality with release of Report module which is already in our roadmap. I'll update this issue when the new module is available.

[DanDavies1989]

Brilliant thank you :)

[AtharvaGaikwad1]

Hii @DanDavies1989 ,

Till the release you try this code in Python to get the report SQL from REST API. Initially you'll have to create a connections object in MSTRIO and then later use it for authenticating while creating instance.

#Creating a instance to get instance ID
params = {'offset': 0, 'limit': 5000}
report_id =  'Your Report ID'
body = {}
create_instance = conn.post(
        url=f'{conn.base_url}/api/v2/reports/{report_id}/instances/',
        json=body,
        params=params,
    )

instance = create_instance.json()
instance_id = instance['instanceId']
print(report_id)
print(instance_id)

# Checking status of the created instance 
url1 = (
        f'{conn.base_url}/api/reports/{report_id}/instances/{instance_id}/status'
    )
Instance_Status = conn.get(
        url=url1,
#        params={'closed': None, 'fields': None},
    )
print(Instance_Status)

# Using the instance ID to get SQL of the report.
url2 = (
        f'{conn.base_url}/api/v2/reports/{report_id}/instances/{instance_id}/sqlView'
    )
sql_View = conn.get(
           url=url2,      
    )
sql_expression = sql_View.json()
sql = sql_expression['sqlStatement'] 
print(sql)