Search code examples
mdxcube

MDX multiple conditions filter on multiple dimensions

I am relatively new to MDX, about a month or so, and I am now writing MDX queries against a remote cube I also work on (Java 8 ActivePivot).

This query works when they are ORs, but when I add parentheses and change that first OR to an AND it works, kind of, but drops the measure resulting in the CDR and BOOK dimensions coming back correctly. Can someone with more MDX knowledge tell me what I have missed or do not yet know?

WITH 
Member [Measures].[CDR_Label] AS [CDR].[CDR].CURRENTMEMBER.MEMBER_CAPTION  
Member [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION 
SELECT 
  NON EMPTY 
  {[Measures].[CDR_Label],
  [Measures].[Book_Label],[Measures].[RepoRate.LATEST]} 
ON COLUMNS, 
NON EMPTY 
FILTER(
([CDR].CHILDREN,[Book].CHILDREN), (LEFT([CDR].[CDR].CURRENTMEMBER.MEMBER_CAPTION,1) = "8") AND 
(LEFT([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION,2) = "ST" OR 
RIGHT([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION,4) = "CIES")) 
ON ROWS 
FROM [TraderCube] 
WHERE ([Date].[Date].[2019-10-23])

enter image description here

And here the query before I changed it, with OR OR etc which works. I wanted the above to return only the CDR beginning with 8, which it does, but it loses the measure (and also breaks my headers - as this is actually being fired from inside an xll / custom Excel function, to a dll calling the remote cube using AdomdClient package as I am building a very custom plugin - Essentially the ability for users to use simple words/from enumerations presented and I then translate and construct MDX in C# to fire at the cube. Data comes back and I send 2D arrays back to Excel :)).

WITH 
Member [Measures].[CDR_Label] AS [CDR].[CDR].CURRENTMEMBER.MEMBER_CAPTION  
Member [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION SELECT NON EMPTY {[Measures].[CDR_Label],[Measures].[Book_Label],[Measures].[RepoRate.LATEST]} 
ON COLUMNS, 
NON EMPTY 
FILTER(
([CDR].CHILDREN,[Book].CHILDREN), 
LEFT([CDR].[CDR].CURRENTMEMBER.MEMBER_CAPTION,1) = "8" OR 
LEFT([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION,2) = "ST" OR 
RIGHT([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION,4) = "CIES") 
ON ROWS 
FROM [TraderCube] 
WHERE ([Date].[Date].[2019-10-23])

enter image description here

Solution

  • Aah...ignore me!

    I think I was tired and didn't see the pattern. Essentially, and I just reproduced this, if you ask for two dimension patterns that do exist with a measure, you get back the dimensions you expect (in my case 3, a real measure and two fake / labels for the dimensions to bring them all back as rows).

    If there are no matches it seems to return you back what you asked for without any measures as they do not exist for the dimension combination.