Advanced formatting options

When you create a report in Management Reporter, additional formatting functions are available, including filters for dimensions, restrictions for columns and reporting units, non-printing rows, and IF/THEN/ELSE statements in calculations.

The following table explains the advanced formatting functions that are available for designing reports.

This topic contains the following sections:

• Advanced cell placement

• Designing the report

• Examples of advanced formatting options

• Restrict a row to a specific reporting unit

• Select print control in a row definition

• Column Restriction cell in a row definition

• Use a calculation formula in a row definition

• IF/THEN/ELSE statement in a row definition

Advanced cell placement

Advanced cell placement, also called forcing, involves the placement of specific values into specific cells. The most common example of forcing is to move the correct balance in a Cash Flow Statement. Forcing can be used to:

• Move values from Microsoft Excel into specific cells.

• Hardcode specific values into a report.

• Modify signs by copying a value from a previous cell and multiplying by -1 (* -1).

Designing the report

When you design a report, you should create all of the detail rows first to make sure that values are being pulled in as expected, and then add NP (No Print) Format Overrides to suppress the detail that includes the final values.

The key concept in forcing is to use a formula of destination column = originating column (dot) row code. Separate any additional placements for that row by a comma and a space, and then add the next column reference. For example:

[Destination Column for this Row]=[Source Column].[Source Row]

C=C.100, F=D.100

Examples of advanced formatting options

The following examples show how to format the row definition and column definition for forcing a basic cash flow report (Example 1), forcing a statistical report (Example 2), and the resulting reports.

Example 1: Basic forcing

The following is an example row definition using basic forcing.

The following is an example column definition using basic forcing in the row.

The following is the generated report using the previously defined row and column definitions.

Example 2: Statistical reports

The following is an example row definition using forcing for a statistical report.

The following is an example column definition using forcing for a statistical report.

The following is the generated report using the previously defined row and column definitions.

Restrict a row to a specific reporting unit

When a report row is restricted to a specific reporting unit, that row only displays the linked data for the named reporting unit, and ignores the data for other reporting units in the reporting tree. For example, you can create a row that provides details for the total operating expenses for a specific department.

Your report may contain duplicate data when the report contains both a reporting tree and a row definition that has more than just the natural account. For example, suppose that you have a reporting tree that lists the six departments in your organization and you also have a row definition that lists a specific account department combination in the row. When you generate the report, the specific account and department combination listed in the row is printed on every level of the reporting tree, even though that department may not match what is in the tree. This is because the row overrides what is typically filtered out by the report definition.

Restricting a row to a specific reporting unit is one way to avoid duplication of data.

Restrict a row to a reporting unit

1. In Report Designer, click Row Definitions, and then select a row definition to modify.

2. Double-click the appropriate Related Formulas/Rows/Units cell.

3. In the Reporting Unit Selection dialog box, in the Reporting tree field, select the tree that is assigned in the report definition.

4. Select a reporting unit, and then click OK. The restriction is displayed in the cell of the row definition.

5. Double-click the cell in the Link to Financial Dimensions column of the restricted row, and then enter a link to the financial data system. For more information, see the “Prepare a link column in a row definition” section in Management Reporter and Microsoft Excel.

Select print control in a row definition

You can specify print codes for each column using the Print Control cell. For a description of available print codes, see Regular Print Control codes and Conditional Print Control codes.

Add print control codes to a report row

1. In Report Designer, open the row definition to modify.

2. Double-click the Print Control cell.

3. In the Print Control dialog box, select a print code, or press and hold the Ctrl key to select multiple codes. You can also type the print codes directly into the Print Control cell. Use commas to separate multiple print codes.

4. Select any conditional print options.

5. Click OK.

Regular Print Control codes

The following table describes the regular Print Control codes for a row definition.

Conditional Print Control codes

The following table describes the conditional Print Control codes for a row definition.

Column Restriction cell in a row definition

