This guide will demonstrate how to add queries into the existing Power BI report using the Sage 200 API to get you started on building your own Power BI reports and dashboards.
This guide is only to be followed once you have completed the following steps:
Registered your Office 365 subscription with us, and onboarded to the API
You have already set up the Power BI report.
Download the Power BI demonstration report here, loaded it and have it connected to your data.
Connecting to your data through Power BI
The Power BI Customers report template that we provide only uses a small set of the data available in your Sage 200 company. If you want to create your own reports, you will probably want to access some additional company data to the customer data available in the report template.
When you use the Sage 200 Power BI connector to access your company data, it does not connect directly to your SQL database, but uses the Sage 200 API. You need to use the Power BI connector if you want to access your data from outside your network, without exposing your SQL server to the outside world.
For help on what data is available in the Sage 200 API and how to query it, see the Sage 200 API documentation.
How to create a new query in Power BI
You will need to use the Customer report we have provided for you, as this has the Sage 200 connector embedded into it, ready to use.
In the following example we will go through adding a new query into Power BI to retrieve Cash Book Account information.
- Open the Customer report template within Power BI
- Go to File > Save As, saving the report with a different name.
- Click on Transform Data in the ribbon bar at the top.
- In the Power Query Editor, in the Queries pane on the left hand side, right-click the Data > Customers node and select Duplicate. A new Customers (2) node in the Data folder is added.
- Make sure the Customers (2) node is selected, then in Query Settings on the right, change the name to Banks.
- In the ribbon at the top, select Advanced Editor.
In the Advanced Editor, change the second line to read:
Source = #"API Query"("banks", null, null)
- Click Done to close the Advanced Editor.
- Select Close & Apply in the Power Editor Query ribbon.
The Banks table has now been added to the report.
The API Query Function
The API Query function takes 3 parameters:
- The API endpoint
- Optional filters
- Optional query parameters
You can add your own additional filters and query parameters by using the Sage 200 API documentation.
Further information on creating visual dashboards and other Power BI functionality can be found in the Power BI help files.