Full MDX - not returning queries with StrToSet

[Wei Wang]

We are starting to leverage the Full MDX views more and have noticed that they don’t seem to work when using “StrToSet” to define the rows or columns. For example in Apliqo_Demo, we would see an error when running the MDX below:

SELECT
  NON EMPTY
   {StrToSet('{[Region].[Region].[1],[Region].[Region].[10],[Region].[Region].[2],[Region].[Region].[9]}')}
  ON COLUMNS ,
  NON EMPTY
   {[Account].[Account].[4],[Account].[Account].[40],[Account].[Account].[400],[Account].[Account].[4100],[Account].[Account].[4110],[Account].[Account].[4200],[Account].[Account].[4500],[Account].[Account].[4130],[Account].[Account].[4140],[Account].[Account].[5000],[Account].[Account].[5020],[Account].[Account].[5050],[Account].[Account].[60],[Account].[Account].[600],[Account].[Account].[6000],[Account].[Account].[6020],[Account].[Account].[6040],[Account].[Account].[6100],[Account].[Account].[620],[Account].[Account].[6200],[Account].[Account].[6210],[Account].[Account].[6220],[Account].[Account].[6230],[Account].[Account].[6240],[Account].[Account].[630],[Account].[Account].[6300],[Account].[Account].[6310],[Account].[Account].[6400],[Account].[Account].[6500],[Account].[Account].[660],[Account].[Account].[6610],[Account].[Account].[6620],[Account].[Account].[6700],[Account].[Account].[680],[Account].[Account].[6810],[Account].[Account].[6820],[Account].[Account].[6830],[Account].[Account].[6840],[Account].[Account].[6850],[Account].[Account].[6860],[Account].[Account].[6920],[Account].[Account].[80],[Account].[Account].[8000],[Account].[Account].[8010],[Account].[Account].[8020],[Account].[Account].[8030],[Account].[Account].[8040],[Account].[Account].[8500]}
  ON ROWS
FROM [General Ledger]
WHERE
  (
   [Version].[Version].[Budget],
   [Year].[Year].[2016],
   [Currency].[Currency].[Local],
   [Department].[Department].[1],
   [General Ledger Measure].[General Ledger Measure].[Amount],
   [Period].[Period].[Year]
  )

‌

It appears no MDX is being passed through but all works fine when we remove the “StrToSet” on the columns. We found a workaround if we wrap the StrToSet around IIF but this in term gives us some extra console error messages (although the view appears to render fine in Apliqo)

‌

[Scott Wiltshire]

That's a strange one. Are you sure that it's specifically to do with StrToSet versus unescaped quotes in the function itself?

[Wei Wang]

Good callout there Scott on the unescaped quotes. If I put “\” in from the quotes, it does seem to do the trick. It is interesting that the MDX parser handles MDX without the escape character if we have IIF inside the StrToSet (see below) but perhaps that is just a coincidence. We’ll remember to not be stingy with the '\” characters in the future!

SELECT
  NON EMPTY
   {StrToSet(IIF('1'='1','{[Region].[Region].[1],[Region].[Region].[10],[Region].[Region].[2],[Region].[Region].[9]}','{[Region].[1]}'))}
  ON COLUMNS ,
  NON EMPTY
   {[Account].[Account].[4],[Account].[Account].[40],[Account].[Account].[400],[Account].[Account].[4100],[Account].[Account].[4110],[Account].[Account].[4200],[Account].[Account].[4500],[Account].[Account].[4130],[Account].[Account].[4140],[Account].[Account].[5000],[Account].[Account].[5020],[Account].[Account].[5050],[Account].[Account].[60],[Account].[Account].[600],[Account].[Account].[6000],[Account].[Account].[6020],[Account].[Account].[6040],[Account].[Account].[6100],[Account].[Account].[620],[Account].[Account].[6200],[Account].[Account].[6210],[Account].[Account].[6220],[Account].[Account].[6230],[Account].[Account].[6240],[Account].[Account].[630],[Account].[Account].[6300],[Account].[Account].[6310],[Account].[Account].[6400],[Account].[Account].[6500],[Account].[Account].[660],[Account].[Account].[6610],[Account].[Account].[6620],[Account].[Account].[6700],[Account].[Account].[680],[Account].[Account].[6810],[Account].[Account].[6820],[Account].[Account].[6830],[Account].[Account].[6840],[Account].[Account].[6850],[Account].[Account].[6860],[Account].[Account].[6920],[Account].[Account].[80],[Account].[Account].[8000],[Account].[Account].[8010],[Account].[Account].[8020],[Account].[Account].[8030],[Account].[Account].[8040],[Account].[Account].[8500]}
  ON ROWS
FROM [General Ledger]
WHERE
  (
   [Version].[Version].[Budget],
   [Year].[Year].[2016],
   [Currency].[Currency].[Local],
   [Department].[Department].[1],
   [General Ledger Measure].[General Ledger Measure].[Amount],
   [Period].[Period].[Year]
  )

‌

[Scott Wiltshire]

fixed locally by escaping quote character within MDX string.