Summarizing Distinct Values Across Multiple Columns

[hoagieryder]

I am not sure if I can accomplish this with CR, given the way the table is structured but...

Group 1 | Group 2 | Group Value 1 | Group Value 2
----------------------------------------------------
A | B | 15 | 20
B | A | 60 | 80

I want to be able to group by the distinct values of Group 1 and Group 2. Then, if the distinct value is in Group 1, grab the value from Group Value 1. If the distinct value is in Group 2 grab Group Value 2.

The resulting summarized data would be:

Distinct | Sum |
--------------------
A | 95 | <--- (15 + 80)
B | 80 | <--- (20 + 60)

 

[CrystalizeCanada]

One approach would be to base the crystal report on an SQL Command (it is normally available in the database expert) and not directly on the table

So

SELECT Group1 AS CategoryField, Group1Value AS SummaryField
FROM YourTableHere
UNION ALL
SELECT Group2, Group2Value
FROM YourTableHere

Then group on the CategoryField and Sum up the SummaryField.

Gordon BOCP
Crystalize