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.

Function	Description
Dimension filter	To access specific sets of data, you can use dimensions in the row definition and column definition. Many reports are built to use just the natural segment in the row format. The rows can also be modified to include dimension values. Dimension filters in the column definition are used to access specific dimension values.
Reporting unit restriction	Set up a report row to display only information that is linked to a specific reporting unit. For more information, see Restrict a row to a specific reporting unit.
Non-printing (NP) rows	Non-printing rows are helpful in many reports. If several calculations are required to obtain a value, these calculations can be hidden on the printed report. Non-printing rows are also helpful for troubleshooting report designs and for advanced cell placement.
Column restriction	The column restriction in the row definition is helpful for hiding values that are only relevant on some rows of the report. When percentage calculations are performed on a row, this column restriction prevents total columns or other columns from printing where these numbers do not apply. For more information, see Column Restriction cell in a row definition.
Column break	Add column breaks in a row definition to display report information side-by-side. Multiple column breaks can be added in a single row definition, and column headers are repeated at the top of each column after the column break. Comments for a report are displayed between the column breaks. For more information about column breaks, see Add a format code.
IF/THEN/ELSE statement	Modify calculations in a row definition or a column definition. For more information, see IF/THEN/ELSE statement in a row definition and IF/THEN/ELSE statements in a column definition.

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).

Note

In many cases, you must configure your report definition to perform column calculations before row calculations. To select this option, complete the following procedure:

1. In Report Designer, open the report definition.

2. Click the Settings tab.

3. Under Calculation priority, select Perform column calculation first and then row.

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.

Important

When you use the CAL format code in the row definition, you cannot drill down into transaction detail.

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.

Row Code	Description	Format Code	Related Formulas/Rows/Units	Format Override	Normal Balance	Print Control	Column Restriction	Row Modifier	Link to Financial Dimensions
100	Cash at Beginning of Period (NP)							Account Modifier = [/BB]	+Segment2 = [1100]
130	Cash at Beginning of Period	CAL	C=C.100,F=D.100
160
190

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

	A	B	C	D	E	F
Header 1
Header 2	A	B	C	D	E	F
Header 3
Column Type	ROW	DESC	FD	FD	FD	CALC
Book Code/Attribute Category			ACTUAL	ACTUAL	ACTUAL
Fiscal Year			BASE	BASE	BASE
Period			BASE	BASE	BASE
Periods Covered			PERIODIC	YTD/BB	YTD
Formula						E-D
Column Width	5	30	14	14	14	14

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.

Row Code	Description	Format Code	Related Formulas/Rows/Units	Format Override	Normal Balance	Print Control	Column Restriction	Row Modifier	Link to Financial Dimensions
50	Statistical Information	REM
100	Headcount - US	CAL	4	###0.;($###0.)
115	Headcount - International	CAL	11	###0.;($###0.)
130
190	US Sales				C				+Segment2 = [41*], Segment3 = [00]
220	International Sales				C				+Segment2 = [41*], Segment3 = [01:99]
250
280
310	US Sales	CAL	D=C.190,E=C.100,F=(C.100/C.190)
340	International Sales	CAL	D=C.220,E=C115,F=(C.220/C.115)

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

	A	B	C	D	E	F
Header 1	A	B	C	D	E	F
Header 2	-	-	YTD	Yearly Sales	Staff	$ Per Person
Header 3
Column Type	ROW	DESC	FD	CALC	CALC	CALC
Book Code/Attribute Category			ACTUAL
Fiscal Year			BASE
Period			BASE
Periods Covered			YTD
Formula						E-D
Column Width	5	30	14	14	14	14

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.

Note

When you restrict a row that includes dimensions to a child reporting unit, the row amount is included without duplication for that unit and for its parent units.

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.

Print Code	Interpretation of Print code	Description
NP	Non-printing row	Excludes the amounts in the row from being printed in the report and from calculations. To include a non-printing column in a calculation, refer to the column directly in the calculation formula. For example, the non-printing row 240 is included in the following calculation: 230+240+250. However, the non-printing row 240 is not included in the following calculation: 230:250.
CS	Currency symbol; use currency format in this row	Includes the currency symbol in all non-percentage amounts. Percentage values never receive a currency symbol.
XD	Suppress row in account detail report	Suppresses the display of accounts on account detail reports or transaction detail reports. This is useful when a row includes multiple accounts that should not be listed in the account detail or transaction detail report.
X0	Suppress row if all zeros	Excludes a row from the report if all cells in that row are either empty or contain zeros. This print control is meaningful only when the option to suppress zero balance is not selected in the report definition.
B0	Leave zero columns blank	Leaves columns empty in the row that contain zero amounts.
XR	Suppress rollup	Suppresses a rollup. If the report uses a reporting tree, the amounts in this row are not rolled up into subsequent parent nodes.
SR	Suppress rounding	Prevents the amounts in this row from being rounded.
XT	Suppress row in transaction detail report	Suppresses the display of transactions in transaction detail reports. This is useful when a row includes multiple accounts that should not be listed in a transaction detail report.

Conditional Print Control codes

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

Conditional Print Control Code	Description
(none)	Clears the conditional print selection.
DR	Prints only the debit balances for this row.
CR	Prints only the credit balances for this row.

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.

Note

You must use the at sign (@) before each row code that you use in a calculation formula, or the number is read as a real number. For example, the formula @100+330 would add $330 USD to the amount in row 100.

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.

Row Code	Description	Format Code	Related Formulas/Rows/Unit	Print Control	Row Modifier	Link to Financial Dimensions
340	Cash at Beginning of Period			NP	BB	+Account=[1100:1110]
370	Cash at Beginning of Year	CAL	@100+@330	NP
400	Cash at Beginning of Period	TOT	340+370

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.

Note

If you use a label row code to reference a row, you must separate the column letter from the label with a period (.). For example, A.GROSS_MARGIN/A.SALES.

The @ character does not require the period separator. For example, @GROSS_MARGIN/@SALES.

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.

Note

When you reference a column in a calculation formula, the @ character is not required.

Row Code	Description	Format Code	Related Formulas/Rows/Unit	Print Control	Row Modifier	Link to Financial Dimensions
340	Cash at Beginning of Period			NP	BB	+Account=[1100:1110]
370	Cash at Beginning of Year	CAL	E=C.340	NP
400	Cash at Beginning of Period	TOT	340+370

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

This calculation	Creates this action
@130*.75	The value in row 130 is multiplied for each column by .75, and then the result is put in the current row of every column.
B=@130*.75	The calculation is performed only on column B.
A,B,C=(@100/@130)*.75	A=(A.100/A.130)*.75B=(B.100/B.130)*.75C=(C.100/C.130)*.75

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.

Note

To use this function, you must have a reporting tree that is associated with the row definition.

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.

Note

You cannot place the results of a calculation in any other column; the results must be in the column that contains the formula.

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