Report Information
Version: 11.0
Author: Copyright © 2004 Business Objects
Comments: The report will show the selected orders with their order dates falling in the specified date range/time period, which you must choose as parameters after you refresh or preview the report.
Title: Record Selection on Date Range
Subject: Selecting orders based on date range parameters
Keywords: date Range, selection, formula
Printer: Unknown/Not Set
Main Body
Data Sources
Source: Connection #1
Database DLL: crdb_odbc.dll
Database Type: ODBC (RDO)
Server: Xtreme Sample Database 11.5
Properties: QE_LogonProperties
DSN: Xtreme Sample Database 11.5
UseDSNProperties: False
QE_SQLDB: True
SSO Enabled: False
Report Parameters
Parameter: reference date
Data Type: Date
Description: Enter a reference order date:
List Type: Static
Default Values: 2004-06-01 12:00:00 AM
Parameter: reference condition
Data Type: String
Description: Enter a range condition with respect to the reference date:
List Type: Static
List Values: 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
Calender 1st half
Calendar 2nd half
Calendar 1st quarter
Calendar 2nd quarter
Calendar 3rd quarter
Calendar 4th quarter
Last 4 weeks to Sunday
Last 7 days
Last full month
Last full week
Last year Month to Date
Last year Year to Date
Month to Date
Next 30 days
Next 31 to 60 days
Next 91 to 365 days
Over 90 days
Week to Date from Sunday
Year to Date
Default Values: Aged 0 to 30 days
Tables
Table: Orders
Connection: Connection #1
Used Columns: {Orders.Order ID} : Number
{Orders.Order Amount} : Number
{Orders.Order Date} : DateTime
{Orders.Ship Date} : DateTime
Record Selection Formulas
Formula: // This record selection formula demonstrates the use of a custom function to create a flexible record
// selection formula that is pushed down to the database server.
//
// The user supplies a reference order date parameter, {?reference date} and a range condition,
// {?reference condition} to indicate a range of dates around the reference order date. The report
// is then previewed with only the records whose order dates fall in the specified range. Notice
// that the SQL query has a Where clause and in fact, this entire record selection is performed on
// the database server. This is because the custom function and all its arguments can be evaluated before
// accessing the database. Writing this formula with an If/Then/Else would not let it be pushed down to
// the database server. See the section on Advanced Record Selection Formulas in the Seagate Crystal
// Reports manual for details as to why.
{Orders.Order Date} in cdSpecialDateRange({?reference date},{?reference condition})
Record Sorting
Sort By: Orders.Order Date -- Ascending Order
Custom Functions
Function: cdSpecialDateRange
Code: ' 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("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("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("LastYearYearToDate")
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
Function: cdFormatDateRange
Code: //cdFormatDateRange
Function (DateVar range rng)
DateVar minValue := Minimum (rng);
DateVar maxValue := Maximum (rng);
StringVar minString := CStr (minValue);
StringVar maxString := CStr (maxValue);
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
)
Formula Fields
Field: displaySelectedDateRange
Formula: // This formula displays the range of dates the user has selected by supplying values for the parameters
// {?reference date} and {?reference condition}.
"The selected date range is " +
cdFormatDateRange (cdSpecialDateRange({?reference date},{?reference condition}))
Report Header
Section: Section a
Fields
Field: Field3
Data Source: PrintDate
Data Type: Date
Format: Date and Time
Order: Date Time
Date Type: System Default
Date Format: YYYY-MM-DD hh:mm:ss AM
Field: Field4
Data Source: PrintTime
Data Type: Time
Format: Date and Time
Order: Date Time
Date Type: System Default
Time Format: MM/DD/YY h:mm:ss AM
Text Fields
Text Field: Text1
Text: Record Selection on Date Range
Text Field: Text2
Text: Reference Date
Text Field: Text3
Text: Time Period/Date Range
Text Field: Text4
Text: Printed at
Text Field: Text5
Text: Selecting Orders Based on Date Range Parameters
Pictures
Picture: RO_Xtreme_Logo
Size: 279x111
Hyperlink: http://www.businessobjects.com
Lines
Line: Line1
Ends in Section: Section1
Line Type: Single
Line Thickness: 30
Section: Section b
Fields
Field: formatSelectedDateRange1
Data Source: {@displaySelectedDateRange}
Data Type: String
Page Header
Section: Section a
Text Fields
Text Field: Text6
Text: Order Date
Text Field: Text7
Text: Order Amount
Text Field: Text8
Text: Ship Date
Text Field: Text9
Text: Order ID
Detail
Section: Section a
Fields
Field: Field5
Data Source: {Orders.Order ID}
Data Type: Number
Format: Number
Currency Symbol: None
Suppress if Zero: False
Leading Zero: True
Reverse Sign: False
Number Style: -5,555,555
Field: Field6
Data Source: {Orders.Order Date}
Data Type: DateTime
Format: Date and Time
Order: Date Only
Date Type: Custom
DateTime Format: MM / DD / YYYY
Field: Field7
Data Source: {Orders.Ship Date}
Data Type: DateTime
Format: Date and Time
Order: Date Only
Date Type: Custom
DateTime Format: MM / DD / YYYY
Field: Field8
Data Source: {Orders.Order Amount}
Data Type: Number
Format: Number
Currency Type: Floating
Currency Symbol: $
Currency Position: Leading Outside Negative $-123
Suppress if Zero: False
Leading Zero: True
Reverse Sign: False
Number Style: $-5,555,555.
Page Footer
Section: Section a
Fields
Field: Field10
Data Source: PageNumber
Data Type: Number
Format: Number
Currency Symbol: None
Suppress if Zero: False
Leading Zero: True
Reverse Sign: False
Number Style: -5,555,555
Text Fields
Text Field: Text10
Text: Business Objects Technical Support Site
Hyperlink: http://support.businessobjects.com
Text Field: Text11
Text: Copyright 2004 Business Objects Software Limited. All rights reserved. Business Objects, the Business Objects logo, Crystal Reports, Crystal Enterprise, Crystal Analysis, WebIntelligence, RapidMarts, and BusinessQuery are trademarks or registered trademarks of Business Objects and its affiliates in the United States and/or other countries. Other trademarks are the property of their respective owners. Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352.
Text Field: Text12
Text: Feedback on Samples by E-mail
Hyperlink: mailto:crfeedback@businessobjects.com
Pictures
Picture: Picture2
Size: 150x40
Hyperlink: http://www.businessobjects.com
Lines
Line: Line2
Ends in Section: Section6
Line Type: Single
Line Thickness: 30