Connecting Enterprise API reports via Excel
KeyedIn - Configuring the Reporting API Connection:
Open the Login record and navigate to the ‘API Access’ tab
- Enable ‘Integration User’
- Enable ‘Generate API Key’
- Enable ‘Reporting API’
Once these have been enabled, please click on ‘Save and Close’
Click on the name [top menu] and select ‘API Keys’
In the following screen click on ‘Create Key’
Set a name for the API key for identification purpose, Set an Expiry Date (Once the key expires it will no longer connect to the Reporting API, please generate a new API key once the key expires.)
Click on ‘Create’ this will show the following screen:
Click on ‘Copy’ and store the API key somewhere safe as this Key will be required for authorization purposes. Please note this screen will only be displayed once, If you lose your API Key then you will have to create another API Key.
**Please do not share this key with anyone else.
Click on ‘Close’
KeyedIn API connection has now been configured.
Excel – Connect report via Enterprise API
- Click on ‘Get Data’ > ‘From Other Sources’ > ‘Blank Query’
2. Click on ‘Advanced Editor’
3. This will show the following screen:
Remove everything in the advance editor screen and copy the following Syntax into the advance editor:
let
GetPage = (i) => let Page = Json.Document(
Web.Contents("https://keyedinenterprise.co.uk/api/reporting/v1.0/Report"
, [Headers = [
#"Content-Type" = "application/json",
#"Authorization" = "mnintegration@kip.com:kiyfSKDkdikwmsjdudykQ==",
#"KIE-INSTANCE-ID" = "SITEID"]
, Content=Json.FromValue([key = 527, recordsPerPage = 1000, pageNumber = i])
]
)
) in Page,
FirstPage = GetPage(1),
TotalPages = FirstPage[data][totalPages],
PageIndicies = {1 .. TotalPages},
Pages = List.Transform(PageIndicies, each GetPage(_)[data][data]),
Data = List.Union(Pages)
in
Data
If you would like to include parameters then the following syntax is an example of how to pass parameters, The Parameters are highlighted in in bold text:
let
GetPage = (i) => let Page = Json.Document(
Web.Contents("https://keyedinenterprise.co.uk/api/reporting/v1.0/Report"
, [Headers = [
#"Content-Type" = "application/json",
#"Authorization" = "mnintegration@kip.com:kiyfSKDkdikwmsjdudykQ==",
#"KIE-INSTANCE-ID" = "SITEID"]
, Content=Json.FromValue([key = 527, recordsPerPage = 1000, pageNumber = I, Params = [param1 = Resource Name, param2 =Month]])
]
)
) in Page,
FirstPage = GetPage(1),
TotalPages = FirstPage[data][totalPages],
PageIndicies = {1 .. TotalPages},
Pages = List.Transform(PageIndicies, each GetPage(_)[data][data]),
Data = List.Union(Pages)
in
Data
**The Highlighted values will need to be amended to match your URL, Authorization and Report Key.
URL – The following table shows a list of URLs to connect to the reporting API depending on your region and Environment, Replace the Web.Contents with the desired URL.
SITED - This is your Instance ID tied to your environment, It can be found in the About section.
|
Environment |
Region |
URL |
|
Production |
UK |
|
|
US |
||
|
Asia |
||
|
Sandbox |
UK |
https://sandbox.keyedinenterprise.co.uk/sbox-1/api/reporting/v1.0/Report |
|
US |
https://sandbox.keyedinenterprise.com/sbox-1/api/reporting/v1.0/Report |
|
|
Asia |
https://sandbox.keyedinenterprise.net/sbox-1/api/reporting/v1.0/Report |
Green – Input the KeyedIn Username followed by the API key which was copied earlier.
Blue – Input the Report Key here
Sample:
4. Click on ‘Done’
5. Following message may show if there are no credentials provided If so please select ‘Edit Credentials’
6. Select ‘Anonymous’ and the last level to apply the settings to via the drop down
7. Click on ‘Connect’
8. If the connection is successful then the report will be connected to Excel:
Convert data to a table:
- Click on ‘Transform’ and then select ‘To Table’:
2. Click on ‘OK’
3. In the following screen click here
4. Deselect ‘Use Original column name as prefix’ and then click on ‘OK’
The data will now be converted to a table
5. Select ‘Close & Load’
