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