Crystal Reports Monthly Reports

Monthly Reports:


Monthly reports are the reports which are scheduled in the server to run monthly once and pulls the previous month data's. In crystal reports server, we can schedule the reports, when to run (i mean the day of the month), but there is no option to specify on which days(from and to dates) it has to show the data.

ORACLE Query:
To get the date of the last month's first day.
 TO_DATE( (ADD_MONTHS (TRUNC (SYSDATE,'MM'),-1) ) ) )
To get the date of the last month's last day.
 TO_DATE( (ADD_MONTHS (TRUNC (SYSDATE,'MM'),0)-1 ) ) )

Crystal reports Formula for last month first date and last date:
Formula to get the date of the last month's first day.
DateAdd('m',-1,CurrentDate-day(CurrentDate)+1)-1+1
Formula to get the date of the last month's last day.
DateAdd('m',0,CurrentDate-day(CurrentDate)+1)-1

Crystal Reports daily reports

Daily Reports:
                     Daily reports are the reports which are scheduled in the server to run on the daily basis and pulls the previous date data's. In crystal reports server, we can schedule the reports, when to run (i mean the time of the day), but there is no option to specify for which day it has to show the data.

We can acheive it by embedding the logic in the query itself, In the sql queries we can add filters like "where employnment_date between sysdate-1 and sysdate-1". it queries for the previous date data's.

To show the previous date range in the reports, we can use this formula:
DataDate-1(It returns the previous date to the current system date).

Here we are using DataDate, because it keeps the last ran date of the report with the report in the server. This is useful if we are scheduling our reports to run daily that fetches data for a day previous.
for an example if today you want to see the report for 01/01/1996, u can get it from the history of the scheduled report in the server. If you use another date formula to address this CurrentDate-1. it will be parsed each time you open the report i-e: if you open a report for 01/01/1996, it will show the data of the CurrentDate though it ran many years ago.