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, nonprinting 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. 
Nonprinting (NP) rows 
Nonprinting rows are helpful in many reports. If several calculations are required to obtain a value, these calculations can be hidden on the printed report. Nonprinting 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 sidebyside. 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:

In Report Designer, open the report definition.

Click the Settings tab.

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 
ED 

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 
ED 

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
In Report Designer, click Row Definitions, and then select a row definition to modify.
Doubleclick the appropriate Related Formulas/Rows/Units cell.
In the Reporting Unit Selection dialog box, in the Reporting tree field, select the tree that is assigned in the report definition.
Select a reporting unit, and then click OK. The restriction is displayed in the cell of the row definition.
Doubleclick 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
In Report Designer, open the row definition to modify.
Doubleclick the Print Control cell.
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.
Select any conditional print options.
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 
Nonprinting row 
Excludes the amounts in the row from being printed in the report and from calculations. To include a nonprinting column in a calculation, refer to the column directly in the calculation formula. For example, the nonprinting row 240 is included in the following calculation: 230+240+250. However, the nonprinting 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 nonpercentage 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
In Report Designer, click Row Definitions, and then open the row definition to modify.
Doubleclick the Format Code cell and select CAL.
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 yeartodate 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 higherlevel 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 datatype 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 nonprinting, 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 calculationtype 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, rightclick in the reporting tree definition, and then select Copy Reporting Unit Identifier (Hcode).
Restrict a calculation to a reporting unit
In Report Designer, click Row Definitions, and then open the row definition to modify.
Doubleclick the Format Code cell, and then select CAL.
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.