MDX escape special character

[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]) + "\"")))}
 

[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])
    )
)}

[PavoGa]

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.

[ascheevel]

Thanks PavoGa, for my own MDX I rarely use the ".CurrentMemember.Properties", can you advise when it should be used?

[Wim Gielis]

This page could be useful:
https://cubewise.com/blog/tm1-attribute ... o-be-aware

[PavoGa]

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.

[lotsaram]

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.

[PavoGa]

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.