Crystal Reports Previous Month Calculation
I needed to get a report to run via Business Objects scheduler which would return data for the previous full month. So a Report run on 1st Feb, would return all data within a date range of 1st Jan – 31st Jan.
But the report would also need to be able to run on specific dates if the user required.
Step 1 – Create “Previous Month” Parameter”.
So I created a parameter which was a simple Y/N drop down. If “Y” was selected it would return the data for the previous Month.
{?CurMonth}
If N was selected, the report would use a Start Month and End Month Parameter values to return the required data.
Step 2. – Create Forumla to work out Previous Full Month Start/End Date
Create forumla @strFDMonth
This will calculate the First Day of the Previous Full Month.
minimum(Lastfullmonth)
Create forumla @strLDMonth
This will calculate the LadDay of the Previous Full Month.
maximum(Lastfullmonth)
Step 3 – Enter Selection Criteria
If {?CurMonth} = ‘Y’ Then
{DATE_FIELD} >= {@StrFDMonth} and {DATE_FIELD}} <= {@StrLDMonth}
else
{DATE_FIELD}} >= cdate({?STARTDATE}) and {DATE_FIELD} <= cdate({?ENDDATE})