BI API – sample application with Postman/Excel

With this example application we would like to show you how easy it is to load your BI data via BI API into an external application and continue working with it there.

To create the desired query, we use the API software Postman .

Finally, we load the BI data into Microsoft Excel, where it can then be automatically updated at any time at the push of a button.

Create a BI API query

In order not to have to create the BI API query manually (see BI API article for all necessary parameters as well as sample URLs), we use a tool, in our example Postman .

Open Postman and click on the +:

Into the field GET insert an example BI API URL . This is composed as follows:

• Your Vertec Server URL
• An example appendix: /api/bi/getdata?f=json&measures=minutesint,minutesext&dimension0=Projekt&startDate=2021-07-01&endDate=2021-12-31

It doesn’t matter what the query is, you can just type this example URL. As soon as you enter it, the parameters will be automatically shown. You can continue with this later:

Vertec Authorization

Under Authorization enter your Vertec Api token . Select Bearer Token:

Vertec Versions before 6.6

In Vertec versions prior to 6.6, authorization was done via Basic Auth > Vertec Login and Password. Starting with Vertec 6.6, the Api token is used for this purpose. For reasons of backward compatibility, login via login and password is still supported, but with Vertec 6.7, it is turned off. Until then, you have to change your queries accordingly.

Once you have deposited the authorization, click on Send. Your data will be loaded and shown below:

This will show you that everything has been entered correctly.

Now you can add more parameters here in the Postman by placing the cursor in the field Key and start typing:

To find out which parameters are available, see the getdata section in the BI API article.

As soon as you click Send, the data with the new parameters will be loaded and shown.

Compile the URL that you want to use later in the external program. For our example, we have compiled the following query. Pay attention to the format that the external program requires. For Excel, we set the format (parameter f) on csv:

You can now copy this URL:

In our example, it is:

/api/bi/getdata?f=csv&measures=minutesint,minutesext&dimension0=Projekt&startDate=2021-07-01&endDate=2021-12-31&dimension1=Projektbearbeiter&dimension0_Projekttyp=typ

Loading data into MS Excel

Now let’s load this query into Microsoft Excel. To do this, click Daten > Daten abrufen und transformieren > Aus dem Web (the terms may vary depending on the language and version):

The following dialog will appear:

• Select Weitere
• Paste the URL copied above
• For the HTTP request header parameter, type Authorization in the field on the left. This text must be typed manually.
• In the field on the right, enter Bearer manually and then paste your Api token .

Click on OK. The first time you run it, the following dialog will appear:

Click Verbinden.

Now the preview window with the data appears. It is important that you change the encoding to UTF-8:

If you now click on Load, the data is inserted into the workbook.

You can now continue to work, create look-ups, pivot tables, etc.

You can access the data via Daten > Alle aktualisieren and reload from Vertec at any time:

Changing login data later

If you need to change the login data later, they are stored here: Daten > Daten abrufen > Datenquelleneinstellungen:

Login via API Token

1. Berechtigungen bearbeiten > Anmeldeinformationen bearbeiten > Anonym:

   

2. Daten aus dem Web > Weitere> Set parameters of the HTTM request header (see above)

Registration via login name + password

Berechtigungen bearbeiten > Anmeldeinformationen bearbeiten > Standard.

This type of login only works up to Vertec version 6.7. We recommend that you use the Api token login.