Can I display a record in multiple groups in a report

[Former Member]

I am attempting to create a Sales Commission Report from our invoicing application. If an Invoice is Split between two Sales Professionals, it will have a Sales Professional in SP1 and then another in SP2 with a SP% percentage value. In my report, we would like to group by Sales Professional. If we have no split invoices, then the invoice appears correctly for the Sales Professional. If we do have a split, then I would like an invoice to display twice, once in the group for the first Sales Professional and then again later in the group for the second Sales Professional. Each time I would perform calculations against it based on the SP% and other criteria for each sales person. Is it possible for me to do this? I think I can only display it once, since it reads the record and processes it, but I wanted to make sure and see if there was a way to do this which I was not aware of. Thanks for your time.

[DellSC · Accepted solution]

You can group by sales person, but you would have to only show the SP1 data on the main report. Then create a subreport to show the SP2 invoices and put it in the sales person group footer. You won't be able to sort the SP1 and SP2 invoices together, but you will be able to get all of the data.

I can think of another potential way to do this using a Command (SQL Select statement) with a union clause that will pull ALL of the report data (e.g., don't link it to any other tables or commands), but that's only an option if you have fairly good SQL skills.

-Dell