Show Me the Money! or Check Who Approved the Document You Are About to Pay in Dynamics 365 Business Central

Power BI and Dynamics 365 Business Central work great together. In a previous blog post, we looked at publishing a custom dataset from the wizard in BC. In this blog post, we will take things a step further by creating a custom query and publishing that for use in Power BI.

The requirement here is simple enough. Running the suggest vendor payment function can create a payment batch that has been summarised by vendor to pay all the purchases due. 

Great, but now we want an review all the approvals as one last step before posting the Payment Journal. 

This can be done manually on a line-by-line basis:

Lets see the documents that have been applied

Picking a single line we can click though to the document card

From the document card we can open the Approval entries 

Approval entries

But even for small payment runs that's a lot of clicking, we just want a simple overview!

Get the data published as a web service 

The relationships that hold this info together are:

[Gen. Journal Line].[Applied-to ID] = [Vendor Ledger Entry].[Applies-to ID]

That gets us from the payment journal to the line in the Vendor ledger entry; from there to link to the approvals its simply the document numbers:

[Vendor Ledger Entry].[Document No] = [Posted Approval Entry].[Document No]

Using that general info you can create a query object and publish to your SaaS instance of BC

It was necessary to exclude rows with a blank Applies-to ID as this is not a primary key and if you don't exclude you can end up matching blank to blank and get a lot of unwanted data!  

Once pushed to BC this should be published from the Web Services page:

Grab the data into Power BI, create a layout and publish

Which can then be grabbed in PowerBI using the built in data source for BC SaaS

The Power BI layout I've gone with a very simple slider to pick the Payment Journal batch and a matrix table to show the Payment Journal document lines, related vendor ledger entry document numbers and values and naturally the approval details - where an approval was completed.

Once published the Power BI report can be surfaced in Business Central or accessed via a custom action from the payment journal page perhaps - something to look at next time...

What do you think?

Please let me know your thoughts in the comments

Connect or follow me on LinkedIn to get all my updates Andrew Wingate | LinkedIn