special grouping

[Anoli]

I am new to Crystal and I have a question re: grouping.
Say I have a report based on one table and in the detail area I have customer number, invoice number, invoice amount, Salesperson1, Salesperson2.
Sometimes a specific salesperson in in Salesperson1 or Salesperson2 filed. I would like for each invoice to be able to show in the detail twice ( once belonging to Salesperson1 then SP2), then I would like to group by salesperson so that if a salesperson named John will show both invoices where John is SP1 and SP2.
I was able to show the invoice info twice by splitting the detail section in a and b but I have trouble grouping. Although the invoice shows twice in the total is added once.
I really appreciate your help

 

[IdoMillet]

Assuming you have a maximum of two Sales Reps per Invoice,
and that the second field is optional - not the best
of data models but I've seen worst... )

Create a UNION Query or View as the data source for your report:

------------------------------------------
Select customer number, invoice number, invoice amount, Salesperson1 as SALES_REP, ... FROM ...
UNION ALL
Select customer number, invoice number, invoice amount, Salesperson2 as Sales REP, ... FROM ...
WHERE Salesperson2 is Not Null
------------------------------------------

Now, simply Group by Sales_REP.

Cheers,
- Ido