Solved

Embedded Select Statement in CR10

Posted on 2004-09-22
4
288 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
4 Comments
 
LVL 28

Accepted Solution

by:
bdreed35 earned 250 total points
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now