Using TM1 attributes from one cube to filter a second cube

[andy-budsol]

Guys.
The answer might be go back to the cube designer and get them to merge the cubes...but, just in case I've missed something I can do, here's the issue.

One cube (Sales) has a time dimension (Year and Week). On there is an attribute for SWLY (same week last year).
Second cube has same time dimension and holds Budgets.

I have a prompt for a Week which filters the first cube and also the second cube (using a macro and substituting the cube names) to get budget information for the corresponding week. User wants to show SWLY on the report. I can get the Caption of SWLY to display if the week prompt is also on the report. Is there a way of converting the Caption say '2016 Wk 16' into its corresponding MUN ([SalesBudget].[t_Weeks].[t_Weeks].[Week]->:[TM].[t_Weeks].[t_Weeks].[@MEMBER].[20555]). I can manually build it apart from the last part.

or is there a better/simpler way or is it back to the cube architect to resolve.

thanks

[New_Guy]

Hi,

Did you try the item(filter(hierarchy of the second cube, rolevalue('_memberCaption' of the hierarchy or attribute of the second cube = memberCaption of the week of the first cube derived by the SWLY attribute)) function to get the week from the secong cube?
Good luck
New guy

[andy-budsol]

Thanks.

I'll see if I can work that out, but if you are using 'item' don't you also need to provide an index position as part of that function?

How will this command perform if I can get it working?

[New_Guy]

Hi,
Item gets you only one member. Forgot to add zero at the end of the expression in my previous post, check for the exact syntax.
item(filter(hierarchy of the second cube, rolevalue('_memberCaption' of the hierarchy or attribute of the second cube = memberCaption of the week of the first cube derived by the SWLY attribute),0)
Good luck
New guy