Kepion offers a number of different options to format data. Take a look at the table below to see which option works best for your case. We will discuss each one in more depth in this article.
| Formatting Option | Description | Scope |
|---|---|---|
| Application Settings | Define the default display format for currency symbol, zeros, and negative numbers. | All Forms |
| Form General Formats | Define the display format in the Form by column/row/cell. | Only the Form with format edits |
| Form Cell Attributes | Define the display format for a scoped dataset. | Only the Form with format edits |
| In Dimension | Define data format by dimension members. | OLAP cubes |
| MDX Rule | Most flexible way to format data. You can specify the style and decimal points. | OLAP cubes |
When multiple formatting options are used for a data set, they are applied in the following order of priority (highest to lowest):
- MDX Rule*
- Dimension Member Formula*
- Form Cell Attributes
- Form General Formats
- Application Settings
*When Override SSAS Format is checked in a Form's Advanced Settings, this format setting will be ignored.
Application Settings
In the Settings node under Application, you can define basic formatting for the Application as a whole. All zeros are displayed as '-' instead of '0', and negative numbers are displayed with parentheses, in the example below.
Note: When you update the Application settings, new formats will not automatically apply to your existing Forms. You need to go to the individual Forms and re-apply the styles (e.g., Comma Style or Currency Style).
Form General Formats
You can select a range of cells or the entire Form to apply a data format style (e.g., Comma Style, Currency Style, and Define Decimal Points).
Form Cell Attributes
You can define advanced Form formatting through the use of Cell Attributes. In the example below, we have three Cell Attributes, each highlighting the cells to a certain color.
Right-click a cell in the Form, and choose Add Cell Attributes.
Go to the Format tab and select Enable formatting. Design the scoped axes and/or data area as desired. In the example below, we want to apply the blue formatting to both the column region and the data region.
Notice that you can also give a specific height/width as part of a Cell Attribute. This feature can be quite powerful when you are trying to display data on a Form with varying widths. With the widths defined in Cell Attribute, you can always ensure the right height/width is applied.
Format in Dimension
Account and Scenario dimensions support Member Formula. To learn more about this feature, refer to Kepion Modeler Guide on Dimension Member formatting. You can define a Dimension Member’s format when Member Formula is enabled.
In a hierarchy, select the 
for the target Member.
There are three Format options you can choose from: Currency, Standard, and Percent. In the example below, we define [Product GM %]’s format as Percent.
Using MDX Rules
We can use MDX Rules to define format from Dimension Member level. It will only apply to the Model in which the Rule has been created.
/**************/
/* FORMATTING */
/**************/
Format_String({[Account].[Account].AllMembers}) = "#,##0";
Format_String({[Account].[Account].[OPERATING MARGIN]}) = "Percent";
Format_String({[Account].[Account].[Gross Margin %]}) = "Percent";
-- Variance
[Scenario].[Scenario].[Variance $] =
[Scenario].[Scenario].[Plan] - [Scenario].[Scenario].[Prior Year];
[Scenario].[Scenario].Variance %] =
(IIF([Scenario].[Scenario].[Prior Year] = Null
,Null
,([Scenario].[Scenario].[Plan] - [Scenario].[Scenario].[Prior Year])
/ [Scenario].[Scenario].[Prior Year]
));
Format_String({[Scenario].[Scenario].[Variance $]}) = "#,##0";
Format_String({[Scenario].[Scenario].[Variance %]}) = "Percent";
--EBITDA
[Account].[Account].[EBITDA] =
[Account].[Account].[REVENUES] - [Account].[Account].[OPERATING EXPENSES]
+ [Account].[Account].[Depreciation and Amortization];
Compared with defining the format in the Dimension, this option is more complicated, but has more flexibility since the former option supports only three kinds of data formats.
Note: Formats defined in Dimensions and formats defined using Rules will apply to OLAP cubes directly.
Override SSAS Format
In a rare cases, you may want to use MDX Rules to format data at the cube level, but would like to have a special format in a Form. To do that, you can check the Override SSAS format option, and apply formats directly in the Form.
Exporting Formatting
When exporting Forms in Planning, only the format defined in the Form will be exported. Therefore, if you have formatting defined for Dimension Members (e.g., a Gross Margin % defined as a Percent in the Account Dimension) it will not show as a percentage in the exported .xml file.
If you want your data format to be shown in SharePoint tables, Excel PivotTables, or any other third party reports (which means they are reading data from the OLAP cubes directly) be sure the formats are defined using MDX Rules or Dimension formulas.