MDX query for SQL equivalent function IN

I need help in writing MDX query which will return the count of Sales for differnt SalesPerson. Something equivalent to SQL query

Select Sales_Count from Sales where SalesPersonId IN (6200, 4367, 87650, 2222, 18, 334, 9090).

There are hundred thousands of SalesPersons, so is there any way i can run the MDX Query for a batch of SalesPersonId s. One more problem is the SalesPersonId s are not consecutive..Please can anyone help me solve this problem.

How are your user's selecting which sales persons to return?

B.

|||

Just place your MDX strings for each ID you want to be returned in the ROWS() part of the MDX.

SELECT { [Measures].[SomeCount] } ON COLUMNS,

{ [Heir].[ID1], [Heir].[ID2], [Heir].[ID3], [Heir].[ID4] } ON ROWS

FROM [Cube]

Will return:

[Heir].[ID1] 1259

[Heir].[ID2] 51454

[Heir].[ID3] 65514

[Heir].[ID4] 124

|||

That is one of many ways to handle this problem. It just depends on what exactly you are wanting to have returned and how you intend to assemble your list of Sales Reps.

B.

|||True, I just supplied the exact syntax for the SQL statement provided.|||If you have a (front-end) Sql client that doesn't expose value hierachies to the user, and hence rely on users typing in the literals. You may consider use the class of functions that convert strings to member/tuple/set; so it will not result into an invalid member error when your client translates sql to mdx.

select city, profit from sales where city in ('Redwood City', 'Fremont') group by city;
==>
select [Measures].[Profit] on columns,
StrToSet("{[Customers].[Redwood City], [Customers].[Fremont]}") on rows from sales

but if the user typed RedwoodCity instead or Redwood City is not a valid member, the query wouldn't fail, and would still return results for Fremont.

That may be why Brian was asking how the IN list is constructed.