By Kevin Hsu - Updated March 19, 2018
There are a few options to format data in Kepion. Take a look the overview and see which one works best for your scenario. We will discuss each one in this article.
|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 dataset, they are applied in the following order of priority (top one has the highest priority):
- MDX Rule*
- Dimension Member Formula*
- Form Cell Attributes
- Form General Formats
- Application Settings
*When Override SSAS Format is checked in form advanced settings, this format setting will be ignored.
You can define the basic formatting at the application level. It sets the defaults to all the forms. In the example below, all zeros are displayed as “-” instead of “0”, and negative numbers will display with parentheses.
Please note when you update the application settings, new formats will not apply to the existing forms automatically. You need to go to the individual forms and re-apply the styles (e.g. comma format or currency format).
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
See Enhanced Cell Attributes for details.
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 dimension member’s format when Member Formula is enabled. There are three options you can choose from: Currency, Standard and Percent. In the example below, we define [Gross Margin %]’s format is Percent.
Using MDX Rules
We can use MDX rule to define format from dimension member level. It will only apply to the model that the MDX rule is created from.
Compared with defining format in dimension, this option is more complicated, but has more flexibility since the former option supports three kinds of data format only (Currency, Standard, Percent).
Please note: Format defined in Dimension and using rules will be apply to the OLAP cubes directly.
Override SSAS Format
In a rare case, you may 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.
When exporting forms in Planning, only the format defined in forms will be exported. Therefore, if you have formatting defined for dimension members, for example: a gross margin % defined as a Percent in account dimension, it will not show as a percentage in exported .xml file.
If you want data format to be shown in SharePoint tables, Excel PivotTables, or any other third party reports, which means they are reading data from OLAP cubes directly, be sure the format are defined using MDX Rules or in dimension formulas.