Skip to main content
Pentaho Documentation

Use Calculated Members

A calculated member is a dimension member that is resolved by calculating an MDX expression to return a value. The value is specified when the calculated member is defined. Calculated members can also be defined as measures.

These topics show you how to work with clauses and named sets within a schema.

Use Clauses to Create Measures

Suppose you want to create a measure whose value comes not from a column of the fact table, but from an MDX formula. One way to do this is to use a WITH MEMBER clause, like this:

WITH MEMBER [Measures].[Profit] AS '[Measures].[Store Sales]-[Measures].[Store Cost]',
   FORMAT_STRING = '$#,###'
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
  {[Product].Children} ON ROWS
FROM [Sales]
WHERE [Time].[1997] 

But rather than including this clause in every MDX query of your application, you can define the member in your schema, as part of your cube definition:

<CalculatedMember name="Profit" dimension="Measures">
<Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula>
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>

You can also declare the formula as an XML attribute, if you prefer. The effect is just the same.

<CalculatedMember name="Profit" dimension="Measures" formula="[Measures].[Store Sales]-[Measures].[Store Cost]">
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>

Note that the <CalculatedMemberProperty>, not <Property>, element corresponds to the FORMAT_STRING = '$#,###' fragment of the MDX statement. You can define other properties here too, but FORMAT_STRING is by far the most useful in practice.

The FORMAT_STRING property value can also be evaluated using an expression. When formatting a particular cell, first the expression is evaluated to yield a format string, then the format string is applied to the cell value. Here is the same property with a conditional format string:

<CalculatedMemberProperty name="FORMAT_STRING" expression="Iif(Value < 0, '|($#,##0.00)|style=red',
 '|$#,##0.00|style=green')"/>

For more details about format strings, see the MDX specification.

One additional calculated member property that is worth mentioning is DATATYPE. As with measures, setting datatype specifies how the calculated member is returned via XML for Analysis. The DATATYPE property of a calculated member can have values String, Integer, or Numeric.

<CalculatedMemberProperty name="DATATYPE" value="Numeric"/>

You can specify SOLVE_ORDER for the calculated member property. Solve order determines the priority of calculation in the event of competing expressions.

<CalculatedMemberProperty name="SOLVE_ORDER" value="2000"/>

You can make a calculated member or a measure invisible. If you specify visible="false" (the default is true) in the <Measure> or <CalculatedMember> element, user-interfaces such as JPivot will notice this property and hide the member. This is useful if you want to perform calculations in a number of steps, and hide intermediate steps from end-users. For example, here only Margin per Sqft is visible, and its factors Store Cost, Margin, and Store Sqft are hidden.

<Measure name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00" visible="false"/>
<CalculatedMember name="Margin" dimension="Measures" visible="false">
<Formula>([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]</Formula>
</CalculatedMember>
<CalculatedMember name="Store Sqft" dimension="Measures" visible="false">
<Formula>[Store].Properties("Sqft")</Formula>
</CalculatedMember>
<CalculatedMember name="Margin per Sqft" dimension="Measures" visible="true">
<Formula>[Measures].[Margin] / [Measures].[Store Cost]</Formula>
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>

Named Sets

The WITH SET clause of an MDX statement allows you to declare a set expression which can be used throughout that query.

WITH SET [Top Sellers] AS 
    'TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse Sales])'
SELECT 
    {[Measures].[Warehouse Sales]} ON COLUMNS,
    {[Top Sellers]} ON ROWS
FROM [Warehouse]
WHERE [Time].[Year].[1997] 

The WITH SET clause is very similar to the WITH MEMBER clause, and as you might expect, it has a construct in schema analogous to <CalculatedMember>. The <NamedSet> element allows you to define a named set in your schema as part of a cube definition. It is implicitly available for any query against that cube.

<Cube name="Warehouse">
...
<NamedSet name="Top Sellers">
<Formula>TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse Sales])</Formula>
</NamedSet>
</Cube>

SELECT 
    {[Measures].[Warehouse Sales]} ON COLUMNS,
    {[Top Sellers]} ON ROWS
FROM [Warehouse]
WHERE [Time].[Year].[1997] 
Warehouse Warehouse Sales
Treehouse Distribution 31,116.37
Jorge Garcia, Inc. 30,743.77
Artesia Warehousing, Inc. 29,207.96
Jorgensen Service Storage 22,869.79
Destination, Inc. 22,187.42

You can also define a named set as global to a schema.

<Schema>
<Cube name="Sales" ... />
<Cube name="Warehouse" ... />
<NamedSet name="CA Cities" formula="{[Store].[USA].[CA].Children}"/>
<NamedSet name="Top CA Cities">
<Formula>TopCount([CA Cities], 2, [Measures].[Unit Sales])</Formula>
</NamedSet>
</Schema>

A named set defined against a schema is available in all cubes in that schema. However, it is only valid if the cube contains dimensions with the names required to make the formula valid. For example, it would be valid to use [CA Cities] in queries against the [Sales] and [Warehouse and Sales] cubes, but if you used it in a query against the [Warehouse] cube you would get an error, because [Warehouse] does not have a [Store] dimension.