Develop > Operational Reports > Customizing Report Settings > Customizing Against a Reference Date

Customizing Against a Reference Date

You can easily run a report against a reference date by completing the following tasks:

  1. Add the Reference Date parameter
  2. Add the Reference Condition parameter
  3. Define the cdFormatDateRange function (Crystal Syntax)
  4. Define the cdSpecialDateRange function (Basic Syntax)
  5. Modify formulas
  6. Run a Customized Report

Add the Reference Date parameter

  1. Open a report to be customized using Crystal Reports. The Design page of the report opens.
  2. From the View menu, click Field Explorer. The Field Explorer panel opens.
  3. On the panel, right-click Parameter Fields and select New. The Create New Parameter window opens.

  4. Configure the following:

    • Name: Reference Date
    • Type: Date
    • List of Values: Static
  5. In the Value Options section, select Read only for Show on (Viewer) Panel option, and then click OK. The Reference Date parameter is added into the Parameter Fields.

Add the Reference Condition parameter

  1. Right-click Parameter Fields, and then select New. The Create New Parameter window opens.

  2. Configure the following:

    • Name: Reference Condition
    • Type: String
    • List of Values: Static

  3. Import values to the parameter.

    1. Create a .txt file with the following content:

      %%%%ShowDescOnly0
Aged 0 to 30 days
Aged 31 to 60 days
Aged 61 to 90 days
All dates from today
All dates from tomorrow
All dates to today
All dates to yesterday
Calendar 1st half
Calendar 2nd half
Calendar 1st quarter
Calendar 2nd quarter
Calendar 3rd quarter
Calendar 4th quarter
Last 1 day
Last 7 days
Last 4 weeks to Sunday
Last full week
Last full month
Last year month to date
Last year to date
Month to date
Quarter to date
Next 30 days
Next 31 to 60 days
Next 61 to 90 days
Next 91 to 365 days
Over 90 days
Week to date from Sunday
Year to date

    2. In the Create New Parameter window, click Actions > Import and select the .txt file. The values are imported to the Reference Condition parameter.

      Note For more information about the definition of these date ranges, see Date Range Definition.

  4. In the Value Options section, do the following:

    • Select Read only for Show on (Viewer) Panel.
    • Select a setting option, for example Month to date for Default Value.
    • Select False for “Allow custom values”.
  5. Click OK. The Reference Condition parameter is added into the Parameter Fields.

Define the cdFormatDateRange function (Crystal Syntax)

  1. From the Report menu, click Formula Workshop. The Formula Workshop window opens.
  2. Click the down arrow next to New , and then select Custom Function. The Custom Function Name dialog opens.
  3. Type cdFormatDateRange in the Name text box, and then click Use Editor. The Custom Function Editor window opens.
  4. Select Crystal Syntax from the drop-down list on the toolbar.

  5. Copy the following to replace the existing information in the editor area:

    Function (DateVar range rng)
 
    DateVar minValue := Minimum (rng);
    DateVar maxValue := Maximum (rng);
 
    StringVar minString := ToText(minValue,"MM/dd/yyyy");
    StringVar maxString := ToText (maxValue,"MM/dd/yyyy"); 
 
    if HasLowerBound (rng) and HasUpperBound (rng) then
    (
        // To, _To, To_ or _To_
        if IncludesLowerBound (rng) and 
           IncludesUpperBound (rng) then
        (
            if minValue = maxValue then
                minString
            else
                "between " + minString + " and " + maxString
        )
        else if IncludesLowerBound (rng) then
            "between " + minString + " and " + maxString
             + " not including right endpoint"
        else if IncludesUpperBound (rng) then   
            "between " + minString + " and " + maxString
             + " not including left endpoint"
        else
            "between " + minString + " and " + maxString
             + " not including endpoints"
    )
    else if HasLowerBound (rng) then
    (
        // Is > or Is >=
        if IncludesLowerBound (rng) then
            "greater than or equal to " + minString
        else
            "greater than " + minString
    )
    else if HasUpperBound (rng) then
    (
        // Is < or Is <=
        if IncludesUpperBound (rng) then
            "less than or equal to " + maxString
        else
            "less than " + maxString
    )

  6. Click Save and close. The function is now defined.

    Note Click Check on the toolbar to look for any possible errors in the code. You must fix all errors to proceed.

