Dynamic Formatting Template Definition

Precedence rules

If you check more than one formatting section, the format settings in lower sections override the ones in upper sections in case of conflicts (for example: inconsistency between a member format and a level format).
Inside each section, if you check several Apply checkboxes, the format settings are applied from the lowest area to the upper area.

Common actions to several formatting sections

Checkboxes. Checkboxes enable you to specify the formatting sections (for example: Hierarchy Level Formatting section) or formatting items inside a section (for example: Base Level Format) that you want to apply to the reports.
Priority to column or row format. These options enable you to specify which one of the defined formats for rows or columns will be applied first in case of conflicts. When you click the Priority to Column option, the Column section is displayed first in the formatting section and the Row section is displayed in second position in the formatting section and the precedence rules apply.
Formatting. You can define the format you want by using the standard Microsoft Office Excel cell formatting features in the 1000 and Label cells. By default, all the format settings are applied and ALL is displayed in the Use column. You can then specify which settings of the defined format you want to apply, or define additional settings. To do so, directly enter the format settings in a Use cell, using a specific syntax - for example: (FontBold = Y) | (FontSize = 18), or double-click a Use cell and define the format settings in the dialog box that opens. In the dialog box, all the format items that you can override are displayed in red. Select one of them, click the Override Selected Item option, enter the format and click Override. To apply no formatting, leave the Use cell blank, or double-click the Use cell and select None in the dialog box.
Note If you have specified the Frame setting and if you have selected the Refresh only Expanded and Inserted Members option in the User Options, the frame setting may not be properly applied to the rows or colums that are displayed consequent to a member expansion or a member insertion. For more information about this option, see Expand or Member Entry Using the Insert Members Dialog Box.
Cell Protection: you can lock and unlock cells on which you define formats. To do so, right-click one or more cells and select EPM Lock Selected Cells or EPM Unlock Selected Cells . As a reminder of the standard Microsoft Excel behavior, locking cells will have no effect until you protect the worksheet.
Example You want to be able to enter data only in the inputable member cells and not in the other member cells of a report. Since the cells are unlocked by default in the EPM dynamic formatting sheet, you do not need to unlock the inputable member cells. You want to lock the cells for the other types of members. Therefore, use EPM Lock Selected Cells in the other member sections of the EPM dynamic formatting sheet, such as the Calculated Member Default Format section. Then, protect the worksheet. You will be able to enter data only in the inputable member cells. For more information on how to protect a worksheet, see Worksheet Protection.

Hierarchy Level Formatting section

This section enables you to define the formatting of levels of hierarchies.

Note In this particular section, the precedence rule is as follows: Base Level Format, then Formatting on Specific Level, then Default Format.

Actions specific to the Hierarchy Level Formatting section:

Apply Format to option: If a row or column axis contains more than one dimension, you can specify to which dimension you want the defined format to be applied; the inner dimension being the last dimension, the outer dimension being the first dimension in the axis.
In the Default Format area, you can define a default format for hierarchy levels.
In the Base Level Format area, you can define a specific format for base level members, meaning members that are not nodes and not calculated.
You can define formatting on specific levels. To add a new level, click Add New Level. To remove a level, click Remove Last Level.
- By default, Level 1, Level 2 and so on are the hierarchy levels just like they are defined in the structure of the source connection. The corresponding option is Structure Levels.
- However, you can define format settings for the levels that are displayed in the reports. To do so, use the Relative Levels option. For example, Country is the second level of the geographical hierarchy in the structure, but it is the first level that is displayed in a report.
  Note When you select the Relative Levels option, the Start formatting from the lowest level displayed option is displayed. This option enables you to invert the level order; the lowest level is displayed in first position.

Dimension Member/Property Formatting section

This section enables you to define and apply the formatting defined to the following types of members:

Custom members, calculated members, local members, blank members when using any connection.
Inputable members and changed members, when using a Planning and Consolidation connection.
Note An inputable member is a member on which you can enter data. A changed member is an inputable member on which you have already entered data but have not yet saved.

You can also define specific formatting:

on a specific member, custom member, blank member, property or local member: To do so, click the Add Member/Property cell and make your selection in the Select Member/Property dialog box.
Note
- When using a SAP BW (INA Provider) connection, you can define a specific formatting for "Total" members.
- In the Specific Selection tab, the name you must enter is the name of the local member as it appears in the Local Members tab of the Report Editor, or, for a grouping local member, as you have named it in the Member Sorting & Grouping area of the Member Selector.
- You can select the context member of a specific dimension as the property value in the Property Selection tab.
- To perfom a member selection by property, you can use a Microsoft Excel named range. See Member Selection by Properties and Named Ranges.
- For local connections, the custom member ID can be used as a property. You can therefore apply a specific formatting for all custom members which ID contains a specific text. To do so, use the property called "CustomMemberName" in the Property Selection tab. Any added custom member which ID contains the specified text will be applied the specified formatting.
on blank members: To do so, click the Add Member/Property cell, then click the Specific Selection tab and select Blank Members.
on a multiple item selection, that is a combination of any of these items: members, properties, local members. To do so, click the Add Member/Property cell and make your selection in the Select Member/Property dialog box. In the first three tabs, the Add to Multiple Selection button enables you to add a member, a property or a local member (depending on the tab you are in) to the multiple selection. The item is automatically added to the Multiple Selection Overview tab.
Caution
- To validate your multiple selection, you must select the Multiple Selection Overview tab and click OK. If you click OK in one of the three other tabs, the multiple selection is not taken into account.
- If you have selected the Refresh only Expanded and Inserted Members option in the User Options, not all the settings may be applied to the multiple selection. For more information on this option, see Expand or Member Entry Using the Insert Members Dialog Box.
Tip To remove an item from the selection in the Multiple Selection Overview tab, right-click in the tab and select Remove Selected Item(s).

Note multiple item selection including a local member: if the local member specified in the formatting sheet row does not exist in the report definition, the formatting defined in the row is simply not applied. If the local member exists in the report definition but is disabled, the formatting is applied.

Example You want to define a specific format for any data at the intersection of Actual and 2010 Total. Click the Add Member/Property. In the Member Selection tab, select Actual and click Add to Multiple Selection. Then, select 2010 Total and click Add to Multiple Selection. Select the Multiple Selection Overview tab and click OK. A new row is added to the formatting sheet, displaying: Actual||2010 Total. Define the format you want for the data.

To remove a member or property from the formatting sheet, click on the Remove cell.

Row and Column Banding section

This section enables you to choose one or two alternating formats that will be applied to data or headers or both. The chosen format in Odd Formatting and Even Formatting will alternate in rows or columns.

Note If you have defined formatting settings only for the odd rows/columns or only for the even rows/columns, and if you have selected the Refresh only Expanded and Inserted Members option in the User Options, the formatting settings may not be properly applied to the rows or colums that are displayed consequently to a member expansion or a member insertion. For more information on this option, see Expand or Member Entry Using the Insert Members Dialog Box.

Page Axis Formatting section

This section enables you to define the formatting for all the dimensions of the page axis. You can also define formatting settings on specific dimensions that are part of the page axis, using the Add Dimension button.