Replacing Parameters with Formulas

Before getting into a discussion of how to modify parameters, you first need to know that parameters are very complicated to work with. Changing them involves a lot of steps and it is easy to make a mistake. You also have to make sure you have the parameter name and data type exact. If anything goes wrong, your code won’t work and the user is still prompted with the dialog box to enter the parameter value. There is a simple solution to all of this: Don’t use parameters. If you think that was a typographical error, then let me repeat it. Don’t use parameters.

Since parameters are the only way to let a user input data directly into a report, most people assume that they are very important. And they are important for non-programmers. But with .NET’s ability to directly modify report objects prior to printing a report, parameters have lost their significance. With a little thought, you can eliminate most parameters from your reports.

Let’s look at a couple of examples. Suppose you have a parameter that prompts the user for a special comment that should appear at the bottom of the report. Rather than making this a parameter field, use a Text object instead and programmatically modify the text via the ReportDocument object. As another example, take a parameter that is used to let a user choose whether a sort will be ascending or descending. Instead of using a parameter, programmatically get a reference to the sort object and modify the sort direction.

The majority of parameters in a report represent a discrete value. Some examples of discrete values are a date, a dollar amount, or an ID of a product or company. These parameters are easily replaced with formulas. A formula doesn’t have to be a complex algorithm of functions or programming code. It can be a simple number, date or string. Wherever you need to use a discrete parameter value, use a formula instead.

Modifying formulas is simple. It only takes one line of code. Here is an example of modifying the formula CustomerId to be the value 999.

MyReport.DataDefinition.FormulaFields("CustomerId").Text = "999"

The FormulaFields collection stores each formula in the report. Formulas are referenced by passing the formula name as the indexer to the FormulaFields collection. The sample code passes the string “CustomerId” to the FormulaFields collection and modifies the Text property of the formula with the new value.

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 back into design mode for the report and copy the text into a formula. It checks the formula’s syntax and gives you a better error message. If it says that there are no syntax errors with the formula, then the error is most likely because you are using Basic syntax. Changing formulas during runtime requires using Crystal syntax. The runtime engine only has access to the Crystal syntax compiler and it can’t interpret Basic syntax. You can only use Basic syntax when you are in design mode of a report. Even if a formula was originally written using Basic syntax, overriding a formula during runtime requires using must Crystal syntax.

There is one flaw in my theory of not using parameters: formulas can’t be modified using the viewer control. Formulas can only be modified using the DataDefinition class found within the ReportDocument object. The DataDefinition class doesn’t exist in either the WinForms viewer control or the ASP.NET viewer control. The viewer control can only modify parameters. Of course, with most applications the choice to use the ReportDocument object or the viewer control is a matter of personal preference.