Grouping/Charting Data from Subreports

[MJRBIM]

CR 8.5 - This report pulls data from thirteen (13) different regional copies of the same database structure on Oracle 8i, and combines the information into a single page report with a national summary calculation on the regional results.

I am running a separate sub-report for each region's current file count in a different detail line of my main report (Detail A to N), and using a shared number var to get a national count of all the files in all the regions.

The report looks like this ->

REGION NAME FILE COUNT
- - - - - - - - - - - - - - - - - - -
AMHERST 75,363
CALGARY 42,025
EDMONTON 107,650
IQALUIT 17,577
NCR 207,953
QUEBEC 52,431
REGINA 1,278
THUNDRBY 50,865
TORONTO 27,587
VANCOUVR 210,182
WHITEHRS 445
WINNIPEG 175,495
YELLOWKN 1,779
- - - - - - - - - - - - - - - - - - -
TOTAL FILE COUNT 970,630
= = = = = = = = = = = = = = = = = = =

That's the data I want, the count is correct and performance is good.

What I am looking to do now is find a way (if possible) to GROUP the data from the sub-reports in each region within my Main Report, so that I could have a pie chart showing File Count by Region as a Percentage of the National count.

I can't seem to think of a way to Group these records, as each line of "detail" I want to group on is a separate sub-report pointing at a different database.

Thanks in advance for the smart ideas....

 

[synapsevampire]

Smart ideas might be a tad optimistic, but if you use a Union Query to bring all of the data in as a single record source you can accomplish this, and improve performance.

Crystal itself will allow you to modify the Database->Show SQL Query to hand roll the Union, or you would be better served to create a View or SP to do this.

A Union looks something like the following, and I've added in a unique identifier for each table for identifying each rowset if need be:

Select field1, field2, field3, 'table1' as field4 from table1
union
Select field1, field2, field3, 'table2' as field4 from table2
union
Select field1, field2, field3, 'table3' as field4 from table3

-k

 

[MJRBIM]

K -

I can't do anything on the Oracle side (SP or View) for "political" reasons, and I didn't think I could modify of the SQL Query in CR 8.5....

Anyway, the Table and Field names are the same in each Region (eg. Profile.FileName) - so I guess I would have to connect the report to each regional DB - then create an Alias for the Table that identified the region. (eg. CGProfile, ATProfile, etc..).

Once I have done that, then I should be able modify the SQL to UNION the data...right?

...and the syntax would be something like -

SELECT FileName, "CALGARY" as field2 from CGProfile
UNION
SELECT FileName, "AMHERST" as field2 from ATProfile

Let me know if I am getting the gist of what you are suggesting