Crystal Reports subquery in select expert

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: subquery in select expert

Post Reply

Post New Topic

Author	Message
vijayk Groupie Joined: 14 Mar 2007 Location: United States Online Status: Offline Posts: 53	Topic: subquery in select expert Posted: 03 Mar 2008 at 12:08pm
	I have 3 questions.. Can we create uncorrelated sub query using select expert.? Does the record selection formula sends the restrictions to the database level.? I am creating my own SQL and I dont have an option for creating SQL expression fields. Thanks Vijay
	IP Logged


BrianBischof Admin Group Joined: 09 Nov 2006 Online Status: Offline Posts: 2458	Posted: 03 Mar 2008 at 12:53pm
	1. Uncorrelated subqueries are supported with SQL Commands. Thus, you have to write the SQL from scratch. 2. The record selection formula MIGHT send the restrictions to the database level. CR doesn't have very sophisticated 'SQL translation' tools available and it can't convert complex selection formulas into raw SQL for processing. So if you have very simplistic formulas (>, <, etc.) then it will do it. 3. If you don't have SQL Expressions as an option, then CR thinks your database doesn't support them. I assume that you're not using SQL Server? I've heard of problems with MySql. With Oracle I think you have to use the ORACLE ODBC drivers. Since you are doing so much SQL work, you should check out my book. I'va already answered all these questions (and a dozen more) in my chapters on databases and database optimization. It will save you hours of work. You can get it here: Crystal Reports Encyclopedia
	Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
	IP Logged

vijayk Groupie Joined: 14 Mar 2007 Location: United States Online Status: Offline Posts: 53	Posted: 03 Mar 2008 at 1:24pm
	Brian, I have few tables on DB2 and they have current year, period information. We don't want to schedule the report and want it to run automatically without changing the prompts. we have reports that run everyweek. Like, it calculates the current year, period, week based on current date using our calender table. But We dont have a calender table in the same database. It is on SQL server database. So Is there a way that I can get the values from that database and pass it to the other query..? Thanks Vijay
	IP Logged

BrianBischof Admin Group Joined: 09 Nov 2006 Online Status: Offline Posts: 2458	Posted: 03 Mar 2008 at 1:44pm
	You can select a data source to be from a different database and link them together. CR XI has been optimized for this. Nonetheless, it can still be more efficient to have the database servers link directly to each other (have SQL Server connect to DB2) and then have CR pull all its data directly from SQL Server.
	Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
	IP Logged

vijayk Groupie Joined: 14 Mar 2007 Location: United States Online Status: Offline Posts: 53	Posted: 03 Mar 2008 at 2:04pm
	How can i link the database servers directly & pull all data from SQL server.? Thanks Vijay
	IP Logged

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum