[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2415
  • Last Modified:

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?
0
jflanner
Asked:
jflanner
1 Solution
 
r_guerreiroCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now