Page 1 of 1
Numeric parameter in TI MDX
Posted: Wed Jun 08, 2022 3:37 pm
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
Re: Numeric parameter in TI MDX
Posted: Wed Jun 08, 2022 3:50 pm
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
Re: Numeric parameter in TI MDX
Posted: Wed Jun 08, 2022 3:57 pm
by Keith Would
I then get the "Process ... saved with errors " message due to:
Syntax error on or before: " pPayrollPeriodNumber invalid operator "
Re: Numeric parameter in TI MDX
Posted: Wed Jun 08, 2022 4:47 pm
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);
Re: Numeric parameter in TI MDX
Posted: Wed Jun 08, 2022 4:56 pm
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
Re: Numeric parameter in TI MDX
Posted: Wed Jun 08, 2022 5:09 pm
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);
Re: Numeric parameter in TI MDX
Posted: Wed Jun 08, 2022 5:26 pm
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.
Re: Numeric parameter in TI MDX
Posted: Thu Jun 09, 2022 3:14 pm
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.