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!

How do you identify these 200 - 400 sales people? Do they all report up to a specific person or work in the same department or something. Or is it just a list of individuals you have? Also, can you modify the cube at all, or do you have to do this in the query? — mmarie
It comes from a stored procedure that queries a table where we store the hierarchy of people, we just pass the user who wants to see the data, and it returns the sales people that work for him, It is very important that we find a way to security trim the cube for reporting — Alex

FrankPl FrankPl · Accepted Answer · 2014-01-28T20:06:24

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

SSAS MDX query, Filter rows by sales people

1 Answers