Crystal Report Tricks and Tips

Round Function

admin — Fri, 12 Nov 2010 21:17:51 +0000

Uses

The Round function can be used to control how numbers are rounded in your reports. By default, numbers will be displayed according to the default formatting you have set up; however the number will not be changed unless you use the Round Function.

Syntax	Variable	Description
Round(x)	x	The numeric or currency field to be rounded.
Round(x,y)	y	The number of places to be rounded to.

Examples

Example	Result
Round(1.234)	1
Round(1.234,2)	1.23
Round(1.49999)	1
Round(1.49999,2)	1.50
Round(1.5)	2
Round(54321.1234)	54321
Round(54321.1234,2)	54321.12
Round(54321.1234,-1)	54320
Round(54321.1234,-2)	54300

Using the Round Function to Round to the Tens Place

You can use the Round function to round to the tens (or hundreds) place if you want, by using a negative value.

For example, Round(54321.1234, -1) will round to the tens place, giving you 54320.

Forcing a Round Up

What if you want the value to round up if it has a decimal value, regardless of the amount? So 1.1, 1.5 and 1.9 would all become 2.

You have to resort to a formula in this case. Create a formula field that evaluates your value using this formula:

If {value}-truncate{value}<> 0 then truncate({value})+1 else {value}

What this say, is if the value has any decimal points, use the truncate function to remove the decimal and add 1 (round it up). If there is no decimal, keep the value.

How to Display the Actual Number

admin — Tue, 09 Nov 2010 21:16:08 +0000

When you configure how numbers display, you select the number of decimal places to show. Crystal Reports then rounds the display value to that decimal position and that is what you see.

But what if you want to display the actual number? If the original number has no decimal places, you want to see that. If the original has three decimal places, you want to see all three.

You can do this using conditional formatting.

Right click on the number field and select Format Field from the pop up menu. You see the Format Editor dialog.
On the Number tab, click the Customize button. You see the Custom Style dialog.
On the Number tab, click the Conditional Format button next to the Rounding option. Enter the following formula:

If CurrentFieldValue = Int(CurrentFieldValue)

Then 0

Else

If

CurrentFieldValue * 10 = Int(CurrentFieldValue * 10)

Then 1

Else

If

CurrentFieldValue * 100 = Int(CurrentFieldValue * 100)

Then 2

Else

If

CurrentFieldValue * 1000 = Int(CurrentFieldValue * 1000)

Then 3

Else

If

CurrentFieldValue * 10000 = Int(CurrentFieldValue * 10000)

Then 4

Else

DefaultAttribute
Click on the Conditional Format button next to the Decimal option and paste the same formula.
Click on OK twice to return to your report and view your formatting changes:

This will show the correct values up to four decimal places. If you need to accommodate more decimal places, you can expand the formula by adding more IF statements.

Rounding in Crystal Reports: Rounding to Unusual Amounts

admin — Thu, 28 Oct 2010 16:15:28 +0000

You can quickly round to the nearest decimal place, but what if you need to round to an unusual point? For example, the nearest eighth mile? Or the nearest quarter hour? These activities are often done when calculating time sheets or mileage calculations. Fortunately, it’s relatively easy to modify the Round function to handle these odd fractions:

Determine the decimal equivalent of your fraction. (1/8 is equal to .125, 1/4 is equal to .25, and so on.)
Modify your Round function as follows: Round(ValueToRound/Decimal,0)*Decimal

For example, to round to the nearest eighth mile you would enter Round({mileage}/.25,0)*.25
Use this function in your calculations.

Rounding in Crystal Reports: Display vs. Function

admin — Mon, 25 Oct 2010 16:11:26 +0000

By default, Crystal Reports rounds all numbers displayed in your reports, according to the global settings. You can override these settings in a specific field by following these steps:

Right-click on the number field.
Select Format Field from the pop up menu. You see the Format Editor Dialog box.
Go to the Number tab and click the Customize button.
You see the Custom Style Dialog.
Set the rounding style you want in the Rounding area. (1 indicates that you will round to the ones place. 1.0 will round to the tenths place, and so on.)

You can also set these options for all new number fields by launching Crystal Reports with no reports open, selecting File|Options, and then following the steps above.

Display Rounding vs. True Rounding

