ISTech Support Forum - Filter Dates in Crystal?

ginawendelin

Member

Essentials For
Safety and
Performance...

Posts: 68
Gender: female

Filter Dates in Crystal?
08/14/06 at 13:45:48

What is the format for filtering out dates in Crystal reports? I've tried "is between 01/01/06 to 06/30/06" or "is between 010106 to 063006" or "is between 01/01/2006 to 06/30/2006" for the BKAR_INV_INVDTE field.

I get the error message: "Failed to retreive data from the database" and "Unknown Database Connector Error" and nothing is displayed. But it connects just fine if I don't specify a filter.

Help!! Does anyone know what I am doing wrong? ???
There is nothing in the help file for Crystal reports.

Thanks!

Back to top

Gina Wendelin
Precise Flight, Inc.
EVO-ERP Version 2006.1 with 05/08/06 Update

IP Logged

JNAPIER

Active Member

Evil-Evo Bad Dog!

Posts: 533
Gender: male

Re: Filter Dates in Crystal?
Reply #1 - 08/14/06 at 14:44:55

Are you using the select expert or are you formating the field yourself? Try different selections with select expert to see if it is related to a specific field. Even if their is no items in the range, the report is just blank, no errors. Are you using only one file to build your report? sometimes fields in other data files are not linked or primary keyed.

John

Back to top

EVO-ERP, ISTS 3.27.08 , 2000I 10 user

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1919 Gender:	Re: Filter Dates in Crystal? Reply #2 - 08/15/06 at 04:11:47 If using the Select Expert, try using the drop-down box to pick a date - that will give you the correct format which you can then modify to the date you want. If writing a formula try the format "Date (2006, 08,15)".
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

ginawendelin

Member

Essentials For
Safety and
Performance...

Posts: 68
Gender: female

Re: Filter Dates in Crystal?
Reply #3 - 08/21/06 at 15:29:21

How do you use the select expert? I havent seen anything like that. If I try to filter it by "Is between" or "In the period" then I get a drop down box that gives me predetermined filters such as MonthToDate, YearToDate, Last7Days, Next30Days, Calendar1stQtr, etc., etc... Is this what you mean by select expert? By the way I am using Crystal Reports 11 if that makes a difference. Also when I try to get the invoice subtotal by using BKARINV_INV_SUBTOT I get all 00.00

Basically, in this report all I want is the Invoice Date, Number, and Subtotal for filtered amount of time.

Could either of you Crystal Report Pros help me? Pretty please...

Back to top

Gina Wendelin
Precise Flight, Inc.
EVO-ERP Version 2006.1 with 05/08/06 Update

IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1919
Gender: male

Re: Filter Dates in Crystal?
Reply #4 - 08/22/06 at 04:54:01

It sounds like you are using the Select Expert, but that it has changed. I'm on version 8, and the only time I get the option of pre-determined dates is on "In the period". I don't see how you could possibly be between LastFull Week and YearToDate....

But here's what you can do (I think).

Go ahead and pick LastFullWeek. Select OK (use saved data), and then go back to the Select Expert, or whatever it's called in v11. There should be a button that says "Show Formula". You should see something like:

{BKAR_INV_INVDTE} in Date LastFullWeek

Change "LastFullWeek" to "(2006, 01, 01) to Date (2006, 06, 30)", click OK, and Refresh Data.

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

Tim Keating

Senior Member

DBA CLASSIC WIN 2000
C/S

Posts: 298

Re: Filter Dates in Crystal?
Reply #5 - 08/22/06 at 06:48:06

I've found Parameters to work well on the type of report you are creating.

You can read up on them in the Crystal manual.

Each time you run the report, you will be prompted for a Begining Date and Ending Date for the report. At this point you can pull down an actual calendar which makes it quick.

Almost all of our reports use Parameters for Dates, Part Numbers, etc....

We're still on Crystal 7 or I'd share one with you.

Back to top

Tim Keating
Motor Guard Corporation

IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1919
Gender: male

Re: Filter Dates in Crystal?
Reply #6 - 08/22/06 at 09:03:56

Quote from Tim Keating on 08/22/06 at 06:48:06:

