Setting up date parameters 4

[iao]

Hi everyone.

I am trying to create a report where the user filters the report based on a date range. Therefore, I am creating two new parameters - "Start date" and "End date".

Setting up the parameter works fine except one minor detail, the "Value type" must be set to "Datetime". However, because of this, the parameter requires an entry for both the date and the time (and I only want them to select a date). If "Value type" is set to "Date", the table does not appear in the "Browse table" drop down list. If I choose "Date" anyway and then type in the table, the parameters will not show up in the Select Expert dialog.

What's weird is that I look at some of the old reports created by the person I replaced. In these reports, the "Value type" is set to "Date", and when I use the Select Expert, the parameters show up. I try and reproduce this exactly and I am unable to get it to work without forcing the users to type in the time. Argh.

It's obviously possible to set up the parameters to only look at the date (and not the time), but I just can't quite figure it out. Any ideas on what I'm missing? Any ideas are appreciated.

Thanks!

 

[lbass]

What CR version?

-LB

 

[iao]

I know this is sad, but I'm on 7.0. It's due to the fact that I need the old version to work with another old software program.

 

[lbass]

I never had 7.0, but did work with 8.0. What if you set up the parameters as date parameters and go to report->edit selection formula->record and enter:

{table.date} >= {?start} and
{table.date} < {?end} + 1

-LB

 

[Kelser]

This is a common problem when dealing with DateTime. This works for me, hope it does the same for you.

1) Create your parameter and select DateTime
2) Click the Set default values button and set the Browse Table to the appropriate table and the Browse Field to the appropriate field.
3) Click OK to close the Set Default Values dialog box and choose other required settings such as Range Values radio button. Click OK to close.
4) Open the Select Expert, choose the Date field the parameter is based on and click OK.
5) Choose the 'is equal to' operator and then choose the parameter you created.
6) When the Parameter prompt box appears, go through the motions of choosing a date etc

You will then get an error message 'DateTime required'

7) Cancel out of this window, open your parameter for editing and change the value from DateTime to Date.

DO NOT CLICK ON THE SET DEFAULT VALUES BUTTON!

8) Click OK. Refresh the screen and you should find that it only looks for a date now.

 

[iao]

Hi Kesler.

I don't understand why it works, but that seems to do it. How interesting...

Thanks so much!!!

 

[synapsevampire]

Gotta Love those undocumented features, thanks for the tip, *Kelser*!

-k

 

[jsw1229]

Just add a formula, for instance "Start Date"
putting Date and table.datetime in parenthesis after Date. (see below)
Date({<table name.date time field>}) and it will change the field to just a "date"

Set up your date range parameter, then use the formula above

{@StartDate} in {?DateRange}
or
{@StartDate}<=maximum({?DateRange})
and {@StartDate}>=minimum({?DateRange})

 

[rosemaryl]

Dear jsw1229,

There is a problem with using a formula in the selection criteria as it generally will not pass to the database. Rather than using a formula you can use the CDATE function to convert a datetime field to a Date field:

CDATE{Table.DateTimeField} in x to y and the statement will pass to the database.

However, the solution is, as LB pointed out, to not use the Select Expert or to use Kelser's work around if you must use the select expert.

Dear IAO

I don't have version 7.0 anymore but I remeber just creating date parameters and not having a problem.

Create your date parameter(s).

Do not use the Select Expert, but rather create your own selection formula.

Report/Edit Selection Formula/Record

Manually build your formula by selecting your datetime field and then stating it is in the two parameters like this:

{Table.DateTimeField} in {?Startdate} to {?EndDate}

Click the save button.

Double check the Database/Show SQL Query to make sure the dates are passed correctly. If not, make sure to use LB's exact solution.

regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[elinsd]

I had a very similar problem with CRXI and Oracle SP with date parameters. I couldn't create the report because CR's default parameter automatically set the data type to "datetime" and it's not updatable to "date" which is what Oracle sp's parameters' data type so the Crystal just threw out errors.

My co-worker and I had tried to find the solution for days and finally she found that it worked using "Oracle Server" (when asked to create new database connection) instead of Oracle ODBC or Crystal Reports' Oracle ODBC driver.

There is no need to modify the way the Oracle sp is because it was tested fine and no need to code the Crystal parameters either.

 

[synapsevampire]

elinsd: Please post your Crystal version if you intend to contribute.

Secondly, you're not supposed to use Oracle's ODBC driver, and native (Oracle Server) is the fastest method.

Third, Crystal does NOT convert an Oracle date parameter to datetime, unless perhaps if you're using the wrong connectivity, and there are a wealth of other concerns when using it, but why discuss them since you aren't suppose to be doing so anyway.

Often you will want to code for this, for inctance SQL Server does NOT have a date type, only datetime.

Good to read that you learned not to use Oracle's ODBC.

Were you yo have posted here when you discovered the problem, and posted your software version and the connectivity you would have resolved it quickly, as this is a well known problem.

-k

 

[kenhamady]

One other point on this topic. In all versions of CR up to 8.5 there was a report option that allowed you to convert DateTime fields to Date fields automatically. This way the report saw the field as a date and would allow you to use a DATE parameter in the select expert. This might be how your original reports were configured.

However you do it I would check the SQL statement to make sure you aren't losing the last date of the range. I have seen some ODBC drivers that lose a day on the end.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/