Sage 200 - Getting started on creating your own Microsoft Power BI reports

This article 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 article is only to be followed once you have completed the following steps:

• Registered your Office 365 subscription with us, and onboarded to the API
  
  • For Sage 200 Standard Online users, these steps are covered here.
  • For Sage 200 Professional users, these steps are covered here.
• You have already set up the Power BI report.
  • For Sage 200 Standard Online users, these steps are covered here.
  • For Sage 200 Professional users, these steps are covered here.

Before getting started, you will need to download the Power BI demonstration report here, loaded it and connected to your data.

NOTE: This article has been prepared and issued to you as a goodwill gesture only and Sage accepts no liability or responsibility for its use. For further support please refer to Microsoft Support. We will not provide any further support from Sage 200 Technical Support or Developer Support.  

Connecting to your data through Power BI

NOTE: We now have a video covering the setup of Power BI and editing queries within Power BI. Click here to watch.

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)​

   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, 

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.

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.