MDX query for hierarchical inclusion/exclusion

Question

I'm trying to craft an MDX query to answer a question of the form:

Show me all records in 2008, except those in November, unless they occurred on November 17th.

The query has to be generalized to working with more than just three clauses and should not be date specific. (I'm asking using dates as an example because it's easy to understand - I'm interested in generalizing my MDX for different hierarchies.

This is the closest I've gotten so far:

SELECT 
NON EMPTY {     [Measures].[Session Count] } ON COLUMNS, 
NON EMPTY {     ([SessionIDs].[Session ID].Children) } ON ROWS 
FROM (
      SELECT 
            Union(
                  Descendants([Start Date].[Date].Year.[2008], , LEAVES), 
                  Descendants([Start Date].[Date].[Date].&[20081117], , LEAVES)
            )
            -
            Descendants([Start Date].[Date].[Month].&[2008]&[11], , LEAVES)
             ON COLUMNS
      FROM [ADM]  
) ;

The problem is that the secondary inclusion (Nov 17, 2008) is eliminated by the exclusion. I've tried maintaining duplicates in the first union, however Exclude (minus operator) eliminates all copies of members that match.

Edit:

Here is another example to help in understanding what I mean when I ask for a "generalizable" solution.

Consider a mapping application where a user can select to see data in geographical regions of their choice. They choose these regions by zooming in and out of the map, and selecting/deselecting quadkeys. Quadkeys map neatly to a hierarchy, which is typically somewhere between 18 and 23 levels deep.

A valid use case would then be:

Select QK0
Deselect QK002
Select QK0021230
Deselect QK002123033201 and QK002123033202

In words (and completely fabricating the place names that map to these quadkeys), this would be something like: Show me all of the data for North America but not data for USA unless that data is for New York State, but not in Albany or Glens Falls.

So the same query structure that worked for dates, above, must work for mapping or any other hierarchical set. It is also worth noting that I'm constructing this MDX on the fly in response to user actions.

Benoit Benoit · Accepted Answer · 2012-07-18T21:29:26

In the subselect you just need to put this on the column:

{Except([Start Date].[Date].Month.Members, {[Start Date].[Date].[Month].&[2008]&[11]}), [Start Date].[Date].[Date].&[20081117]}

I assumed that you have a Month level. The first part of the expression will return all the monthes but November 2008, then November 17 2008 is added.

EDIT:

For your second example you can write something like this:

Except(all the cities in north america, 
        Except(all the cities in USA, 
                Except(all the cities in New York state,
                        {Albany , Glens Falls})))

You can retrieve the cities in the North America, in the USA and in New York state with the Descendants function and the LEAVES flag.

MDX query for hierarchical inclusion/exclusion

2 Answers