Crystal Reports Dynamic Scheduling

[Former Member]

Problem Statement:

Currently, user has the ability to schedule Crystal Reports by applying the “From” and “To” date values for the input parameters. But this does not help because customer wants to set up a report to run monthly in Crystal Reports to retrieve the last month data.

There is a limitation with the existing scheduling system that, the report will run only for the date range mentioned in the parameters. Thus, currently there is no difference between the view mode and scheduling mode except in the mode of running – foreground or background.

The user would prefer more reasonable date type intervals while running the report in the scheduling mode such as Date Range, Last 30 Days, Last 7 Days, Last Month, Last Quarter, Last Week, Yesterday etc., which the existing system currently doesn’t support.

Expectation:

Is there a way to hide/remove the “From“ and “To” date parameters when user selects other than “Date Range” as input in Date Type Interval parameter.

[former_member205840]

Hi Rakesh,

The base report was designed to pull data for a specific date range using From and To dates.  You can very well remove this condition but before you remove implement new criteria ( Last 30 Days, Last 7 Days, Last Month, Last Quarter, Last Week, Yesterday) so that report will filter data and display.  Otherwise report will retrieve entire data by including other report filters.

Logic to Implement new requirement is :

Create a parameter with ( Last 30 Days, Last 7 Days, Last Month, Last Quarter, Last Week, Yesterday) as static values, it should be string parameter and accepts single value.

Now write below code to run report for selected option :

If {?DateRang} = 'Last 30 days' Then

{DatabaseDateField} >= dateadd('d',-30, currentdate) and {DatabaseDateField} <= Currentdate

Else

If {?DateRang} = 'Last 7 days' Then

{DatabaseDateField} >= dateadd('d',-7, currentdate) and {DatabaseDateField} <= Currentdate

Else

If {?DateRang} = 'Last Month' Then {DatabaseDateField} in  LastFullMonth

Else

If {?DateRang} = 'Last Week' Then {DatabaseDateField} in  LastFullWeek

Else

If {?DateRang} = 'Yesterday' Then {DatabaseDateField} = currentdate -1

Place this formula in your record selection to filter records as per parameter selection.

Thanks,

Sastry

[Former Member]

Thanks Sastry.. Thanks for the quick response. Will try.

Thanks,

Rakesh

[Former Member]

Hi Sastry,

Thanks for your suggestion. 

Is it possible to have a Crystal Report which supports both custom date range ( "From Date" and "To Date" which ideally used in view mode) and dynamic date range parameter with ( Last 30 Days, Last 7 Days, Last Month, Last Quarter, Last Week, Yesterday) as static values as you mentioned above.

[ido_millet]

Yes, it is possible.  Some of the 3rd-party Crystal Reports Desktop Schedulers (see list at: Crystal Reports Viewers, Schedulers and Related Products) support special date constants as parameter values (e.g. Today, Yesterday, ...).  Those date constants get translated during the execution of the report to the dynamic date values relative to the date of execution.