Skip to main content
Version: ADONIS 15/ADOIT 16/ADOGRC 12

MS Office

This article shows how to access the REST API of ADOxx-based products using MS Office's power queries in MS Excel.

Creating the REST request

We want to load a list of all application components and their availability into MS Excel using the search endpoint of our ADOxx-based product's REST API.

We will use the following JSON query to retrieve all application components including their availability attribute.

{
"filters":
[
{"className":"C_EAM_APPLICATION_COMPONENT"}
],
"attributes": ["A_EAM_AVAILABILITY"],
"scope":
{
"repoObjects":true
}
}

We will encode the string representation of this query (e.g. using JavaScript's encodeURIComponent function, or https://www.onlinewebtoolkit.com/url-encode-decode), which results in %7B%22filters%22%3A%5B%7B%22className%22%3A%22C_EAM_APPLICATION_COMPONENT%22%7D%5D%2C%22attributes%22%3A%5B%22A_EAM_AVAILABILITY%22%5D%2C%22scope%22%3A%7B%22repoObjects%22%3Atrue%7D%7D

The URL against which we will now send our GET request is therefore (if our repository has the ID "2b0d81d1-8f9e-415a-94ec-c785ba336adc") http://ADO/rest/3.0/repos/2b0d81d1-8f9e-415a-94ec-c785ba336adc/search?query=%7B%22filters%22%3A%5B%7B%22className%22%3A%22C_EAM_APPLICATION_COMPONENT%22%7D%5D%2C%22attributes%22%3A%5B%22A_EAM_AVAILABILITY%22%5D%2C%22scope%22%3A%7B%22repoObjects%22%3Atrue%7D%7D

Loading data into MS Excel

In MS Excel we need to navigate to the Data tab and select From Web.

Load data from a web source

In the opening dialog, we paste the URL we want to send the request to.

Specify URL to retrieve data from

Since we need to authenticate, MS Excel will ask us for credentials. Select Basic and provide username and password.

Authentication

MS Excel will now load the data from the REST API and show a list of the resulting records. Make sure all entries are selected and click on To Table.

Convert data to table

In the opening dialog, keep the default values and click OK.

Convert data to table - Parameters

The list is now converted to a table and we can expand each contained record using the icon on the right side of the column header. A dialog will pop up in which we select the fields id, name and attributes.

Converted data table Selection of fields to expand

Now for each record, name and id are already shown. Attributes are also shown but as compact list. For the attributes column again click on the icon on the right side of the column header and pick "Expand to New Rows". Select the fields name and value to expand.

Expansion of list column Selection of list fields to expand

Now we have all the data we need on the right layer. On the top left, click on "Close & Load" to get the data into an Excel sheet.

Close and load the processed data

In the sheet, next to the data that is shown, create two new cells "Availability" and "Count". Beneath "Availability", put the formula =UNIQUE(D2:D?) (where D? should point to the last row in your sheet that contains data, e.g. D102 if you have 100 entries).

Excel will then list all of the unique Availability values beneath the cell "Availability". In the cell next to the first value, put the formula =COUNTIF($D$2:$D$?;F2) (where $D$? should again point to the last row in your sheet that contains data, e.g. $D$102 if you have 100 entries).

Copy that value to each cell next to one of the unique values.

Unique Availability values

Unique Availability value count

Finally, select the unique data values and the counts, go to the tab "Insert" and pick a pie chart. This will result in a pie chart showing slices representing the percentage of a certain unique value in the overall set.

Insert chart

Piechart