I am attempting to create a dynamic YTD column in my crosstab, but the issue I am running into is the fact that for my MDX command periodsToDate I have to pull members from two different hierarchies.
Here is the expression I am working with: periodsToDate([Fiscal Year],#prompt('pClosingMonth','token',[Last Closed Fiscal Mo YTD]')#)
I have been trying to figure out how to manipulate that last part of the function to allow the member value from the other hierarchy without it knowing it is from there..make sense???
Any suggestions on how I can create this YTD if I am unable to use this periodsToDate function?
Thanks!
You'll need to find the matching member in the hierarchy you're referencing in the periodsToDate function. There are a few ways of doing that. Can you give some more details on the structure of your cube, and the members you're using?
If the Last Closed Fiscal Mo YTD is a calculated member from inside your cube, you could try using the linkMember function.
If you're prompting for years from a different hierarchy, you could use macro functions to substitute the path of the MUN to repoint it to the fiscal year.
You could also use the filter function to find matching members:
filter([Fiscal Year], caption(currentMember([Fiscal Hierarchy]) = '2012')