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
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