MDX left in SSRS report query

Question

I want to filter some Product codes from report in SSRS using LEFT() and I got problem.

This is piece of my query:

FROM ( SELECT ( { [Plan type].[Plan type].&[100000002] } ) ON COLUMNS 
FROM ( SELECT ( STRTOSET(@ProducersName, CONSTRAINED) ) ON COLUMNS 
FROM ( SELECT ( STRTOSET(@CalendarTime, CONSTRAINED) )  ON COLUMNS 
FROM ( SELECT ( LEFT([Product].[Product code],3) <> 'ALE')  ON COLUMNS --codes that I want to filter out
FROM ( SELECT ( LEFT([Product].[Product code],3) <> 'DBN' )  ON COLUMNS
FROM ( SELECT ( LEFT([Product].[Product code],3) <> 'DBR' )  ON COLUMNS
FROM [Sales]))))))

Problem is, I can't get it working because I get some syntax errors on those LEFT's with Product Codes. Rest works perfectly. Can you help me?

Answer 1

Please come out of the SQL world, this is MDX.

LEFT([Product].[Product code],3) makes no sense. What you needed probably would have to be LEFT([Product].[Product code].&[foo].membername, 3). But you have to iterate over the Product codes to filter in only those which match the criteria.

So instead of LEFT([Product].[Product code],3) <> 'ALE', the below is syntactically correct.

FILTER([Product].[Product code].MEMBERS AS prd,
       LEFT(prd.currentmember.name,3) <> 'ALE'
      )

You can accommodate the other conditions in trailing AND clauses.

......FROM(SELECT 
           FILTER([Product].[Product code].MEMBERS AS prd,
           LEFT(prd.currentmember.name,3) <> 'ALE' AND
           LEFT(prd.currentmember.name,3) <> 'DBN' AND
           LEFT(prd.currentmember.name,3) <> 'DBR'
          ) ON 0 FROM [Sales]

Of course, there are many more and much better ways to handle such kind of requirements, but this is just to drive home how to correctly do filtering in this case.