Modifying Formulas

To override a formula, pass the formula name to the FormulaFields collection and assign the new formula to the Text property. The tricky part is knowing the proper way to assign data to the formula. Formulas are always stored internally as a string and the syntax for storing each data type to the formula is slightly different.

When overriding the text of an existing formula, you must use Crystal syntax. The Crystal Reports object model doesn’t let you use Basic syntax when overriding formulas from a .NET application. Oddly enough, it doesn’t matter if the formula was originally written using Basic syntax. You always have to use Crystal syntax. The runtime engine only has access to the Crystal syntax compiler and it can’t interpret Basic syntax.

When referring to a formula name in your .NET application, don’t put @ in front of the formula name. Although this is required when referencing formulas from within a report, it isn’t used by .NET when referencing report objects via the object model. You’ll get the error Invalid Index if you do so.

Let’s look at examples for assigning numbers, strings, dates and Boolean data to a formula.

Numbers

To assign a number to a formula, simply use the text equivalent of that number. No special formatting is needed

Listing 16-1. Assigning numbers to formulas
[VB.NET]
'Assign the value 999 to the formula called CustomerId
myReport.DataDefinition.FormulaFields("CustomerId").Text = "999"
'Assign the integer variable UserId to the formula called UserId
myReport.DataDefinition.FormulaFields("UserId").Text = UserId.ToString()
[C#]
//Assign the value 999 to the formula called CustomerId
myReport.DataDefinition.FormulaFields["CustomerId"].Text = "999";
//Assign the integer variable UserId to the formula called UserId
myReport.DataDefinition.FormulaFields["UserId"].Text = UserId.ToString();

Strings

Programmers frequently use the wrong syntax when assigning a string variable to a formula. Since both the formula and the variable are the same data type, it’s easy to think that you can just assign the variable to the Text property. Most people forget to put quotes around the variable so that Crystal Reports knows that you are passing it a string. Otherwise, it doesn’t know how to interpret the data. You could be trying to pass a formula, a report field, etc. Putting quotes around the data makes it clear to Crystal Reports that it should interpret it as a string constant.

Listing 16-2. Assigning strings to formulas
[VB.NET]
'Assign the string variable NewName to the formula called Name
myReport.DataDefinition.FormulaFields("Name").Text = Chr(39) & NewName & Chr(39)
'Override the formula ReportTitle
myReport.DataDefinition.FormulaFields("ReportTitle").Text = "'New Report Title'"
'Override a formula with a string and data field concatenated together
myReport.DataDefinition.FormulaFields("InvoiceHeader").Text = _
Chr(39) & "Invoice Number:" & Chr(39) & "+Cstr({Invoices.InvoiceNumber})"
[C#]
//Assign the string variable NewName to the formula called Name
myReport.DataDefinition.FormulaFields["Name"].Text = (char)39 + NewName + (char)39;
//Override the formula ReportTitle
myReport.DataDefinition.FormulaFields["ReportTitle"].Text = "'New Report Title'";
//Override a formula with a string and data field concatenated together
myReport.DataDefinition.FormulaFields["InvoiceHeader"].Text =
(char)39 + "Invoice Number:" + (char)39 + "+Cstr({Invoices.InvoiceNumber})";

The first example assigns the variable NewName to the formula. Notice that it is surrounded by the function Chr(39) to hard-code a single quote around the value. You could also use Chr(34) to surround it with double-quotes because Crystal Reports uses them both interchangeably. In the second example, rather than using the Chr() function, it uses the single quote within the string constant. This is acceptable as well, but I prefer using the Chr() function because the single quote is hard to read when it is located next to a double quote.

The third example concatenates a string constant with a report field. Again, notice how the quotes are used around the text. Since the formula is telling Crystal Reports to concatenate string together, the & operator is used. Lastly, the InvoiceNumber field is a number so we have to use the CStr() function to tell Crystal Reports to convert it from a number to a string. If you are just concatenating two strings together, then the CStr() function isn’t necessary

Dates

Dates are similar to numbers in that they must be converted to their string equivalent. The tricky part is that you have use the proper syntax so that Crystal Reports knows you are assigning a date to the formula. You can use any of the date conversion functions that come with Crystal Reports or surround it with #.

Listing 16-3. Assigning dates to formulas
[VB.NET]
'Assign today's date to the formula called PrintDate
myReport.DataDefinition.FormulaFields("PrintDate").Text = _
"#" & DateTime.Now.ToShortDateString() & "#"
'Assign the date variable InvoiceDate to the formula called PrintDate.
myReport.DataDefinition.FormulaFields("PrintDate").Text = _
"CDate(" & Chr(39) & InvoiceDate & Chr(39) & ")"
[C#]
//Assign today's date to the formula called PrintDate
myReport.DataDefinition.FormulaFields["PrintDate"].Text =
"#" + DateTime.Now.ToShortDateString() + "#";
//Assign the date variable InvoiceDate to the formula called PrintDate.
myReport.DataDefinition.FormulaFields["PrintDate"].Text =
"CDate(" + (char)39 + InvoiceDate.ToShortDateString() & (char)39 + ")";

The first example surrounds the date with # to tell Crystal Reports it is a date constant. The second example is a little more tricky because it uses the CDate() function to convert the string to a date. The tricky part isn’t the use of CDate(), it’s the fact that since CDate() requires a string constant to be passed to it. You need to surround the string with quotes. If you don’t include the Chr(39) function before and after the string, then you will get a very strange date on your report.

When there are problems with modifying a formula during runtime, you get this error. Unfortunately, the error doesn’t give you any indication of what is wrong. The easiest way to debug formulas is to use the report designer. It gives you the exact error message and is much easier to figure out. Go into debug mode in Visual Studio and use the Command Window to print the Text property of the formula. Copy this text to the Notepad program and then stop your application. Open the report and use the Formula Editor to copy this text to the formula and save it. Crystal Reports will check the formula’s syntax and give you an error message that is more descriptive.