Aged0To30Days I just don't get it..

[rgouette]

Folks, I have a simple objective(well...it seeeems simple)

I have a report with TWO fields:
mydate, and myapp.
Both come from SQL, and are varchar types.
mydate is the classic datetime type, as in:

4/18/2006 4:07:47 PM

There's about 1.6 million records total.
I want to only show records from the last say, 30 days.
I would think that

Aged0To30Days

should apply here, but I cannot make it work.

Thanks lads,
Rich



Business Objects IX
SQL Server 2005


Programmer/Analyst
Portland,Maine

 

[CoSpringsGuy]

You do not show how you are attempting to use it.

the correct method should be in your record selection formula:

{table.mydate} in Aged0To30Days

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[rgouette]

Tried several iterations, and here's what I get.
I'm fairly certain this is just education..

sorry for the newbieness factor..
See attachemnt.

Programmer/Analyst
Portland,Maine

 

[CoSpringsGuy]

It has to do with the data structure of your field....
if it is a format which will easily convert to a date then converting the field to a date would work...

date({T_Success.zenDateTime} in Aged0to30Days

If this does not work then please show some samples of what is in that field from your database...

also. I see you are creating a formula for this .... your original post said you were using this because you only wanted to show records for the last 30 days. In that case I would enter the above formula in the record selection Editor



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[rgouette]

ok, two things:
1) I apologise.
I am using the record selection Editor.

2) my original post shows the source date format, which is:

4/18/2006 4:07:47 PM
that method (date({T_Success.zenDateTime} in Aged0to30Days) looks familiar.

When I run it, it yields:
"Bad date format string" at some point during the report generation.

hmm





Programmer/Analyst
Portland,Maine

 

[CoSpringsGuy]

dont want you to think I am giving up but I have to run to a meeting for about an hour .. Ill take a look at this when I get back. If you figure it out in the meantime let me know...

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CR85user]

It is simply a missing parenthis:

(date({T_Success.zenDateTime}) in Aged0to30Days)

Usually the cursor will move to where the error is to give you a tip when there is a skew in the logic.

'J

 

[rgouette]

yeah, the actual line reads:

date({T_Success.zenDateTime}) in Aged0to30Days

it's just not posted properly.
Rich



Programmer/Analyst
Portland,Maine

 

[CoSpringsGuy]

you said ""Bad date format string" at some point during the report generation."

could that date field possibly sometimes be null?

If so try changing the formula to use the default value for NULL. Or use an if statement to test for null.



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ShortyA]

In later versions of Crystal Reports eg XI if a formula has an error it will show more information about the exact record in a pane on the left hand side of the formula editor that will pop up. Failing that you could try a formula that checks to see if there is a date value in MyDate. For example :
------
// @Mycheckformula
// This checks to see if it is a valid date
if isdate(date(MyDate)) then 'Good'
// if not the value will be output
else 'Bad'&'-'&MyDate
------
Slam this formula in your report and have a look for the "Bad" value. BTW - remember to edit your record selection formula (perhaps remove the existing aged0to30days whilst testing or change it)
ShortyA

 

[rgouette]

Ok, I'll look into that..
btw, did I mention that the SQL data type that's generating this date value, is a varchar?
A vendor created it, and I'm not sure why it wasn't created as a DateTime.

Is that why Crystal chokes on it?
Rich


Programmer/Analyst
Portland,Maine

 

[ShortyA]

Varchar is fine because you use the "date(MyDate)" syntax to convert it into a date value. Crystal will choke if parts of the date are not in a valid range eg month isn't between 1-12.

Good luck!

ShortyA

 

[rgouette]

hm, I thought for sure that the "Invalid Date Time parameter" message would have been that field not coming across as a true DateTime type..

bummer!
Rich



Programmer/Analyst
Portland,Maine