quote issue on a MDX TurboIntegrator function

Post Reply
sblot
Posts: 2
Joined: Thu Jan 19, 2023 3:51 pm
OLAP Product: Planning Analytics
Version: 2.0.82
Excel Version: Office 365

quote issue on a MDX TurboIntegrator function

Post by sblot »

Hello,

I would like to create a MDX subset in my TurboIntegrator process, with an attribute variable. But I have a quote issue.
Let's illustrate with an example:

***************************************************************

This code works well:

vMDX = '{FILTER(TM1SubsetAll([vDimension1].[vDimension1]), ([vDimension1].[vDimension1].CURRENTMEMBER.PROPERTIES("vAttribute1") = "vValue"))}';
vSubsetName1='zSubSet_' | vDimension1;
SubsetCreatebyMDX(vSubsetName1,vMDX);
SubsetMDXSet(vDimension1,vSubsetName1,'');

DataSourceType='SUBSET';
DatasourceNameForServer=vDimension1;
DatasourceDimensionSubset=vSubsetName1;

***************************************************************

Now, I would like to use a variable on my "vValue", so i wrote:
vMDX = '{FILTER(TM1SubsetAll([vDimension1].[vDimension1]), ([vDimension1].[vDimension1].CURRENTMEMBER.PROPERTIES("vAttribute1") = ' | vValue | ' )}';

But the result is wrong, because my code does not have the vValue between quotes. It generates: {FILTER(TM1SubsetAll([vDimension1].[vDimension1]), ([vDimension1].[vDimension1].CURRENTMEMBER.PROPERTIES("vAttribute1") = vValue))}

I guess there is a simple solution, but I don't have it. Do you have any clue?
Have a nice day.
sblot
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: quote issue on a MDX TurboIntegrator function

Post by burnstripe »

Change the syntax to this

vMDX = '{FILTER(TM1SubsetAll([vDimension1].[vDimension1]), ([vDimension1].[vDimension1].CURRENTMEMBER.PROPERTIES("vAttribute1") = "' | vValue | '" )}';

Notice the double quote before the single quote before vvalue and a double quote after the following single quote
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: quote issue on a MDX TurboIntegrator function

Post by gtonkin »

Another approach to consider is using Expand() - This makes these MDX queries easier to read by limiting all the quotes needed:

Code: Select all

vMDX = Expand('{FILTER(TM1SubsetAll([vDimension1].[vDimension1]), ([vDimension1].[vDimension1].CURRENTMEMBER.PROPERTIES("vAttribute1") = "%vValue%")}');
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: quote issue on a MDX TurboIntegrator function

Post by lotsaram »

+1 I would also always use Expand for such cases. It's just easier.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
sblot
Posts: 2
Joined: Thu Jan 19, 2023 3:51 pm
OLAP Product: Planning Analytics
Version: 2.0.82
Excel Version: Office 365

Re: quote issue on a MDX TurboIntegrator function

Post by sblot »

Thank you burnstripe, it is working well now.

I did not know this expand function. I will try it so, thanks for the tip.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: quote issue on a MDX TurboIntegrator function

Post by MarenC »

I did not know this expand function.
My advice, learn it!
User avatar
PavoGa
MVP
Posts: 616
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: Charleston, Tennessee

Re: quote issue on a MDX TurboIntegrator function

Post by PavoGa »

Always use EXPAND() as well. Makes parsing more complex MDX easier to read vs. concatenations to me.
Ty
Cleveland, TN
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: quote issue on a MDX TurboIntegrator function

Post by Wim Gielis »

Just pay attention to 2 things:
- Expand with numbers gives you a certain format with 3 decimal digits
- Expand of an Expand whereby the inner Expand resolves to something that contains a % character will cause you headaches.
Best regards,

Wim Gielis

IBM Champion 2024
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
Post Reply