MDX escape special character

Post Reply
RonLat
Posts: 19
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1, Planning Analytics
Version: 2.0
Excel Version: 365

MDX escape special character

Post 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]) + "\"")))}
 
ascheevel
Community Contributor
Posts: 244
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: MDX escape special character

Post 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])
    )
)}
User avatar
PavoGa
MVP
Posts: 594
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX escape special character

Post 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.
Ty
Cleveland, TN
ascheevel
Community Contributor
Posts: 244
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: MDX escape special character

Post by ascheevel »

Thanks PavoGa, for my own MDX I rarely use the ".CurrentMemember.Properties", can you advise when it should be used?
Wim Gielis
MVP
Posts: 2965
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.14
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX escape special character

Post by Wim Gielis »

Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis
User avatar
PavoGa
MVP
Posts: 594
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX escape special character

Post 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.
Ty
Cleveland, TN
lotsaram
MVP
Posts: 3599
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX escape special character

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
PavoGa
MVP
Posts: 594
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX escape special character

Post 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. :?
Ty
Cleveland, TN
Post Reply