How to connect to Atria External Billing API via Excel

How to connect to Atria External Billing API via Excel

Atria External API via Excel

This document illustrates how to use Atria's external API platform to retrieve billing invoice data via an Excel document.

Applies to:

  1. Atria 12.11 or later

Create Token

Use the following KB as a step by step to receive your API Authorization key. Atria AP

Retrieve End Dates List

Now that you have the token, please run excel and click on the Data Tab, from the ribbon menu select Get Data -> Select From Other Sources and then select From Web.



In the new page click on Advanced and click on URL Parts to enter your external API address:



Now, we need to retrieve all End Dates of billing API. This data will be used as a parameter to receive billing data.
Enter your external API URL to retrieve end Dates:

Note that you have to enter Authorization in the Key field of Http header and input the word Bearer together with your Authorization code that you received previously as shown in the above picture.

Once you click on OK, you can retrieve end Dates as a list.

On the Power Query Editor page which will be opened automatically once you retrieve data from Atria API, under Convert Menu click on parameters Query on the left side of the page and then select RECORD next to the data field


On the next page, you will see endDates and customers Lists. Click on the List next to endDates.
Here we will have data shown in JSON format and to extract data and show it in a table structure, under Transform Menu click on Into Table icon on the top left side of the page:

To Table page will be opened to define the way you want to create your table:


Click on OK. We need to extract columns from the list shown in Column 1, to extract columns click on  icon next to Column1 header:


The column select page will be shown. On the Column Select page, select the columns you want to be shown. In this example, we just need to click on OK


Once you click on OK, the parameters table will be shown and we can use it as a parameter to retrieve our required information.


Now that we have end dates we can use this data as a parameter. On the Home tab click on Manage Parameters and then select New Parameter.
In the newly opened page enter endDates as parameter Name, select Text as Type and enter a current value like '2020-10-31' and click on OK.


At the moment, you have a list that includes your end dates and created a parameter that later on can be used to retrieve all data from the API.

Call Atria External API

Now it is time to call the Invoice API. From the page you are in select New Source -> Other Sources -> Web


Click on Advanced and enter your external API address in the following format:
now click on the add part and select parameter:


Again click on Add part and select Text and enter all the information as follows:
 

Do not forget to click on Http Request header and enter "Authorization" as follows:

Then click on the value part and enter the word bearer with your authorization code that you received previously as follows:


Now, Excel will retrieve the data from the Atria API and you can view data


Click on Record link and then click list of items:


Here you will come up with a list of records inside list columns that each one contains another list of data. In order to extract data click on To Table option on the top left menu and click on the OK in the newly opened page.
Now that the data is converted to a table you can extract data by clicking on the right end side of the Column1  column:


in the opened menu click on OK to view all columns:



From now on, you can view all data by clicking on each extract button on the right end of each column or you can drill down by clicking on the List of the data.
The above data is only for one billing period. In order to get data for all billing periods please click on the newly created query on the left side of the page and then right-click and select Create Function:


On the new page enter a name for your function like 'Get Invoices'. It will automatically pick endDate as a parameter.
On the left side of the page click on the parameters and then click on Add Column tab and select Invoke Custom Function:


Enter the information as follows in the Invoke Custom Function


This will let you to use endDates parameter to send as a parameter to Atria External API and retrieve all invoice data.
Once you click on OK a warning might be shown as follows:


Click on the Continue button and once the following page is shown, click on Ignore Privacy Levels... check box and then click on Save.
The reason you viewed the above warning is associated with accessing two sources of data in your excel page. In our case retrieving data from 2 source in your External API address, thus you can safely ignore it.

Here is a sample page showing extracted data:









    • Related Articles

    • How to connect Power BI to Atria API

      Overview In this article, we are going to show you how to connect to Atria API via Power BI.  Power BI will be able to retrieve data to create visuals and reports based on your requirements. The following article will illustrate a step by step guide ...

    • Billing with Atria

      Atria Billing Index Atria has a systematic approach to tracking services that it manages, this data can be exported or directly integrated via API into external PSA or billing systems. Background Information Quick video on billing and auditing How ...

    • Atria API

      Atria API Purpose The Atria API is a new Rest-based integration point that over time will replace the functionality of the old API, (known as the CortexAPI). If you have developed against The CortexAPI, don’t worry, it’s still working and supported. ...

    • Billing Process Overview

      Objective This article outlines, at a high level, the billing features available in Atria. The billing feature provides transparency of Atria billable services that have been consumed, down to the day. Applies To Introduced in Atria version 12.0.0. ...

    • Atria API User Guide (CortexAPI)

      Overview The application programming interface (API) is a powerful interface that allows you to interact directly with Atria without using the ATRIA Web User Interface (UI).  The API grants a user, with some development knowledge, the ability to ...