The Reporting API allows you to load your Meisterplan data into a Microsoft Excel file. You only need to enter a few details to download the data you need for your reports.

• Prerequisites
• Loading Meisterplan Data in MS Excel
• Get Data
• Transform Data
• Loading Data with a Report Template

Prerequisites

Ensure that the following prerequisites are met to access Meisterplan data with MS Excel:

• Create an API token in your Meisterplan user profile and save it to a secure location.
• Install MS Excel 2019. Earlier versions do not support the required queries.

Loading Meisterplan Data in MS Excel

Download your Meisterplan data into a MS Excel file and create pivot reports, import the data into other tools or combine it with data from other sources.

Get Data

To retrieve the data of an endpoint, follow these steps:

1. Open an empty MS Excel file and save it to your computer. Then select Data > Get Data > From Other Sources > From Web:
   
2. In the From Web window, select the Advanced option and enter the URL of the respective API endpoint in the URL parts field. Under HTTP request header parameters, instead of selecting from the dropdown, click into the box and type in "Authorization" and in the next box paste your API token:
   
3. When you click OK, MS Excel retrieves the data and the Power Query Editor window opens where you can transform the data into a table.

Filtering with Query Parameters

Query parameters are filters that define which endpoint details should be retrieved. This could be, for example, the date range with the query parameters startDate and finishDate, or the required scenario with the parameter scenario. If no filters are defined, default values are used.

This is how you add a filter to your query:

"<URL endpoint>?<query parameter>=<value>"

You can combine two or more filters with "&":

"<URL endpoint>?<query parameter>=<value>&<query parameter>=<value>"

For example:

"https://api-reporting.us.meisterplan.com/v1/projects?startDate=2020-01-01&finishDate=2020-12-31"

Transform Data

The Reporting API endpoint returns data in a so-called JSON format. In the MS Excel Power Query Editor, you can transform the data into a table in three easy steps:

1. Click List to transform the original JSON object into a list that contains the elements of the next level:
   
2. Click To Table and accept the default settings in the new window by clicking OK. The list is then transformed into a table, and each line contains a JSON object with the values of the respective columns:
   
3. Click the double arrow icon on the upper right of the column and then click ;OK. The available fields are extracted and transformed into table columns. You can uncheck the Use original column name as prefix box as this is not necessary with this data:
   

If you want to undo one or more of the steps described above, you can do this from the Applied Steps list on the right:

Your data is now ready for further processing. Click Close and Apply in the upper left to load the data and return to the main screen.

Repeat these steps for each endpoint whose data you want to load in MS Excel. You can find all API endpoints with a short description and its respective URL in the API documentation.

Loading Data with a Report Template

Get started with our MS Excel template and load the data of three endpoints at once: Projects, Allocation Slices and Resources. Two pivot tables give an example of how you can use the raw data for creating reports, e.g., to display allocations per projects or per resources.

Simply enter your API token and your server's location, then go to the Data tab and click Refresh All. Refresh the two pivot sheets, too, or start preparing your own pivot table from the raw data.

Note
You need to have MS Excel 2019/365 installed. Earlier versions do not support the required queries.

Download Excel Pivot Report Template