Data Format Options

Introduction

There are a number of different options to format data in Kepion. Take a look at the overview table below and see which one works best for your scenario. We will discuss each one in more depth in this article.

When multiple formatting options are used for a data-set, they are applied in the following order of priority (top has the highest priority):

• 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

You can define basic formatting at the application level. It sets the defaults in all Forms. In the example below, all zeros are displayed as '-' instead of '0', and negative numbers are displayed 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 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 on a cell in the Form, and choose Add Cell Attributes. Select the FORMAT tab. You can apply the format scope to different axes on the Form. 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.

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.

Just click on the gear icon  in the Dimension Member Hierarchy.

There are three 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 that the MDX Rule is created from.

/**************/
/* 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 format in the Dimension, this option is more complicated, but has more flexibility since the former option supports only three kinds of data formats (Currency, Standard, and Percent).

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.