Two Queries in One Report

[jakecolman]

Using Crystal XI with Oracle 9.

I have two tables that are logically related in business terms but are not physically related in any way in database terms. My requirement is to select data from both tables and display one set of results at the beginning of the report and the second set of results at the end of the report.

Is this possible in Crystal? Can the report have distinct sections that refer to data from two different tables?

Thanks!

...Jake

 

[lbass]

You can insert one of them in an unlinked subreport, and use the main report for the other.

-LB

 

[jakecolman]

Thanks, LB. I need to read up on using unlinked subreports since I have never used them. Having said that, can I use this technique to have both reports print on the same page? This assumes, of course, that the number of rows from each table (i.e., from the main report and thhe unlinked subreport) are few enough in number that it will fit.

...Jake

 

[lbass]

Sure. Why not try it? Place the subreport in the report footer.

-LB

 

[synapsevampire]

Also turn off the keep together in the report footer.

You can also create a UNION ALL type query to bring in all of the data in one recordset using a Command Oject (listed under the Oracle data source), as in:

select 'table1' MySource, field1, field2, field3 from table1
UNION ALL
select 'table2' MySource, field1, field2, field3 from table2

Just make sure that you either have the same data types in the same location in the select, or pass a phoney value for those that aren't the same in the other query.

Common technique in SQL to generate a single data source.

-k

 

[jakecolman]

Thanks to you both for your replies.

I cannot combine the two tables using a UNION query since the two sections will have two different sets of headers.

Let me explain what's going on here. We have a process that will produce a set of results in one table and a set of exeception results in another table. These two tables have NO linking fields in common. The regular results will contain some calculated values; the exception results will contain information about the elements that could not be included in the calculation. The only thing in common is that the user wants to see the data from the calculated result set at the top of the page with the exception data at the bottom of the page. Essentially I am producing two distinct reports, with two distinct sets of header columns and data, but doing it on one page.

I imagine that using an unlinked subreport is still the best way to go?

 

[lbass]

I think so.

-LB

 

[wreiche]

You can put two subreports on the same page. One below the other or side by side.

W.

 

[synapsevampire]

2 sections having different headers have nothing to do with whether you can use a UNION.

A subreport is basically the same thing though in your example. Place the subreport in the report footer.

-k