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
• Report Template for MS Excel
• How to Use the Report Template
• Loading Meisterplan Data without Report Template
• Get Data
• Transform Data

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. The API token allows access to data according to your user rights.
• You need to have MS Excel 2019 (Desktop or on-premises) or higher installed. Earlier versions or the web version do not support the required queries.

Report Template for MS Excel

The report template for MS Excel contains two pivot tables that display role and resource allocations grouped by month or week. One table displays allocations sorted by project, the other by role and resource.

Download Excel Pivot Report Template

This template loads the data of three endpoints at once: Projects, Allocation Slices and Resources.

How to Use the Report Template

1. Download our report template and save it to your computer.
2. On the first sheet, enter your API token and your server's location (us, or eu) and check the pre-filled details:
   
3. In the Data tab, click Refresh All:
   
4.  Refresh the two pivot sheets by opening them and clicking Refresh All, or start preparing your own pivot table from the raw data.

To refresh your report, click Refresh All in the Data tab of your Excel file.

Loading Meisterplan Data without Report Template

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. Add a question mark at the end, then enter the date parameters in the field below. Without date parameters, only the data for the last 30 days will be loaded in the report.
   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 "Bearer <api-token>" with your API token:
   
3. When you do this for the first time, the Access Web Content window may open. Always select Anonymous here, because the API token is sufficient authentication and other options may cause problems at a later time.
   
4. Depending on the firewall settings of your organization, you might have to enter the privacy level next. Select "Organizational" and click Save.
   
5. MS Excel will now retrieve 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"

MS Excel for On-Premise
You can also create MS Excel reports with On Premise systems. To do so, you need to change the URL of each endpoint to read as follows:
"https://<internal domain>/reporting-api/v1/<endpoint>"

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.