Solved

Pass variable to Oracle Query form Crystal Reports

Posted on 2003-12-11
2
504 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

11 Experts available now in Live!

Get 1:1 Help Now