Filter rows based on cell value

[grvbahal]

Hello All,

I have a employee roster cube (flat cube with just the list of employees and measures) and one of the measures is department. I also have a department hierarchy in a dimension. The roster stores the leaf level department values in a measure.

I am trying to write a MDX that filters the list of employees based on the department value parameter. I can easily do that if the department value parameter passed is a leaf department (using combination of Filter by level and Filter by value). Trying to figure out the MDX if the value of the department passed is a consolidation. In that case I want to show all employees whose department value is a leaf level descendant of the department value passed.

Any help is appreciated.

Note - I can always introduce the department dimension in my employee roster cube and use zero suppression to solve this but am trying to keep the employee roster cube flat.

Thanks

[lotsaram]

Note - I can always introduce the department dimension in my employee roster cube and use zero suppression to solve this but am trying to keep the employee roster cube flat.

Then why not have a virtual rule driven helper cube that pulls its values via rule from the roster cube and use the helper cube to drive the member set.

[grvbahal]

Note - I can always introduce the department dimension in my employee roster cube and use zero suppression to solve this but am trying to keep the employee roster cube flat.

Then why not have a virtual rule driven helper cube that pulls its values via rule from the roster cube and use the helper cube to drive the member set.

Thanks lotsaram. Yes, that's always an option but I wonder if there is a way to solve it through MDX query without creating additional cubes in TM1. If I am not able to figure out a query to do this then I will probably use what you are suggesting.

[declanr]

The below example assumes that you have an attribute against each department (in the department dim) with a friendly name... this was due to me taking the logic from something I did that was a little different to you as in my case the equivalent of departments had a "code" and then friendly names in an alias - these were held against the equivalent of employee in a cube.
You could remove the need to pull in the "StringName" attribute by using MemberToStr probably... but I haven't tested that.

Code: Select all

{ GENERATE( 
	{TM1FilterByLevel({TM1DrillDownMember({[DEPT].[GROUP1]},ALL,RECURSIVE)},0)}, 
	{Filter(
		{[EMP].members},
		[CUBE].([MEASURE].[DEPT]) = [DEPT].CurrentMember.Properties("StringName") 
	)} 
)}

"Generate" tells it to loop through the first set of elements (which is n-level descendants of "Group1" in the dept dim) and for each element check which employees have "Dept" in the cube populated with a string that matches the string attribute held against the element from the first set.


EDIT - Should point out that I haven't made it all that easy to understand as I have used "DEPT" as the name of the measure int he cube and the name of the department dimension in the code above.

CUBE - Name of Cube
DEPT - Name of Department Dimension
DEPT - Name of Cube Measure (String)
EMP - Name of employee dimension
StringName = Name of attribute/alias


2nd Edit - Just looked again and realised the following code would mean you can ignore the attribute and not need to use MemberToStr:

Code: Select all

{ GENERATE( 
	{TM1FilterByLevel({TM1DrillDownMember({[DEPT].[GROUP1]},ALL,RECURSIVE)},0)}, 
	{Filter(
		{[EMP].members},
		[CUBE].([MEASURE].[DEPT]) = [DEPT].CurrentMember.Name
	)} 
)}

[declanr]

but I wonder if there is a way to solve it through MDX query without creating additional cubes in TM1.

There is almost always a way to solve things in MDX with TM1 as it is a hugely powerful tool; the problem often comes down to speed... for example the GENERATE function is fantastic but on a large dimension can be incredibly slow.
I also use TM1 with caution due to the fact that a large number of TM1 developers are not familiar with MDX beyond the basic functionalities; so although something can be done in it... you can not guarantee that future developers/admins will be able to understand what the hell it is doing.

[grvbahal]

The below example assumes that you have an attribute against each department (in the department dim) with a friendly name... this was due to me taking the logic from something I did that was a little different to you as in my case the equivalent of departments had a "code" and then friendly names in an alias - these were held against the equivalent of employee in a cube.
You could remove the need to pull in the "StringName" attribute by using MemberToStr probably... but I haven't tested that.

Code: Select all

{ GENERATE( 
	{TM1FilterByLevel({TM1DrillDownMember({[DEPT].[GROUP1]},ALL,RECURSIVE)},0)}, 
	{Filter(
		{[EMP].members},
		[CUBE].([MEASURE].[DEPT]) = [DEPT].CurrentMember.Properties("StringName") 
	)} 
)}

"Generate" tells it to loop through the first set of elements (which is n-level descendants of "Group1" in the dept dim) and for each element check which employees have "Dept" in the cube populated with a string that matches the string attribute held against the element from the first set.


EDIT - Should point out that I haven't made it all that easy to understand as I have used "DEPT" as the name of the measure int he cube and the name of the department dimension in the code above.

CUBE - Name of Cube
DEPT - Name of Department Dimension
DEPT - Name of Cube Measure (String)
EMP - Name of employee dimension
StringName = Name of attribute/alias


2nd Edit - Just looked again and realised the following code would mean you can ignore the attribute and not need to use MemberToStr:

Code: Select all

{ GENERATE( 
	{TM1FilterByLevel({TM1DrillDownMember({[DEPT].[GROUP1]},ALL,RECURSIVE)},0)}, 
	{Filter(
		{[EMP].members},
		[CUBE].([MEASURE].[DEPT]) = [DEPT].CurrentMember.Name
	)} 
)}

Thanks declanr. This works and yes if I try to pass a department with lot's of children the query does slow down but for practical uses it will work perfectly.