MDX IIF to check if Element Exists

[MarenC]

Hi,

I am trying to write some MDX which will return a subset depending on who is logged in.

For Admin users it should just return all elements.
For Super Users it would also return all elements.
For Planning users it should only return the elements they created, i.e. added to the dimension.

The problem is if a Planner logs in and they have not created any added elements then the view throws an error and the subset is empty saying "Internal Error 500".

The MDX is as follows:

Code: Select all

STRTOSET(IIF([}ClientGroups].([}Groups].[Planning],STRTOMEMBER("[}Clients].[" + USERNAME + "]")) <> 'Planning',"TM1SubsetToSet([Employee].[Employee] , 'Added Employees' , 'public')", IIF( COUNT(TM1FILTERBYLEVEL(Descendants(STRTOMEMBER('[Employee].[UserAdded_' + STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("ClientName") + ']')),0)) = 0, "{[Employee].[Please Select]}","{TM1FILTERBYLEVEL(Descendants(STRTOMEMBER('[Employee].[UA_' + STRTOMEMBER('[}Clients].[' + USERNAME + ']').PROPERTIES('ClientName') + ']')),0)}")))

Basically when a user adds a new employee into the system it creates a consolidated element called "UserAdded_clientname" and the elements get added to this parent.

The count part of the MDX was an attempt to avoid the error but it errors because it cannot find the element to do the count.

I also tried the following but this basically does the same as count,

Code: Select all

VAL(STRTOMEMBER('[Employee].[UserAdded_' + STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("ClientName") + ']').PROPERTIES("member_weight")) > 0

Can anyone think of a way to avoid the error by checking if the logged on user has added any elements? If not I will have to create an attribute against the client.

Maren

[Paul Segal]

Can't help on the MDX I'm afraid, but you could consider changing the process: when a new client is added, add the "UserAdded_clientname" consolidation to the dimension with one element underneath of "NoElementsAdded_clientname", and then when they add one or more elements (I'm assuming this is a TI of some kind) check for "NoElementsAdded_clientname" and delete it.

[MarenC]

Hi Paul,

thank you for the response.

I don't think adding the child element will help because the issue occurs when the consolidation itself doesn't exist.

The consolidations only get created the first time a user adds in a new employee.

At the moment I am dealing with this via an attribute against the client but was interested to see if there was an MDX solution to this.

Maren

[declanr]

Hi,

Your issue stems from the fact that with a StrToMember function you are telling it that the member DOES exist; so when it doesn't find it the system will throw an error. A set can be "empty" but a member can't be.

The idea of doing a COUNT with IIF is a good one but the count needs to be built on a set that doesn't utilize any function where you are telling it the member does exist.
For example you can use a wildcard search of all members in the dim to see if any of them match the name of the element you are looking for:

Code: Select all

{TM1FILTERBYPATTERN(TM1SubsetAll([Employee].[Employee]),"UserAdded_" + STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("ClientName") )}

And then the overall would be something like:

Code: Select all

STRTOSET(
	IIF(
		[}ClientGroups].([}Groups].[Planning],STRTOMEMBER("[}Clients].[" + USERNAME + "]")) <> 'Planning',
		"TM1SubsetToSet([Employee].[Employee] , 'Added Employees' , 'public')", 
		IIF( 
			COUNT(
				{TM1FILTERBYPATTERN(TM1SubsetAll([Employee].[Employee]),"UserAdded_" + STRTOMEMBER("[}Clients].[" + USERNAME + "]").PROPERTIES("ClientName") )}
			) = 0, 
			"{[Employee].[Please Select]}",
			"{TM1FILTERBYLEVEL(Descendants(STRTOMEMBER('[Employee].[UA_' + STRTOMEMBER('[}Clients].[' + USERNAME + ']').PROPERTIES('ClientName') + ']')),0)}"
		)
	)
)

[MarenC]

Hi Declan,

I see what you did there, absolute fricking genius! Works like a charm.

Maren