Crystal Reports, SUM FORMULA for union of 2 tables that creates too many records

1
votes

I've created a crystal report based on 1 table with multiple database fields and it works perfectly for what I need, however, when I add (via union) another table it duplicates all my records from the first table.

For example:

Table1 A1, Table1 B1, Table1 C1
Table1 A2, Table1 B2, Table1 C2

Is perfect.

I add D from table 2 and it begins looking like this:

Table1 A1, Table1 B1, Table1 C1 , Table2 D1
Table1 A1, Table1 B1, Table1 C1 , Table2 D2
Table1 A1, Table1 B1, Table1 C1 , Table2 D3
Table1 A1, Table1 B1, Table1 C1 , Table2 D4

Table1 A2, Table1 B2, Table1 C2 , Table2 D5
Table1 A2, Table1 B2, Table1 C2 , Table2 D6
Table1 A2, Table1 B2, Table1 C2 , Table2 D7
Table1 A2, Table1 B2, Table1 C2 , Table2 D8

The 2 tables are unioned correctly. Basically what I want to do here is add a formula that will sum the corresponding D's so I can go back to my original number of records and not have so many duplicates:

Table1 A1, Table1 B1, Table1 C1, SUM of table2 D
Table1 A2, Table1 B2, Table1 C2, SUM of table2 D

Any help is appreciated Thank you

1
crystal-reports
for every combination how many records are retrived for added D column? – Siva

1 Answers

0
votes

Create a group on A1 field then put B1,C1 @SumOfD in group header.Suppress the details section.

@SumOfD

Sum ({D Column}, {A1});