The row definition Column Restriction cell has multiple purposes. Depending on the type of row, you can use the Column Restriction cell to specify one of the following functions:

• The cell can limit the printing of the row amounts to a specific column. This is useful for creating a tabular balance sheet. For information, see Tabular report example.

• The cell can specify the column of amounts to be sorted. For information, see the “Select a sorting code for a row definition” section in Related formulas/rows/units.

Use a calculation formula in a row definition

A calculation formula in a row definition can include the +, -, *, and / operators, in addition to IF/THEN/ELSE statements. Additionally, a calculation can involve individual cells and absolute amounts, which are actual numbers included in the formula. The formula can be up to 1024 characters long.

Calculations cannot be applied to rows that contain cells defined as the Link to Financial Dimensions (FD) type. However, you can include calculations on consecutive rows, suppress the printing of those rows, and then total the calculation rows.

Operators in a calculation formula

A calculation formula uses more complex operators than a row total formula, but lets you multiply (*) and divide (/) amounts with the additional operators.

To use a range or sum in a calculation formula, you must use the at sign (@) in front of any row code, unless you are using a column in the row definition. For example, to add the amount in row 100 to the amount in row 330, you could use the row total formula 100+330, or the calculation formula @100+@330.

When you reference a column in a calculation formula, the @ character is not required. For more information about columns in a calculation formula, see Select report columns in a row definition.

Create a calculation formula

1. In Report Designer, click Row Definitions, and then open the row definition to modify.

2. Double-click the Format Code cell and select CAL.

3. In the Related Formulas/Rows/Units cell, type the calculation formula. For more information about this cell, see Related formulas/rows/units.

Example of a calculation formula for specific rows

In this example, the calculation formula @100+@330 means that the amount in row 100 is added to row 330. The row total formula of 340+370 adds the amount in row 340 to the amount in row 370, which includes the amount from the calculation formula.

Select report columns in a row definition

When the row in a row definition has a Format Code of CAL, and you enter a mathematical calculation in the Related Formulas/Rows/Units cell, you must also enter either the letter of the associated column and row in the report, such as A.120 to represent column A, row code 120, or you can use the at sign (@) to indicate all columns. For example, you can enter @120 to represent all columns in row 120.

Any mathematical calculation that does not have a column letter or an at sign (@) is assumed to be a real number.

Example of a calculation formula for a specific column

In this example, the calculation formula E=C.340 means that the calculation in the cell in column C, row code 340, is performed only on column E.

Modify a number in selected columns

When you modify a number or calculation in one column of a particular row but do not want to affect other columns in the report, you can specify CAL (for Calculation) in the Format Code column of the row definition.

• To perform a calculation on all report (FD) columns, do not enter a column assignment.

• To restrict a formula to certain columns, enter the column letter followed by an equal sign (=) and the formula.

• You can specify multiple columns. When you use the @ character with specific column placement, the @ character relates to the row.

• You can enter multiple column formulas in one row if they are separated by commas.

Calculation examples

IF/THEN/ELSE statement in a row definition

IF/THEN/ELSE statements can be added to any valid calculation and used with the CAL format. Enter IF/THEN/ELSE calculation formulas in the cell in the Related Formulas/Rows/Units column according to the following format:

IF <true/false statement> THEN <formula> ELSE <formula>

The ELSE <formula> part of the statement is optional.

IF statements

The statement that follows the IF statement can be any statement that can be evaluated as true or false. The statement that follows the IF statement can appear in the following conditions:

• IF A.200>0 (simple evaluation)

• IF A.200>0 AND A.200<10,000 (complex statement)

• IF A.200>10000 OR ((A.340/B.1200)*2 <1200) (complex statement that contains multiple expressions)

The term Periods in an IF statement is the number of periods for the report. This term is typically used for calculating a year-to-date average. For example, when you run a report for period 7 YTD, B.150/Periods means to divide the value in row 150 of column B by 7.

