Page 1 of 1

MDX escape special character

Posted: Tue Dec 06, 2022 2:49 pm
by RonLat
I have a filter on the dimension [investmentID], that shows me all Investmentelements with an attribute "creation_date" = "2022".

Code: Select all

{FILTER(TM1SubsetAll([investmentID]), ([investmentID].CURRENTMEMBER.PROPERTIES("creation_date") = "2022"))}
I can get the year 2022 from a parameter cube that shows me the current year in a string element. The problem is that I can not use simply the 2022 but "2022". So I need somehow to escape the " character. A simple \" does not work here. What is the correct method for escaping the " character in this situation?

Code: Select all

 {FILTER(TM1SubsetAll([investmentID]), ([investmentID].CURRENTMEMBER.PROPERTIES("creation_date") = 
STRTOMEMBER("\"" + [SysParameters].([Parameters].[sCurrentYear], [sysParameterMSR].[sDate]) + "\"")))}
 

Re: MDX escape special character

Posted: Tue Dec 06, 2022 3:02 pm
by ascheevel
If you're filtering by an attribute string, you don't need to use StrToMember, the below should work assuming no typos on my part.

Code: Select all

{FILTER(
    TM1SubsetAll([investmentID])
, 
    ([investmentID].CURRENTMEMBER.PROPERTIES("creation_date") = 
    [SysParameters].([Parameters].[sCurrentYear], [sysParameterMSR].[sDate])
    )
)}

Re: MDX escape special character

Posted: Tue Dec 06, 2022 3:47 pm
by PavoGa
ascheevel wrote: Tue Dec 06, 2022 3:02 pm If you're filtering by an attribute string, you don't need to use StrToMember, the below should work assuming no typos on my part.

Code: Select all

FILTER(
    TM1SubsetAll([investmentID]), 
    [investmentID].[creation_date] = 
    [SysParameters].([Parameters].[sCurrentYear], [sysParameterMSR].[sDate]
    )

This can be refined and shortened even further to the above. Note the "{}" do not have to be used because FILTER returns a set, even if it only returns a single member.

Re: MDX escape special character

Posted: Tue Dec 06, 2022 4:32 pm
by ascheevel
Thanks PavoGa, for my own MDX I rarely use the ".CurrentMemember.Properties", can you advise when it should be used?

Re: MDX escape special character

Posted: Tue Dec 06, 2022 8:37 pm
by Wim Gielis

Re: MDX escape special character

Posted: Tue Dec 06, 2022 10:12 pm
by PavoGa
ascheevel wrote: Tue Dec 06, 2022 4:32 pm Thanks PavoGa, for my own MDX I rarely use the ".CurrentMemember.Properties", can you advise when it should be used?
After reading the link Wim provided, I have to say it makes a good point about not using the shortcut. I have used both the cube query and the short cut although most of the time I've used the shortcut syntax. Will probably adjust that going forward as it is more clear using the cube query.

Re: MDX escape special character

Posted: Wed Dec 07, 2022 9:24 am
by lotsaram
PavoGa wrote: Tue Dec 06, 2022 10:12 pm After reading the link Wim provided, I have to say it makes a good point about not using the shortcut. I have used both the cube query and the short cut although most of the time I've used the shortcut syntax. Will probably adjust that going forward as it is more clear using the cube query.
Also once we get v12 the TM1 specific shortcut syntax for attributes and subsets will no longer be valid.

Re: MDX escape special character

Posted: Thu Dec 08, 2022 3:27 pm
by PavoGa
lotsaram wrote: Wed Dec 07, 2022 9:24 am
PavoGa wrote: Tue Dec 06, 2022 10:12 pm After reading the link Wim provided, I have to say it makes a good point about not using the shortcut. I have used both the cube query and the short cut although most of the time I've used the shortcut syntax. Will probably adjust that going forward as it is more clear using the cube query.
Also once we get v12 the TM1 specific shortcut syntax for attributes and subsets will no longer be valid.
Hmmm. Going to be fun for a lot of older code. :?