Issue Combining Financials - AR Receipts and Applications with AR Transactions Subject Areas

Question

Hello,

I could do with a little advice on combining subject areas. I'm building a visualisation to get AR Receipts and the associated AR Invoices.

So I have brought in the Paying Customer, Account, Site, Receipt (Number, Date, Amount) from AR Receipts and Applications

To link to AR Transactions over a common measure I thought of brining in AR Receipt Application Details.Applied To Transaction Number but that doesn't link properly to the transaction in "AR Transactions". Using Transaction Details>Document Details>Transaction Number from "Financials - AR Transactions" works and I can then pull in invoice amount but what I am finding is that for selective invoices I get a blank Invoice Amount.

If I create a workbook on just "AR Transactions" then I get the correct Invoice Amount for all invoices. I could understand if it was for all invoices I get the issue but seems odd that its selective (it seems on the data the differentiator is Receipt Methods ).

Looking at the generated SQL, I'm kind of wondering if its something to do with sub ledger accounting, but as I can get the data when quering AR Transactions subject area its got me a little lost

David Saggioro · Answer

Sounds great @Richard Halford I'm glad it worked, and thanks for the note on paying customer, that concept is very useful as well.

Richard Halford · Answer

Thanks, I think the answer I got to was pulling in customer rather than paying customer as looking at the SQL is seems to go to XLA tables with paying customer and I guess it makes sense as an unpaid invoice won't have a paying customer only billing customer.

David Saggioro · Answer

Hi @Richard Halford,

I've been facing similar issues (duplicate lines and incorrect totals) when combining 2 or more Subject Areas, even if they're part of the same module (such as AR Aging and AR Transactions, or AP Invoices and AP Aging). This might be related to the way each subject area is built. What I did in some cases is to create a dataset from scratch and add the subject areas - I can then select the fields I want to use in the join condition and that seems to create a better match overall. I also make sure I have at least one measure from each subject area added to the visualization / report (if you don't want it to be visible, you can add it to the tooltip). I have a report I built with AR Transactions and AR Receipts and Applications using this dataset approach and it seems to be working fine so far.

Hope that helps.

Thanks, David