Define the cdSpecialDateRange function (Basic Syntax)

  1. From the Report menu, click Formula Workshop. The Formula Workshop window opens.
  2. Click the down arrow next to New , and then select Custom Function as shown below. The Custom Function Name dialog opens.
  3. Type cdSpecialDateRange in the Name text box, and then click Use Editor. The Custom Function Editor window opens.
  4. Select Basic Syntax from the drop-down list on the toolbar.

  5. Copy the following to replace the existing information in the editor area:

    ' The user supplies a reference date parameter,
' "d" and a range condition,"condition" to indicate
' a range of dates around the reference date.
 
Function cdSpecialDateRange (d As Date, condition As String) _
                            As Date Range
    'lower case condition
    condition = LCase (condition)
    'elminate spaces
    condition = Replace (condition, " ", "")
    'eliminate tabs
    condition = Replace (condition, "   ", "")
 
    Select Case condition
    Case LCase("Aged0To30Days")
        cdSpecialDateRange = (d - 30) To d
    Case LCase("Aged31To60Days")
        cdSpecialDateRange = (d - 60) To (d - 31)
    Case LCase("Aged61To90Days")
        cdSpecialDateRange = (d - 90) To (d - 61)
    Case LCase("AllDatesFromToday")
        cdSpecialDateRange = Is >= d
    Case LCase("AllDatesFromTomorrow")
        cdSpecialDateRange = Is >= (d + 1)
    Case LCase("AllDatesToToday")
        cdSpecialDateRange = Is <= d
    Case LCase("AllDatesToYesterday")
        cdSpecialDateRange = Is <= (d - 1)
    Case LCase("Calender1stHalf")
        cdSpecialDateRange = CDate(Year(d), 1, 1) _
            To CDate(Year(d), 6, 30)
    Case LCase("Calendar2ndHalf")
        cdSpecialDateRange = CDate(Year(d), 7, 1) _
            To CDate(Year(d), 12, 31)
    Case LCase("Calendar1stQtr"), LCase("Calendar1stQuarter")
        cdSpecialDateRange = CDate(Year(d), 1, 1) _
            To CDate(Year(d), 3, 31)
    Case LCase("Calendar2ndQtr"), LCase("Calendar2ndQuarter")
        cdSpecialDateRange = CDate(Year(d), 4, 1) _
            To CDate(Year(d), 6, 30)
    Case LCase("Calendar3rdQtr"), LCase("Calendar3rdQuarter")
        cdSpecialDateRange = CDate(Year(d), 7, 1) _
            To CDate(Year(d), 9, 30)
    Case LCase("Calendar4thQtr"), LCase("Calendar4thQuarter")
        cdSpecialDateRange = CDate(Year(d), 10, 1) _
            To CDate(Year(d), 12, 31)
    Case LCase("Last4WeeksToSun"), LCase("Last4WeeksToSunday")
        cdSpecialDateRange = (d - 27 - (Weekday(d) - 1)) _
            To (d - (Weekday(d) - 1))
    Case LCase("Last1Day")
        cdSpecialDateRange = (d - 1) To d
    Case LCase("Last7Days")
        cdSpecialDateRange = (d - 6) To d
    Case LCase("LastFullMonth")
        cdSpecialDateRange = DateSerial(Year(d), Month(d) - 1, 1) _
            To DateSerial(Year(d), Month(d), 1 - 1)
    Case LCase("QuarterToDate")
        Dim m as Number
        m = ToNumber(Month(d))
        if (m >=1 and m <=3) _
        then  cdSpecialDateRange = CDate(Year(d), 1, 1) To d _
        else if (m>=4 and m<=6) _
            then cdSpecialDateRange = CDate(Year(d), 4, 1) To d _
        else if (m>=7 and m<=9) _
            then cdSpecialDateRange = CDate(Year(d), 7, 1) To d _
        else if (m>=10 and m<=12) _
            then cdSpecialDateRange = CDate(Year(d), 10, 1) To d 
    Case LCase("LastFullWeek")
        cdSpecialDateRange = (d - 6 - Weekday(d)) _
            To (d - Weekday(d))
    Case LCase("LastYearMTD"), LCase("LastYearMonthToDate")
        cdSpecialDateRange = CDate(Year(d) - 1, Month(d), 1) _
            To CDate(DateAdd("yyyy", -1, d))
    Case LCase("LastYearYTD"), LCase("LastYearToDate")
        cdSpecialDateRange = CDate(Year(d) - 1, 1, 1) _
            To CDate(DateAdd("yyyy", -1, d))
    Case LCase("MonthToDate")
        cdSpecialDateRange = CDate(Year(d), Month(d), 1) To d
    Case LCase("Next30Days")
        cdSpecialDateRange = d To (d + 30)
    Case LCase("Next31To60Days")
        cdSpecialDateRange = (d + 31) To (d + 60)
    Case LCase("Next61To90Days")
        cdSpecialDateRange = (d + 61) To (d + 90)
    Case LCase("Next91To365Days")
        cdSpecialDateRange = (d + 91) To (d + 365)
    Case LCase("Over90Days")
        cdSpecialDateRange = Is <= (d - 91)
    Case LCase("WeekToDateFromSun"), LCase("WeekToDateFromSunday")
        cdSpecialDateRange = (d- (Weekday(d) - 1)) To d
    Case LCase("YearToDate")
        cdSpecialDateRange = CDate(Year(d), 1, 1) To d
    Case Else
        'provide default handling and specify a valid range
        cdSpecialDateRange = CDate(1899, 12, 30) _
        To CDate(1899, 12, 30)
    End Select
