Its shows aged invoices and the total balance for each customer. The only grouping i have setup is by customer in order to have a total for each customer.
I have been asked to add all the memos for each customer onto this report. So in the BAQ i had to link the Memo table.
I added the memo description field in within the customer group on the SSRS report. But it comes out like this (memo field in red):
I’ve done this kind of thing with either a table or list within a group row. You will have to move the invoices into a group to hide the duplicates, and redo your summaries so they don’t include the duplicates. It’s a pain but might be easier than a subreport for you.
ACME INDUSTRIES
Invoice PrdCode Code Inv Num PONum ....
======= ======= ======== ======= ======== ....
12345 5504 EPD MACH 12345 ABC-DEF ....
12350 5504 EPD MACH 12350 ABC-XYZ ....
12400 5512 EPD CAST 12400 ZZZ-123 ....
Emailed 60 .... (this is a memo for one of the invoices)
Called on .... (this is another memo for a differant invoice) BALZAR LTD
Invoice PrdCode Code Inv Num PONum ....
======= ======= ======== ======= ======== ....
12348 5512 EPD CAST 12348 1234567 ....
12351 5504 EPD MACH 12351 1234569 ....
12404 5512 EPD CAST 12404 1234575 ....
Spoke with .... (this is a memo for one of the invoices)
Called Joe .... (this is another memo for a differant invoice)
```
With all the memos, for the invoices for that customer, listed after the section listing the aging?
Okay. I see that Memos are linked to customers. But how do you filter which memo’s to include? I assume you don’t want to see a memo from 3 years ago. Or maybe you do.
So you want (I’m not going to spend the time to make it pretty)…
ACME IND
Invoice Prod code ...
12345 512 ...
12350 504 ...
Totals .......
ACME IND Memo #1
ACME IND Memo #2
ACME IND Memo #3
He’s already got the memos linked to the customer. I don’t believe a subreport is necessary here. Group by customer and invoice. Put invoice information in the invoice group header. In the customer group footer, put a table that is grouped by memo number, with information in the group header. Hide detail rows in both tables. Use OVER() and PARTITION BY in a BAQ calculated field to calculate totals without duplicates.
As evidence, here’s something similar I’ve done…this BAQ/report is for determining which midlevel assemblies need to be built so they’re available for top-level assemblies. The single query produces lots of duplicate values.
Main table of report is grouped by kanban code and part number. Part info in the part header, and demand and supply tables in the part footer.
There are internet resources that could probably explain HOW these work better than I can, but for our purposes, it means “add up this expression only where these values match”. Your calculated field would be something like “sum(InvcAmt) over(partition by CustNum, MemoNum)”. Instead of adding up that number in the report, just grab it with First(calculated_total), as it’s already been totaled.
This should work because each memo for a given customer should have one copy of each invoice line. The total for one memo will match the total for another memo, provided they have the same customer. Because the sums are split by memo, they don’t get added more than once.
Here’s a quick example of the records you might get by joining tables this way and using partition for your sums. Customer A has two memos, so each invoice gets repeated twice. If you just add those up, you’ll get the wrong total. But if you partition by customer and memo, it will only add the invoices that match the customer and memo, removing duplicates. (Customer B has no memos, so partitioning by customer would yield the correct total on each line.)
)