#PlanningAnalyticswithWatson

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
------------------------------

Hi 

You should not try to filter on the rows. Instead use a WHERE clause eg 

WHERE 
[}Clients].[}Clients].[SAMAccountName]

and that should do it.


------------------------------
Paul Simon
------------------------------

Paul, WHERE clause only provides context for the actual data, and therefore would only, at best, have effect if the data were Null/Empty vs Non Empty and if Robby had a NON EMPTY clause on the axis, which he hasn't. On top of that, you can not use an attribute in the WHERE clause, SAMAccountName is an attribute, so that wouldn't work either.
PS If it had been the SAMAccountName attribute he had been filtering on then the axis definition in the MDX should simply have gotten a FILTER function passing it the MEMBERS and then filtering the [}Clients].[}Clients].CurrentMember.Properties('SAMAccountName') by comparing that with the name he'd be after.

------------------------------
Hubert Heijkers
------------------------------

Original Message:
Sent: Fri April 30, 2021 02:17 PM
From: Paul Simon
Subject: RestAPI Filter Cellset Error

Hi 

You should not try to filter on the rows. Instead use a WHERE clause eg 

WHERE 
[}Clients].[}Clients].[SAMAccountName]

and that should do it.


------------------------------
Paul Simon
------------------------------

Hi Hubert

I was confused by the question. I thought that SAMAccountName was a place marker for the client that he was trying to query rather than an attribute, as the question seemed to be about retrieving data for just one Client.

Anyway, you seem to have resolved it. For some reason I did not see your reply before I posted mine.

Regards

Paul Simon

------------------------------
Paul Simon
------------------------------

Original Message:
Sent: Mon May 03, 2021 04:42 AM
From: Hubert Heijkers
Subject: RestAPI Filter Cellset Error

Paul, WHERE clause only provides context for the actual data, and therefore would only, at best, have effect if the data were Null/Empty vs Non Empty and if Robby had a NON EMPTY clause on the axis, which he hasn't. On top of that, you can not use an attribute in the WHERE clause, SAMAccountName is an attribute, so that wouldn't work either.
PS If it had been the SAMAccountName attribute he had been filtering on then the axis definition in the MDX should simply have gotten a FILTER function passing it the MEMBERS and then filtering the [}Clients].[}Clients].CurrentMember.Properties('SAMAccountName') by comparing that with the name he'd be after.

------------------------------
Hubert Heijkers
------------------------------


Original Message:
Sent: Fri April 30, 2021 02:17 PM
From: Paul Simon
Subject: RestAPI Filter Cellset Error

Hi 

You should not try to filter on the rows. Instead use a WHERE clause eg 

WHERE 
[}Clients].[}Clients].[SAMAccountName]

and that should do it.


------------------------------
Paul Simon