Using Report Calculations

About report calculations

When you are working with a Financial Analyzer report, you can define ad hoc formulas to calculate figures such as sums, counts, and averages without having to define a model or a solve definition. These formulas, called report calculations, operate only on the data that is currently included in the report.

When to use report calculations

If you require a temporary formula that you do not need to use in other documents, you can use a report calculation. If, however, you require the same formula frequently or in multiple documents, you should consider using one of the following methods to perform calculations:

• Create a formula financial data item, as described in Chapter 4.

• Add a line item to the Line dimension and incorporate its calculation into a model, as described in Chapter 8.

Inserting rows and columns for calculations

In order to define a report calculation, you must insert a new row or column in the report and then define a formula for that row or column. When you finish defining the formula, the system populates the appropriate report cells with data calculated using the formula. You can move or delete the column or row as you can any inserted column or row.

If you insert a new report calculation and it conflicts with an existing calculation (for example, if you create a column calculation when a row calculation has already been defined), the most recently inserted report calculation takes precedence.

Inserted rows or columns, and their associated formulas, exist only in the context of the current report. You cannot link inserted rows or columns between reports or gain access to them via the Selector.

Associating formulas with report cells

You can associate a formula with:

• All of the cells in the current column or row

• Just one cell in a column or row

• Financial data items

You cannot associate a formula with multiple randomly selected cells in a row or column. You must select either just one cell or all cells in a column or row related to a financial data item.

How inserted calculations relate to dimension values

Inserted rows and columns and their associated formulas can be linked to the dimension to which they are added or to a position in the report. If a row or column is inserted in relation to a particular dimension value, that dimension value becomes the "owner" of the insert. For example, if you insert a row before the dimension value Widgets, Widgets becomes the insert's owner. If you insert a row or column by specifying a position (for example, "after the 2nth FinData"), the inserted row or column stays in the same relative position, even if other dimension values change status, because it is not associated with a particular dimension value.

How moving dimension values affects calculations

You can move dimension values in reports either by swapping tiles or selecting Move Insert from the Edit menu. The following table describes methods of moving dimension values and the effect that each method has on inserted calculations.

Method of moving a dimension value	Effect on inserted calculations
Moving the dimension value that owns the insert to another position in the report	The insert moves with the moved dimension
Removing the dimension value that owns the insert from the report by using the Selector to remove the value from status	The insert and its associated formula are deleted from the report
Moving the dimension to the page edge of the report	The insert is suppressed, but retained; if the dimension is moved to an across or down position, the insert reappears
Hiding the dimension value that owns the insert from the report using drilling	The insert is hidden until the dimension value is redisplayed
Moving the inserted column or row that contains the formula relative to another dimension value (by selecting Move Insert from the Edit menu)	The insert becomes "owned" by the dimension value that you moved it relative to
Moving the inserted column or row that contains the formula positionally (by selecting Move Insert from the Edit menu)	The insert is no longer "owned" by a dimension value and will remain in the current position in the report until moved

Methods for creating formulas

You can create formulas by:

• Selecting the Formula Tools button on the toolbar and selecting values from the tabs

• Entering the formula manually into the Edit bar

• A combination of entering the formula manually and using the mouse to select dimension values

Formula construction

Report formulas are always constructed as follows:

result = expression

where:

• result is the location in the report of the cell or cells where the result will be displayed

• expression is an algebraic expression made up of the following elements: operators, numeric constants, dimension-value pairs, and functions

Note that if you create a formula using Formula Tools, after you exit the Formula Tools dialog box, the newly created formula appears in the edit box, but is not reflected in the report until you select the Enter button.

Using dimension values in formulas

You can include dimension values in report formulas. In report formulas, dimension values:

• Can be displayed either by their description name or by their internal identifier. How they are displayed depends on the Use Internal IDs in Report Calcs setting in the Options dialog box. If Use Internal IDs in Report Calcs is set to Yes, dimension values are displayed by their internal identifier. If Use Internal IDs in Report Calcs is set to No, dimension values are displayed by their description name. Note that internal IDs are always unique, but description names do not have to be unique. If you choose to display dimension values as their description name, you should ensure that the description name you select for inclusion in your formula is unique to avoid unexpected results.