The rounding you see as the result of setting your display options, however, only modifies what is displayed; it doesn’t change the underlying values. This means that when you add (or perform any other calculations) on these values, the results may not be what you expect based on what you see. Here is an example to show you what this means:

	Actual Value	Display Rounding	Rounding Function
	1.10	1	1.00
	2.20	2	2.00
	4.60	5	5.00
	1.40	1	1.00
	1.50	2	2.00
	1.60	2	2.00
	1.70	2	2.00
	1.80	2	2.00
Total:	15.90	16	17.00

In the first column is the actual value. The second column contains the actual value, but configured to display with no decimal spaces in Crystal Reports. The final column shows the same value actually rounded to the ones spot.

As you can see from the totals row, the actual total is 15.9. This rounds in the display option to 16. However, if each value is rounded individually and then those rounded values are summed, you get 17.

MonthName Function

admin — Mon, 18 Oct 2010 20:15:33 +0000

Uses

The MonthName function can be used to display the name of the month, when you provide a number between 1 and 12 (1 being January). It is useful for showing the month name in Group titles or labeling groups in charts.

It can be combined with the DatePart function to return the month name of a variable or calculation.

MonthName

Syntax	Variable	Description
MonthName(month, abbr)	Month	A number from 1 to 12.
	abbr	Optional. A Boolean value. If true, the month name is abbreviated. The default is false.

Examples

Example	Result
MonthName(5)	“May”
MonthName(10)	“October”
MonthName(10,True)	“Oct”
MonthName(DatePart(“m”, CurrentDate))	“October” when the current date is 10/5/10.

Other ways of showing the Month Name

You can also use the ToText field to display the name of the month.

Examples (where CurrentDate=10/5/10)

Example	Result
ToText(CurrentDate,’MMM’)	“Oct”
ToText(CurrentDate,’MMM’)	“October”
ToText(CurrentDate,’MMMM yyyy’)	“October 2010″
ToText(CurrentDate,’MMMM, yyyy’)	“October, 2010″

DayOfWeek

admin — Thu, 07 Oct 2010 21:08:38 +0000

Uses

The DayOfWeek function can be used to determine what day of the week a given date falls on, returned as a 1-7. By default, Sunday is assumed to be the first day of the week (1).

DayOfWeek

Syntax	Variable	Description
DayOfWeek(date,firstday)	Date	The date you want to find the day of week for.
	Firstday	Optional. The day of the week to consider the first day of the week.

Examples

Example		Result
DayOfWeek(Date(2010,10,4))		2 (10/4/2010 falls on a Monday)
DayOfWeek(Date(2010,10,4),crMonday)		1 (formula says to consider Monday first day of week)

To return the name of the day, you can use WeekDayName along with DayOfWeek:

Example	Result
WeekdayName(DayOfWeek(Date(2010,10,4)))	Monday

DateAdd Function

admin — Mon, 04 Oct 2010 21:07:02 +0000

Uses

The DateAdd function can be used to add an interval of time to a date. For example, you can quickly calculate a date one week from today. It’s very useful for calculating deadlines.

The resulting value will always be correct; it takes into account the number of days in each month, including during leap year.

DateAdd

