Solved

Pass variable to Oracle Query form Crystal Reports

Posted on 2003-12-11
2
507 Views
Last Modified: 2006-11-17
I want to pass a date variable to a sql statement from Crystal
Reports to limit the scope of the report.  The part of the query
accepting the variables looks like:

"Calendar_Date between To_Date('2003/01/01','yyyy/mm/dd') and
To_Date('2003/01/28','yyyy/mm/dd')"

And I'd be wanting to replace the date part of the query with a
user-entered date.  As I'm new to Oracle and Crystal, I don't know
where to start.  Is there a way to replace the '1-JUN-03' part with a
variable in Oracle, then modify that variable when I'm designing the
report?

Grabbing all the results and then filtering them in Crystal won't work
as this table is very large.  Apologies in advance if this question
has been asked a million times.
0
Comment
Question by:wayne_baswell
2 Comments
 
LVL 3

Accepted Solution

by:
AnnetteHarper earned 50 total points
ID: 9920620
Just create two date parameter fields, Start_Date and End_Date, and put them in your selection formula like this.

Calendar_Date between {?Start_Date} and {?End_Date}

Assuming there aren't any other complications, Crystal will convert the date parameters to date literals with the proper TO_DATE function when it creates the query to send to Oracle. You can check this by looking at the menu Database | Show SQL Query.

Crystal will not, however, create Oracle bind variables for you. If you need that, you'll have to use a stored procedure. (You'd need that only if you are concerned about the amount of parsing going on.)
0
 
LVL 1

Author Comment

by:wayne_baswell
ID: 9920696
Thanks, I'm new at Oracle/Crystal and that's what I needed to know.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

920 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

14 Experts available now in Live!

Get 1:1 Help Now