**************************************************************************** Location : Local or Network Folders Host : FINDITEZ-T460P Path : C:\Users\ken\Documents\Test Cases\en\Samples\Reports\Feature Examples\Custom Functions.rpt **************************************************************************** Report Information Version: 11.0 Author: Copyright © 2004 Business Objects Comments: This report demonstrates the usage of Crystal's Custom Functions feature. Title: Custom Functions Demo Keywords: custom functions Printer: No Printer 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 Tables Table: Customer Connection: Connection #1 Used Columns: {Customer.Customer ID} : Number {Customer.Region} : String[62] {Customer.Country} : String[62] Table: Orders Connection: Connection #1 Used Columns: {Orders.Order ID} : Number {Orders.Order Amount} : Number {Orders.Customer ID} : Number {Orders.Order Date} : Date {Orders.Required Date} : Date Table Links Joins: Customer LEFT OUTER JOIN Orders ON Customer.[Customer ID] = Orders.[Customer ID] Record Selection Formulas Formula: {Customer.Country} in ['Canada','USA'] //and //{Orders.Order Date} in date(2004,01,01) to date(2004,12,31) Record Sorting Sort By: Customer.Country -- Ascending Order Group #1 Customer.Region -- Ascending Order Group #2 Orders.Order ID -- Ascending Order Custom Functions Function: cdExpandRegionAbbreviation Code: Function cdExpandRegionAbbreviation (regionAbbreviation As String, Optional country As String = "USA") Select Case UCase (country) Case "CANADA" cdExpandRegionAbbreviation = cdExpandRegionAbbreviationCanada (regionAbbreviation) Case "USA", "U.S.A.", "US", "U.S.", "UNITED STATES", "UNITED STATES OF AMERICA" cdExpandRegionAbbreviation = cdExpandRegionAbbreviationUSA (regionAbbreviation) Case Else cdExpandRegionAbbreviation = regionAbbreviation End Select End Function Function: cdExpandRegionAbbreviationCanada Code: //cdExpandRegionAbbreviationCanada Function (stringVar abbreviation) if length (abbreviation) = 2 then ( select UpperCase (abbreviation) // Canadian provinces and territories case "AB": "Alberta" case "BC": "British Columbia" case "MB": "Manitoba" case "NB": "New Brunswick" case "NF": "Newfoundland" case "NS": "Nova Scotia" case "NT": "Northwest Territories" case "NU": "Nunavut" case "ON": "Ontario" case "PE": "Prince Edward Island" case "QC", "PQ": "Québec" case "SK": "Saskatchewan" case "YT": "Yukon Territory" default: abbreviation; ) else abbreviation; Function: cdExpandRegionAbbreviationUSA Code: //cdExpandRegionAbbreviationUSA Function (stringVar abbreviation) if length (abbreviation) = 2 then ( select UpperCase (abbreviation) //US states case "AA": "Armed Forces Americas" case "AE": "Armed Forces Europe" case "AL": "Alabama" case "AK": "Alaska" case "AP": "Armed Forces Pacific" case "AR": "Arkansas" case "AS": "American Samoa" case "AZ": "Arizona" case "CA": "California" case "CO": "Colorado" case "CT": "Connecticut" case "DC": "District of Columbia" case "DE": "Delaware" case "FL": "Florida" case "FM": "Federated States of Micronesia" case "GA": "Georgia" case "GU": "Guam" case "HI": "Hawaii" case "IA": "Iowa" case "ID": "Idaho" case "IL": "Illinois" case "IN": "Indiana" case "KS": "Kansas" case "KY": "Kentucky" case "LA": "Louisiana" case "MA": "Massachusetts" case "MD": "Maryland" case "ME": "Maine" case "MH": "Marshall Islands" case "MI": "Michigan" case "MN": "Minnesota" case "MO": "Missouri" case "MP": "Northern Mariana Islands" case "MS": "Mississippi" case "MT": "Montana" case "NE": "Nebraska" case "NC": "North Carolina" case "ND": "North Dakota" case "NH": "New Hampshire" case "NJ": "New Jersey" case "NM": "New Mexico" case "NV": "Nevada" case "NY": "New York" case "OH": "Ohio" case "OK": "Oklahoma" case "OR": "Oregon" case "PA": "Pennsylvania" case "RI": "Rhode Island" case "PR": "Puerto Rico" case "SC": "South Carolina" case "SD": "South Dakota" case "TN": "Tennessee" case "TX": "Texas" case "UT": "Utah" case "VA": "Virginia" case "VI": "Virgin Islands" case "VT": "Vermont" case "WA": "Washington" case "WI": "Wisconsin" case "WV": "West Virginia" case "WY": "Wyoming" default: abbreviation; ) else abbreviation; Function: cdFormatCurrencyUsingScaling Code: Function cdFormatCurrencyUsingScaling (value As Currency, _ nDecimalPlaces As Number, _ thousandsSymbol as String, _ millionsSymbol as String) nDecimalPlaces = Round (nDecimalPlaces) If nDecimalPlaces < 0 Or nDecimalPlaces > 9 Then nDecimalPlaces = 2 End If If Abs (value) >= 1000000 Then cdFormatCurrencyUsingScaling = CStr (value / 1000000, nDecimalPlaces) + millionsSymbol ElseIf Abs (value) >= 1000 Then cdFormatCurrencyUsingScaling = CStr (value / 1000, nDecimalPlaces) + thousandsSymbol Else cdFormatCurrencyUsingScaling = CStr (value, nDecimalPlaces) End If End Function Function: cdConvertUSToCanadian Code: //ConvertUSToCanadian Function (CurrencyVar usDollarAmount) //The exchange rate used by Business Objects Finance for March 2001 usDollarAmount * 1.48 Function: cdDateAddSkipHolidays Code: Function cdDateAddSkipHolidays (nWorkingDays As Number, startDateTime As DateTime) As DateTime 'the result = startDateTime + nWorkingDays + nHolidays (including weekends) 'the algorithm below also works when nWorkingDays is negative Dim totalDays 'give a rough estimate for total days. For each 5 work days there are 7 'days. Also add in an extra 2 weekend days to account for a partial week. totalDays = Fix(nWorkingDays * 7 / 5 + 1) + 2 Dim iteratedDiff iteratedDiff = cdDateDiffSkipHolidays (startDateTime, startDateTime + totalDays) If (iteratedDiff >= nWorkingDays) Then Do While iteratedDiff >= nWorkingDays totalDays = totalDays - 1 iteratedDiff = cdDateDiffSkipHolidays (startDateTime, startDateTime + totalDays) Loop cdDateAddSkipHolidays = startDateTime + totalDays + 1 Else Do While iteratedDiff < nWorkingDays totalDays = totalDays + 1 iteratedDiff = cdDateDiffSkipHolidays (startDateTime, startDateTime + totalDays) Loop cdDateAddSkipHolidays = startDateTime + totalDays End If End Function Function: cdDateDiffSkipHolidays Code: Function cdDateDiffSkipHolidays (startDateTime As DateTime, endDateTime As DateTime) As Number Dim reversed reversed = False If startDateTime > endDateTime Then Dim tempDateTime tempDateTime = startDateTime startDateTime = endDateTime endDateTime = tempDateTime reversed = True End If Dim diffWithoutWeekends diffWithoutWeekends = cdDateDiffSkipWeekends (startDateTime, endDateTime) Dim startYear, endYear startYear = Year (startDateTime) endYear = Year (endDateTime) Dim statsInStartYear () As Date statsInStartYear = cdStatutoryHolidays (startYear, True) Dim dateRange As Date Range dateRange = CDate (startDateTime) To CDate (endDateTime) Dim numberOfStatsInRange As Number numberOfStatsInRange = 0 'We are assuming that the number of statutory holidays in a year is always the same Dim numberOfStatsInOneYear numberOfStatsInOneYear = UBound (statsInStartYear) Dim i For i = 1 To numberOfStatsInOneYear If statsInStartYear (i) In dateRange Then numberOfStatsInRange = numberOfStatsInRange + 1 End If Next i If startYear <> endYear Then Dim statsInEndYear () As Date statsInEndYear = cdStatutoryHolidays (endYear, True) For i = 1 To numberOfStatsInOneYear If statsInEndYear (i) In dateRange Then numberOfStatsInRange = numberOfStatsInRange + 1 End If Next i numberOfStatsInRange = numberOfStatsInRange + _ (endYear - startYear - 1) * numberOfStatsInOneYear End If cdDateDiffSkipHolidays = diffWithoutWeekends - numberOfStatsInRange 'Can happen in rare situations e.g. The difference between Friday March 28 (Good Friday) 'and Saturday March 29 is -1 at this point. If cdDateDiffSkipHolidays < 0 Then cdDateDiffSkipHolidays = 0 If reversed Then cdDateDiffSkipHolidays = -cdDateDiffSkipHolidays End Function Function: cdDateDiffSkipWeekends Code: Function cdDateDiffSkipWeekends (startDateTime As DateTime, endDateTime As DateTime) As Number 'this function assumes that weekends are on Saturday and Sunday. You can modify the constants 'below to change this assumption. cdDateDiffSkipWeekends = _ DateDiff("d", startDateTime, endDateTime) - _ DateDiff("ww", startDateTime, endDateTime, crSaturday) - _ DateDiff("ww", startDateTime, endDateTime, crSunday) End Function Function: cdStatutoryHolidays Code: Function cdStatutoryHolidays (y As Number, Optional asRecognized As Boolean = False) 'returns a Date array Dim holidays (10) As Date Dim d As Date Dim offset As Number Dim officialDay As Number 'New Year's day is January 1 d = CDate (y, 1, 1) If asRecognized Then officialDay = WeekDay (d) Select Case officialDay Case 1 'Sunday d = d + 1 Case 7 'Saturday d = d + 2 End Select End If holidays (1) = d 'Good Friday is the Friday before Easter Sunday holidays (2) = cdEasterDate (y) - 2 'Since 1952 Victoria day is the Monday prior to May 25 d = CDate (y, 5, 25) offset = WeekDay (d, crMonday) - 1 holidays (3) = d - offset 'Canada Day is July 1 d = CDate (y, 7, 1) If asRecognized Then officialDay = WeekDay (d) Select Case officialDay Case 1 'Sunday d = d + 1 Case 7 'Saturday d = d + 2 End Select End If holidays (4) = d 'BC day is the first Monday of August d = CDate (y, 8, 1) offset = (7 - WeekDay (d, crTuesday)) Mod 7 holidays (5) = d + offset 'Since 1894 Labour day is the first Monday of September d = CDate (y, 9, 1) offset = (7 - WeekDay (d, crTuesday)) Mod 7 holidays (6) = d + offset 'Since 1957 Thanksgiving day is the second Monday of October d = CDate (y, 10, 1) offset = 7 + (7 - WeekDay (d, crTuesday)) Mod 7 holidays (7) = d + offset 'Remembrance day is November 11 d = CDate (y, 11, 11) If asRecognized Then officialDay = WeekDay (d) Select Case officialDay Case 1 'Sunday d = d + 1 Case 7 'Saturday d = d + 2 End Select End If holidays (8) = d 'Christmas day is December 25 d = CDate (y, 12, 25) If asRecognized Then officialDay = WeekDay (d) Select Case officialDay Case 1 'Sunday d = d + 1 Case 7 'Saturday d = d + 2 End Select End If holidays (9) = d 'Boxing day is December 26 d = CDate (y, 12, 26) If asRecognized Then officialDay = WeekDay (d) Select Case officialDay Case 1 'Sunday. Christmas day will be recognized on Monday, so Boxing Day is recognized on Tuesday d = d + 2 Case 7 'Saturday d = d + 2 End Select End If holidays (10) = d cdStatutoryHolidays = holidays End Function Function: cdEasterDate Code: Function cdEasterDate (y As Number) As Date ' EASTER DATE CALCULATION FOR YEARS 1583 TO 4099 ' y is a 4 digit year 1583 to 4099 ' d returns the day of the month of Easter ' m returns the month of Easter ' Easter Sunday is the Sunday following the Paschal Full Moon ' (PFM) date for the year ' This algorithm is an arithmetic interpretation of the 3 step ' Easter Dating Method developed by Ron Mallen 1985, as a vast ' improvement on the method described in the Common Prayer Book ' Because this algorithm is a direct translation of the ' official tables, it can be easily proved to be 100% correct ' This algorithm derives values by sequential inter-dependent ' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS! ' The \ operator may be unfamiliar - it means integer division ' for example, 30 \ 7 = 4 (the remainder is ignored) ' All variables are integer data types ' It's free! Please do not modify code or comments! ' ========================================================== Dim FirstDig, Remain19, temp 'intermediate results Dim tA, tB, tC, tD, tE 'table A to E results DIM m, d FirstDig = y \ 100 'first 2 digits of year Remain19 = y Mod 19 'remainder of year / 19 'calculate PFM date temp = (FirstDig - 15) \ 2 + 202 - 11 * Remain19 If FirstDig > 26 Then temp = temp - 1 If FirstDig > 38 Then temp = temp - 1 'this next line uses underscore (_) line continuation characters 'in older versions of VB you may need to revert to one long line If ((FirstDig = 21) Or (FirstDig = 24) Or (FirstDig = 25) _ Or (FirstDig = 33) Or (FirstDig = 36) Or (FirstDig = 37)) _ Then temp = temp - 1 temp = temp Mod 30 tA = temp + 21 If temp = 29 Then tA = tA - 1 If (temp = 28 And Remain19 > 10) Then tA = tA - 1 'find the next Sunday tB = (tA - 19) Mod 7 tC = (40 - FirstDig) Mod 4 If tC = 3 Then tC = tC + 1 If tC > 1 Then tC = tC + 1 temp = y Mod 100 tD = (temp + temp \ 4) Mod 7 tE = ((20 - tB - tC - tD) Mod 7) + 1 d = tA + tE 'return the date If d > 31 Then d = d - 31 m = 4 Else m = 3 End If cdEasterDate = DateValue(y, m, d) End Function Function: cdDateAddSkipWeekends Code: Function cdDateAddSkipWeekends (nNonWeekendDays As Number, startDateTime As DateTime) As DateTime 'the result = startDateTime + nNonWeekendDays + nWeekendDays 'the algorithm below also works when nNonWeekendDays is negative Dim totalDays 'give a rough upper bound for total days. For each 5 non weekend days there are 7 'days. Also add in an extra 2 weekend days to account for a partial week. totalDays = Fix(nNonWeekendDays * 7 / 5 + 1) + 2 Do While cdDateDiffSkipWeekends (startDateTime, startDateTime + totalDays) >= nNonWeekendDays totalDays = totalDays - 1 Loop cdDateAddSkipWeekends = startDateTime + totalDays + 1 End Function Function: cdFirstDayOfMonth Code: Function cdFirstDayOfMonth (Optional dayInMonth As DateTime = CurrentDate) As Date cdFirstDayOfMonth = CDate (Year (dayInMonth), Month (dayInMonth), 1) End Function Function: cdFirstDayOfQuarter Code: Function cdFirstDayOfQuarter (Optional dayInQuarter As DateTime = CurrentDate) As Date Dim y As Number y = Year (dayInQuarter) Select Case DatePart ("q", dayInQuarter) Case 1 cdFirstDayOfQuarter = CDate (y, 1, 1) Case 2 cdFirstDayOfQuarter = CDate (y, 4, 1) Case 3 cdFirstDayOfQuarter = CDate (y, 7, 1) Case 4 cdFirstDayOfQuarter = CDate (y, 10, 1) End Select End Function Function: cdLastDayOfMonth Code: Function cdLastDayOfMonth (Optional dayInMonth As DateTime = CurrentDate) As Date cdLastDayOfMonth = DateSerial (Year (dayInMonth), Month (dayInMonth) + 1, 1 - 1) End Function Function: cdLastDayOfQuarter Code: Function cdLastDayOfQuarter (Optional dayInQuarter As DateTime = CurrentDate) As Date Dim y As Number y = Year (dayInQuarter) Select Case DatePart ("q", dayInQuarter) Case 1 cdLastDayOfQuarter = CDate (y, 3, 31) Case 2 cdLastDayOfQuarter = CDate (y, 6, 30) Case 3 cdLastDayOfQuarter = CDate (y, 9, 30) Case 4 cdLastDayOfQuarter = CDate (y, 12, 31) End Select End Function 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 ) Function: cdFormatDateRangeArray Code: Function cdFormatDateRangeArray (dra () As Date Range) As String Dim newLine as String newLine = Chr (13) + Chr (10) Dim i, nValues nValues = UBound (dra) For i = 1 To nValues cdFormatDateRangeArray = cdFormatDateRangeArray & cdFormatDateRange (dra (i)) If i < nValues Then cdFormatDateRangeArray = cdFormatDateRangeArray & newLine End If Next i End Function Function: cdFormatNumberRange Code: //cdFormatNumberRange Function (NumberVar range rng) NumberVar minValue := Minimum (rng); NumberVar 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 ) Function: cdFormatNumberRangeArray Code: Function cdFormatNumberRangeArray (nra () As Number Range) As String Dim newLine as String newLine = Chr (13) + Chr (10) Dim i, nValues nValues = UBound (nra) For i = 1 To nValues cdFormatNumberRangeArray = cdFormatNumberRangeArray & cdFormatNumberRange (nra (i)) If i < nValues Then cdFormatNumberRangeArray = cdFormatNumberRangeArray & newLine End If Next i End Function Function: cdFormatNumberUsingScaling Code: Function cdFormatNumberUsingScaling (value As Number, _ nDecimalPlaces As Number, _ thousandsSymbol as String, _ millionsSymbol as String) nDecimalPlaces = Round (nDecimalPlaces) If nDecimalPlaces < 0 Or nDecimalPlaces > 9 Then nDecimalPlaces = 2 End If If Abs (value) >= 1000000 Then cdFormatNumberUsingScaling = CStr (value / 1000000, nDecimalPlaces) + millionsSymbol ElseIf Abs (value) >= 1000 Then cdFormatNumberUsingScaling = CStr (value / 1000, nDecimalPlaces) + thousandsSymbol Else cdFormatNumberUsingScaling = CStr (value, nDecimalPlaces) End If End Function Function: cdFormatStringRange Code: //cdFormatStringRange Function (StringVar range rng) StringVar minValue := Minimum (rng); StringVar 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 ) Function: cdFormatStringRangeArray Code: Function cdFormatStringRangeArray (sra () As String Range) As String Dim newLine as String newLine = Chr (13) + Chr (10) Dim i, nValues nValues = UBound (sra) For i = 1 To nValues cdFormatStringRangeArray = cdFormatStringRangeArray & cdFormatStringRange (sra (i)) If i < nValues Then cdFormatStringRangeArray = cdFormatStringRangeArray & newLine End If Next i End Function Function: cdFormatTimeInterval Code: Function cdFormatTimeInterval ( _ timeInterval As Number, _ baseUnit As String, _ roundTo As String, _ daysSymbol As String, _ hoursSymbol As String, _ minutesSymbol As String _ ) As String 'ensure that baseUnit is one of: days, hours baseUnit = LCase (baseUnit) If Not (baseUnit In Array ("days", "hours")) Then baseUnit = "days" End If 'ensure that roundTo is one of: days, hours or minutes roundTo = LCase (roundTo) If Not (roundTo In Array ("days", "hours", "minutes")) Then roundTo = "minutes" End If 'ensure that baseUnit is not finer grained than roundTo If roundTo = "days" Then baseUnit = "days" End If Dim days, hours, minutes, timeRemaining, signOfResult signOfResult = sgn (timeInterval) timeInterval = abs (timeInterval) Select Case roundTo Case "days" days = Round (timeInterval) If days = 1 And Trim (daysSymbol) = "days" Then daysSymbol = Replace (daysSymbol, "s", "") End If cdFormatTimeInterval = CStr (days, 0) & daysSymbol Case "hours" If baseUnit = "days" Then 'timeRemaining in fractional days timeRemaining = timeInterval days = Int (timeRemaining) 'timeRemaining in hours timeRemaining = (timeRemaining - days) * 24 hours = Round (timeRemaining) 'handle rounding to the nearest hour If hours = 24 Then days = days + 1 hours = 0 End If cdFormatTimeInterval = CStr (days, 0) & daysSymbol & CStr (hours, 0) & hoursSymbol Else hours = Round (timeInterval * 24) If hours = 1 Then If Trim (hoursSymbol) In Array ("hrs", "hours") Then hoursSymbol = Replace (hoursSymbol, "s", "") End If End If cdFormatTimeInterval = CStr (hours, 0) & hoursSymbol End If Case "minutes" If baseUnit = "days" Then 'timeRemaining in fractional days timeRemaining = timeInterval days = Int (timeRemaining) 'timeRemaining in fractional hours timeRemaining = (timeRemaining - days) * 24 hours = Int (timeRemaining) 'timeRemaining is fractional minutes timeRemaining = (timeRemaining - hours) * 60 minutes = Round (timeRemaining) 'handle rounding to the nearest minute 'For example, 2d 23h 60m is rounded to 3d 0h 0m If minutes = 60 Then hours = hours + 1 minutes = 0 If hours = 24 Then days = days + 1 hours = 0 End If End If cdFormatTimeInterval = CStr (days, 0) & daysSymbol & _ CStr (hours, 0) & hoursSymbol & CStr (minutes, 0) & minutesSymbol ElseIf baseUnit = "hours" Then 'timeRemaining in fractional hours timeRemaining = timeInterval * 24 hours = Int (timeRemaining) 'timeRemaining is fractional minutes timeRemaining = (timeRemaining - hours) * 60 minutes = Round (timeRemaining) 'handle rounding to the nearest minute 'For example, 23h 60m is rounded to 24h 0m If minutes = 60 Then hours = hours + 1 minutes = 0 End If cdFormatTimeInterval = CStr (hours, 0) & hoursSymbol & CStr (minutes, 0) & minutesSymbol End If End Select If days = 1 And Trim (daysSymbol) = "days" Then cdFormatTimeInterval = Replace (cdFormatTimeInterval, "days", "day") End If If hours = 1 Then Select Case Trim (hoursSymbol) Case "hours" cdFormatTimeInterval = Replace (cdFormatTimeInterval, "hours", "hour") Case "hrs" cdFormatTimeInterval = Replace (cdFormatTimeInterval, "hrs", "hr") End Select End If If minutes = 1 Then Select Case Trim (minutesSymbol) Case "minutes" cdFormatTimeInterval = Replace (cdFormatTimeInterval, "minutes", "minute") Case "mins" cdFormatTimeInterval = Replace (cdFormatTimeInterval, "mins", "min") End Select End If If signOfResult = -1 Then cdFormatTimeInterval = "- " + cdFormatTimeInterval End If End Function Function: cdIncreaseCurrencyByAPercentage Code: //cdIncreaseCurrencyByAPercentage Function (CurrencyVar valueToIncrease, NumberVar percentIncrease) valueToIncrease + valueToIncrease * percentIncrease / 100; Function: cdIncreaseNumberByAPercentage Code: //cdIncreaseNumberByAPercentage Function (NumberVar valueToIncrease, NumberVar percentIncrease) valueToIncrease + valueToIncrease * percentIncrease / 100; Function: cdPercentageDifference Code: Function cdPercentageDifference (baseValue As Currency, updatedValue As Currency) As Number If baseValue <> 0 Then cdPercentageDifference = (updatedValue - baseValue) / baseValue * 100 ElseIf updatedValue = 0 Then cdPercentageDifference = 0 ElseIf updatedValue > 0 Then cdPercentageDifference = 10^300 'positive infinity ElseIf updatedValue < 0 Then cdPercentageDifference = -10^300 'negative infinity End If End Function Formula Fields Field: Region Long Name Formula: cdExpandRegionAbbreviation ({Customer.Region}, {Customer.Country}) Field: Order Amount, $US Formula: cdFormatCurrencyUsingScaling ({Orders.Order Amount}, 2, "K", "M") Field: Order Amount, $Cdn Formula: currencyvar cdn; cdn:= cdConvertUSToCanadian ({Orders.Order Amount}); cdFormatCurrencyUsingScaling (cdn, 2, "K", "M") Field: Suppressor Formula: whileprintingrecords; numbervar suppressor:=suppressor+1 Field: Suppressor Reset Formula: whileprintingrecords; numbervar suppressor:=0 Field: Business Days Available Formula: cdDateDiffSkipHolidays ({Orders.Order Date}, {Orders.Required Date}) Field: Calendar Days Between Formula: {Orders.Required Date}-{Orders.Order Date} Page Header Section: Section a Fields Field: ReportComments1 Data Source: ReportComments Data Type: String Field: ReportTitle1 Data Source: ReportTitle Data Type: String Text Fields Text Field: Text4 Text: Report Description: Pictures Picture: Picture1 Size: 150x40 Hyperlink: http://www.businessobjects.com Boxes Box: Box1 Ends in Section: PagerHeaderSection1 Size: 676x58 Line Type: Single Line Thickness: 20 Groups Group: Group #1 Grouped By: {Customer.Country} Group Header Section: Section a Fields Field: Group1Name1 Data Source: GroupName ({Customer.Country}) Data Type: String Field: SuppressorReset1 Data Source: {@Suppressor Reset} Data Type: Number Format: Number Currency Symbol: None Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: -5,555,555. Boxes Box: Box2 Ends in Section: GroupFooterSection1 Size: 677x117 Line Type: Single Line Thickness: 20 Group Footer Conditional Formulas Condition: EnableNewPageAfter Formula: not onlastrecord Group: Group #2 Grouped By: {Customer.Region} Group Header Section: Section a Fields Field: GroupNameRegion1 Data Source: GroupName ({Customer.Region}) Data Type: String Field: Suppressor1 Data Source: {@Suppressor} 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: Text6 Text: ({@Region Long Name}) Section: Section b Conditional Formulas Condition: EnableSuppress Formula: whileprintingrecords; numbervar suppressor > 1 Text Fields Text Field: Text7 Headings Heading: Text14 Text: Required Date Heading: Text19 Text: Business Days Available Heading: Text12 Text: Order ID Heading: Text20 Text: Calendar Days Between Heading: Text2 Text: Order Date Heading: Text8 Text: Amount (in $US) Heading: Text9 Text: Amount (in $Cdn) Detail Section: Section a Fields Field: OrderAmountUS1 Data Source: {@Order Amount, $US} Data Type: String Tool Tip Formula: "This formula uses a custom function to create an abbreviated currency display." Field: OrderAmountCdn1 Data Source: {@Order Amount, $Cdn} Data Type: String Tool Tip Formula: "This formula uses custom functions to calculate the Orders Amount in Canadian dollars and to create an abbreviated currency display." Field: OrderID1 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: -5555555 Field: OrderDate1 Data Source: {Orders.Order Date} Data Type: Date Format: Date and Time Order: Date Time Date Type: System Default Date Format: YYYY-MM-DD hh:mm:ss AM Field: RequiredDate1 Data Source: {Orders.Required Date} Data Type: Date Format: Date and Time Order: Date Time Date Type: System Default Date Format: YYYY-MM-DD hh:mm:ss AM Field: BusinessDaysAvailable1 Data Source: {@Business Days Available} Data Type: Number Tool Tip Formula: "This formula uses a custom function to calculate the number of business days between the Order Date and the Required Date. Holidays and weekends are excluded from this value." Format: Number Currency Symbol: None Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: -5,555,555 Field: CalendarDaysBetween1 Data Source: {@Calendar Days Between} Data Type: Number Tool Tip Formula: "This formula calculates the number of days between the Order Date and the Required Date." Format: Number Currency Symbol: None Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: -5,555,555 Page Footer Section: Section a Fields Field: Field6 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: Text1 Text: Feedback on Samples by E-mail Hyperlink: mailto:crfeedback@businessobjects.com Text Field: Text3 Text: Business Objects Technical Support Site Hyperlink: http://support.businessobjects.com Text Field: Text5 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. Pictures Picture: Picture3 Size: 150x40 Hyperlink: http://www.businessobjects.com Lines Line: Line1 Ends in Section: Section13 Line Type: Single Line Thickness: 10