Customer Payment Allocation information to Power BI

(0) Share

Report

Posted on by PortellaDM

Good evening all,

I have a very particular query and can not find an answer anywhere!

I need to generate a report from our Business Central environment that shows payment allocation information, i.e. An invoice number alongside the payment number (or credit memo) that was allocated to that invoice.

I am ideally trying to do this in PowerBI. On Business Central I have looked into creating the necessary OData V4 URL in Web Services, and the closest page I can find that might contain this information is Applied Customer Entries (61, List). However, when this table is brought into PowerBI via Web Services it is blank.

Does anyone know a way of accessing this data without requiring development? It seems to be such a basic thing to report on that there must be a way of doing it.

Thanks in advance for any help given.

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

MahGah 15,529 on at

Like (0)

Report

Hi

You need to publish Detailed Cust. Ledg. Entries Page 573 and Customer Ledger Entries Page 25.

Then from Cust. Ledg. Entries look for Transaction No. For all similar Transaction No find the Cust. Ledger Entry No and from there you can have the Invoice and payment number.

Test these steps in BC before working on it to see if it works or not.

Was this reply helpful? Yes No
Suggested answer

S.Kawamura 1,530 on at

Like (0)

Report

Hi,

I opend Page61 directly and saw nothing. (This is Chronos.)

However, Customer Ledger Entries definitely exist.

This means that either Page61 is not functioning properly or there is a filter condition that cannot be guessed from the name.

I recommend that you try the method suggested by MahGah .

I hope this helps you.
S.Kawamura

Was this reply helpful? Yes No
PortellaDM 10 on at

Like (0)

Report

Hi MahGah, thanks for your response. Can you please explain in a little more detail? What do you mean by "For all similar Transaction No"?

You mention to use Detailed Cust. Ledg. Entries, but you don't say how.

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (1)

Report

Hi

I hope below info helps. Basically from below you can find Cust Ledger Entry 1770|1776|1758 which are invoices and 1878|1880|1882 which are payment. Now you need a method in Power BI to extract and link these data together. If you can design Query in BC to do this then use that Query as your data feed in BI then it is easier.

Was this reply helpful? Yes No
PortellaDM 10 on at

Like (0)

Report

That is fantastic, thank you. I can see the data when I export to Excel, now I am working on trying to put a measure together that will separate the invoice and payment data into two columns so that they can sit next to each other in a table.

Could you point me in the right direction? I.e - Measure or Calculated Column? Which formulas to use and so on.

Thanks again for your help, much appreciated!

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (1)

Report

You are welcome. Honestly, I am not good at Power BI. If I want to do in Excel I probably create a new column to compare cust ledger entry and applied cust ledger in Cust Ledger Detail. Then if they are not matching mark that as invoiced, otherwise payment . Then in new column use Cust Ledger for those to vlookup Ledger Entry to find invoice or payment no. then use my Cust Ledger Entry as a main table and vlookup the data back. Then show. (this was just my imagination).

I assume in Power Bi you can do such an action then use. But I am not good with Power BI. or try your question in Power BI forum

https://community.powerbi.com/

Was this reply helpful? Yes No
PortellaDM 10 on at

Like (0)

Report

Thank you again, I have raised the query in the PowerBI forum.

Was this reply helpful? Yes No