Reporting from multiple tables for a single report 1

[gopat]

Database used: MS SQL 2000
Crystal Version: 10

In my database, there are multiple tables with the same structure but not having any link between them
For a particular report, I need have seperate counts from each table (each query having seperate selection condition).
I am not using any programming language, ie working directly off Crystal Report X.
So, is it possible to have seperate queries within one report itself?
Sub-reports do not quite solve the problem since I would like to add the counts from the different table.

Please help me out here
thanks

 

[gopat]

Sorry for not providing enough info. Here goes

Connectivity : ODBC

Sample Query 1:
select count(*)
from t1
where a=1 and b=2 and t1date > '01/01/2005

Sample Query 2:
select count(distinct a)
from t2
where c=1 and b=2 and t2date > '01/01/2005

Sample Query 3:
select count(xyz)
from t3 join t5 on t3.aa = t5.bb
where r=1 and s=5 and t3date > '01/01/2005

Now in my report I need to perform mathematical operations on these results(counts from the queries)

Is it possible to accomplish what I intend to do?

I tried using Command objects and it did return the results and everything, but there is one more thing,
I need to make the date parameterised so the the same date changes apply to all 3 queries

thanks

 

[kskid]

Create subreport A for Query 2 and subreport B for Query 3

Insert an additional report footer in the main report and insert Subreport A in Report Footer A and Subreport B in Report Footer B.

That should do it.

Cheers,
-LW

 

[gopat]

What about query 1 ?
Also, is there some thing that can be done by creating views ?
Since subreports are not supported by adhoc, this approach will disable the adhoc capability.

Also this would involve creating 3 reports to create one report. Is there no easier method possible? easier in the sense that it can be created easily in Adhoc.

sorry for redefining the problem
thanks

 

[kskid]

The main report would hold Query 1 involving t1 with a grand total summary using "count"

This count will appear in Report Footer a

Widen Report footer A so you can insert Subreport B

You did not mention Adhoc unless you want to control which report to view based on user supplied parameters.

If so, you need to provide more details and will probably require a restructure of the report to meet your needs

Cheers,
-LW