Reporting on multiple tables independently in Crystal Reports 11

Question

I am using Crystal Reports Developer Studio to create a report that reports on two different tables, let them be "ATable" and "BTable". For my simplest task, I would like to report the count of each table by using Total Running Fields. I created one for ATable (Called ATableTRF) and when I post it on my report this is what happens:

1) The SQL Query (Show SQL Query) shows:

SELECT "ATABLE"."ATABLE_KEY"
FROM   "DB"."ATABLE" "ATABLE"

2) The total records read is the number of records in ATable.

3) The number I get is correct (total records in ATable).

Same goes for BTableTRF, if I remove ATableTRF I get:

1) The SQL Query (Show SQL Query) shows:

SELECT "BTABLE"."BTABLE_KEY"
FROM   "DB"."BTABLE" "BTABLE"

2) The total records read is the number of records in BTable.

3) The number I get is correct (total records in BTable).

The problems starts when I just put both fields on the reports. What happens then is that I get the two queries one after another (since the tables are not linked in crystal reports):

SELECT "ATABLE"."ATABLE_KEY"
FROM   "DB"."ATABLE" "ATABLE"

SELECT "BTABLE"."BTABLE_KEY"
FROM   "DB"."BTABLE" "BTABLE"

And the number of record read is far larger than each of the tables - it doesn't stop. I would verify it's count(ATable)xcount(BTable) but that would exceed my computer's limitation (probably - one is around 300k rows the other around 900k rows).

I would just like to report the count of the two tables. No interaction is needed - but crystal somehow enforces an interaction.

Can anyone help with that?

Thanks!

Ryan Ryan · Accepted Answer · 2012-01-31T14:12:02

Unless there is some join describing the two tables' relationship, then the result will be a Cartesian product. Try just using two subqueries, either via a SQL Command or as individual SQL expressions, to get the row counts. Ex:

select count(distinct ATABLE_KEY) from ATABLE

If you're not interested in anything else in these tables aside from the row counts, then there's no reason to bring all those rows into Crystal - better to do the heavy lifting on the RDBMS.

Reporting on multiple tables independently in Crystal Reports 11

2 Answers