Data Format Options

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.