Query report data via REST API

Query data from CONNECT reports via a REST API, for further analysis in third-party tools such as Microsoft Power BI.

Follow these steps to retrieve report data into Power BI Desktop.

1. Create an access token with at least a Report Viewer role. See Create access tokens for details.

2. In Power BI Desktop, from the Home ribbon tab select Get data and then Blank query. The Power Query Editor opens.

3. From the View ribbon tab of the Power Query Editor, select Advanced Editor. The Advanced Editor dialog opens.

4. Enter your query into the Advanced Editor. The following is an example query to retrieve credit transaction data. Replace YOUR_ACCESS_TOKEN with the access token you created.

   let

   url = "https://services.connect.aveva.com/reports/v1/integration/creditsTransaction",

   headers =

   [

   #"Content-Type"="application/json",

   #"Authorization"="Bearer YOUR_ACCESS_TOKEN"

   ],

   body = "{""fromDate"": ""2023-08-1"", ""toDate"": ""2023-08-30""}",

   source = Json.Document(

   Web.Contents(

   url,

   [

   Headers = headers, Content = Text.ToBinary(body)

   ]

   )

   )

   in

   source

   See Report data REST API reference for more information on the available REST API calls.

5. Select Done. The Advanced Editor closes and the data are retrieved as a series of records. Select results in the Query Settings pane to see the list of records.

6. In the Transform ribbon tab, select To Table. The To Table dialog opens.

7. Leave the settings as they are and select OK. The list of records is converted into a table.

8. Select the Expand Columns icon. A list of columns in the table appears.

9. Select the check box next to each column you want to show, then select OK.

10. If desired, rename columns or the query itself, in the Name field of the Query Settings pane. When finished, from the Home ribbon tab select Close & Apply. The Power Query Editor closes and the query results appear in the main Power BI Desktop window.