Hi Robby,
The interesting thing with querying information from a Cellset (in this case directly the cellset returned by the ExecuteMDX action) is that you are effectively querying information from the result of another query, one query expressed in MDX and the other using OData (select, expand, filter, orderby etc). What I'm really trying to say here that that might not be the most efficient way and that, if at all possible, I'd always use MDX in this case to express the complete query and only use OData queries to then either partially retrieve chunks of that result and/or augment that result by expanding navigation properties. In this case replacing [}Clients].[}Clients].MEMBERS with {[}Clients].[}Clients].[Admin]} would be way more efficient as well.
Having said that though I'd really want to explain why the $filter doesn't work as well and apologize for the cryptic, albeit purely technically correct, error message.
Your filter expression, the filter expression to be applied to each and every cell in the Cells collection, is "Members/Name eq 'Admin'". Now I know your intention from your problem description and therefore immediately notice that's not what this expression does. You are asking the system to do here is to grab the Name property of the Members property and compare that to 'Admin'. The thing however is that 'Members' identifies the collection of all Members making up the cell, in your case a collection of two members, the first one representing a group and the second one representing a client. A collection itself doesn't have a property named 'Name' and TM1's implementation doesn't support functions to be executed against collections either while the OData standard leaves room for it, hence the cryptic error.
If you really wanted to keep the MDX query as is I suppose what you wanted to do, and again I'm not suggesting you should (read: update the MDX instead;-), is filter based on the fact that one of the members has the name 'Admin' for which you could use a lambda function. And since a group can have the same name as client, and typically would in the case of 'Admin' I'd suggest you'd verify based on the unique name in that case as in:
https://server:port/api/v1/ExecuteMDX?$expand=Cells($filter=Members/any(e:e/UniqueName eq '[}Clients].[}Clients].[Admin]');$select=Value,Members;$expand=Members($select=Name,Attributes))
Hope that helps!
------------------------------
Hubert Heijkers
------------------------------
Original Message:
Sent: Thu April 29, 2021 11:57 AM
From: Robby Meyers
Subject: RestAPI Filter Cellset Error
I have been working with the RestAPI to integrate our SOX Account Control System with TM1 and so far have been able to supply them with what they need to get TM1 Accounts and attributes for quarterly review. They have asked if it is possible to filter for a single user in some cases so I tried to add a filter to my url for the returned cellset but Im getting "Bound functions are not currently supported"
Working URL
https://server:port/api/v1/ExecuteMDX?$expand=Cells($select=Value,Members;$expand=Members($select=Name,Attributes))
MDX
{
"MDX": "SELECT {
[}Groups].[}Groups].[ADMIN]
} DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0,
[}Clients].[}Clients].MEMBERS DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY,
[}Clients].[}Clients].[SAMAccountName] ON 1
FROM [}ClientGroups]"
}
Updated to add filter to Cells for a single name
https://server:port/api/v1/ExecuteMDX?$expand=Cells($filter=Members/Name eq 'Admin';$select=Value,Members;$expand=Members($select=Name,Attributes))
Is this a known issue or do I have my url wrong? My workaround is modify the MDX to pull back a single member instead of the entire }Clients dimension.
#PlanningAnalyticswithWatson