Can dimensional functions be used inside a macro expression?

[ctrout1]

I am using a transformer OLAP cube as my data source and RS 10.2.1.  Often we will use the substitute function to find the same MUN in another cube, for example;

#substitute('Cube1','Cube2', prompt('Param_1','MUN'))#

However, now I would like to use the parent or ancestor of my prompt, so I tried this;

#substitute('Cube1','Cube2', ancestor(prompt('Param_1','MUN'),1))#

But I get the following error;

QE-DEF-0406
Unknown function 'ancestor' in macro expression.

Please let me know if anyone has any ideas?  Or if you think this is possible.

[MFGF]

Hi.

You can't use normal report functions within a macro - only macro functions. This is why you are getting the error you are seeing. Is it feasible to code the expression with the ancestor() prompt outside the macro?

eg

ancestor(#substitute('Cube1','Cube2', prompt('Param_1','MUN'))#,1)

MF.

[ctrout1]

MF,

Thank you for the feedback, I had a hunch that was the problem, but couldn't find anything that specifically said you couldn't.

I simplified my example below, my actual use case is using the substitute expression to jump from one dimension to another dimension within the same cube.  I would like to present the consumer with a list of members from one level, then I need to go up a level and use the substitution expression to find a similar member in another dimension.

Perhaps is there a way to use a separate data item for the prompt/ancestor step and then insert the MUN from that data item in my substitution expression?

On a side note, thank you all of your answers to other questions, I've utilize many of your responses over the years.

[Francis aka khayman]

how about you just find the thingie in the other dimension using the caption?

Prompt:
p_Dim1 =     [Dim1]

caption([Dim2]) = paramDisplayValue('p_Dim1')