Setting Formulas


The RDC gives you an extra level of flexibility by allowing you to control the contents of Report objects as each record is processed by the report (see Changing Text Objects at Run Time and Conditional Formatting and Formatting Sections later in the chapter). Using these techniques, you can actually have objects on your report show the results of Visual Basic calculations and procedures. However, it still may be more straightforward to simply modify the contents of Crystal Reports formulas dynamically at run time. This will be useful for changing formulas related to groups that can also be changed from within your code, for changing a formula that shows text on the report, or for changing a formula that relates to math calculations or conditional formatting on the report.

Setting formulas at run time is similar to setting the record selection formula at run time (described in the preceding section). You need a good understanding of the Crystal Reports formula language to adequately modify formulas inside your VB code. If you need a refresher on Crystal Reports formulas, review Chapter 5.

The RDC provides a FormulaFields collection in the Report object that can be read or written to at run time. This collection returns multiple FormulaFieldDefinition objects, one for each formula defined in the report. Although a great deal of information may be gleaned from the FormulaFieldDefinition object, the simplest way to change the contents of a formula is simply to set the Text property for the particular member of the collection that you want to change. The collection is one based, with each member corresponding to its position in the list of formula fields.

In the Xtreme Orders sample application, the Order + Tax formula is modified according to the user s completion of the Tax Rate text box. The formula is changed using the following code:

 'Set @Order + Tax formula 
If txtTaxRate = "" Then
Report.FormulaFields(1).Text = "{Orders.Order Amount}"
Else
Report.FormulaFields(1).Text = "{Orders.Order Amount} * " &_
Str(txtTaxRate / 100 + 1)
End If 'txtTaxRate = ""

You needn t change the property for all formulas in the report ”just the ones you want to modify at run time. Remember that you can t specify the formula name directly as the collection index argument ”you must use a one-based index number. This is determined by where the formula is in the formula list in the RDC design window ”the first formula will have an index of 1, the fifth, 5, and so on. Review the sample code earlier in the chapter if you wish to refer to formulas by name instead of index number.

The Text property requires a string expression or variable, correctly conforming to Crystal Reports formula syntax, that contains the new text you want the formula to contain. Take care when assigning a value to the formula. The Order + Tax formula is defined in Crystal Reports as a numeric formula. Therefore, you should pass it a formula that will evaluate to a number. If the user leaves the text box on the form empty, the VB code assumes no additional sales tax and simply places the Order Amount database field in the formula ” the formula will show the same amount as the database field.

If, however, the user has specified a tax rate, the VB code manipulates the value by dividing it by 100 and then adding 1. This creates the proper type of number to multiply against the Order Amount field to correctly calculate tax. For example, if the user specifies a tax rate of 5 (for 5 percent tax), the VB code will change the value to 1.05 before combining it with the multiply operator and the Order Amount field in the formula.

If you ve used other integration methods from earlier Crystal Reports versions, such as the ActiveX control or the Report Engine API, you ve probably used a formula modified at run time to show some form of text on the report, such as the report criteria that have been supplied by the user. Although this can also be accomplished with a formula here, the RDC s capability of manipulating text objects at run time gives you the opportunity to approach this from another angle. Therefore, a formula is not used to accomplish this in the Xtreme Orders sample application with the RDC. The following section explains how this is accomplished by using a text object.

Tip  

Crystal Reports 10 features two formula syntaxes: Crystal syntax, which is compatible with Crystal Reports 7 and earlier, and Basic syntax, which was introduced in Crystal Reports 8. If you need to know which syntax an existing formula is based on, execute some code to access the formula, such as retrieving the Text property. Then, retrieve the Report object s LastGetFormulaSyntax property to determine the syntax of the formula you retrieved before. If you want to assign a specific syntax to a certain formula, set the Report object s FormulaSyntax property first. Then, set the formula s contents by passing formula text in the correct syntax to the Text property.




Crystal Reports 10
Crystal Reports 10: The Complete Reference
ISBN: B005DI80VA
EAN: N/A
Year: 2004
Pages: 223
Authors: George Peck

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net