New eazyBI 5.3 feature: Drill into a single measure by another dimension level

This new amazing feature is available for eazyBI for Jira Server and Jira Cloud.

Here the official documentation

Drill into measure (column) by another dimension level (example: Time)

To cross join only one particular measure with a particular dimension level, you may use option Drill into: click on the measure, select Drill into and the dimension and the member level you need.

Very useful for example to drill a Column by Time (monthly, daily, weekly or yearly!)

We will do now, in this exercise, a PPM (Project Portfolio Management) report example:

Mixed view of “Gantt” with Resources workload using eazyBI

First of all, we will add a new Calculated Measure called “Planned Time”

In this example we use the Original Estimate between Issue creation date and the Issue Due date. But we can change it for the Remaining Estimate measure or use other dates as range, just replace it in the code and customize your Planned Time measure.

CASE WHEN 
-- show for default time periods on working days only
([Time].CurrentHierarchyMember.Level.Name = "Day" 
  and 
 NOT CoalesceEmpty([Time].CurrentHierarchyMember.Get("Week day name"),"") MATCHES "Saturday|Sunday")
 or [Time].CurrentHierarchyMember.Level.Name MATCHES "Year|Week|Month|Quarter"
THEN
SUM(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  -- check if an issue has start and end date, and it falls into selected period:
  NOT IsEmpty([Measures].[Issue created date])
  AND
  NOT IsEmpty([Measures].[Issue due date])
  AND
  (DateBetween(
    [Time].CurrentHierarchyMember.StartDate,
    DateWithoutTime([Measures].[Issue created date]),
    [Measures].[Issue due date])
    OR 
  DateInPeriod(
    [Measures].[Issue created date], 
    [Time].CurrentHierarchyMember)
  )),
  -- total planned time
  ([Measures].[Original estimated hours],
  [Time].CurrentHierarchy.DefaultMember)
  /
  -- planned time per one workday
  DateDiffWorkdays(
    DateWithoutTime([Measures].[Issue created date]),
    DateAddDays([Measures].[Issue due date],1)
  )
  *
   -- days in period
   DateDiffWorkdays(
     TimestampToDate(IIF(DateInPeriod([Measures].[Issue created date], [Time].CurrentHierarchyMember), 
       DateToTimestamp(DateWithoutTime([Measures].[Issue created date])),
       DateToTimestamp([Time].CurrentHierarchyMember.StartDate))),
     TimestampToDate(IIF(DateInPeriod([Measures].[Issue due date], [Time].CurrentHierarchyMember), 
       DateToTimestamp(DateAddDays([Measures].[Issue due date],1)),
       DateToTimestamp([Time].CurrentHierarchyMember.NextStartDate)))
   )
)
END

If you Drill the Planned Time Calculated Measure (column) by the Time measure, you will see something similar a Gantt with the workload of the assignees. This is a great example original created by the eazyBI team. …Is a Gantt.. but isn’t a Gantt 😀

By MrAddon