Calculated Members - How to filter based on project component in EazyBI?

[graemejohnson]

Hi

My requirement is to have 2 calculated members on the Project domain; to filter out the jobs that two separate teams will have worked on.

Here's the proposition....

We have two separate development teams that work on 3 separate projects, for simplicity let's call the teams "Team A" and "Team B" and the projects "Project X", "Project Y" and "Project Z".

"Team A" will work on

• jobs in "Project X" where the Component is "Component 1" or "Component 2"
• jobs in "Project Y" where the Component is "Component 1" or "Component 3"
• jobs in "Project Z" where the Component is "Component 1" or "Component 2 " or "Component 3" or "Component 4"

"Team B" has a much more complex way of working and will work on all the other components in each project bar the ones worked on by Team A i.e.

• jobs in "Project X" where the Component is "Component 5"  all the way to "Component 20"
• jobs in "Project Y" where the Component is "Component 15" all the way to "Component 30"
• jobs in "Project Z" where the Component is "Component 5" all the way to "Component 12" 

I want to be able to have two calculated members on the project domain that filter out all the issues according to the rules above.

In trying to implement this I first built this calculated member which I called "Team A Project Jobs"....

Aggregate({
[Project].[X].[Component 1],
[Project].[X].[Component 2], 
[Project].[Y].[Component 1],
[Project].[Y].[Component 3], 
[Project].[Z].[Component 1],
[Project].[Z].[Component 2], 
[Project].[Z].[Component 3]
})

This appears to work - but in defining "Team B Project Jobs" I don't want to name each component.   I would have thought that I could use an approach below - but that appears to still bring through the components that Team A work on 

Aggregate(
 Except(
 [Project].[Component].Members,
 { 
[Project].[Team A Project Jobs] 
 }
 )
)

Please can you help - I'm 4 hours into trying to crack this one and I'm losing my mind?

[Daina Tupule eazyBI]

Hi Graem,

I would suggest extracting the numeric part from the component name and then do some numeric operations with extracted number.

You could try this formula for calculated member 'Team B Project Jobs':

Aggregate(
   Filter(
     [Project].[Component].Members,
     ([Project].CurrentHierarchyMember.Parent.Name = 'Project X' 
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) >= 5
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) <= 20
     )
     OR
     ([Project].CurrentHierarchyMember.Parent.Name = 'Project Y' 
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) >= 15
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) <= 30
     )
     OR
     ([Project].CurrentHierarchyMember.Parent.Name = 'Project Z' 
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) >= 5
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) <= 12
     )
    )
)

Daina / support@eazybi.com

[graemejohnson]

Thanks for taking the time to answer this Daina.  Unfortunatley, in the example, for clarity I gave component names such as Component 1 and Component 2 etc.  However, in reality the components are named such as ".NET" and "MySQL" and "Oracle" etc etc 

[graemejohnson]

Without wishing to sound desperate (although I am), please can anyone advise?