Basic vs Crystal Syntax

(1)

Crystal Reports

Comparison of Crystal and Basic Formula Syntax

Overview

Crystal Reports version 8 and higher offers two different syntaxes for writing formulas: the Crystal syntax and the Basic syntax.

This paper describes some of the differences between the two syntaxes and provides general information on the control structures that are available for the syntaxes.

Introduction

Most formulas in Crystal Reports can be written in either Crystal or Basic syntax. However, Record Selection and Group Selection formulas must be written in Crystal syntax.

Crystal syntax is similar to formula writing in C++ while Basic syntax is similar to formula writing in Microsoft Visual Basic.

Formula Comments

Formula comments are notes included with a formula to explain its design and operation. Comments do not print and they do not affect the formula; they appear only in the Formula Workshop. Use comments to explain the purpose of a formula or explain the steps involved in writing it.

Crystal syntax uses two forward slashes (//) to denote a comment. Comments can begin on a new line or on the same line as the rest of the code.

Comments begin with Rem or an apostrophe (‘). A comment beginning with

Rem is a separate statement and must begin on a new line or must be separated

from the rest of the line by a colon.

A comment beginning with an apostrophe can begin on the same line as the rest of the code without needing to be separated by a colon.

Crystal syntax

// This is a comment in Crystal syntax

"Hello World"

<OR>

"Hello World" // This is a comment in Crystal syntax

Basic syntax

Rem This is a comment in Basic syntax

formula = "Hello World" : Rem This is a comment in Basic

_ syntax

<OR>

' This is a comment in Basic syntax

(4)

The Result of a Formula

There are two main differences between Crystal and Basic syntax when returning the result of a formula:

• Basic syntax uses a special variable called formula whereas Crystal syntax does not.

• Basic syntax returns the value assigned to the formula variable whereas Crystal syntax returns the last line within the formula.

Formula Variable

Basic syntax requires a special variable called formula to return the value of the formula whereas Crystal syntax does not.

Crystal syntax

// This formula will output "Hello World"

"Hello World"

Basic syntax

' This formula will output "Hello World"

Formula = "Hello World"

Output of the Formula

Crystal syntax returns the last line within the formula while Basic syntax returns the value after the special variable called “formula”. This means that in Basic syntax there can be other statements below the variable but the formula will not output these values.

Crystal syntax

// This formula will output the number 5

Numbervar x; "Hello World"; x :=5

Basic syntax

' This formula will output "Hello World"

Dim x as number

Formula = “Hello World” x = 5

(5)

Assignment Operator

The assignment operator is the colon equal sign (:=) in Crystal syntax as opposed to the equal sign (=) in Basic syntax.

Crystal syntax Numbervar x;

x := 5; // Notice the colon equal sign versus the equal

// sign

x;

Basic syntax dim x as number

x = 5 ' Notice the equal sign versus the colon equal sign

formula = x

Statements

All formulas exist as a sequence of statements. For a formula to evaluate correctly, these statements must be separated and continued correctly.

Separating Statements

In Crystal syntax a semicolon must separate each statement (;). In Basic syntax, either a new line or a colon must separate statements (:).

Crystal syntax

// A semicolon separates the statements

Numbervar ID;

if {Customer.Last Year's Sales} > 10000 then ID := {Customer.Customer ID};

Basic syntax

' A line break separates the statements

dim ID as number

if {Customer.Last Year's Sales} > 10000 then ID = {Customer.Customer ID}

formula = ID

<OR>

' A colon separates the statements

dim ID as number : if {Customer.Last Year's Sales} > 10000 then ID = {Customer.Customer ID} : formula = ID

(6)

Continuing Statements

In Crystal syntax you can continue a statement onto the next line by simply starting the text on the next line. In Basic syntax you must use the line continuation character, a space followed by an underscore ( _).

Crystal syntax

// Statement is continued by using a line break

if {Customer.Last Year's Sales} > 10000 and {Customer.Country} = "USA"

then "Good sales"

Basic syntax

' Statement is continued using the underscore

if {Customer.Last Year's Sales} > 10000 _ and {Customer.Country} = "USA" then formula = "Good sales"

end if

Using Date, Time, or DateTime values

When using date, time, or date time values, the Date, Time, and DateTime functions cannot be used in a formula written in Basic syntax. This is because these are all data type names in Basic syntax.

Crystal syntax

Date(2000,01,01); // Crystal syntax only

Time(13,30,50); // Crystal syntax only

DateTime(2000,01,01,13,30,50); // Crystal syntax only

CDate(2000,01,01); CTime(13,30,50); CDateTime(2000,01,01,13,30,50); DateValue(2000,01,01); TimeValue(13,30,50); DateTimeValue(2000,01,01,13,30,50);

(7)

Basic syntax Formula = CDate(2000,01,01) Formula = CTime(13,30,50) Formula = CDateTime(2000,01,01,13,30,50) Formula = DateValue(2000,01,01) Formula = TimeValue(13,30,50) Formula = DateTimeValue(2000,01,01,13,30,50)

Extracting Elements from a String

You can extract individual elements from a string by specifying the character position or a range of character positions.

In Crystal syntax this is done using square brackets [ ] while Basic syntax uses parentheses ( ). Negative values specify a position starting from the end of the string.

Crystal syntax

"HELLO" [5] // Returns "O"

"HELLO" [-3] // Returns "L"

"HELLO" [3 to 4] // Returns "LL"

"HELLO" [-3 to 2] // Returns "EL"

Basic syntax

formula = "HELLO" (5) ' Returns "O"

formula = "HELLO" (-3) ' Returns "L"

formula = "HELLO" (3 to 4) ' Returns "LL"

formula = "HELLO" (-3 to 2) ' Returns "EL"

Arrays

When using arrays, the main differences between the two syntax types are:

• Declaring an array

• Referencing elements within an array • Assigning values to an array

In both syntaxes the result of a formula cannot be an array. The formulas shown in the “Declaring an array” and “Assigning values to an array” sections require additional syntax to ensure the result is a single value and not an array, otherwise the formula will return an error.

(8)

Declaring an Array

Crystal syntax uses the Array function to declare an array while Basic syntax uses parenthesis instead.

Crystal syntax

StringVar Array MyArray; // Notice the Array function

Basic syntax

Dim MyArray() as String ' Notice the parenthesis

Referencing Elements within an Array

Crystal syntax uses square brackets [ ] to reference an element within an array whereas Basic syntax uses parenthesis ( )

Crystal syntax

MyArray[3] // Notice the square bracket

Basic syntax

formula = MyArray(3) ' Notice the parenthesis

Assigning Values to an Array

Crystal syntax does not require use of the Array function when assigning values to an array but Basic syntax does use the Array function.

Crystal syntax

MyArray := ["a", "b", "c"] // Notice no Array function

Basic syntax

MyArrray = Array("a", "b", "c") ' Notice the Array function

Variables

A variable represents a specific data item or value, and acts as a placeholder for that value. When a formula encounters a variable, the formula searches for the value of the variable and uses it in the formula. Unlike a constant value, which is fixed and unchanging, a variable can be repeatedly assigned different values.

When you assign a value to a variable, the variable maintains the value until you later assign a new value. Because of this flexibility, it is necessary for you to declare variables before you use them so that Crystal Reports is aware of them and understands how you intend to use them. There are three elements involved when declaring variables:

(9)

• Scope • Type • Name

Variable Scopes

There are three types of variable scopes:

• Local • Global • Shared

You can use these three types of variable scopes for both Crystal and Basic syntax. However, Crystal syntax does not contain the Dim local scope whereas Basic syntax does.

If no scope is specified, the variable will default to a global scope regardless of the formula syntax being used.

• Dim or Local – Dim (Basic syntax only) or Local variables are only recognized within the formula in which they are declared.

• Global – Global variables use the same memory block to store a value for access throughout a single report object, whether that is the main report or a subreport.

• Shared – Shared variables are recognized in all report objects within a single Crystal Reports file. Shared variables are frequently used to pass data from subreports to the main report.

Crystal syntax

// Variable scope is declared first, then variable type, // then variable name

Local CurrencyVar C; Global NumberVar N; Shared StringVar S;

Basic syntax

' Variable scope is declared first, then variable name, _ then variable type

Dim B as Boolean Local C as Currency Global N as Number Shared S as String

(10)

Variable Types

Variable types can be declared in multiple ways:

Seven simple types

Number Boolean

Currency Date

String Time

DateTime

Six range types

Number Range DateTime Range

Currency Range Date Range

String Range Time Range

Crystal syntax requires that the variable type be specified when declaring the variable. In Basic syntax if the type is not declared then it will be set when the variable is first used.

Crystal syntax

// Note that the type is declared before the variable is // used

Global StringVar s; s := “Hello”;

s;

Basic syntax

' Note that the type is not declared until the variable is

_ used

Global S S = “Hello” formula = S

(11)

Variable Names

When you declare a variable, you also specify its name. A variable cannot have the same name as any function, operator or other keyword that is valid for the syntax used in the formula.

In the Formula Workshop the names of the built-in functions, operators and other keywords are displayed in a different color so it is easy to see if the variable name conflicts.

Declaring Multiple Variables in One Statement

In Crystal syntax you must declare variables in separate statements. In Basic syntax you can declare more than one variable in a single statement.

Crystal syntax

// Note that X, Y, and Z are declared in separate // statements Local NumberVar X; Local StringVar Y; Local BooleanVar Z; X := 5; Y := "hello"; Z := True; X; Basic syntax

' Note that X, Y, and Z are declared in a single statement

Local X, Y, Z X = 5

Y = "hello" Z = True formula = X

If-Then-Else Control Structures

In an IF statement, there are three main differences between Crystal and Basic syntax:

• Else If versus ElseIf • Single-Line IF statements • Multi-Line IF statements

(12)

Else If versus ElseIf

In Crystal syntax this command is two words; in Basic syntax it is one word.

Crystal syntax

If {Customer.Last Year's Sales} < 100 Then "Under $100"

Else If {Customer.Last Year's Sales} in 100 to 200 Then "Between $100 and $200"

Basic syntax

If {Customer.Last Year's Sales} < 100 Then formula = "Under $100"

ElseIf {Customer.Last Year's Sales} in 100 to 200 Then formula = "Between $100 and $200"

End If

Single-Line IF Statements

In Basic syntax, a single-line IF statement does not start a new line after the first THEN statement.

Line continuation characters such as the underscore (_) can be used to stretch a single-line IF statement over multiple lines. Starting a new line after the first THEN makes an IF statement into a multi-line IF statement.

Crystal syntax

// One line

StringVar x

If {Customer.Customer ID} = 1 then x := "One"; x;

<OR>

// Two lines without line continuation character

StringVar x;

If {Customer.Customer ID} = 1 then x := "One";

(13)

Basic syntax

' One line

Dim x as string

If {Customer.Customer ID} = 1 then x = "One" Formula = x

<OR>

' Two lines with underscore line continuation character

Dim x as string

If {Customer.Customer ID} = 1 then _ x = "One"

Formula = x

Multi-Line IF Statements

More than one statement can be executed as the result of a condition being met. However, in Crystal syntax the two results are enclosed within parentheses whereas Basic syntax encloses the results within the condition using the ElseIf, Else, or End If statements.

Crystal syntax NumberVar x; NumberVar y;

If {Customer.Customer ID} = 1 then (

x := 1; y := 2

); // Notice the parentheses as opposed to End If

x;

Basic syntax shared x as number

If {Customer.Customer ID} = 1 then x = {Customer.Customer ID} formula = "One"

(14)

Select or Case Statements

The Select or Case statement is similar to an If-Then-Else statement. However, you can write formulas that are clearer and less repetitive using a Select statement. The major differences between Crystal and Basic syntax are:

• Crystal syntax uses Select while Basic syntax Select Case

• Crystal syntax uses a colon to separate the result from the output whereas Basic syntax uses a line break

• Crystal syntax uses Default and Basic syntax uses Case Else

• Crystal syntax uses two semi colons to end the statement but Basic syntax uses End Select

Crystal syntax

Select {Customer.Customer ID} // Notice the Select versus

// Select Case

Case 1 : "One" // Notice the colon versus line break

Case 2 : "Two"

Default : "Other" // Notice Default versus Case Else

;; // Notice the two semicolons versus the End Select

Basic syntax

Select Case {Customer.Customer ID} ' Notice the Select Case

_ versus Select

Case 1 ' Notice the line break versus colon

formula = "One" Case 2

formula = "Two"

Case Else ' Notice the Case Else versus Default

formula = "Other"

End Select ' Notice the End Select versus two semicolons

Looping

Loops enable you to evaluate a sequence of statements multiple times.

Any one evaluation of a formula can have at most 29,999 loop condition evaluations. Therefore, if a loop structure performs a loop 30,000 or more times, the following error message appears:

“A loop was evaluated more than the maximum number of times allowed”.

(15)

For/Next Loops

For loops require a For Counter variable to count the number of iterations through the loop. If you add a Step statement to the For loop you can control the increment that the For Counter uses. It can be a negative step number if you wish to have the loop go from a large number to a small number.

For loops will support an Exit statement. The main differences between Crystal and Basic syntax are:

• Crystal syntax uses the Do statement to begin the contents of the For loop whereas Basic syntax does not.

• Crystal syntax uses parenthesis around the contents of the For loop while Basic syntax encloses the contents of the For loop by ending it with the Next <Counter variable> statement.

Crystal syntax

// The following formula will return “abc”

Numbervar x;

StringVar Array MyArray := ["a","b","c"]; StringVar Concatenate;

For x := 1 To count(MyArray) Do // Notice the Do statement

(

Concatenate := Concatenate + MyArray[x]

); // Notice the parenthesis versus the Next statement

Basic syntax

' The following formula will return “abc”

dim x as number

dim MyArray() as string MyArray = Array("a","b","c") dim Concatenate as string

For x = 1 To count(MyArray) Step 1 ' Notice there is no Do

_ statement

Concatenate = Concatenate + MyArray(x)

Next x ' Notice the Next statement

(16)

Exiting a For/Next loop

You can exit from a For loop by using the Exit For statement.

The following example searches the Global array names for the name "Fred". If it finds the name, it returns the index of the name in the array and exits out of the For loop. Even though the For Counter variable is set to loop four times, the formula only loops three times due to the Exit For statement.

Crystal syntax

stringvar array names;

names := ["Frank", "Helen", "Fred", "Linda"]; numbervar i;

numbervar output;

For i := 1 to 4 Do

( If names [i] = "Fred" Then ( output := i;

Exit For // Exit the loop once Fred is found

); ); output Basic syntax

Global names () As String

names = Array("Frank", "Helen", "Fred", "Linda") Dim i

For i = 1 to 4

If names (i) = "Fred" Then formula = i

Exit For ' Exit the loop once Fred is found

End If Next I

Do Loops

The Do loop that is supported in Crystal syntax is:

(17)

There are 4 different types of Do Loops supported in Basic syntax: • Do While … Loop • Do Until … Loop • Do … Loop While • Do … Loop Until Do … While

The Do ... While statement evaluates the expression once no matter what. It then evaluates the condition, and if the condition is true, evaluates the expression again. This process continues until the condition is false. This is the only Do loop supported in Crystal syntax; it is not supported in Basic syntax.

Crystal syntax only

// The following formula will return the number 1. The loop // will evaluate once.

numbervar x; Do

x:=x+1 While x>5; x

Do While … Loop and Do Until … Loop

The main difference between these two loops is that the Do While loop evaluates a condition and does actions while the condition is true, whereas the Do Until loop evaluates a condition and does actions until the condition is true. Both loops evaluate the condition first and then do the action. All Do loops support an Exit Do statement.

Basic syntax only

' The following formula will return the number 0. The loop _ will not evaluate.

dim x as number Do While x = 2 x = x + 1 Loop

(18)

' The following formula will return the number 6. The loop _ will evaluate three times.

Dim x as number Do Until x > 5 x = x + 2 Loop formula = x

Do … Loop While and Do … Loop Until

The main difference between these two loops is that the Do Loop While evaluates the loop while a condition is true, whereas the Do Loop Until evaluates until the condition is true. Both loops do the action first and then evaluate the condition to see if they should loop again. All Do loops support an Exit Do statement

Basic syntax only

' The following formula will return the number 1. The loop _ will evaluate once.

dim x as number Do

x = x + 1 Loop While x = 2 formula = x

' The following formula will return the number 6. The loop _ will evaluate six times.

dim x as number Do x = x + 1 Loop Until x > 5 formula = x

While Loops

While loops are similar to Do While Loops except that they generally do not support Exit statements. While loops are supported in both Basic and Crystal syntax . The main differences between Basic syntax and Crystal syntax are:

• Basic syntax ends a While loop with Wend whereas Crystal syntax encloses the contents of the While loop with parenthesis

(19)

• Crystal syntax requires While…Do whereas Basic syntax only requires While

• Crystal syntax supports the Exit While statement whereas Basic syntax does not

Crystal syntax

// The following formula will return the value of 1 // and will not evaluate the loop due to the exit while // statement

numbervar x;

While x < 3 Do// Notice the While Do statement versus While

(

x := x + 1;

if x = 1 then exit while; // Notice the Exit While

// statement

); // Notice the parenthesis versus the Wend statement

x

Basic syntax

' The following formula will return the number 1 and _ evaluate the loop once

dim x as number

While x = 0 ' Notice the While statement versus While Do

x = x + 1

Wend ' Notice the Wend statement versus parenthesis

Formula = x

www.businessobjects.com

The Business Objects product and technology are protected by US patent numbers 5,555,403; 6,247,008; 6,578,027; 6,490,593; and 6,289,352. The Business Objects logo, the Business Objects tagline, BusinessObjects, BusinessObjects Broadcast Agent, BusinessQuery, Crystal Analysis, Crystal Analysis Holos, Crystal Applications, Crystal Enterprise, Crystal Info, Crystal Reports, Rapid Mart, and WebIntelligence are trademarks or registered trademarks of Business Objects SA in the United States and/or other countries. Various product and service names referenced herein may be trademarks of Business Objects SA. All other company, product, or brand names mentioned herein, may be the trademarks of their respective owners. Specifications subject to change without notice. Not responsible for errors or omissions. Copyright © 2004 Business Objects SA. All rights reserved.

Basic vs Crystal Syntax

Crystal Reports

Comparison of Crystal and Basic Formula Syntax

Overview

Contents

Introduction

Formula Comments

The Result of a Formula

Formula Variable

Output of the Formula

Assignment Operator

Statements

Separating Statements

Continuing Statements

Using Date, Time, or DateTime values

Extracting Elements from a String

Arrays

Declaring an Array

Referencing Elements within an Array

Assigning Values to an Array

Variables

Variable Scopes

Variable Types

Variable Names

Declaring Multiple Variables in One Statement

If-Then-Else Control Structures

Else If versus ElseIf

Single-Line IF Statements

Multi-Line IF Statements

Select or Case Statements

Looping

For/Next Loops

Exiting a For/Next loop

Do Loops

While Loops

www.businessobjects.com