Syntax	Variable	Description
DateAdd (intervalType, number, startDate)	intervalType	The interval type to use (see the table of legal interval types below
	number	The number of intervals to add. Can be positive or negative.
	startDate	The DateTime value to which you want to add the intervals.

Interval Type Values

Interval Type	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year (same result as w or d.)
d	Day (same result as w or y.)
w	Weekday (same result as y or d.)
ww	Weeks Adds the indicated number of weeks to startDate.
h	Hours
n	Minutes
s	Seconds

Examples

Example	Result
DateAdd(“d”, -32, #Sept 28, 1999#)	8/27/1999 12:00:00AM
DateAdd(“m”,1,#1/31/1996#)	12/29/1996 12:00:00AM
DateAdd(“q”,17,#Sept 28,1999#)	12/28/2003 12:00:00AM
DateAdd(“h”,-24,#Oct 14, 2010#)	10/13/2010 12:00:00AM

DateAdd returns a DateTime value. If you want to convert to a Date value, use Cdate. For example:

Cdate(DateAdd(“h”,-24,#Oct 14, 2010#)) will return 10/13/2010.

DateDiff Function

admin — Thu, 30 Sep 2010 20:41:30 +0000

Uses

The DateDiff function is used to determine the amount of time between two datetimes. You control the interval used in measuring the different (years, hours, etc.)

Show the days between two datetimes.
Show hours a ticket has been open.

ToText

Syntax	Variable	Optional
DateDiff (Intervaltype, startDateTime, endDateTime)	IntervalType	The interval type to use. This is a string value that must use the defined forms (see table below.)
	y	The number of decimal places to include in result (optional). The value will be rounded to that decimal place.
	z	The character to use as the thousands separator. If you don’t specify one, it will use your application default. (Optional.)
	w	The character to use as the decimal separator. If you don’t specify one, it will use your application default. (Optional.)

ToText with optional day of week constraint

Syntax	Variable	Optional
DateDiff (Intervaltype, Date1, Date2, firstDay)	IntervalType	The interval type to use. This is a string value that must use the defined forms (see table below.)
	y	The number of decimal places to include in result (optional). The value will be rounded to that decimal place.
	z	The character to use as the thousands separator. If you don’t specify one, it will use your application default. (Optional.)
	w	The character to use as the decimal separator. If you don’t specify one, it will use your application default. (Optional.)
	firstDay	The first day of week to use. The default is Sunday (crSunday.)

Interval Type Values

Interval Type	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weeks If Date1 is on a Tuesday, counts the number of Tuesdays between Date1 and Date2 not including the initial Tuesday (Date1). (It will count the last Tuesday if Date2 falls on a Tuesday.)
ww	Number of firstDays between dates Almost identical to w, except it counts the day you specify rather than the day Date1 falls on.
h	Hours
n	Minutes
s	Seconds

Examples

Example	Result	Notes
DateDiff(“d”, #3/10/2010#, #3/12/2010#)	2
DateDiff(“yyyy”,#1/1/2010#,#12/31/2010#)	0	yyyy only looks at the years; even though these dates are nearly a year apart, the result is 0.
DateDiff(“yyyy”,#12/31/2009#,#1/1/2010#)	1	yyyy only looks at the years; even though these dates are one day apart, the result is 1.
DateDiff(“q”,#1/1/2010#,#2/1/2010#)	0	The dates are in the same quarter.
DateDiff(“q”,#3/1/2010#,#4/1/2010#)	1	The dates are in different quarters.
DateDiff(“m”,#3/1/2010#,#3/31/2010#)	0	m only looks at the month; even though these dates are 30 days apart the result is 0.
DateDiff(“m”,#3/31/2010#,#4/1/2010#)	1	m only looks at the month; even though these dates are 1 day apart the result is 1.
DateDiff(“w”,#3/31/2010#,#5/1/2010#)	4	3/31/10 is a Wednesday. DateDiff counts the number of Wednesdays between the two dates, not counting 3/31/10.
DateDiff(“ww”,#3/31/2010#,#5/1/2010#,crThursday)	5	3/31/10 is a Wednesday. DateDiff counts the number of Thursdays between the two dates, because 3/31/10 is not a Thursday, it counts the Thursday in that week, resulting in a higher number than the previous example.

ToText Function

admin — Tue, 28 Sep 2010 20:39:25 +0000

Uses

The ToText function can be used to convert number, date, boolean, or time values to a string (text). It provides you with controls that let you control how the resulting string looks.

It’s mostly used for concatenation (for example, you can create a title for your report that shows the records covered, or a group name that shows the date range included.)

Add parameters to a Report title
Control how group names appear (you can force your dates to appear in the format 3/7/10 or 20100901.

ToText for Boolean Values

Syntax

ToText(x)

Examples

Example

Result

ToText({Completed})

True when the field Completed is true.

False when the field Completed is false.

ToText for Numbers (and Currency)

Syntax	Variable	Optional
ToText(x, y, z, w)	x	The number to convert to text
	y	The number of decimal places to include in result (optional). The value will be rounded to that decimal place.
	z	The character to use as the thousands separator. If you don’t specify one, it will use your application default. (Optional.)
	w	The character to use as the decimal separator. If you don’t specify one, it will use your application default. (Optional.)

Examples

Example	Result
ToText(12345.678)	“12345.678″
ToText(12345.678,2)	“12345.67″
ToText(12345.678,0)	“12345″
ToText(12345.678, 0, ‘,’, ‘.’)	“12,345.67″
ToText(12345.678,0,”")	“12345″
“Time Logged for Change # ” & ToText(12345.567,0,”")	“Time Logged for Change # 12345″

ToText for Dates

Syntax	Variable	Optional
ToText(x, y)	x	The date value to be converted.
	y	A text string that defines how the value is to be formatted. (Optional)

Examples (where datefield = January 15, 2011)

Example	Result
ToText({datefield}, “yyyyMM”)	“201101″
ToText({datefield},”dd-MMM-yyyy”)	“15-Jan-2011″
ToText({datefield},”dd MMM yyyy”)	“15 Jan 2011″
ToText({datefield},”MM/dd/yy”)	“01/15/11″
ToText({datefield},”dddd, MMM d, yyyy”)	“Saturday, Jan 1, 2011″

This looks a little confusing at first. Sometimes d’s return the day of the week and sometimes they return a number. This is because the format is determined by the specific pattern:

Pattern	Result
d	Numeric day of month without leading zero (1, 7, 31)
dd	Numeric day of month with leading zero (01, 07, 31)
ddd	Three day abbreviation of day of week (Mon, Sat)
dddd	Full name of day of week (Monday, Saturday)
M	Numeric month without leading zero (1, 7, 12)
MM	Numeric month with leading zero (01, 07, 12)
MMM	Three letter abbreviation of month (Jan, Feb, Mar)
MMMM	Full name of month (January, February, March)
yy	Last two digits of year (11, 14, 22)
yyyy	Full four digits of year (2011, 2014, 2022)

ToText for Times

Syntax	Variable	Optional
ToText(x, y, z, w)	x	The time value to be converted.
	y	A text string that defines how the value is to be formatted. (Optional)
	z	A text string defining the AM string. (Optional)
	w	A text string defining the PM string. (Optional)

Examples (where timefield =2:43:23 p.m.)

Example	Result
ToText({timefield}, “HH:mm:ss”)	“14:43:23″
ToText({timefield}, “hh:mm:ss tt”)	“02:43:23 PM”
*ToText({timefield},”hmmss tt”,’am’, ‘pm’)*	*“24323 pm”*

Here are the patterns used by ToText when converting time:

Pattern	Result
h	Hours without leading zeros in 12 hour format (1, 7, 12)
hh	Hours with leading zeros in 12 hour format (01, 07, 12)
H	Hours without leading zeros in 24 hour format (1, 12, 17, 24)
HH	Hours with leading zeros in 24 hour format (01, 12, 17, 24)
m	Minutes without leading zeros (5, 15, 55)
mm	Minutes with leading zeros (05, 15, 55)
s	Seconds without leading zeros (5, 15, 55)
ss	Seconds with leading zeros (05, 15, 55)
t, tt	Includes a single or multi-character AM/PM string.

If you want to include any of the pattern characters in your resulting string, you can. You just need to enclose them in quotes.

Example	Result
ToText({timefield}, “hh ‘h’ mm ‘min’ ss ‘sec’”)	“02 h 43 min 23 sec”

Picture Function

admin — Mon, 20 Sep 2010 14:40:11 +0000

The Picture function lets you provide a template to use when formatting a string. For example, you can force a phone number to appear in the format (906) 555-1212, or a Social Security number to appear in the format 326-75-3847. Use it in conjunction with the ToText function, and you can also control how a number is displayed.

Syntax	Example	Result
Picture(x, “y”)	Picture({ssn},”xxx-xx-xxxx”	“326-75-3847″ where ssn=”326753847″

Numbers (and Currency)

Syntax	Variable	Optional
Picture(x, “y”)	x	The string to format
	“y”	The template to use when formatting (must be enclosed in quotes)

Examples

Example	Result
Picture(CA,”[xx]“)	“[CA]“
Picture({phone},”(xxx) xxx-xxxx”	“(906) 555-1212″
Picture({city},”xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”)	“Calumet” result is cropped to max number of x’s you have included; so make sure to use enough to cover longest result. The max is 252 characters.

Note: You cannot use an asterisk as a wildcard in the picture argument. If you put an asterisk in the argument, it will show up as an asterisk in the resulting string.