Trying to get prior day data formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7821
Printed Date: 07 May 2024 at 7:20am
Topic: Trying to get prior day data formula
Posted By: u000098
Subject: Trying to get prior day data formula
Date Posted: 28 Sep 2009 at 9:45am
|
Every morning i generate reports to see prior day data. I'm looking for some assistance on creating a formula that will showcase prior date information. When i do a "browse field data" this is what i see:
Type: String
Length: 30
xx/xx/xxxx
xx/xx/xx
xx/xx/xx xx:xx:xx
Your prompt assistance will be greatly appreciated. |
Replies:
Posted By: DBlank
Date Posted: 28 Sep 2009 at 9:55am
|
try: date(table.field)=dateadd('d',-1,currentdate)
|
Posted By: u000098
Date Posted: 28 Sep 2009 at 11:12am
|
Thanks DBlank for the quick turn around...
But i get a "Bad Date Format String" error.
The table name is - Tec_T_EVT_REP
Dates under - Date_Event
Should it look like this:
date({TEC_T_EVT_REP.DATE_EVENT})=dateadd('d',-1,currentdate)
I'm really terrible on this formula thing, i apologize.
The way i get the prior day today is by right clicking the "Date_EVENT" title, then choosing "Select Expert" then "starts with", then typing in what ever the prior date was. Example "Sep 27".
Not sure if this is helpfull or not, but this is my daily process.
Thanks again.
|
Posted By: DBlank
Date Posted: 28 Sep 2009 at 11:28am
|
2 questions: When you browse the data you were seeing strings in 3 different formats, one with xx/xx/xxxx a second with xx/xx/xx and a third with xx/xx/xx xx:xx:xx.
Is that correct?
Secondly, is this in a month/day/year or day/month/year format in the strings?
|
Posted By: u000098
Date Posted: 28 Sep 2009 at 11:50am
|
1. Yes 2. Month/Day/Year
Thanks again.
|
Posted By: DBlank
Date Posted: 28 Sep 2009 at 12:17pm
|
You had the syntax correct: date({TEC_T_EVT_REP.DATE_EVENT})=dateadd('d',-1,currentdate)
To try and figure this out make another formula field (call it datetest or whatever) as:
isdate({TEC_T_EVT_REP.DATE_EVENT})
Now place the formula field and the Date_Event field on your detail section.
YOu will see the date event field and a TRue or False next to it (the datetest formula)
What are sample records that are showing up as False?
Are most True but some are false?
I am trying to figure out why the the other formula failed trying to convert your data to a daet field.
|
Posted By: u000098
Date Posted: 28 Sep 2009 at 1:19pm
|
k..so:
I got lot's of prior dates data, with 4 different dates format:
Picked a random day:
1. Sep 25, 2009 xx:xx:xx
2. Sep 25 xx:xx:xx
3. 09/25/09 xx:xx:xx
4. 09/25/2009
Did not return any true or false.
|
Posted By: DBlank
Date Posted: 28 Sep 2009 at 1:45pm
|
Yikes. That is some bad data consistency.
That helps some but sounds likeyou did not add the formula field for the T/F.
Sorry I can't give you a straight formward answer here but with that many variations in the data it is hard to make a conversion from a string to a date type. My guess is that it is choking on the likes of the second example where it has no year.
When there is no year how do you know if the data is from the previous day or the previous year and a day?
Try and use an isDate function in your select criteria.
You can either filkter all the records that are not or include them and see if there is a consistent patternt o which fields are not able to be converted.
So if you put this as your select criteria:
if isDate({TEC_T_EVT_REP.DATE_EVENT}) then
date({TEC_T_EVT_REP.DATE_EVENT})=dateadd('d',-1,currentdate)
else
NOT(isDate({TEC_T_EVT_REP.DATE_EVENT}))
It should give you all the records from yesterday and all the records that are not considered real dates.
What do you get back from this?
|
Posted By: u000098
Date Posted: 28 Sep 2009 at 2:00pm
|
Tell me about it, is a mess here. =)
I didnt add the formula, sorry. I tried, maybe i'm doing it wrong..
Now with the new statement, i only get these formats:
Sep 25 xx:xx:xx
Sep 25, 2009 xx:xx:xx
Thanks again for your outstanding assistance DBlank, you're the man!
|
Posted By: DBlank
Date Posted: 28 Sep 2009 at 2:22pm
|
OK from what I have gathered you likely added my select statment along with your "Like Sep" statement and you ended up with the 2 records that were on the your random Sep 25 selection and that are also not considered a date converted field. YOu could try and match the dateswhen it is convertable and then match the first 6 characters when it is not... if isdate({TEC_T_EVT_REP.DATE_EVENT}) then
date({TEC_T_EVT_REP.DATE_EVENT})=dateadd('d',-1, currentdate) else left({TEC_T_EVT_REP.DATE_EVENT},6)=totext(dateadd('d',-1, currentdate),'MMM dd') |
Posted By: DBlank
Date Posted: 28 Sep 2009 at 2:22pm
|
Use that without your LIKE select statement.
It also will ignore the year when it is missing in the string so you have to decide if that logically makes sense for you.
|
Posted By: u000098
Date Posted: 29 Sep 2009 at 7:54am
Hey DBlank....This one worked, gave me prior date, but with all of the differernt date formats.
 |
Posted By: u000098
Date Posted: 29 Sep 2009 at 8:01am
|
How about a formula for a line to auto expand?
Same table:
TEC_T_EVT_REP
LONG_MSG
Thanks again for your awesome assistance.
 |
Posted By: DBlank
Date Posted: 29 Sep 2009 at 8:07am
|
I think you mean you want the field to grow based on the length of the data for that row. This is handled by setting the field to "Can Grow".
When you place it on the report canvas, right click on it and select Format Field, click onthe Common tab and mark the Can Grow box to true (checked).
This makes the field expand on the Y axis only. It also will expand over other fields that are below it on the same section. To counter act that part you place it on it's on section (like header1b or detailsb).
Is that what you need?
|
Posted By: u000098
Date Posted: 29 Sep 2009 at 10:41am
| Yes, got it. Thanks again... |