Building a report off of a Sql statement?

[johnism]

First I am on win 2000 and running version 8 of crystal.
Now does any body know how I can build a report off of a custom sql statement , this is the statement ( an easy statement )
(select A.* from tkhist1 A
Union all
Select B.* from tkmhist B
Union all
Select C.* from rrhist C )
I can not use the crystal sql Designer ( to build the statement and then build rpt of that )this will not work cause you can not change the data source once you have created it with the designer. Cause with the (crystal VCL component) we re point the data source.
Any ideas?
Thanks, John

 

[JustineB]

I am not near a machine that has access to Crystal and I'm sorry if this makes no sense!
Can't you design it using the Query Designer and then connect through ODBC. Get rid of all data except for the table and field names then when the data source is changed (via ODBC) the data will change too. I'm sure I manged this in version 6 of Crystal. I remeber that the query file had to be in the same folder as the .rpt in order for it to work correctly.....
Hope it helps.
Justine.

 

[jjames]

You can your SQL when using the Query Designer. I've found that you need to save the modified query under a different name and then in the report go Database/Set Location and select your new SQL query. Hope this helps.

 

[johnism]

The only problem is this is a distributed rpt and not everybody would have the same named data source. What you described doing, I have done something similar with ( betrive ) but never any luck with (odbc).

 

[JustineB]

All user would have to have the same ODBC named Datasource - can you not ask them to do this - it would make your life loads easier!

 

[johnism]

Believe me I would to be able to do that , but I can't.

 

[balves]

You didn't mention the database, but chances are you would be better off using a stored procedure (if possible).

 

[johnism]

Cannot store views or procedures on data base.

 

[databaseman]

I am not sure I understand your question, but I'll give it my best shot. What I think you are asking is how to build a report using a SQL statement wherein the datasource will change.

I'd really recommend using the ODBC source as well. If this is not practical, you can build your SQL statement as a view with standardized field names, then change the view as needed, leaving the field names the same. As below:
Create view SQLQRY as
Select
tbl1.username NAME
tbl1.userid ID
tbl2.course COURSE
tbl3.testid TSTID
From
CRMdb1.tbl1, CRMdb1.tbl2, CRMdb1.tbl3......

and change it to

Select
tbl1.username NAME
tbl1.userid ID
tbl2.course COURSE
tbl3.testid TSTID
From
CRMdb4.tbl1, CRMdb4.tbl2, CRMdb4.tbl3

This way, the report pulls its data from the view (which has effectively created a new table) which has static field names but changable data sources.

Lotsa Luck!

Databaseman Database Man
Defender of Life, Liberty and the pursuit of accurate database reporting! Follow my free tek-tip adventures at:

http://www.hammerman.com/dbman.html

 

[johnism]

Ok I cant use views (our data base doesn’t support (joins, or unions)) Pervasive ). Is there any way I can be passed a sql statement and build the rpt off the returned values of the statement?

 

[gsomerville]

Hi,

It is possible to use the SQL UNION statement by creating a
report in report designer and manually editing the SQL statement using the "database show query" option.
Insert the line "Where 1=0" at the end of the query. Crystal will evaluate the first section of the SQL to this statement and find it to be valid. The statement will obviously always be false and return no records.
You can then manually insert the query you want executed
using a UNION statement.
You will need to specify all your criteria in the SQL statement. The select expert will be disabled as a result of the customised SQL query.

e.g.
Select field1,field2
From Table Where 1=0
UNION
Select field1,field2
From Table1 Where <criteria1>
UNION ALL
Select field1,field2
From Table2 Where <criteria2>
UNION ALL
Select field1,field2
From Table3 Where <criteria3>

Hope this helps.

Regards,

Gavin