Chart contains 0's when reporting from data of 2005 1

[ITCPhil]

I'm working on Crystal 10 with an Oracle ODBC. This is my first new year's working on Crystal so it's the first time I encounter pulling data from a previous year.

We recently upgraded from CR8.5 to 10. I went through the reports in late 2005 and converted them all successfully to 10, but now I've some reports giving me problems with the year change.

In this case, it's a report that used to have a print time formula that was being charted on. This worked in 8.5 but no longer in 10. I ended up reworking the report and used running totals instead of print time formulas on which to do the charts. The report works up to December 31st but the second I get to 2006, all the charts are coming up with 0's.

I am thinking this might be in the way I am using my dates, could someone look at the formula and tell me if I can fix this? I tried playing with the DateSerial function but could not get it to report properly.

The running total starts with a count of the following formula titled {@1month} (there is a 2 and 3 months formula as well):
month({SRMIS_REQUEST.AST_DATE_RECORDED}) = month(currentdate)-1

It evaluates on :
{@1month} = true

And resets on the Group #1 : {SRMIS_REQUEST.RG_RESOLVER_GROUP_NAME}.

The chart here is a simple bar chart done using the Advanced option. It's placed in the header and appears once for each {SRMIS_REQUEST.RG_RESOLVER_GROUP_NAME}).
On change of : {SRMIS_REQUEST.RG_RESOLVER_GROUP_NAME}
Show Value : #3 months, #2 months, #1 months

The charts (11 of them containing 3 bars) appear and contain 0 for the 3 running totals for each group.


To attempt to fix, I changed the {@1month} formula to say :
{SRMIS_REQUEST.AST_DATE_RECORDED} = dateserial(year(currentdate),month(currentdate)-1,day(currentdate))

but even though it now gives a bit of data, it is incredibly off so I doubt I used the command properly.

Ideas?

Thanks in advance,
Phil

 

[lbass]

You're on the right track. All you need to do is create a range for the date:

{SRMIS_REQUEST.AST_DATE_RECORDED} in dateserial(year(currentdate),month(currentdate)-1,1) to dateserial(year(currentdate),month(currentdate),1)-1

-LB

 

[ITCPhil]

Fantastic, thank you so much.

One question though if you don't mind; The ',1)' and ',1)-1' , how are they interpreted? Am I correct in assuming that 1 means it starts at the first of the month and the -1 meaning it goes 1 day prior to the first of the month making it the last day of the previous month?

It appears to be that but I just want to confirm I understood correctly.

 

[lbass]

Yes, correct.

-LB

 

[ITCPhil]

Great, I jotted down this formula for future use as I am sure I will run into similar problems down the road.