Data Formatting

By - Apr 29, 2013

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.

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


Application Settings

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.


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


Exporting Formatting

When exporting forms in Planning, only the format defined in forms will be exported. However if you have formatting defined for dimension member, 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 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.