SSAS MDX query, Filter rows by sales people

Question

I am learning how to query Cubes using MDX (SQL Server 2012) queries. I have been presented with a challenge. We have a hierarchy of sales people, a stored procedure returns a table with all sales people working under a manager. I have a classic sales cube where FactSales PK is invoice number and invoice line and a dimension for all our Sales people.

How can I filter the invoices where the sales person is in a table ?

Something like this but translated to MDX:

select * from sales where SalesPerson in (select SalesPerson from #salespeople)

The only way I see this could work is by writing the query dynamically and adding each salesperson in a filter, but that is not optimal in my opinion, we can have 200 or 400 people that we want to return sales from.

thanks!

Answer 1

If the dimension containing the sales people contains the hierarchy (who works for whom), you can resolve the challenge without using the stored procedure. Let's say your manager is named "John Doe" and your sales person hierarchy is named [Sales Person].[Sales Person]. Then just use

[Sales Person].[Sales Person].[John Doe].Children

in your query if you want to see sales for the people working directly for John, and you are done. In case you want to see John himself and everybody working for him directly or indirectly, you would use the Descendants function as follows:

Descendants([Sales Person].[Sales Person].[John Doe], 0, SELF_AND_AFTER)

This function has many variants, documented here.

In the Microsoft sample Adventure Works cube, where a similar hierarchy is called [Employee].[Employees], you could run the following query:

SELECT {
       [Measures].[Reseller Sales Amount]
       }
       *
       [Date].[Calendar].[Calendar Year].Members
       ON COLUMNS,
       Descendants([Employee].[Employees].[Jean E. Trenary], 0, SELF_AND_AFTER)
       ON ROWS
FROM [Adventure Works]

to see the sales of employees working directly or indirectly for "Jean E. Trenary".