End Function

  6. Click Save and close. The function is now defined.

    Note Click Check on the toolbar to look for any possible errors in the code. You must fix all errors to proceed.

Modify formulas

  1. On the Field Explorer panel, expand Formula Fields, and double-click Date Range. The Formula Editor window opens.

  2. Copy the following to replace the existing information in the editor area:

    // This formula displays the range of dates the user has selected by
// supplying values for the parameters 
// selected by supplying values for the parameters
 
"The selected date range is " +cdFormatDateRange 
(cdSpecialDateRange({?Reference Date},{?Reference Condition}))
  3. Click Save and close to save the modification. The Formula Workshop window is closed.
  4. On the Field Explorer panel, expand Parameter Fields, right-click Start Date and select Find in Formulas. The Formula Editor window opens.

  5. In the editor area, replace all instances of >= {?Start Date} with in cdSpecialDateRange({?Reference Date},{?Reference Condition}).

    • Click Check on the toolbar to look for any possible errors in the code. You must fix all errors to proceed.
    • Use the search and replace feature to change all instances of >= {?Start Date}.
    • Make sure the code in cdSpecialDateRange({?Reference Date},{?Reference Condition}) in the same line in case of errors.

    Before

    After

  6. Click Save and close to save this modification.
  7. Optional step: Right-click Start Date on the Field Explorer panel and select Delete.
  8. Click Save on the Standard toolbar to save all modifications that you have made.

Run a Customized Report

  1. Start the legacy listener. See Start the legacy listener.
  2. Click Print Preview on the Standard toolbar. The Enter Values window opens.

  3. Enter the date in “mm/dd/yyyy” format in the Reference Date text box. Or click the calendar icon and select a date.

    Note To modify a customized reference date, click the drop-down list below the Enter Reference Condition text and select the option you need.

  4. To run the report, click OK.

    Note To modify the prompt text displayed in Enter Values window, double-click a parameter on the Field Explorer panel and modify the content for Prompt Text. For example, to modify Enter Reference Date:, double-click Reference Date parameter on the Field Explorer panel, locate the Prompt Text row in the Value Options section, and replace the text as needed.