A variable is simply a formula that has a name. You can create a variable by going to Data, Variables, Add. This will bring up 2 tabs , Definition and Formula. If you enter a formula in the formula section but forget to enter a name in the definition section, you will create a formula not a variable. You can also create a variable from a formula by highlighting the appropriate formula cell and going to Data, Define as Variable. This will allow you to define the variable in its context which can be useful if you want to use the variable in another part of the report but retain the original context.

There are a number of reasons to use a variable rather than a formula:

• Performance. When you create a formula and then go back and edit it, BusinessObjects will keep all versions of the formula, even those that you created in error. It will calculate all formulae including those not used in the report when you edit your report. If you have lots of unused formulae this can have performance implications.
  
  
• Ease of Maintenance. Having unused formulae can cause confusion and make maintenance difficult. The report becomes untidy and unwieldy. Also, importantly, you only have to edit a variable once for use throughout the report(s) in the query.
  
  
• Reuse. You can reuse a variable throughout the report(s) in your query in the same way as any other object.
  
  
• User friendliness. Naming a formula can make it more meaningful.
  
  
• Use with other features. You cannot utilise some BusinessObjects’ features with formulae eg you cannot apply sorts, filters, alerters or breaks with a formula but you can with a variable.
  
  
• Functionality. You can build upon variables ie you can use one variable as part of another variable or formula.

BusinessObjects will lead you through the formula steps. If an option is not available in the Operators section it generally means you cannot use it or you have missed something such as a set of brackets.

If you are looking for a function such as Trim, it is often quicker to look in the ‘all functions and aggregates’ list than to try to remember which category it is in.

‘Where’ clauses are commonly used with measures, either to sum, count or filter eg to determine the EFTSU of students in Masters Research programs, a typical formula would be =<Total EFTSU> Where (<Program Level Code>="03").

Things to note about ‘where’ clauses in BusinessObjects

• ‘Where’ clauses can only be used with =. They cannot be used with ‘in list’; <> or any other operator. To get around this, you can create another variable.
  
  For Example:  If you want to include both masters research and masters coursework in your formula you need to create the following variable: = If <Program Level Code> InList ("03" , "04") Then "Masters" Else "Other" (Note: the text in quotes is arbitrary, you could use “Yes” and “No”, for example). Give the variable a meaningful name eg Program Level Def. Now, you can use the where clause =<Total EFTSU> Where (<Program Level Def>=”Masters”. Remember always to use brackets with a ‘where’ clause or you will get a syntax error.
  
  
• ‘Where’ clauses cannot be used with numeric values. This appears to be a bug.

You must follow these guidelines when you write a formula:

• A formula must begin with an “equal to” sign (=). If you remove this sign, the formula is considered as a constant or as text.
• Variables included in formulae must be surrounded by <>, eg., <Program Type>.
• Text included in formulae must be surrounded by double-quotes (“).

If you get a syntax error, the part of the formula that contains the error is highlighted.

Syntax – see above

Computational – One common reason for this error is if you use an If Then formula  in a block, when the formula includes a variable that is not used in the block. For example, if you have a block that contains faculty, period and payments to date and the formula is based on posting code.

When using formulae, the only time you can use a variable that is not in the block, is when you are using a where clause with a condition eg =<Payments to Date> where [Posting Code = “040”].

You will also get computational errors when BusinessObjects does not know which context to use. For example, if you include a formula in a break footer you may have to define the context. (see Contexts below).

Multivalue - The #MULTIVALUE error is often encountered when working with variables and formulae. The error will commonly appear in Section Headers when a cell has been inserted next to an object that has been Set as Master. When an object is manually placed outside a table, it loses it’s context as defined in the paragraph above.

The report below displays numbers of students by program and career.

Set as Master is used to split the table up by Career code.

Career description is currently displayed in the table but as it is related to Career Code, it would be better to display it within the section heading next to the code.

Inserting a cell next to Career Code and populating it with Career description results in a #MULTIVALUE error. This occurs because BusinessObjects is trying to display a single value in the cell but believes that there is more than one value available. The cell inserted next to the heading has no context so BusinessObjects sees no link between Career Code and Career Description.

The solution outlined here is to use the MAX function to define a single value for Career Description. BusinessObjects can then display this value in the cell next to Career Code.

  
Context

Context is the level at which a formula or variable applies. BusinessObjects automatically applies the context according to the placement of a variable. So if you apply a formula in a table, the context is determined by the data in the same row, but in a footer it is based on a total. In these circumstances you do not need to do anything. BusinessObjects understands the context. It is when you try to use the variable to calculate results based on data that is either not displayed or at a level other than displayed that you need to tell BusinessObjects what the context is.

For example, if you wish to use a total in the calculation of a percentage elsewhere in a report or in another report tab you will have to define the context of the total. One of the easiest ways to do this is to define the formula (ie total) as a variable and tick the Define Variable in its Context box. Then, when you use that variable in another formula BusinessObjects will know exactly what you mean.