Assets: Calculated measure over inbound reference

[rmaire]

I have an assets schema in which software is registered. To illustrate my problem, I have created a very simplified schema: A software is divided into three objects in the following hierarchy:

Software
- Software Identification
- Support Contract

Software is the generic software title, supplemented with the department that supports the software (MS SQL Server, for example):

Software Identification is the specific version of a software that links the generic software to a support contract (MS SQL Server 2016 for example):

Support Contract contains the date when the vendor support expires:

I have been tasked with creating a report that lists per department (software) how many software versions are no longer supported (date on the support contract). Unfortunately, I don’t see a way to use the departments as a dimension and sum up the support contracts from this object type, as the reference directions are not following the hierarchy:

Software ← Software Identification → Support Contract

What would be the best way to solve this task?

Thanks and kind regards…

[janis.plume]

Hi,

The solution requires creating custom hierarchies in the Object dimension and a highly tricky formula for counting the expired licenses per Department. As an introductory source, I recommend checking this article on how to create a report with inbound references:: Assets: How to Import "Inbound References" in eazyBI

I created a similar object schema for demonstration:

Software object has its Department and the Provider Qualification has the End of Support date field.
I imported all needed attributes:

Then I created two additional hierarchies in the Object dimension to group Objects by Derpatmetn and L_Software:

I used the Object department hierarchy in the report and created a formula to count related Provider Qualifications with expired dates:

Nonzero(Count(
Filter(
Generate(
  [Object.Software Identification L_Software].[L_Software].GetMemberByKey(
    [Object.Software Department].CurrentHierarchyMember.KEY
  ).Children,
  [Object].[Object].GetMembersByKeys(
    [Object.Software Identification L_Software].CurrentHierarchyMember.Get('Software Identification L_PQ')  
  )
),
DateCompare([Measures].[Object Provider Qualification End of Support],Now())<0
)
))

That allows creating a report like this:

Kindly,
Janis, eazyBI support