Blog: Consuming web services from Sparkrock 365

Modified on Sat, 20 Jul at 10:18 AM

Sparkrock 365 supports three types of web services: API, SOAP, and OData. Web services are a lightweight, industry-standard way to make application functionality available to various external systems and users. Developers can create and publish functionality as web services, where they expose pages, code units, or queries, and even enhance a page web service by using an extension code unit.

When Sparkrock 365 objects are published as web services, they're immediately available on the network. Sparkrock 365 offers users the ability to query page data in Excel using Web Services. This allows users to have real-time Sparkrock 365 data available in Excel sheets or Power BI that can be queried and saved for future uses or reporting purposes.

In this article, we will be looking at how to create a web service from a page or query, how to make use of an existing web service and the connection with Excel and power bi.

Related: A webinar covering this topic is available in the following link: Web Service Integration in Sparkrock 365: Unleashing Powerful Reporting and Record Management

Accessing/Creating Web Services

In Sparkrock 365, you can go into the web services page to access a list of existing web services or create a new one:

Of the three Object Types, Page is available for the user to control. Query needs development intervention, and Codeunit means running a job queue task that can be scheduled overnight for things such as exchange rates or running tables and tasks.

Under Page, find the corresponding Page number to return data, for example, page 29 for Vendor Ledger Entries. Users can find the page number by going to the table, then clicking the question mark, click help and support, and then inspect pages and data to find the page number:

This page already exists in the list of web services, so we will create a new page web service like the sales invoice, which does not seem to be available:

In the Web Services page, choose New.
In the Object Type column, select Page.
In the Object ID column, select the object ID of the object that you want to expose. For example, to expose the sales invoice as a web service, enter 43.
In the Service Name field, assign a name to the web service. For example, if you expose the sales invoice as a web service, enter Sales_Invoice. (The service name is visible to consumers of your web service. It's the basis for identifying and distinguishing web services, so you should make the name meaningful.)
Select the check box in the Published column to make it available for use.

6. When you publish the web service, you see the URLs that are generated for the web service in the OData URL and SOAP URL fields. You can test the web service immediately by choosing the links in the OData URL. Optionally, click the value of the field in the OData URL to open in a new tab and copy save it for later use.

7. After you publish a web service, it's available across all Sparkrock 365 Server instances running.

8. The OData V4 URL will look like this:

https://sparkrock365.api.bc.dynamics.com/v2.0/00b606da-0000-0000-000-0000000/Sandbox-Finance/ODataV4/Company('eLearning%20Demo')/Sales_Invoice

Using Web Service API in Excel

To use the created Sales invoice web service API in Microsoft Excel:

Click on Get Data.
Click on From Online Services.
Click on From Dynamics 365 (Online).

We use the URL generated in the previous section; sparkrock365.api.bc.dynamics.com/v2.0/00b606da-0000-0000-000-0000000/Sandbox-Finance/ODataV4/Company('eLearning%20Demo')/Sales_Invoice

After pressing the OK button above, you may be prompted with a sign in window like the screenshot below;
In this window:
Click Organizational Account
Click Sign in
A login window will then open where you enter your email then your password
After logging in, you will get back to this window below, and press Connect

The following window will appear, which we will click on either the load button to load the data into excel or Transform Data to open the data in the power query editor.

Use Business Central as a Data Source in Power BI

Use Power BI connector to work with Business Central APIs, instead of with web services only. While Sparkrock 365 already offers tight integration with Power BI, it relies strictly on web services. This feature enables more modern and richer data sources for your reports hosted in Power BI.

With this feature, instead of having to enable pages as web services, you can now create Power BI reports and dashboards using the modern Business Central APIs, including both the built-in APIs v2.0 provided with Business Central and custom APIs created by partners or developers. This gives customers and partners access to better and faster data analytics in Power BI.

The new connector for Power BI has the following features:

Supports v2.0 APIs from Business Central.
Supports custom API pages.
Continues to support web services.
Is fully backward-compatible.
Works with any Business Central online version.
Delivers better performance just by using APIs.

Once you have Power BI Desktop installed, you can use it to import data from Business Central. To add Business Central as a data source in Power BI Desktop, follow these steps:

In Power BI Desktop, in the left navigation pane, select Get Data.
On the Get Data page, select Online Services, select Dynamics 365 Business Central, and then select the Connect button.

Power BI displays a wizard that guides you through the connection process, including signing into Business Central. Select Sign in, and then select the same account that you signed into Business Central with.

Once you've successfully connected to Business Central, you aren't prompted again to sign in.
Select the Connect button to continue.
The Power BI wizard shows a list of Business Central environments, companies, and data sources. These data sources represent all the APIs and web services that you have access to in Business Central. The modern Business Central APIs include both the built-in APIs v2.0 provided with Business Central and custom APIs created by partners or developers. So customers and partners have access to better and faster data analytics in Power BI.
The Navigator window displays these data sources.

Expand the Advanced APIs, Standard APIs, or Web Services. (We will be using the standard API)
The Power BI wizard shows a list of Business Central environments, companies, and data sources. These data sources represent all the web services that you've published from Business Central. The Navigator window displays these data sources.

Specify the data you want to add to your data model, and then select the Load or Transform Data button.
Repeat the previous steps to add more Business Central or other data to your Power BI data model.

Once the data is loaded, you can see it in the right navigation on the page. You've successfully connected to your Business Central data, and you can begin building your Power BI report.

Here is a simple report I built using the Purchase Invoice API to achieve the Total number of purchase invoices, Total line items, and the count of PI numbers by status.