I need an MDX cross join w/ no "all" members

Folks:

In a report - I need to rollup measures across two hierarchies.  Further - each hierarchy contains a code and a description I need to put on the report.  If I do a cross join between the two using:

SELECT NON EMPTY
     {
                 various measures
      }
ON COLUMNS,
NON EMPTY
     {
          (    
                [Request Code].[BK Request Code].[BK Request Code].ALLMEMBERS *
                [Request Code].[Request Code Description].[Request Code Description].ALLMEMBERS *
                [Derived Request Code].[BK Derived Request Code].ALLMEMBERS  *
                [Derived Request Code].[Derived Request Code Name].ALLMEMBERS
               
          )
      }
on rows
from yada yada yada....

I get 4 lines of result for each 1 line I want.  The second code and description come out as

all  all
all  Description
Code all
Code Description.

I am only interested in the last line - where both the code and the description are filled in.  How can I do a "limited cross join" where I do not get the "all" results?
jflannerAsked:
Who is Participating?
 
r_guerreiroConnect With a Mentor Commented:
Try to use the except function to remove members from a set.
SELECT NON EMPTY 
     { 
                 various measures
      } 
ON COLUMNS, 
NON EMPTY 
     { 
          (     
                EXCEPT([Request Code].[BK Request Code].[BK Request Code].ALLMEMBERS, [Request Code].[BK Request Code].[BK Request Code].[ALL]) * 
                EXCEPT([Request Code].[Request Code Description].[Request Code Description].ALLMEMBERS, [Request Code].[Request Code Description].[Request Code Description].[All]) *
                EXCEPT([Derived Request Code].[BK Derived Request Code].ALLMEMBERS, [Derived Request Code].[BK Derived Request Code].[ALL])  * 
                EXCEPT([Derived Request Code].[Derived Request Code Name].ALLMEMBERS, [Derived Request Code].[Derived Request Code Name].[ALL])
                
          ) 
      } 
on rows

Open in new window

0
 
jflannerAuthor Commented:
Worked like a champ - cut and paste and I'm done.

Thanks!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.