Numeric parameter in TI MDX

[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

[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

[Keith Would]

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

Syntax error on or before: " pPayrollPeriodNumber invalid operator "

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

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]

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);

[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.

[PavoGa]

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.