Combining multiple 1-M related tables in one report w/o subreports

[tservo]

Hi all,

I'm working in Crystal Reports 2011 and have been dealing with this data conundrum for a while now. The requirements of several reports that I need to create involve the combination 3 one-to-many relationships that are supposed to be reported all together. Here's the situation:

This is a Vegetation Management database, so each Tree has a unique ID. This TreeID is used to link to 3 different tables, all with a one-to-many relationship: ActivityRecords, RestrictionNotes and PropertyNotes.

Typically, the use of subreports would be the answer here, correct? Say we group by TreeID, display the desired data from the ActivityRecords in the Details section, then create 2 subreports for the RestrictionNotes and PropertyNotes so that you don't print out every permutation of data from these three tables. The problem is, with anything other than a very small result set, this causes Crystal Report Designer to crash while running all of those subreports. If it is run on the Enterprise Server, it runs for 20 minutes then times out.

This is a large database with several million rows at play. We've made sure that all of our join fields are indexed in the database (which is Oracle, by the way) so that was ruled out as a cause of the poor performance.

So my question is, what other techniques could be used to report these data structures?

Thanks Much,
Dan

 

[lbass]

You could try using union all queries in a command with each of three segments linking the Tree table to one of the other tables. Since this "merges" fields into one field, you would have to plug fields that are not held in common with "null" and also identify each segment to you can track back to the table. If you build selection criteria into the From clauses, it will also speed up the compilation of the command. Something like this:

select 'Activity' "Whichtable", "Tree"."ID", "Activity"."Date", "Activity"."Type", null "Note"
from ("Tree"
inner join "Activity" on
"Tree"."Tree ID"="Activity"."Tree ID" and
"Tree"."Tree"='Oak' and
"Activity"."Type"='Trim'
)
union all
select 'Restriction' "Whichtable", "Tree"."ID", "Restriction"."Date", null, "Restriction"."RestrictionNotes" "Note"
from ("Tree"
left outer join "Restriction" on
"Tree"."Tree ID"="Restriction"."Tree ID" and
"Tree"."Tree"='Oak'
)
union all
select 'Property' "Whichtable", "Tree"."ID", "Property"."Date", null, "Property"."PropertyNotes" "Note"
from ("Tree"
left outer join "Property" on
"Tree"."Tree ID"="Property"."Tree ID" and
"Tree"."Tree"='Oak'
)
Order by 2, 1

Each field to be merged must be in the same ordinal position. Any order clauses must be at the end of the entire query and use the ordinal position as above.

-LB

 

[tservo]

Thanks. I'll definitely give that a try.

--Dan