• Must always be included as part of a dimension-value pair. For example, TIME:JAN00, where TIME is the dimension name, and JAN00 is the dimension value.

• Can only be included if they are in status in the current report.

You cannot use the same dimension-value pair in both the result and expression side of a formula.

Using qualified data references in formulas

You can use qualified data references, known as QDRs, to describe specific cells that are referenced by one or more dimensions. For example, you can use a QDR to reference the cell that represents the Total Assets line item for the year 2000 for the East organization.

QDRs have many uses in report formulas. For example, in the expression side, you can use a QDR to specify that you want to use data contained in a specific cell in a formula. In the results side, you can use a QDR to choose a specific cell into which a formula can be inserted. A useful application for this would be to insert an NA value at the intersection of an inserted row that contains a formula and a column that contains a formula, when neither inserted formula produces an appropriate result.

You use the Qualify tab in the Formula Tools dialog box to create qualified data references (QDRs) to the data in specific cells in the current report.

Example 1: Applying calculations to rows and columns

The report in this example has two inserted columns and one inserted row for calculated data. For this report:

• The formula was created using the mouse to select dimension-value pairs from the current report

• Dimension values are displayed by their description names (as opposed to their internal identifiers)

• The columns labeled Year to Go and Year to Go % Year, and the Row labeled Operating Income % Revenue have been inserted

• The Year to Go column contains the following formula:

  'Time:Year to Go'='Time:Nov 00'+'Time:Dec 00'
  

  where:

  • The single quotations (` `) are required delineators

  • Time represents the dimension

  • The colon (:) is a required separator between the dimension and dimension value

  • Time:Year to Go represents the inserted column

  • The plus sign (+) is an operand

  • Nov 00 and Dec 00 are the description names of the dimension values

• The Year to Go % Year column contains the following formula:

  'Time:Year to Go % Year'= 'Time:Year to Go'/'Time:Year 00'*100
  

• The Operating Income % Revenue row contains the following formula:

  'Line:Operating Income % Revenue'= 
  
  `Line:Operating Income'/'Line:Total Revenue'*100
  

After inserting the formulas in the specified cells, the report appears as follows:

Example 2: Applying a calculation to a specific cell

In the previous example, the number 82% (located at the intersection of the two inserted formulas) is meaningless. To ensure that this value is not misinterpreted, we can replace the incorrect value with an NA value.

For this report, the formula to do this would be as follows:

'Line:Operating Income % Revenue;Time:Year to Go % Year
'=NAN()

where:

• The single quotations (` `) are required delineators

• Line is the dimension

• The colon (:) is a required separator between the dimension and dimension value

• Operating Income % Revenue is the inserted row

• The semi-colon (;) is a required separator between the two dimension-value pairs

• Time is a dimension

• Year to Go % Year is the inserted column

• NAN( ) is the NAN function, which places an NA value in the specified cell.

To avoid typing mistakes in the long QDR, the results side of the expression was created using the Qualify tab in the Formula Tools dialog box.

After inserting the above formula in the specified cell, the report appears as follows:

Note that in this example, the NA value appears as a dash (-) because that is how NA Spell is specified in report options.

Report calculation errors

If you enter a formula incorrectly, you will receive an error in the cell into which you were attempting to insert a formula. The following table describes these errors and their meanings.

Error	Description
#CIRC!	The formula contains a circular reference
#DIV/0	The formula is attempting to divide by zero
#ERROR!	This is a generic error
#MISSING!	The dimension-value pair used in the formula is not currently in status
#NAME?	The formula contains a function name that is not recognized
#NULL!	The formula contains dimensions that do not intersect in the report
#NUM!	There is a problem with a number
#OVERFLOW!	The expression evaluates more values than are specified in the result
#PAGE!	The dimension-value pair used in the formula is not in the current page
#REF!	The formula contains an invalid reference to a cell
#VALUE!	The formula contains the wrong type or number of operands

Related information

For more information about using calculations in reports, search for the following topics in the Financial Analyzer Help system:

"Defining Formulas for Report Calculations"

"Displaying Internal Identifiers in Report Formulas"

"Defining Formulas for Report Calculations"

"Formula Syntax"

"Inserting Rows or Columns"

"Moving Inserted Rows and Columns"