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.
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.
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.
how about you just find the thingie in the other dimension using the caption?
Prompt:
p_Dim1 = [Dim1]
caption([Dim2]) = paramDisplayValue('p_Dim1')