CRYSTAL REPORTS

How to shade every other Report Line?

Shading lines in alternate colors or shades facilitate ease in reading data across/down the page. This is especially so when there is a rather substantial amount of text information. The MOD function can be used to achieve such an effect.

In the Design tab of Crystal Report, the following steps are suggested:

  1. Right-click on the Details section name.
  2. Choose Format Section or “Section Expert” for later versions.
  3. Select the “Details” section tab and click on color tab.
  4. Click the Conditional Formatting button.
  5. Enter the following formula:
    • If RecordNumber Mod 2 = 0 Then crSilver Else crNoColor

Date Functions

DateValue(n1,n2,n3)

  • n1 – numeric value indicating the year.
  • n2 – numeric value indicating the month.
  • n3 – numeric value indicating the day of month.

DateValue (2010, 10, 15) means 15 October 2010.

CurrentDate

CurrentDate returns just that – the current date (system current date).

DateAdd (s, n, dt)

  • s – String value indicating interval type (days, weeks etc.)
  • n – numeric value indicating number of intervals to add/minus to/from the source date. This value could be positive or negative.
  • dt – Source date. This is a date or date-time value.

DateAdd (“d”, 5, date(2010, 10, 01)) returns 6 October 2010. This has added 5 days to the source date which in this example is 1 October 2010.

 

The Crystal Report syntax for DATESERIAL:

DATESERIAL(year({datefield}), month({datefield}), {day number})

This crystal report is entered as a formula and then used as a field in the report itself. It offers flexibility in manipulating dates for reporting purposes. Within the arena of this syntax, you can explore various inputs within the formula itself.

Taking a simple example, Crystal report designers know that there are several inherent built-in syntax / formulas that can be used on its own. Combine this built-ins with other formula can produce quite powerful effects on the output. This is when you might have to automatically force a date entry based on the date of a record etc. For example, a scenario might be that all invoices issued on any day of the month will have a due date on the last day of the following month.

dateserial(year({@Date}),month({@Date})+1,1-1)

Crystal Report Date Syntax

This formula returns the day of the week:

DayOfWeek(CurrentDate)

returns the value of 5 if the current date is a Thursday. It assumes Sunday is equal to 1. The first day of the week can also be defined in the formula as follows:

DayOfWeek(CurrentDate, crMonday)

In the above example, the first day of the week has being defined as Monday. Therefore if the current date is Thursday, it will return a value of 4.

Date Intervals

The following interval types can be used with date functions like DATEADD and DATEDIFF:

“m” – Month
“d” – Day (equivalent to “y”)
“y” – Day of Year (equivalent to “d”)
“w” – Weekday or Number of Weeks
“ww” – Week or Number of FirstDayOfWeeks
“q” – Quarter
“h” – Hour
“md” – Days excluding Years and Months

Leave a Reply


*