Page 1 of 1
Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 2:54 am
by Abinaya
Hi
When I reference using the Format " & F16 & " it's not working? What is the correct format?
Re: Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 4:38 am
by EvgenyT
"&I10&" is correct format, yeah.
Please post your mdx ...
ET
Re: Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 4:50 am
by Abinaya
{FILTER( {TM1SUBSETALL( [Customer] )},[Customer] .Group=" &F16&")}
When I hard code the the Group it works.
Re: Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 5:04 am
by EvgenyT
Abinaya wrote:{FILTER( {TM1SUBSETALL( [Customer] )},[Customer] .Group=" &F16&")}
When I hard code the the Group it works.
Ah ok. you gota use ' " & F16 &" ' ... Single, double, ampersand...
Because, I bet you when you look at your MDX string:
Abinaya wrote:{FILTER( {TM1SUBSETALL( [Customer] )},[Customer] .Group=" &F16&")} doesnt have ' ' around the value and MDX engine requires value to be in ' ' when you are filtering by attribute for example hence need for ' " & & " ' syntax
ET
Re: Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 5:17 am
by Abinaya
Sorry Still not working. Very strangely when I tried Text(F16,"") in the cell it worked.
Re: Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 5:26 am
by EvgenyT
Abinaya wrote:Sorry Still not working. Very strangely when I tried Text(F16,"") in the cell it worked.
So you tried {FILTER( {TM1SUBSETALL( [Customer] )},[Customer].[Group]= '"&F16&"')} ?
see simple example below
Re: Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 5:33 am
by Abinaya
Worked. Sorry for the confusion.
Re: Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 5:37 am
by Abinaya
Thanks
Re: Reference Parameter to MDX Query from a Excel Cell
Posted: Fri Aug 09, 2013 5:39 am
by EvgenyT
All good.
' " & Cell Reference &" ' is a trick to use
ET