On eazyBI evaluation period: Request for support on a customised report

[OoDy]

Hi All,

I am evaluating eazyBI for Jira and plan to use it for creating customised reports / dashboards for my company. I sent my question to eazyBI Support team on 27.01.2020 but I have not heard any feedback. So, I try to find out the solution myself and found eazyBI community. Hope I can get support from eazyBI experts here.

I use Jira Software Server 8.0.2 as core application and have BigPicture as a Jira’s plugin for Project and Resources Management. Now, I could not see both Allocation hours and Spent hours per day of any assignee for each issue on the same table. I can see Allocation hours per day of any assignee for each issue on Resource Allocation table on BigPicture. While I can see Spent hours per day of any assignee for each issue on Jira’s Timesheet. Since there are too many assignees, around 80 users, in the system, it is not easy to monitor whether each assignee is working as plan or not. So, I need eazyBI for creating a comparison report between Allocation hours and Spent hours per day of any assignee for each issue on a selected period such as 10 working days, last week and next week. I am not sure whether eazyBI can support me on this kind of customised reports or not. I would like to ask for kind support from eazyBI community.

Steps that I worked and created the report can be found below:

1. Created an issue as sub-task in Jira, put Original Estimate, select Start Date and End Date – customised fields in Jira by BigPicture, and choose an assignee

(Figure 01 and Figure 02)

2. Got updated resource plan on Resource Allocation table on BigPicture

(Figure 03)

3. Assignee started working and logged Spent hours per day on the issue

(Figure 04)

4. Got updated status of the issue on Gantt chart on BigPicture

(Figure 05)

5. Found Spent hours on the issue on Jira’s Timesheet

(Figure 06)

6. Imported data from Jira to eazyBI and created a customised report to compare between Allocation hours and Spent hours. The current report can be found below.

(Figure 07)

However, I would like to have the expected report that can be calculated from the following information:

Original Estimate = 40 hours
Working Duration, customised field = End Date – Start Date + 1 day = 07.02.2020 – 03.02.2020 + 1 = 5 days
Average Allocation, customised field = Original Estimation / Working Duration = 40 / 5 = 8 hours / day
Fill-in Average Allocation 8 hours per day on Allocation hours, customised field, on the report from Start Date to End Date

I am a new user and not allowed to put more than one figure on the topic. For eazyBI support team, if you need more details and all figures, please contact me.

How can I achieve this using eazyBI? I would appreciate it if anyone can help me on this. Thank you so much for your kind help in advance.

Best regards,

OoDy

[daina.tupule]

eazyBI does not have default measures for allocation plan. However, here is an example formula you can use for this. The formula below will calculate estimation per period for Original estimated hours between two dates - start date and end date:

CASE WHEN 
-- show for default time periods on workingdays 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"
 or [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
NonZero(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 Start date])
  AND
  NOT IsEmpty([Measures].[Issue End date])
  AND
  (DateBetween(
    [Time].CurrentHierarchyMember.StartDate,
    [Measures].[Issue Start date],
    [Measures].[Issue End date])
    OR 
  DateInPeriod(
    [Measures].[Issue Start date], 
    [Time].CurrentHierarchyMember)
  )),
  -- total planned time
  ([Measures].[Original estimated hours],
  [Time].CurrentHierarchy.DefaultMember)
  /
  -- total count of workdays in planned period between start date and end date
  DateDiffWorkdays(
    [Measures].[Issue Start date],
    DateAddDays([Measures].[Issue End date],1)
  )
  *
   -- days per period 
   DateDiffWorkdays(
     TimestampToDate(IIF(DateInPeriod([Measures].[Issue Start date], [Time].CurrentHierarchyMember), 
       DateToTimestamp(DateWithoutTime([Measures].[Issue Start date])),
       DateToTimestamp([Time].CurrentHierarchyMember.StartDate))),
     TimestampToDate(IIF(DateInPeriod([Measures].[Issue End date], [Time].CurrentHierarchyMember), 
       DateToTimestamp(DateAddDays([Measures].[Issue End date],1)),
       DateToTimestamp([Time].CurrentHierarchyMember.NextStartDate)))
   )
))
END

Here I created a measure Estimates with the formula above and used it in the report and split (drill into) it’s values per month.

Daina / support@eazybi.com

[Twiny]

Hello Daina,

I was wondering if you could help me with the following request.

I have a Roadmap plan for my project with issues assigned to my team members and planned to be worked on during various periods of time. Each issue has a Target start and Target end date and an Estimated number of hours.

However, an estimate for each issue is not necessarily equal to the duration of the issue. For example, the Target start of an issue can be Monday, Oct 16, 2023, and the Target end is Friday, Oct 20, 2023, for a total duration of 5 days.

However, the actual work required to complete this issue is, let’s say, two days (16 hours). This is because the team member who is assigned this issue will have to wait for other stakeholders to complete their work before proceeding with the issue. And while the team member is waiting for the stakeholders, he/she can start working on another issue, which, for example, has the same Target start and Target end dates but a different time estimate, let’s say 8 hours. And so on. The same goes for all other team members.

In other words, each team member has multiple issues assigned to them during the same period of time, but the sum of the estimates is not the sum of the durations; otherwise, the workload would be multiples of the number of issues.

But here is the problem: how do I know, on any given day, that the workload of each team member does not exceed 8 hours? In other words, how do I know the sum of estimates for each team member on any given day? Is it fewer than 8 hours? More than 8 hours? Are they overloaded or underloaded? Can I add more tasks to them, or shall I remove tasks? Shall I apply resource leveling or resource smoothing?

I’m looking for a report that can display this information. I’m attaching a screenshot of my plan with the mockup of the report that I have in mind.

Do you know if creating such a report is possible?

Any help would be highly appreciated!