| 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