Trying to create subquery in calculated member

teibrich · December 18, 2021, 10:46am

Hi,

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.

Replacing [Object].CurrentMember.get('Label') with a concrete String gives a result.

I appreciate any kind of help. Thanks a lot!

Alex

teibrich · December 22, 2021, 7:56am

Perhaps also to expand on that question as I learned more now. The central question seems to be: How to get related objects based on incoming references in Jira Insight / Eazy BI.

For the outgoing reference from Person to Team this is straight forward something like

[Object].[Person].CurrentMember.GetLinkedMember("Person Teams")

How can I do this in the other direction starting with a [Object].[Teams]?

daina.tupule · December 23, 2021, 2:18pm

Do I understand this correctly? You have Person objects with two attributes Role and Team. I would suggest considering importing them both as properties and dimensions.

The simplest option would be using those two attribute dimensions as filters. For example, add dimension Person Team on Rows and use dimension Person Rows on Pages. Then you can select Developer from Person role - and eazyBI will filter data in the report for persons with role developer only. The dimension Person team, when expended will show how many developers are in each team.

While I do not have the same data, here is one example for my object Person. Person has two attributes - Country and Department. Here I have a report where I can see how my persons are split by countries and departments. I used object attribute dimensions for this.

However, some reports need more advanced setup and formulas. Here are some pointers on the formula you are trying to use.

The formula iterates through Object dimension Person objects. The “only” objects this formula can “see” are persons.

in this formula
[Measures].[Object Person Teams] = [Object].CurrentMember.get('Label')

[Measures].[Object Person Teams] will give a team name of the person [Object].CurrentMember.get(‘Label’) will give this person name (because you are inside the formula iterating through person objects).

You need to use a different “address” either for teams or for persons of the teams. You would like to use either a different dimension or hierarchy for representing Teams or use some different way on how you get Person details.

For example, you can use the same Person attribute dimensions to access the count of particular persons:

([Measures].[Objects created],
[Object Type].[Person],
[Person Role].[Developer],
-- switch from team object to person team dimesnion
[Person Teams].[Teams].GetMemberByKey(
  [Object].CurrentMember.Key
),
[Object].DefaultMember
)

Here I used a similar formula to access IT persons in a country:

Daina / support@eazybi.com