Skip to content
Developerhome
Product homepage
Sage 200

Getting started - Microsoft Power BI reports

  Less than to read

:

CAUTION: This guide has been prepared and issued as a goodwill gesture only and Sage accepts no liability or responsibility for its use. Sage 200 Technical Support or Developer Support can not provide any further support, for further support please refer to Microsoft Support.

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.

Prerequisites

This guide is only to be followed once you have completed the following steps:

  1. Registered your Office 365 subscription with us, and onboarded to the API

    • Sage 200 Standard Online: These steps are covered here.
    • Sage 200 Professional: These steps are covered here.

  2. You have already set up the Power BI report.

    • Sage 200 Standard Online users, these steps are covered here.
    • Sage 200 Professional users, these steps are covered here.

  3. 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.

  1. Open the Customer report template within Power BI
  2. Go to File > Save As, saving the report with a different name.
  3. Click on Transform Data in the ribbon bar at the top.
  4. 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.
  5. Make sure the Customers (2) node is selected, then in Query Settings on the right, change the name to Banks.
  6. In the ribbon at the top, select Advanced Editor.

  7. In the Advanced Editor, change the second line to read:

     Source = #"API Query"("banks", null, null)​
  8. Click Done to close the Advanced Editor.
  9. Select Close & Apply in the Power Editor Query ribbon.

The Banks table has now been added to the report.

:

Tip: “banks” is the name of the API endpoint we are trying to access. Make sure the endpoint name is typed out correctly in order for this to work, 

The API Query Function

The API Query function takes 3 parameters:

  1. The API endpoint
  2. Optional filters
  3. 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.