Skip to main content

Custom Member Properties

Introduction

Members have standard properties, such as names or captions (which are defined in the MDX standard). They are also allowed to have non-standard properties. These non-standard properties are called custom member properties.

For example, in a hierarchy of the employees of a company, the employees (who are the members of the hierarchy) could have their phone numbers as properties.

Custom member properties can be displayed on a pivot table delivered by Excel or ActiveUI. They can also be used to filter the result.

Defining a Custom Member Property

A custom member property is an extended plugin implementing the ICustomProperty interface.

Here is an example of a custom member property that retrieves the date of the earliest trade for a given desk (additional snippet on the sandbox project):

@QuartetExtendedPluginValue(intf = ICustomProperty.class, key = DeskNextTradeDateProperty.PLUGIN_KEY)public class DeskNextTradeDateProperty extends ADatastoreVersionAwareProperty {/** For serialization. */
private static final long serialVersionUID = 3592828338042225441L;
/** Plugin type. */
public static final String PLUGIN_KEY = "DESK_NEXT_TRADE_DATE";
/**
* Constructs the plugin.
*
* @param name The name of the plugin.
*/
public DeskNextTradeDateProperty(String name) {
super(name);
}
public DeskNextTradeDateProperty(String name, String expression) {
super(name, expression);
}
@Override
public Object getValue(Object desk, IDatastoreVersion datastore) {
// Warning: doing complex computations in this function will
// hurt performance, especially when doing big cross-joins.
// In a distributed environment, the datastore is null
if(datastore == null){
return null;
}
//////////////////////////////////////////////////////////////
// Retrieve all the trade dates corresponding to this desk,
// using the condition query API.
final ICondition cond = BaseConditions.Equal("Desk", desk);
final IRecordQuery getProductNameQuery = new RecordQuery(
"Trade",
cond,
Arrays.asList("Date"));
final ICursor results = datastore.execute(getProductNameQuery);
//////////////////////////////////////////////////////////////
// Iterate over the result to find the next trade date
Temporal nextDate = null;
while (results.next()) {
// Read the first and only value, which is the date.
Object result = results.getRecord().read(0);
// Result might also be the N/A String.
if (result instanceof Temporal) {
final Temporal date = (Temporal) result;
ChronoUnit unit = null;
if (date.isSupported(ChronoUnit.MILLIS)) {
unit = ChronoUnit.MILLIS;
} else {
unit = ChronoUnit.DAYS;
}
if (nextDate == null || date.until(nextDate, unit) > 0) {
nextDate = date;
}
} else {
// Verifies that it was actually the N/A string.
assert result instanceof String && ((String) result).equals("N/A") : "Expected N/A String, but was: " + result;
}
}
return nextDate;
}
@Override
public String getType() { return PLUGIN_KEY; }
}

That custom member property can then be set for the desk in the cube definition:

StartBuilding.cube("DeskNextTradeDatePropertyCube")
.withDimension("Booking")
.withHierarchy("Desk")
.withLevel("Desk")
.withMemberProperty()
.withNameAndExpression("nextTradeDate")
.withPluginKey(DeskNextTradeDateProperty.PLUGIN_KEY)
.build();

Accessing a Custom Member Property via MDX

There are two ways to access custom member properties via MDX:

  • Using the PROPERTIES keyword:
DIMENSION PROPERTIES [Booking].[Desk].[nextTradeDate]
  • Using the Properties function:
[Booking].[Desk].[ALL].[AllMember].[DeskA].Properties('nextTradeDate')

You can also filter a query based on the custom member property:

WITH
Member Measures.NextTrade AS [Bookings].CurrentMember.Properties(
"nextTradeDate"
)
SELECT {
[Measures].[contributors.COUNT]
} ON COLUMNS,
Filter(
[Bookings].[Desk].Members,
[Measures].[NextTrade] = "2021-01-02"
) ON ROWS
FROM [DeskNextTradeDatePropertyCube]

Viewing a Member Property in Excel

When connecting to the ActivePivot Server from Excel, you can view member properties by hovering over the members.

Alternatively, you can add a column to your table and display the member properties as a column.

As of Excel Version 2101, a simple right-click on the pivot-table where a member displays will open a pop-up menu that contains the "Show Properties in Report" option. If the level corresponding to the member you right-clicked has defined custom properties, those properties will display and you can choose which member property to add to your report as a column.

Viewing a Member Property in ActiveUI

By default, member properties do not display in the ActiveUI PivotTable. If you add a member property via the PROPERTIES keyword to the MDX query, that member property will display when you hover over a cell that contains a member with that property:

SELECT NON EMPTY Hierarchize(
DrilldownLevel(
[Booking].[Desk].[ALL].[AllMember]
)
) PROPERTIES [Booking].[Desk].[Desk].[nextTradeDate] ON ROWS
FROM [DeskNextTradeDatePropertyCube]