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 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, "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 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]