Search code examples
sqldatabasecrystal-reportsreport

How do I get two sums of two separate tables with no joint keys in a single crystal report?


I have data in two tables (see below for a sample) - how do I create a Crystal report (more of a "score card" really) displaying only sum(table1.column1) and sum(table2.column1) with no other details? When I try, one of the sums gets way too big, indicating it has been included in some inner loop in the calculations.

Table1:
Column1: Integer
Column2: Varchar(100)
...

Table2:
Column1: Integer
Column2: Varchar(50)
...

Note - there are no joint keys, the only relation between the tables is that they relate to the same business area.


Solution

    1. Add a grouping levels for Table1.uid. Create a running total Table1Sum, sum on Table1.Column1, on change of group Table1.uid, reset never. Create a running total Table2Sum, sum on Table2.Column1, on every record, reset on change of group Table1.uid. Print both running totals in the report footer.
    2. Place your queries in separate subreports. (This is what I'd probably do.)

    The first one obviously requires (1) a unique key in Table1 and (2) printing the values in the footer. If those constraints won't work, two subreports should still work.