User-specific settings for Cube-Lookup to determine Elements

[Lukas Meyer]

Hello,

first of all sorry for the maximum length title - I'll explain what I want to to achieve here

I have a User-Parameter Cube named PL_BENUTZEREINSTELLUNGEN having the }Clients dim (surprise!) and PL_BENUTZEREINSTELLUNGEN_Werte (value-dimension).

I want to access a Cube (PL_PERS_Stammdaten) having the dimensions
Jahr (year)
Monat (month)
PL_Werte (scenarios)
PERS_Kost_Struktur (department of employee)
PERS_Personal (employees)
PERS_Merkmale (measurements)

to find all employees (in dimension PERS_Personal) having in the measurements (dim PERS_Merkmale) a 1
This mdx-statement does as wished:

Code: Select all

{FILTER(
	{TM1SUBSETALL( [PERS_Personal] )},
	[PL_PERS_Stammdaten].(
		[Jahr].[2009],
		[Monat].[13],
		[PL_Werte].[fs],
		[PERS_Kost_Struktur].[8100],
		[PERS_Merkmale].[ZUORDNUNG])
	= 1)}

sadly I don't know the value of PERS_Kost_Struktur (department) - (I'd prefer to keep year and month in the parameters aswell) but my approach fails:

Code: Select all

{FILTER(
	{TM1SUBSETALL( [PERS_Personal] )},
	[PL_PERS_Stammdaten].(
		[Jahr].[2009],
		[Monat].[13],
		[PL_Werte].[fs],
		StrToMember("[PERS_Kost_Struktur].["+([PL_BENUTZEREINSTELLUNGEN].([}Clients].[Admin],[PL_BENUTZEREINSTELLUNGEN_Werte].[PERS_Kost_Struktur]))+"]"),
		[PERS_Merkmale].[ZUORDNUNG])
	= 1)}

Summarizing I want to get a dynamic subset, elements are determined by a value of a cube - but to access those I need to access yet another cube to get all dimensions values needed to access the first cube

thank you,
Lukas

[Steve Vincent]

Lukas, assuming you have a hierarchy in those dimensions you could pick the top level of that instead of the department they are in. As for the year / month, i think you can pick the value of a subset in MDX rather than "hard code" it. I'll have a look later and see if i have an example, but in the mean time you might find this link of use. Its got all sorts of handy TM1 stuff including a great section on MDX that has helped me in the past