Can't count a column data and access Members

Questions & Answers
, ,
1

Hello!

I am learning to use eazyBI and I have some issues.

I need to generate reports for an inventory in eazyBI. Firstly, I would like to determine, based on two date fields, whether certain objects are active or inactive. I have already done this as a whole, but I need to count them instead of having them individually. I have attached screenshots of the process.

https://everisgroup-my.sharepoint.com/:f:/g/personal/csantosb_emeal_nttdata_com/EuDpcrpA2ZRMvdniotltv7ABn4g5b80BT96dZ5BCjD2OyQ?e=6GqopR

I want to count active and inactive objects created.

2

Hi @csantosb,

You were using the following expression to find the figure.

Sum(
Filter(
[Object].Members,
[Object].CurrentMember.GetString("Tool Jira Type") = [Tool Jira Type].CurrentMember.Getstring("KEY")
AND IsEmpty([Object].CurrentMember.Get("Tool Disabled date"))
AND IsEmpty([Object].CurrentMember.Get("Tool Archived date"))
)
)

The approach is a move in the right direction but has some flaws within.

The first thing to improve is the usage of .Members method. You have used that directly on the dimension, and that leads to the creation of a set of all dimension members - first the top level of “All members” without any of the properties populated, then the category level if such exists in the dimension hierarchy, and then the actual members.
To avoid this duplication, specify the actual level for the members in the created set.

That might be

[Object].[Category].Members

or

[Object].[Object].Members

As you noted in the direct communication - you use calculated members within the “Object” dimension. Therefore, you might use the DescendantsSet function for the creation of the set of members.

The next thing is that you have not specified the measure or numerical value for the Sum function.
The default measure “Issues created” is used in Jira or another measure depending on the dataset. Using an explicit measure is best as that ensures calculation transparency.

The comparison of member property against the currently applicable “Tool Jira Type” from the report context might only work if there is just one item selected from “Jira Tool Type”.
You might better use “Objects created” measure to filter against the whole report context. However, if you use that measure for the Sum, you might as well drop this condition.

So the resulting expression might be as follows.

Sum(
 Filter(
--create set of objects
  DescendantsSet(
   [Object].CurrentHierarchyMember,]
   [Object].[Object]),
--conditions for the objects
--condition 1 - tool not disabled
   IsEmpty([Object].CurrentMember.Get("Tool Disabled date"))
  AND
--condition 2 - tool not archived
   IsEmpty([Object].CurrentMember.Get("Tool Archived date"))
  ),
--numeric value for sum - also checks the relation to the report context
  [Measures].[Objects created]
)

The inactive tools would have the NOT before conditions on lines 9 and 12.

Regards,
Oskars / support@eazyBI.com