May 4, 2009 at 10:31 am
Hi,
My "Location Entity" dimension has the following "Geography" hierarchy:
Region > Country > Partner.
At Partner level, there's a "Is Active" property (attribute).
I'm trying to display Region members with the correct measure quantities filtered by the "Is Active" attribute at Partner level.
The following MDX expression (part) returns all Partner members filtered by this property:
FILTER
(
[Location Entity].[Geography].[Partner]
, [Partner].CurrentMember.Properties("Is Active") -- or not
)
What I'd like is to reflect the correct aggregated quantities at Region and Country levels corresponding to the Partner members filtered above.
I tryed using DRILLUPLEVEL, ASCENDANTS, ANCESTORS functions... but no result.
Any suggestion?
Thanks in advance.
Lybax
May 5, 2009 at 2:51 am
To be more precise, this is what I get with the following MDX query:
WITH MEMBER [Measures].[Active Quantity]
as 'IIF(([Partner].CurrentMember.Properties("Is Active") or [Partner].CurrentMember.Properties("Is Active") = null)
and not isempty([Measures].[Quantity]), [Measures].[Quantity], null)'
MEMBER [Measures].[Inactive Quantity]
as 'IIF(not [Partner].CurrentMember.Properties("Is Active")
and not isempty([Measures].[Quantity]), [Measures].[Quantity], null)'
SELECT
[Time].[YMWD].[Year].&[2006] ON 0,
NONEMPTY(
DRILLDOWNMEMBER(
DRILLDOWNMEMBER(
[Location Entity].[Geography].[Region].&[Asia],
{
[Location Entity].[Geography].[Region].&[Asia]
}
),
{
[Location Entity].[Geography].[Country]
}
), [Time].[YMWD].[Year].&[2006])
ON 1
FROM [Cube]
WHERE ([Measures].[Inactive Quantity]) -- Active or Inactive partner
--------- RESULTS -----------
1 INACTIVE PARTNER (TOTO - Partner = 201 quantity):
Locations Quantities for 2006
--------- -------------------
Asia 4486 (should be 201)
HK - Hong Kong 804 (should be 201)
TOTO - Partner 201
IN - India 86 (should be 0)
KR - Korea 281 (should be 0)
MY - Malaysia 223 (should be 0)
SG - Singapore 2892 (should be 0)
ACTIVE PARTNER ONLY:
Locations Quantities for 2006
--------- -------------------
Asia 4486 (should be 4285)
HK - Hong Kong 804 (should be 603)
TATA - Partner 182
TITI - Partner 421
IN - India 286
XXX - Partner 286
KR - Korea 281
YYY - Partner 72
ZZZ - Parnter 209
MY - Malaysia 223
AAA - Partner 223
SG - Singapore 2892
BBB - Parnter 2892
May 5, 2009 at 6:43 am
OK, I found the solution:
WITH MEMBER [Measures].[Active Quantity] AS SUM
(
{
FILTER
(
DESCENDANTS
(
[Location Entity].[Geography].CurrentMember,
[Location Entity].[Geography].[Partner]
)
, [Partner].CurrentMember.Properties("Is Active")
)
}
, [Measures].[Quantity]
)
SELECT
[Time].[YMWD].[Year].&[2006] ON 0,
NON EMPTY
(
DRILLDOWNMEMBER
(
DRILLDOWNMEMBER
(
[Location Entity].[Geography].[Region]
,[Location Entity].[Geography].[Region].&[Asia]
)
,[Location Entity].[Geography].[Country].&[HK]
)
) ON 1
FROM [Cube]
WHERE ([Measures].[Active Quantity])
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply