Crystal Reports: Named-Date-Range Parameters

Often it’s useful to be able to use date ranges that are relative to the date the report was run, like ‘Month to Date’, when running a report. While Crystal Reports’ parameters do not possess this level of flexibility, it is fairly simple to approximate this capability. This approach uses two parameters and Crystal Reports’ date ranges to do so.

Date-Range Parameter

The first parameter, named ‘Date Range’, will allow an individual to choose one of these values: Yesterday, Last Full Week, Last Full Month, Month To Date or Custom Date Range. Add these values to the parameter’s Default Values by clicking the ‘Default Values…’ button.

Custom Date-Range Parameter

The second parameter, named ‘Custom Date Range’, will allow an individual to choose specific starting and ending dates. Ensure that the parameter’s ‘Range Values’ option is chosen. NOTE: the value ‘Custom Date Range’ needs to be chosen from the Date Range parameter, otherwise the value entered in this field is ignored.

Record Selection Formula

The record selection will use the selected value from the Date Range parameter to choose between one of Crystal Reports built-in date ranges or the value entered in the Custom Date Range parameter.

//replace {TABLE.FIELD} with the desired value
{TABLE.FIELD} IN (
IF {?Date Range}= "Last Full Month" THEN LastFullMonth
ELSE IF {?Date Range}= "Month To Date" THEN MonthToDate
ELSE IF {?Date Range}="Last Full Week" THEN LastFullWeek
ELSE IF {?Date Range}= "Yesterday" THEN DataDate-1
ELSE {?Custom Date Range}
)

NOTE: DataDate is immutable once a report’s data has been generated, unlike PrintDate. Even with saved data, PrintDate will be refreshed each time a report is viewed. This distinction is important if the report will be deployed in Crystal Enterprise.

Display the Date Range

If you would like to display the actual starting and ending dates from the chosen parameter combination on the report, create a formula name ‘Date Range’. Add the following to the formula’s text:

select {?Date Range}
case "Yesterday":
CStr(DataDate-1)
case "Last Full Week":
CStr(Minimum(LastFullWeek)) + "-"+ CStr(Maximum(LastFullWeek))
case "Last Full Month":
CStr(Minimum(LastFullMonth)) + "-"+ CStr(Maximum(LastFullMonth))
case "Month To Date":
CStr(Minimum(MonthToDate)) + "-"+ CStr(Maximum(MonthToDate))
case "Custom Date Range":
CStr(Minimum({?Custom Date Range})) + "-"+ CStr(Maximum({?Custom Date Range}))

Summary

In addition to making your report more flexible, this technique will make it easier to deploy, manage and schedule your report in Crystal Enterprise.

Samples

Crystal Reports 10 – Advanced Parameter Sample

11 thoughts on “Crystal Reports: Named-Date-Range Parameters

  1. Moore Greenberg

    Hello,
    I am having trouble making your fomula work for a “yesterday” date selection. Crystal cannot identify “DataDate”. What do you recommend so I can set up Enterprise to just distribute Yesterdays report every day?

    Thank You

    Moore

  2. System Administrator

    Which version of Crystal Reports are you using? This keyword is valid for versions 8.5 – 10.

  3. Mark

    Much appreciated! I was looking all over for the syntax to display the date ranges of the function-based date ranges.

  4. Annette

    Hello, that’s brilliant and very clear thanks. I have a question though. Does anybody know how to create a Date Range “This Week”? I want the report run automatically for this week, like LastFullWeek.
    Any ideas, experiences or advices are highy appreciated!!!
    Thanks in advance
    Annette

  5. Craig Buchanan

    You could use the WeekToDateFromSun range in the Record Selection Formula:

    ELSE IF {?Date Range}= “Week To Date” THEN WeekToDateFromSun

    And this in the Date Range function:
    case “Week To Date”:
    CStr(Minimum(WeekToDateFromSun)) + “-”+ CStr(Maximum(WeekToDateFromSun))

  6. Syed Gilani

    How do you set default values for a daterange parameter? I would like to choose different values for the Minimum and Maximum ranges.

  7. Craig Buchanan

    I’m not sure if this is possible. When I entered ’1/1/2007′ into the date-range parameter’s ‘Default Value’ textbox, this value was used by the ‘Start of Range’ and ‘End of Range’ values. Not ideal, but close. Next, I tried a number of other default values such as:

    [2007-1-1..2007-12-31]
    2007-1-1,2007-12-31
    2007-1-1;2007-12-31

    but none passed the Default Value field’s validation process.

    If you find a solution, please let me know.

  8. Catherine Williams

    I am now using this and it has the potential to cut the number of reports we have by 60% so it’s amazing. I have one question: it appears that you always have to enter dates in the date range even when you don’t choose custom date range, although it does ignore these dates. Is that correct or am I doing something wrong. If I leave the date range empty, when I click on month to date, then the report fails.

  9. Craig Buchanan

    @Catherine: Yes, you always need to enter a value in custom-date range, even if you choose a named-date range.

  10. Gladys Torres

    I am getting an error when trying to use the above Formula in the Record Selection: “A string is required here”, and highlights everything from the parenthesis forward. Any thoughts as to what I may be doing wrong? User Crystal XI

    {TABLE.FIELD} IN (
    IF {?Date Range}= “Last Full Month” THEN LastFullMonth
    ELSE IF {?Date Range}= “Month To Date” THEN MonthToDate
    ELSE IF {?Date Range}=”Last Full Week” THEN LastFullWeek
    ELSE IF {?Date Range}= “Yesterday” THEN DataDate-1
    ELSE {?Custom Date Range}
    )

  11. James DiBernardo

    AMAZING!

    We used it like this and it works!! THANK YOU THANK YOU!!

    {TABLE.FIELD} IN (
    If {?Date Range} = “1″ then LastFullMonth
    Else If {?Date Range} = “2″ then LastFullWeek
    Else If {?Date Range}= “3″ then Last7Days
    Else If {?Date Range}= “4″ then (DateAdd(“w”, -2, CurrentDate) to CurrentDate) //Last 2 Weeks
    Else If {?Date Range} = “5″ then (DateAdd(“w”, -3, CurrentDate) to CurrentDate) //Last 3 Weeks
    Else If {?Date Range} = “6″ then (DateAdd(“w”, -4, CurrentDate) to CurrentDate) //Last 4 Weeks
    Else If {?Date Range}= “7″ then (DateAdd(“w”, -5, CurrentDate) to CurrentDate) //Last 5 Weeks
    Else If {?Date Range} = “8″ then (DateAdd(“w”, -6, CurrentDate) to CurrentDate) //Last 6 Weeks
    Else {?Custom Date Range}
    )

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>