You’re offline. This is a read only version of the page.
Announcements
436
I've built a small Power BI model that uses CustAgedBalances entity to do an analysis of customer debt and aging. The entity gives me CustAccount and Custname; however, I want to be able to report which invoice numbers make up the balances for each customer.
Sales Order Headers/Lines and Sales Invoice Headers/Lines will tell me what's been invoiced or part invoiced, but not what's been settled. I can see a reference to the Ledger voucher in for each Sales Invoice Line but no status for it (or the invoice line itself). Is there a way of deducing which vouchers or sales invoice lines are still due (or settled)?
Ian W.
Hiya,
For what it's worth, our SI partner Tisski created a custom entity for me that contains the Cust Transactions table in full. I now have this available to download to our BYOD server using Data Management or to selectively access via an ODATA connection. Works really well.
So, now sorted :-)
Good morning Ian,
Another way than creating a new data entity is using electronic reporting.
With electronic reporting you have access to all tables and can created excel exports to a SharePoint document library that you can integrate with your PowerBI report.
Maybe this is another option you might want to check out.
Pretty easy actually.
Best regards,
Ludwig
Thanks Andre (and indeed Ludwig),
I'll ask our system integrator (Tisski) if they can add a custom entity encapsulating this for us. It is available on Azure Data Lake with Entity Store Trickle Feed as a Data Flow, but that will then tie us over until that GA's.
Ian W.
Hi Ian,
There is no out of the box data entity for customer transactions. You have to create a customization to get specific data to your BYOD. E.g. you can create a new entity for open customer invoices where you define a query to include invoices in case the invoice amount is not fully settled yet.
Hi Ludwig,
I'm agonising of how to get Power BI connecting to a table of this name when it doesn't appear in the list of entities I can reach using directquery (on our BYOD instance using the SQLserver connector) or ODATA (direct into AxDW) connections. Or a method of using Power BI embedded native on AxDB/AxDW if we were ever allowed to go in that way (suggested back in the early days of V8.0 but which has never been made available to customers afaik). Data Lake solves it with some available Data Flows but i'm having to rewrite the model over the weekend as all our debt reporting has stopped pending AZDL coming back on stream.
I have a sneaking suspicion that someone's going to tell me to get Visual Studio out on a dev OneBox to find what I need and to map out the schemas :-}
Ian W.
Hi Ian,
Can't your build your report on the cust trans table?
From that table you have the linkage to the customer table and get all of the information out that you need?
Best regards,
Ludwig
Thankyou Andre.
I can't see a Customer Transactions entity to link to. Only a Customer Payment Journal Lines and Customer Payment Journal Payment Line Settled Invoice one, which appear to be incomplete (lots of gaps). Any idea how I can link to Customer Transactions to see a "Paid/Part Paid/Still Open" status?
Ian W.
Hi Ian,
The posting of invoices and payments results in customer transactions. The settlement is on the customer transaction level. From the Sales invoice header, you can link to the customer transaction to check if it is an outstanding invoice, paid or partly paid.
#1
André Arnaud de Cal...
294,099
Super User 2025 Season 1
#2
Martin Dráb
232,866
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
