Some of pentaho mondrian dimension, based on our need, require dimension to be displayed in percentage format. This blog post will show you how to create a dimension with percentage display format.
This dimension little bit tricky. If we just convert the number into string, and add percent symbol, then this dimension will be stored as pure string, and when you sort this dimension, you will end up will wrong sequence. for example, 100% will appear before 20% since 20 is alphabetically greater than 100.
what we need are 2 separate column, 1 column store numerical representation of that percentage, and another column to store desired display. for example, dimension 100% will be stored as 100 at 1st column and ‘100%’ at second column. another example, 20% will be stored 20 at 1st column and ‘ 20%’ at secord column.
this is what I have in my dimension table:
next, we need to tell mondrian schema about our dimension table design. here example of my mondrian schema script:
<Dimension highCardinality="false" name="Depreciation Rate"> <Hierarchy name="Depreciation Rate" hasAll="true" allMemberName="All Depreciation Rate" allMemberCaption="All Depreciation Rate" primaryKey="id" primaryKeyTable="dim_asset"> <Table name="dim_asset" schema="public"/> <Level name="Depreciation Rate" column="tarifdepresiasi2" nameColumn="tarifdepresiasi" type="Numeric" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" table="dim_asset" ordinalColumn="tarifdepresiasi2"/> </Hierarchy> </Dimension>
plase note script above, we have a level with column set to tarifdepresiasi2 and nameColumn set to tarifdepresiasi, also ordinalColumn set to tarifdepresiasi2. it’s mean:
- this level will be sorted by tarifdepresiasi2.
- for sql selection and where clause, mondrian will use numeric syntax, and column tarifdepresiasi2 will be used.
- for display, or getName() function, column tarifdepresiasi will be used.
then, the result: