Good Day,
I have a report that I have been working on for about a year now. I can typically solve most problems on my own, but I cannot figure this one out despite how simple it seems. The report has 15+ tables, but I only need the following tables for this purpose: Calendar, Invoiced(e.g. sales), a table connecting "lead sales personnel" and their "regional sales personnel" (e.g. lead sales personnel may have several regional sales personnel report to them), customer master, and quota table by month, which only shows the "lead sales personnel". Within the customer master, I have the "lead sales personnel" and the "regional sales personnel", by customer.
What I am trying to do is create a matrix that shows "Lead Sales personnel" as rows, with columns for sales by month, ytd sales, and ytd quota.
I can create separate matrices that show all the correct values, but when I try to combine them into one matrix, I end up with what I believe is a relationship issue. Depending with how my relationship is setup, the resulting matrix I get is either a table with a YTD quota that is blank for all rows, or my monthly sales number is the same for each of row (the "lead sales personnel") and the YTD quota is correct. I have tried various ways to adjust the relationships, but I have not been able to figure it out.
I am hoping to get some guidance on how these relationships should be set-up. Ideally, I would use the "lead sales personnel" and the "regional sales personnel" from the customer master, because that is automatically updated each day. But, when I do that, the ytd quota values are all blank currently. If I use the "lead sales personnel" directly from the quota table, the YTD quota is correct, but the sales by month are not.
I appreciate this may be hard to follow, but I can answer questions. I cannot share my data unfortunately due to its sensitivity.
Cheers
