I’m fairly new to MDX and am currently trying to create a report based on a data import from Jira Insight.
I have two entities:
- Team (containing e.g. a team name)
- Employee (containing e.g. a name, a role and a reference to team)
I’m trying to create a list of Teams with one column containing people of a certain role, e.g. all developers in that team.
In SQL I would do something like …
SELECT Team.'name', (Select Employee.'name' FROM Employee WHERE Employee.'role' = "Developer" AND Employee.'team_id' = Team.'id')) as Developers, FROM Team
… somewhat simplified as it should probably be a group by as there could be multiple results.
How would I create a similar calculated member in MDX?
SetToStr( Filter( [Object].[Person].Children, [Measures].[Object Person Role] = "Developer" AND [Measures].[Object Person Teams] = [Object].CurrentMember.get('Label') ) )
… where the rows of that report are the teams.
[Object].CurrentMember.get('Label') with a concrete String gives a result.
I appreciate any kind of help. Thanks a lot!