I have a unioned crosstab that pulls back a bunch of data grouped by sales rep...this data is fine. The last 3 fields they want shown grouped by sales rep but the filter is actually on xnac which is where my issue lies.
Example
rep activity count install revenue order revenue
dave 16 20,000 30,000
emma 2 16,000 10,000
Activity count and install revenue is perfect. However when it comes to order revenue the rep who owns the xnac gets credit for the revenue even if they did not book it. So if emma sold 8,000 in xnac 1 and 2,000 in xnac 2 (which belongs to dave) the data shoudl look like this
rep activity count install revenue order revenue
dave 16 20,000 32,000
emma 2 16,000 8,000
Am I making any sense and Is this even possible
This is more of a model than a report issue. You are basically using a wrong association or the underlying model / database is to blame. If the salesrep is not stored with the transaction, then you are forced to use the relationship defined elsewhere (as owner in this case) You NEED to have the salesrep stored with the actual booking to get the proper results. Talk to whoever build the model / dwh / database
Thanks