CODEWITHSUNDEEP
reporting-servicesssasmdx
Alex_404
Alex_404

Reputation: 409

Filter out a specific member of a dimension hierarchy in MDX query

I am pretty new to MDX and I have written a basic MDX query which returns a measure as well as full hierarchy of my product dimension:

SELECT {[Measures].[Amount]} on 0,
{
DESCENDANTS([ProductH].[ProductH], [ProductH].[ProductH].[Lvl4], LEAVES)
}
ON 1
FROM
(
  SELECT 
    {StrToSet('[AccountH].[AccountH].[Lvl1].&[TST17:0]',CONSTRAINED)} ON COLUMNS
  FROM
  (
    SELECT
      {StrToSet('[ProductH].[ProductH].[All]',CONSTRAINED)} ON COLUMNS
    FROM [Model]
  )
)

This returns the result set for my SSRS report which is basically the amount for all levels in product dimension for the user's chosen account hierarchy

Now, I want to exclude from this set the amounts under the 2nd level of account hierarchy with the specific name "Exclude". I've managed to add this filter using crossjoin and except - based on account node's unique name:

[AccountH].[AccountH].[Lvl2].&[TST17:0]&[TST17:1000]

but I want specifically to filter out based on the shown name, remove amounts where:

[AccountH].CURRENTMEMBER.MEMBER_CAPTION = "Exclude"

How do I filter out based on hierarchy node's name?

Upvotes: 0

Views: 1485

Answers (2)

Alex_404
Alex_404

Reputation: 409

I've figured it out with some help, "Exclude" account nodes are not unique in the hierarchy, even on the same level (level2 for me), so apperently removing them with EXCEPT() does not work. This works however:

Filter([AccountH].[AccountH].[Lvl2]].Members
      ,[AccountH].[AccountH].CURRENTMEMBER.MEMBER_CAPTION <> "Exclude")

since it checks the name. But apperently the filter function is a slow one so Im going with a cross join Account * Product and then removing the "Exclude" accounts in SSRS report

Upvotes: 0

Danylo Korostil
Danylo Korostil

Reputation: 1484

The Where clause must be enough:

select
...
from [Model]
where ({[AccountH].[AccountH].Members - [AccountH].[AccountH].[Exclude]})

Upvotes: 0

Related Questions