Numeric parameter in TI MDX

Post Reply
Keith Would
Posts: 10
Joined: Tue Dec 06, 2016 11:24 am
OLAP Product: TM1
Version: 10.3.0
Excel Version: 2010

Numeric parameter in TI MDX

Post by Keith Would »

Hello

Code: Select all

pPayrollPeriodNumber=114;

MDXRef='{FILTER([Year for Payroll Total].[Year for Payroll Total].MEMBERS , 
            ([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Year") = "'|CellGetS('Parameter','Parameter','Payroll year')|'")
            AND
	  #(VAL([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Number")) <= 114)
	    (VAL([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Number")) <= "'|pPayrollPeriodNumber|'")
            )}';
SubsetMDXSet(DimNameSource5,SubNameSource,MDXRef);
The hashed out (VAL line works when I run the TI process to create a cube view.
When I try to change it to work off a parameter it doesn't work. Could someone help with what is needed around the pPayrollPeriodNumber in the (VAL line.

Thanks
Keith
User avatar
Steve Rowe
Site Admin
Posts: 2286
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Numeric parameter in TI MDX

Post by Steve Rowe »

Don't you just need to drop the double quotes ?

(VAL([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Number")) <= '|pPayrollPeriodNumber|' )

Your testing vs a number so no need for the quotes
Technical Director
www.infocat.co.uk
Keith Would
Posts: 10
Joined: Tue Dec 06, 2016 11:24 am
OLAP Product: TM1
Version: 10.3.0
Excel Version: 2010

Re: Numeric parameter in TI MDX

Post by Keith Would »

I then get the "Process ... saved with errors " message due to:

Syntax error on or before: " pPayrollPeriodNumber invalid operator "
Keith Would
Posts: 10
Joined: Tue Dec 06, 2016 11:24 am
OLAP Product: TM1
Version: 10.3.0
Excel Version: 2010

Re: Numeric parameter in TI MDX

Post by Keith Would »

That bit worked fine Mark, it was the next bit.

This works.
I needed single quotes around the number defined in the parameter and had to remove the double quotes as Steve suggested.
I thought that I had tried every combo, but obviously not that one!

Thanks for looking.

Code: Select all

pPayrollPeriodNumber='114';

MDXRef='{FILTER([Year for Payroll Total].[Year for Payroll Total].MEMBERS , 
            ([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Year") = "'|CellGetS('Parameter','Parameter','Payroll year')|'")
            AND
		#(VAL([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Number")) <= 114)
		(VAL([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Number")) <= '|pPayrollPeriodNumber|')
            )}';
SubsetMDXSet(DimNameSource5,SubNameSource,MDXRef);
Mark RMBC
Community Contributor
Posts: 284
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Numeric parameter in TI MDX

Post by Mark RMBC »

Hi keith,

I deleted my comment as I knew it was wrong. It has been a long day!

I was going to suggest you numbertostring pPayrollPeriodNumber but I think you are now saying it works ok.

regards,

Mark
Keith Would
Posts: 10
Joined: Tue Dec 06, 2016 11:24 am
OLAP Product: TM1
Version: 10.3.0
Excel Version: 2010

Re: Numeric parameter in TI MDX

Post by Keith Would »

I'm glad you posted your new comment Mark.

When I tried to substitute my typed in parameter for a cube based one, it stopped working again.

But the NumberToString that you suggested made it work, so thank you!

This seems to be rather bizarre converting numbers to strings and back to numbers mind you.

Code: Select all

#pPayrollPeriodNumber='114';
pPeriod=CellGetS('Parameter','Parameter','Payroll year')|CellGetS('Parameter','Parameter','Payroll period');
pPayrollPeriodNumber=NumberToString(ATTRN('Year for Payroll Total',pPeriod,'Number'));

MDXRef='{FILTER([Year for Payroll Total].[Year for Payroll Total].MEMBERS , 
            ([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Year") = "'|CellGetS('Parameter','Parameter','Payroll year')|'")
            AND
			#(VAL([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Number")) <= 114)
            (VAL([Year for Payroll Total].CURRENTMEMBER.PROPERTIES("Number")) <= '|pPayrollPeriodNumber|')
            )}';
SubsetMDXSet(DimNameSource5,SubNameSource,MDXRef);
User avatar
gtonkin
MVP
Posts: 1054
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.10; PAW 2.0.71
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Numeric parameter in TI MDX

Post by gtonkin »

This is sometimes a good case for the Expand() function.
No need to use NumberToString and fiddle with quotes.
Potentially easier to read too.
User avatar
PavoGa
MVP
Posts: 582
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: Numeric parameter in TI MDX

Post by PavoGa »

gtonkin wrote: Wed Jun 08, 2022 5:26 pm This is sometimes a good case for the Expand() function.
No need to use NumberToString and fiddle with quotes.
Potentially easier to read too.
You beat me to it.
Ty
Cleveland, TN
Post Reply