In order to easily use data from smenso Cloud in other tools, a Reporting API is available for each Workspace.
Through this Reporting API, data can be retrieved live from smenso Cloud and used in other tools for reporting or analysis purposes.
This article describes how the Reporting API can be used to make smenso Cloud data available in Microsoft Power BI.
Since the data from smenso Cloud is not simply exported but synchronized, authorization is required at the Reporting API.
Each smenso Cloud user requires its own authentication. This ensures that no unauthorized user can access data via the Reporting API to which they have no access.
The authentication is done via a token. This token can be obtained here:
- Open https://admin.smenso.cloud and log in with your user name
- Change to the tab "API Keys" in your profile
- Create a new key via "Add Key
- Select the workspace
- Create an optional description, e.g. "Excel Reporting".
- Select an expiration date for the API key
- Create the key via "Add
Copy the token from the following dialog.
You need this Authorization Header for Power BI
API URL of the project view
In order for Power BI to know which data is to be used, a view must first be created in smenso Cloud. This view is used to define which fields are to be transferred to Excel. For example, a separate view can be created for reporting only, in which a large number of fields are available.
In the following example, the view "🦅 Project overview (list)" was selected in the portfolio. This view contains all the desired projects and fields that are to be synchronized.
An API URL is required for Power BI. This API URL is provided directly via the view. To do this, open the drop-down list of views and click on the Share icon.
In the following dialog you will find the area with the API link of the view:
Now you only have to copy the link.
Reporting in Power BI
If Power BI Desktop is not already installed, you can download it here:
After opening Power BI, we find ourselves on the start page:
Here we click directly on REQUEST DATA and search the following mask for "Web", select the appropriate result and then click on "Connect".
In the following mask we select the option "More" and the following mask appears:
- The copied API link of the desired view is pasted here
- The word "Authorization" must be entered here
- The copied authentication token must be entered in this field
After everything has been entered, confirm the mask with "OK" and the preview table with the data will be loaded directly. This can be confirmed with "Load" and Power BI then creates the connection to smenso Cloud and builds all fields that are contained in the view so that they can be used directly for the creation of graphics, tables, etc..
Note on the language
Since our servers are English language systems, certain values may be displayed incorrectly due to a different locale (comma, dot, etc.).
To avoid this, you can explicitly specify the corresponding locale scheme for the language in the API URL. To do this, simply add the following for e.g. Germany: "&lang=de".
The following are available: DE, US, EN
If you want to evaluate data from a single project in Power BI, you just have to use the API link for the desired view in the list of the corresponding project.