YYYYMM in record selection 2

[antonx]

Hi,

I have CR 10 and having problems in the record selection. There is a field in a DB2 table that all it has is YYYYMM values. For example this field FSCL_YR_MTH_NUM has 200501, 200502, ... 200712. The field datatype is NUMERIC.

My report needs to extract the previous month. For example today November 30, 2005, I need to extract records where FSCL_YR_MTH_NUM is = 200510. When I run it in January 2006, then FSCL_YR_MTH_NUM must = 200512.

I wrote a formula @YYYYMM below but when I insert this formula in the record selection, the report takes very long too run. Is there a faster/simpler way to to have my record selection formula extract records of the previous month?

@YYYYMM
WhileReadingRecords;
local NumberVar myCurrEndYear;
local NumberVar myCurrEndMth ;
local NumberVar myCurrEndDate;

if (Month(dataDate) = 1) then
myCurrEndYear := (Year(dataDate) - 1)
else
myCurrEndYear := (Year(dataDate));

if (Month(dataDate) = 1) then
myCurrEndMth := 12
else
myCurrEndMth := (Month(dataDate) -1);

myCurrEndDate := (myCurrEndYear * 100) + myCurrEndMth;

myCurrEndDate;

Thanks

 

[Ridyen]

Will the dateadd command work?

DateAdd ("m", -1, yourdate)

 

[synapsevampire]

You're likely not passing the SQL to the database, check under Database->Show SQL Query

It appears that you always want to base it on todays date, so try:

val(totext(dateserial(year(datadate),month(datadate)-1,1),"YYYYMM"))

I don't have Crystal on this box right now so I can't test, but the idea is to simplify the formula so thatCrystal will pass it in a query.

-k

 

[Ridyen]

I think it works if you modify it to use "yyyyMM" rather than "YYYYMM". Darn lower case thing took me a couple minutes to find.

 

[mmwdmr]

Using Crystal 10 & SQL database:

This should be simple. My first group is on employee name. My second group is on transaction date (with the by month option). I have a summary count of the date details (how many transactions) but I also need to count the number of distinct months/year for each employee. For instance, I need a count of 5 total months and 54 total transactions:

Smith
January 2004 14
May 2004 10
November 2004 5
February 2005 9
January 2006 16
5 months 54 transactions

Anyone? Thanks!

 

[mmwdmr]

PLEASE DISREGARD MY POST - I DIDN'T REALIZE I WAS IN SOMEONE'S THREAD. SORRY!