Solved

Embedded Select Statement in CR10

Posted on 2004-09-22
4
293 Views
Last Modified: 2006-11-17
Hi,

We're aiming to embed a select statement into a crystal report that has been created just using the database expert. We were wondering whether this is possible using crystal itself or whether we need to write the sql query first and then base a report on the query.

Our sql would be something like:

(select fkey from notes where created = max(created).....).  and we want to in bed this as a column in another sql statement created by crystal using the database expert.

Thanks

Steve
0
Comment
Question by:sbarwood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 28

Accepted Solution

by:
bdreed35 earned 250 total points
ID: 12122115
As long as your database/driver allow it, you can use a SQL Expression to do this.

Create a new SQL Expression in the Field Explorer of the report that you wanted to add it to.  If "SQL Expressions" is not available in the Field Explorer, than the database/driver doesn't support it.
Paste in your SQL in to the SQL Expression editor.  Make sure that you surround the SQL statement in parenthesis.
Just remember that the SQL Expression can only return one value when it executes.
Save and Close the editor.
Drag the SQL expression from the Field Explorer to the report canvas and Crystal will add it to the SQL it generates for the main report.

If you can't use SQL Expressions, you could always create a SQL Command.
The SQL Command allows you to use pure SQL  that you have written or generated, and uses it as a datasource just like a table or view.
To access a SQL Command, open the Database Expert.
Below an active connection to a datasource, you should see "Add Command", amongst other things.
Double click it to add it to the right list.
In the dialog that opens up, you can paste your SQL here.
When you click OK it will test the SQL works.
Once you close out of the Database Expert, you will see the command available the same as a table or view.
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12122211
Bdreed is right. There is no addition for that. But still I will go with a stored procedure in the db. It is simple, fast and puts your logic in the db.
Regards
Emre
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Suggested Courses

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question