THEN and ELSE formulas

The THEN and ELSE formulas can be any valid calculation from the simplest value assignments to complex formulas. Note the following formula:

IF A.200>0 THEN A=B.200

The statement specifies that "if the value in the cell in column A of row 200 is greater than zero, then place the value from the cell in column B of row 200 into the cell in column A of the current row."

In this statement, you can also use the @ character in either TRUE/FALSE evaluations or in the formula to represent all columns. For example, the formula described in the previous IF/THEN statement places a value in one column of the current row. Other examples are described in the following sections:

• IF A.200 >0 THEN B.200: When the value in cell A.200 is positive, the value from cell B.200 is positioned into every column of the current row.

• IF A.200 >0 THEN @200: When the value in cell A.200 is positive, the value from each column in row 200 is positioned into the corresponding column in the current row.

• IF @200 >0 THEN @200: If the value in row 200 of the current column is positive, the value from row 200 is positioned into the same column in the current row.

Restrict calculation to a reporting unit in a row definition

To restrict a calculation to a single reporting unit in a reporting tree, so that the resulting amount is not rolled up to a higher-level unit, you can use the @Unit code in the Related Formulas/Rows/Units cell in the row definition. The @Unit code is listed in column B of the reporting tree, Unit Name. The values are not rolled up, but the calculation is evaluated at every level of the reporting tree.

The calculation row can refer to a calculation row or a financial data row.

The calculation is recorded in the Related Formulas/Rows/Units cell of the row definition and the financial data-type restriction. The calculation must use a conditional calculation starting with an **IF @Unit** construction, such as the following calculation:

IF @Unit(SALES) THEN @100 ELSE 0

This calculation includes the amount from row 100 in each column of the report, but only for the Sales unit. If there were multiple units named SALES, the amount appears in each of those units.

Additionally, row 100 could be a financial data row and defined as non-printing, which prevents that amount from appearing in all units in the tree. You can also limit the amount to a single column of the report by using the column restriction, such as column H, to print the value only in specific columns of the report.

You can include OR combinations in an IF statement, for example:

IF @Unit(SALES) OR @Unit(SALESWEST) THEN 5 ELSE @100

You can specify a unit in a calculation-type restriction in one of the following ways:

• Enter a unit name to include units that match. For example, IF @Unit(SALES) allows the calculation for any unit named SALES, even if there were several SALES units in the reporting tree.

• Enter the company and unit name to restrict the calculation to SALES units in the ACME company only. For example, IF @Unit(ACME:SALES).

• Enter the full hierarchy code from the reporting tree to restrict the calculation to a specific unit. For example, IF @Unit(SUMMARY^ACME^WEST COAST^SALES).

  Note

  To find the full hierarchy code, right-click in the reporting tree definition, and then select Copy Reporting Unit Identifier (H-code).

Restrict a calculation to a reporting unit

1. In Report Designer, click Row Definitions, and then open the row definition to modify.

2. Double-click the Format Code cell, and then select CAL.

3. Click the Related Formulas/Rows/Units cell and type the conditional calculation starting with an **IF @Unit** construction.

IF/THEN/ELSE statements in a column definition

An IF/THEN/ELSE statement enables any calculation to be conditional upon the results of any other column. You can refer to other columns, but not to a report cell in the IF statement. Any calculation must be applied to the whole column.

For example, the statement IF B>100 THEN B ELSE C*1.25 means the following: If the amount in column B is greater than 100, place the value from column B in the CALC column. If the amount is not greater than 100, multiply the value in column C by 1.25, and place the result in the CALC column.

Always follow the IF clause with a logic statement that evaluates to TRUE or FALSE. The formulas that you use for both the THEN clause and the ELSE clause can contain references to any number of columns, and can be as complex as you want.

For more information about how to create IF/THEN/ELSE statements, see IF/THEN/ELSE statement in a row definition.

See Also

Create and manage report components