Linking Invoice lines and payment detail in Power Pivot | CLEARIFY

Hi all ,

I have a customeer that wants a donor letter to send to clients showing Item name  (for which I will cacluate tax deductibily), payment information (check#, etc). there can be mult lines per invoice and multiple payments per invoice.   I have joined the invoice header table  to receivepaymentline table via "applied to txnid".

I have been sucessful in getting data by item and showing some payment data.   yet when I add payment data (like receipt number or payment method), PP blossoms to include all payments in table however the amount shown is correct for the individual item I am looking at. 

I have scoured the PP books and done searches and for most part, understand their approach but can't quite get it adapted correctly in above situation. I feel like I am misssing a measure or caculated column somewhere.  Any ideas would be greatly appreciated.

This has been all consuming and seems like something that PP was made for 

Or once again am I trying to do the impossible. 

Fran

These are great questions.

For the record, the Sales Subject has a Linked Transaction folder (table for you database geeks) which contains just a handful of fields e.g. linked date, amount, reference number.

If you want more information about the linked transaction, e.g. receive payment type, etc. then it requires a manual linking - which an expert like yourself have already figured out.

Now, the question of using PowerPivot for this method.

1. We do not have Linked Transactions in PowerPivot for technical reasons, and will announce new methods for handling linked transactions this fall.
2. You can create a manual SQL Query, and start a new PowerPivot Data Model from scratch.  This would be a great blog post.
3. The other option is to use the General Ledger Subject for this project.

The General Ledger Subject contains both the original invoice, and the linked payments - but it is configured in a much different manner.  There is a master number that actually links the original invoice with the linked payments.  This would allow you to use PowerPivot out of the box.

Here is an example of the G/L with the master number:

You can find the actual Excel example in the QQube Configuration Tool. For Regular Excel it starts with "general_ledger_linked_transactions_", and in Power Pivot it starts with "general_ledger_master_linking_pp_2013_"