لطفا منتظر باشید ...
Creating Formulas with Basic Syntax
As mentioned above, Crystal Reports has two different types of formula syntax available for use. Crystal syntax was originally the only syntax available for use in formulas, but Visual Basic developers complained bitterly about having to learn yet another language - particularly one that seemed to be part Pascal and part Basic.
What is Basic Syntax?
With the introduction of Crystal Reports 7.0 came Basic syntax. This closely resembled Visual Basic code, using similar functions and operators, but with the ability to access all of the Crystal-specific functions and features. Over time, the two syntaxes have grown closer together, with the Crystal version having gone farther to reach its Basic cousin.
Which syntax you choose depends upon your background and experience. If you''re a dyed-in-the-wool Crystal Reports developer, the chances are that you''ll be more familiar with Crystal syntax. If you''re a Visual Basic developer who has been pushed into report development as well, you''ll be more comfortable using Basic syntax.
Since the two versions of syntax have grown closer together, we''re going to concentrate our discussion here around the Basic version. A little later in the chapter, we''ll look at the differences between the two, which should allow you to use the syntax of your choice.
Basic Syntax Coding Conventions
The structure used by Basic syntax in Crystal Reports closely resembles the structure used in Visual Basic, but there are a few slight differences. Open operators.rpt from the sample code, right-click on Formula Fields in the Field Explorer, and select New. Enter LearnSyntax in the dialog as the name of the formula:
To start with, field names are enclosed in braces, and use the naming convention of {tablename.fieldname}, so enter the following formula that would calculate extended price in operators.rpt:
{Orders_Detail.Quantity) * (Orders_Detail.Unit Price)
Make sure that the crystal syntax is selected, and not basic syntax, and click on the check icon. The formula should be fine.
Other fields use a prefix to indicate the type of field you''re working with. Parameter fields, for example, are prefixed by a question mark, and formula fields are prefixed with the @ character. Change your formula to calculate the sales tax using the extended price from this report by entering the following:
{@Extended Price} * .10
Click on the check icon again, to confirm that the formula is correct, as we were writing this formula in Crystal syntax. Now you can drag and drop the formula field onto the Details section of a report, it would be evaluated once for each record, and the value would be displayed.
Basic syntax, however, is slightly different. For each formula you write in Basic syntax, you need to use a special Formula variable to tell Crystal Reports what to return. Using the same LearnSyntax example in the Formula Editor, change the syntax to Basic syntax using the drop-down menu, and click on the Check icon.
The correct code for our sales tax formula would be:
Formula = {Orders.OrderTotal) * {?SalesTax}
Enter this formula, and click on the Check icon to ensure this line of code is correct.
Even if you don''t need to output a value, you still have to use the Formula variable and assign some value to it (even if you just make it up).
If, for example, we create a global variable and insert a calculation to add up the number of orders as we go down the page, but we don''t actually want to print anything out until the end, we still have to set the Formula variable, to avoid getting a syntax error. Try the checker on the following code with and without the line that says Formula = 999.
Global TotalOrders as Number
TotalOrders = TotalOrders + {Orders_Detail.Quantity}
Formula = 999
As you''ve probably noticed, assignments are made using the equal operator. Just like other versions of the Basic language, you can add comments to your formulas with either the single quote, or the REM statement:
'' This formula calculates the Total Sales
Global TotalOrders as Number
TotalOrders = TotalOrders + {Orders_Detail.Quantity}
Formula = 999
REM The formula variable is required
If you want to use REM on the same line as some formula text, you need to add a colon before you begin your REM statement, as shown below:
Formula = 999 : REM The formula variable is required
If you''re using the apostrophe, you can just append it to the end of the line:
Formula = 999 '' The formula variable is required
Simple Operators
Now we need to look at a few of the simple operators that are available for use. Some of these are used in the sample reports that are included with the download files for this chapter. Many are self explanatory, and don''t need much guidance for use, so we won''t describe how to use every one. The easiest way to become familiar with the large number of operators is to actually use them, or play about with them in the Formula Editor, using the Check button to your ensure your syntax is correct.
Although the majority are the same, Basic syntax and Crystal syntax occasionally utilize different operators. These differences are explained in Appendix C, Crystal vs. Basic Syntax.
Arithmetic
Crystal Reports .NET supports all of the basic arithmetic operators (including addition, subtraction, multiplication, and division), but also has support for a number of others, as discussed in the table below:
Operator
Symbol
Description
Integer DivideDivision where only the integer is returned (for example, 9\2 would return a result of 4)
ModulusMod
For dividing two numbers and returning the remainder
Negate-( )
To negate or change the sign of a number
Exponentiate^
For exponents, used for calculating squares, for example, 3^2 would return 9
Open operators.rpt in the report designer, and create a new formula by right-clicking on Formula Fields. By using the negate operator, we could calculate a value representing the number of items returned to the company from an order:
-({Orders_Detail.Quantity})
The negate function is also useful when working with financial information, where a negative amount may indicate a credit.
Boolean
For formulas and logic that need to return a True or False value, we also have a number of Boolean operators available within Basic syntax.
Operator
Description
Not
Reverses the value - for instance, Not (True) is False.
And
Where all conditions are True, returns True. Where one condition does not meet the criteria, returns False.
Or
Returns True if one or the other condition is met, or both.
Xor
Returns True if one and not the other condition is met.
Eqv
Returns True if both values compared are True, or if both values compared are False. If the two values compared are different, it returns False.
Comparison
For comparing two values, Basic syntax supports the usual comparison operators, including:
Operator
Symbol
Equal to=
Not equal<>
Less than<
Greater than>
Less or equal<=
Greater or equal>=
Type Conversion
Within Crystal Reports, there are eight different data types available for use:
Boolean
Number
Currency
Date
Date-time
Time
String
BLOB (Binary Large Object)
BLOB fields can be inserted into a report, but they cannot be converted to any other field type. They are handy when you need to insert non-traditional records into your report. (The sample database, for example, has a graphic file inserted into the Employee table that, when placed on your report, will display the employee''s photo. This can be seen in the Employee_Listing.rpt report that''s included with the chapter''s sample files.)
When working with all of these different types of fields, we sometimes need to perform a conversion before we can use them in our formulas (for example, where a numeric value is stored as a string in the database). To convert field types, we have the following conversion functions:
Function
Use
CBool()
Returns True if the argument is positive or negative but not zero, and returns False if the argument is zero
CCur()
Converts Number, Currency, or String types to Currency
CDbl()
Converts Number, Currency, or String types to Number
CStr()
Converts Number, Currency, or Date types to String
CDate()
For converting to a true Date field
CTime()
For converting to a Time field
CDateTime()
For converting to a DateTime field
ToNumber()
For converting String and Boolean types to Number
ToText()
For converting Number, Currency, Date, Time, or Boolean to text
ToWords()
For spelling out numbers or currency values (for example, 101 is "One hundred and one")
In addition to these, there are also functions for converting DateTime strings - you''ll find them in the Formula Editor, under the heading Additional Functions. These functions will accept a DateTime string and return a Date field, a Time field, or a number of seconds.
So, to convert a number to a currency-format field, the formula would look something like this:
Formula = CCur({Orders.OrderAmount})
or, to use the ToWords () function to spell out the same currency amount:
Formula = ToWords(CCur({Orders.OrderAmount}))
which, for a value of 1001.50, would return the string One thousand and one and 50/100.
For date functions, you can pass the date in any number of formats, including the full date, days elapsed since 01/01/1900, date literals, and date components:
CDate("Sep. 05, 2002")
which returns a date value for September 5th, 2002,
CDate(#Jan. 01, 2005 12:02pm#)
which returns a date value for January 1st, 2005,
CDate(1960, 10, 10)
which returns a date value for October 10, 1960.
Summary Functions
When you insert a summary into your report, a corresponding summary function is used to create a specialized summary field. These summary fields can be used within your formulas just like any other field, and include sums, averages, and counts.
Summary fields are generally shown in one of two different ways. The first of these, where the summary function and a single field are shown, represents a grand total that may appear on your report (usually in the report footer). An example of this type would be:
Sum({Customer.Sales})
In addition to grand totals, summary fields can also be used at the group level. So in the same report that shows the summary above, you could also have a summary field that appears in the group header or footer. For example, if the group field were {Customer.Country}, the summary field would look like this:
Sum({Customer.Sales}, {Customer.Country})
For more information on inserting summary fields into your report, turn back to Chapter 2.
String Functions
The string functions within Crystal Reports are used to manipulate database and other fields that contain text. When working with strings, we can concatenate two strings together using either the plus operator (+), or the ampersand (&).
When working with two strings, we could use the plus operator, as shown:
Formula = "This is the Customer Name " + {Customer.Name}
To concatenate a string and another type of field with the plus operator, we would first have to do a type conversion to ensure that both of these fields were strings. Using the ampersand operator, you can concatenate strings with any other type of field, without performing a type conversion first:
Formula = "This is the Sales Amount " & {Customer.SalesTotal}
Although this method is easier, you may still need to perform a type conversion in order to have more control over how the field is converted (such as setting the number of decimal places when moving from a number to string).
In addition to concatenating strings, you can reference individual characters or sets of characters using the subscript operator, noted by square brackets ({fieldname} [n], where n is a position within the string).
To return the first letter of a customer''s first name, the formula would look like this:
Formula = {Customer.FirstName}[1]
You''ll notice here that Crystal treats strings as 1-based arrays (instead of 0-based). It also provides the ability to return a range of characters from the array - in this case, the first three letters:
Formula = {Customer.FirstName}[1 to 3]
In addition to concatenating and pulling strings apart using simple operators, we have a number of functions that can be used with string-type fields, including:
Function
Description
Len(string)
Finds the length of a string
Trim (string)
Trims extra spaces from either side of a string
LTrim(string)
Trims extra spaces from the left side of a string
RTrim(string)
Trims extra spaces from the right side of a string
UCase (string)
Converts a string to all uppercase letters
LCase (string)
Converts a string to all lowercase letters
StrReverse(string)
Reverses the order of a string
IsNumeric(string)
Tests to see if a field is numeric
InStr (string1, string2)
Searches for the position of string2 inside string1
InStr (start, string1, string2)
Searches for the position of string2 inside of string1 using a numeric starting point
For example, to find the length of a string, you''d use the Len function:
Formula = Len({Customer.Country})
In the sample report shown below (employee_listing.rpt), some of these functions have been used to create an e-mail address to be displayed on a report that follows the naming convention of "first initial, last name", combined with the domain name:
Date and Period Functions
Since most reports will involve date and time information in one form or another, Crystal Reports includes a number of predefined periods to help make life a little easier. You can think of the periods as pre-built arrays of dates, based on the current date. For example, you could use the period LastFullWeek in a comparison, and the range of date values from the Sunday to Saturday of the previous week would be included.
A complete list of these period functions appears below:
WeekToDateFromSun
MonthToDate
YearToDate
Last7Days
Last4WeeksToSun
LastFullWeek
LastFullMonth
AllDatesToToday
AllDatesToYesterday
AlDatesFromToday
AllDatesFromTomorrow
AgedOTo30Days, Aged31To60Days, Aged61To90Days
Over90Days
Next30Days, Next31To60Days, Next61To90Days, Next91To365Days
CalendarlstQtr, Calendar2ndQtr, Calendar3rdQtr, Calendar4thQtr
Calendar1stHalf, Calendar2ndHalf
LastYearMTD
LastYearYTD
Just to drive the point home, all of the above functions act like arrays of dates. In the function AllDatesFromTomorrow, an array is created behind the scenes that includes all dates from tomorrow onwards. Likewise, when you access the function AgedOTo30Days, an array is built of all dates that are 0 to 30 days behind the date you are comparing with. This is especially handy when working with financial reports, where you need to show aging of debts, invoices, and other time-sensitive documents.
We use these functions by comparing date-type fields against them, to determine whether those dates fall within the represented period. For example:
If {Orders.OrderDate} In Over90Days Then Formula = "Overdue"
This also can be used to create complex ''month-to-date'' and ''year-to-date'' reports, by displaying the data that falls in these periods in two separate columns.
In this chapter we will look at an example of a report has been created from the Customer and Orders tables within the Xtreme sample database (customer_orders.rpt, included with the code download). Some basic fields have been displayed on the report (like Customer Name, Order Date, and Order Amount), with a grouping inserted on Customer Name.
So, open the sample application, BasicSyntax_basic. You may prefer to build this example from scratch by creating your own project and viewer - it makes no difference to the finished result, as we will be working more or less exclusively in the Report Designer.
To display the two columns we need to create two separate formulas, which are then summarized, and the details of the report hidden. For the ''month-to-date'' column, locate the Formula Field section of the Field Explorer, right-click and select New, and enter a name of MTD. The formula text looks something like this:
If {Orders.Order Date} In MonthToDate Then Formula = {Orders.OrderAmount}
For the year-to-date column, repeat the same process, but name the formula YTD, and enter the formula text shown here:
If {Orders.OrderDate} in YeartoDate Then Formula = {Orders.OrderAmount}
Having defined your two formula fields, you can drag and drop them onto your report in the ''details'' section.
The next step in creating our summary report is to right-click directly on the MTD field and select Insert Subtotal from the context menu. Then repeat that for the YTD field
To finish off, right-click on the Details section in the Report Designer, select Hide Section, and then do the same for the Group Header #1 on Customer Name.
Your report should now show the two columns for month-to-date and year-to-date values; to get the customer name to appear as well, drag the Group #1 Name field out of the header into your Group Footer, and your report should appear as below.
If you''d like to have a closer look, the complete report is available in the download file for this chapter.
Compile and run the report - but don''t be alarmed if you get a nasty surprise!
Does your report look similar to the download? Probably not - there''s one little trick that we forgot. Since the periods we used in this report are built from the current date and evaluated against the sample data (which is at least a couple of years old), the chances are that your MTD and YTD columns will be empty. Happily, you can set the system date in Crystal Reports by right-clicking on the report, selecting Report | Set Print Date, and entering a new date and time, as shown below.
This allows you to change the internal date and time used by Crystal Reports to build the time periods, and some other date- and time-driven functionality. After setting the date, Crystal Reports will think that it''s actually processing the report on that date, and behave accordingly. (This is especially handy if you need to do ''point in time'' reports.)
In our case, if you set this date to 07/01/2001 or 20/12/1997 (either of these dates should be compatible with the sample data), your report should show both the MTD and YTD columns, with the totals for each customer.
Print State and Document Properties
Crystal Reports has a number of special fields and properties that are generated by the system when a report is run. You will have already encountered some of these fields when adding page numbers, or summary information such as the report''s title, author, etc.
Here is a list of these types of functions:
Function
Description
PreviousValue (fieldname)
The value of the previous field to appear
NextValue (fieldname)
The value of the next field to appear
IsNull (fieldname)
Tests whether a field is null
PreviousIsNull (fieldname)
Tests whether the previous field value is null
NextIsNull (fieldname)
Tests whether the next field value is null
PageNumber
Returns the current page number
TotalPageCount
Returns the total page count
PageNofM
Returns the current page number of the total page count
RecordNumber
Returns Crystal Reports internal reference of record number
GroupNumber
Returns Crystal Reports internal reference of group number
RecordSelection
Returns the current record selection formula for the report
GroupSelection
Returns the current group selection formula for the report
OnFirstRecord
Returns a True value when the first record is displayed
OnLastRecord
Returns a True value when the last record is displayed
Using these functions, you can quickly create formulas that can mark new records in a sorted list:
If Previous({Customer.CustomerName}) <> {Customer.CustomerName} Then _
Formula = "New Customer Starts Here"
or you could print a text message at the end of your report:
If OnLastRecord = True Then _
Formula = " ***** END OF REPORT ***** "
Control Structures
Crystal Reports supports a number of control structures that can control branching within a formula.
lf...Then Statements
If...Then statements provide an easy method for controlling branching within your formula text. If...Then statements can work on the basis of a single condition, for instance:
If {Customer.Country} = "USA" Then Formula = "Local Customer"
In the customer sales report example that''s included with this chapter (customer_sales.rpt), we can create a Formula Field that will assess if the value in the Country field is the USA. If it is, a message will be printed showing the customer as a "Local Customer". To do this, you merely open the Field Explorer, right-click on Formula Fields | New, and after giving the field a name (local_customer_flag), enter your formula into the editor.
You can also use an Else clause for when the condition is not met, for example:
If {Customer.Country} = "USA" Then Formula = "Local Customer" _
Else "International"
Multi-line If...Then...Else statements can also be used, but keep in mind that once a condition is met, Crystal Reports will not process the rest of the formula text. For example, let''s look at this early version of the formula field sales_performance_flag in customer_sales.rpt:
If {Customer.Last Year''s Sales} > 30000 Then Formula = "Excellent job!" _
Else If {Customer.Last Year''s Sales} > 10000 Then Formula = "Fine job!" _
Else If {Customer.Last Year''s Sales} > 20000 Then Formula = "Great job!"
In this formula, if the value passed was 25,000, the formula would immediately stop on the second line (because the condition has been met), giving an incorrect result. In order to have a multi-line If...Then...Else formula work correctly, you need to put the conditions in the correct order, like so:
If {Customer.Last Year''s Sales} > 30000 Then Formula = "Excellent job!" _
Else If {Customer.Last Year''s Sales} > 20000 Then Formula = "Great job!" _
Else If {Customer.Last Year''s Sales} > 10000 then Formula = "Fine job!"
In addition to multi-line use, you can also use compound If...Then...Else statements, nesting two or more statements in one formula, as shown below in the compound_if_then formula field from this report:
If {Customer.Country} = "USA" Then _
If {Customer.Sales} > 30000 Then Formula = "Excellent job!" _
Else If {Customer.Sales} > 20000 Then Formula = "Great job!" _
Else If {Customer.Sales} > 10000 Then Formula = "Fine job!"
This would result in your report looking something like this:
Select Statements
Another popular control structure is the Select statement, which can be used with Case to evaluate a particular condition. If that condition is True, control of the formula will go to the formula text for the met condition, as shown below:
Select Case {Customer.PriorityNumber}
Case 1, 2, 3
Formula = "high priority"
Case 4
Formula = "medium priority"
Case Else
Formula = "low priority"
