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.