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 Atoti UI. 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 ADatabaseVersionAwareProperty {
/** For serialization. */
private static final long serialVersionUID = -7829974450124238675L;
/** Plugin type. */
public static final String PLUGIN_KEY = "DESK_NEXT_TRADE_DATE";
/**
* Constructs the plugin.
*
* @param name The name of the plugin.
*/
public DeskNextTradeDateProperty(final String name) {
super(name);
}
public DeskNextTradeDateProperty(final String name, final String expression) {
super(name, expression);
}
@Override
protected Object getValue(final Object desk, final IDatabaseVersion databaseVersion) {
// Warning: doing complex computations in this function will
// hurt performance, especially when doing big cross-joins.
//////////////////////////////////////////////////////////////
// Retrieve all the trade dates corresponding to this desk,
// using the condition query API.
final var query =
databaseVersion
.getQueryManager()
.listQuery()
.forTable("Trade")
.withCondition(BaseConditions.equal(FieldPath.of("Desk"), desk))
.withAliasedFields(List.of(AliasedField.fromFieldName("Date")))
.toQuery();
Temporal nextDate;
try (ICursor results = databaseVersion.getQueryRunner().listQuery(query).run()) {
//////////////////////////////////////////////////////////////
// Iterate over the result to find the next trade date
nextDate = null;
while (results.next()) {
// Read the first and only value, which is the date.
final 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 && 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 Atoti UI

By default, member properties do not display in the Atoti UI 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]