Aptem is Microsoft Azure hosted and each MWS client has its own separate database. Microsoft Excel is a powerful reporting tool and is a part of the Office 365 suite. This article describes how you can use Excel to create your own Aptem reports.

Connecting your data to Excel

Open Excel. On the Data tab, select Get Data.

Excel will then request that you select the type of data you need. Select From Other Sources and then click OData Feed. 

Enter the following URL but replace the section highlighted with the URL from your own Aptem account.

This can be found in the address bar as shown when connected to your account.

You will then have created an initial connection between Excel and your Aptem data. At this stage we have still not approved the transit of data. You must complete some more steps to authorise the process.

Select the table from your data that you wish to connect to Excel.

In this example we have selected the table users, but you may select more than one table.

A warning message will appear in the righthand pane; ignore this as we still haven’t completed all the steps required, click cancel.

Click on Load once you have chosen your table(s).

A dialog box will appear. Click Cancel.

A warning message will be displayed. Click OK.

From the Excel menu choose Get Data, select Launch Power Query Editor.

Click Users, highlighted in green, then click Advanced Editor in the menu section

The advanced editor window will now appear. 

At this stage we have still not authorised the connection between Excel and your live Aptem data. To do this we need to insert an API key.

We need to replace the section of the text current called Null with the API key that can ONLY be generated by a member of the MWS / Aptem Admin team (such as your implementation consultant or a member of the support team). An API token will be generated for a named user in your organisation. This user must have administrator-level privileges assigned to them.

A long string API will be sent to the named user when requested. This will be in a plain text format and will need to be copied precisely to ensure all parentheses and commas are included. You can copy the key to a text editor like Notepad, You must add [#"X-API-Token before the key, and add "] after the API key.

Whilst still within the Advanced Editor screen from the steps above carefully replace the previous Null value with the long API key that has been issued to you.

Click Done.

Then click on Edit Credentials and you will be prompted to connect the data

You will know the connection has been successful when you can see the contents of that table displayed in the query editor screen and the yellow warning symbol has disappeared.

You can then select the desired columns by using the manage columns in the menu bar. Once you have your chosen columns click close and load.

You now need to repeat this process for any other tables you wish to import.

The data you have connected in the steps above will be your organisation’s live data. Data in Excel is read-only and will not be written back to your Aptem system. However, for security reasons, caution should be exercised as to who has access to the tables in Power BI. If any data appears to be missing/incomplete it will be because this data has not been sufficiently entered into your Aptem system.