Almost all of our reports use Parameters for Dates, Part Numbers, etc....

Never thought of that. I'll have to remember - that will make the report a lot more user-friendly, and drastically reduce the training requirements.

Is there some way to "force" entry of new parameters?

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

Tim Keating Senior Member DBA CLASSIC WIN 2000 C/S Posts: 298	Re: Filter Dates in Crystal? Reply #7 - 08/22/06 at 11:34:33 Quote from David Waldmann on 08/22/06 at 09:03:56: Is there some way to "force" entry of new parameters? Once you define a Parameter and use it in your report (such as using it for selection criteria), the user will be prompted to enter the parameter at run time.
Back to top	Tim Keating Motor Guard Corporation IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1919 Gender:	Re: Filter Dates in Crystal? Reply #8 - 08/23/06 at 03:14:16 In version 8 it only asks you if you want to enter new paramenter values (and the default selection is to use the current ones).
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

Tim Keating

Senior Member

DBA CLASSIC WIN 2000
C/S

Posts: 298

Re: Filter Dates in Crystal?
Reply #9 - 08/23/06 at 07:26:56

Quote from David Waldmann on 08/23/06 at 03:14:16:

In version 8 it only asks you if you want to enter new paramenter values (and the default selection is to use the current ones).

O.K. - you're talking about refreshing the report in design mode. That's the same in v7.

I was talking about running the executable report which is all I let the users do.

Back to top

Tim Keating
Motor Guard Corporation

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1919 Gender:	Re: Filter Dates in Crystal? Reply #10 - 08/29/06 at 05:33:52 I'm looking forward to the CR Training in NH. I've "learned" CR by trial and lots of error. I knew that there was such a thing as executable reports, but no idea how to make/access/install provisons for such.
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

dameng

Senior Member

DBA / EVO Software
Consultant

Posts: 275
Gender: male

Re: Filter Dates in Crystal?
Reply #11 - 09/06/06 at 16:08:03

basically the date selection will work like this:
{BKARHINV.BKAR_INV_INVDTE} in Date (2006, 01, 01) to Date (2006, 08, 31)

you can enter this into the Record Selection or use the Select Expert.

p.s. ver 11 offers these fixed values of "in the Period... 1stQuarter, etc." i don't remember what version that was introduced. i'll look it up.

2nd, Ver 7 has the deployment of reports as executables. the ability of that deployment was removed in 8 and 8.5 but you could use an Add-On which was the same v7 deployment.
needless to say, ver 9 and newer, that option is completely gone.

Back to top

Dave Mengelkamp
Digital Consulting
760.832.2536
davemeng@earthlink.net

IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1919
Gender: male

Re: Filter Dates in Crystal?
Reply #12 - 09/07/06 at 04:25:10

Quote from dameng on 09/06/06 at 16:08:03:

Ver 7 has the deployment of reports as executables. the ability of that deployment was removed in 8 and 8.5 but you could use an Add-On which was the same v7 deployment.

Yes, I've now looked into it. I'll need to do some more research, as I get an error when I try to compile the report. Something about "the location of the database must be set ti 'same as in report', please change and try again". Tried to find such an option and couldn't.

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

JNAPIER

Active Member

Evil-Evo Bad Dog!

Posts: 533
Gender: male

Re: Filter Dates in Crystal?
Reply #13 - 09/07/06 at 07:13:25

Quote from David Waldmann on 09/07/06 at 04:25:10:

Yes, I've now looked into it. I'll need to do some more research, as I get an error when I try to compile the report. Something about "the location of the database must be set ti 'same as in report', please change and try again". Tried to find such an option and couldn't.

I think it is in DATABASE, SET LOCATION

John

Back to top

EVO-ERP, ISTS 3.27.08 , 2000I 10 user

IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1919
Gender: male

Re: Filter Dates in Crystal?
Reply #14 - 09/08/06 at 03:54:34

Yes it is.

However, on some files, when I try it I get error messages:

Btrieve [12]: File not found

Error opening file
File could not be opened "file", at file location: "file location"

Error detected by database DLL

It seems to be when the reports are using the native Btrieve driver. ODBC ones seem to be already set the same ("Same as in report" is greyed out)?

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged