Take the Employee dimension of Adventure Works Cube. I want to see a list of all Male Employees and the total Reseller-Sales they supervise. My MDX query looks like this
Select [Measures].[Reseller Sales-Sales Amount] on Columns,
non empty [Employee].[Employees].AllMembers on Rows
from [Analysis Services Tutorial]
where [Employee].[Gender].&[M]
and I get a result of
Reseller Sales-Sales Amount
All Employees $44,244,815.17
Ken J. Sánchez $44,244,815.17
Brian S. Welcker $44,244,815.17
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $39,562,401.78
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $172,524.45
ok so it gave me all the male employees, which is good, but the sales amounts are incorrect, it is only summing
the sales of the male underlings. Ken J. Sánchez actually has 80 million in sales under him, Stephen Y. Jiang has 63 million, etc... The Cube is not counting the female underlings sales. How do I include everything in the roll-ups, but only return the male supervisors?
Thanks
Todd Wilder
Select [Measures].[Reseller Sales-Sales Amount] on Columns,
non empty
Exists(
[Employee].[Employees].AllMembers
,[Employee].[Gender].&[M]
) on Rows
from [Analysis Services Tutorial]
There seems to be a slight twist to this, since Employee is a parent-child dimension. Apparently, using the parent-child hierarchy, a member can "exist" with an attribute like [Employee].[Gender].&[M] if any of its descendants are male. But a member of the key attribute hierarchy works as with regular dimensions. So LinkMember() can be used to map key attribute members to the corresponding parent-child members, like:
Select [Measures].[Reseller Sales Amount] on Columns,
non empty Generate(exists([Employee].[Employee].[Employee],
[Employee].[Gender].&[M]),
{LinkMember([Employee].[Employee].CurrentMember,
[Employee].[Employees])}) on Rows
from [Adventure Works]
In this case, there is a discrepancy between the 2 queries of a single (non empty) member - Amy E. Alberts (female):
Select [Measures].[Reseller Sales Amount] on Columns,
non empty exists([Employee].[Employees].Members,
[Employee].[Gender].&[M]) -
Generate(exists([Employee].[Employee].[Employee],
[Employee].[Gender].&[M]),
{LinkMember([Employee].[Employee].CurrentMember,
[Employee].[Employees])})on Rows
from [Adventure Works]
--
Reseller Sales Amount
All Employees $80,450,596.98
Amy E. Alberts $15,535,946.26
