Hi All,
I'm working with an SSAS OLAP cube and am attempting to use the value entered in a value prompt to filter a a different dimension. I believe in order to do this I need to use the substitute function with a prompt macro to do so.
I'm having trouble getting the syntax right. The MUN of the dimension the value prompt is based on looks like:
[Project Cube].[Period].[Financial Year].[Fin Month]->:[M8].[[Period]].[Financial Year]].[Fin Month]].&[201512]]]
and the MUN of the dimension I'm attempting to pass this as a filter to looks like:
[Project Cube].[Project Master].[Call Closed Month].[Call Closed Month]->:[M8].[[Project Master]].[Call Closed Month]].&[Jun 2015]]]
This is what I've come up with so far:
#substitute('Period','Project Master',
substitute('Financial Year','Call Closed Month',
substitute('Fin Month','Call Closed Month',
substitute('Period','Project Master',
substitute('Financial Year','Call Closed Month',
substitute('Fin Month','Call Closed Month',
prompt('pPeriod' , 'MUN'))))))) #
My issue is I end up with 2 lots of the [Call Closed Month]] on the right hand side of the MUN where I only need this once. I haven't been able to work out how to only have this once.
Any pearls of wisdom that could be passed my way would be very much appreciated.
Thanks
Mickyo73