Hi Mitchell,
eazyBI doesn’t have an integration with Vacation Manager add-on, however, there is a workaround you may want to try in this case.
What I find strange is in your Jira ticket screenshot I see that “used vacation days” field results does not include additional holidays from Jira holiday scheme, does it?
See following attachments with my findings:
And you are right, when importing cf “used vacation days” as measure in eazyBI there is a set of new measures created and if you use any of them it would show results only at the first and last date of vacation period by default.
Here is what you could do in your case to return counter 1 for each date which belong to vacation.
Create a new calculated member in “Measures” dimension to calculate results at day level and show them for each day between vacation start and end dates.
This is the code from my report (see attached image below):
Nonzero(
Count(
Filter(
Descendants([Time].CurrentHierarchyMember,[Time].[Day]),
DateDiffWorkDays(
[Time].CurrentHierarchyMember.StartDate,
[Time].CurrentHierarchyMember.NextStartDate
)>0
AND
DateBeforePeriodEnd(
Order(
Filter(
[Time].[Day].Members,
[Measures].[Used vacation days with start date]>0
),
[Time].CurrentMember.Startdate,
BASC
).item(0).Startdate,
[Time].CurrentHierarchyMember
)
AND
DateAfterPeriodEnd(
Order(
Filter(
[Time].[Day].Members,
[Measures].[Used vacation days with end date]>0
),
[Time].CurrentMember.Startdate,
BDESC
).item(0).Startdate,
[Time].CurrentHierarchyMember.PrevMember
)
)
)
)
By doing so you may notice that some future (and past) dates may not be available in “Time” dimension because eazyBI creates dimension members based on issue field timestamps. If none of the actions are happening in the date, this date is not created by default in “Time” dimension. That means that calculation seems incorrect, which is true unless you add all time members for “Time” dimension.
That is why you would need to manually add missing date members for past and future dates. It is safe to add date members for larger periods.
Please find our documentation page how to add missing date members: https://docs.eazybi.com/display/EAZYBI/Create+reports#Createreports-AddmemberstoTimedimension
One more thing - all additional holidays additonaly should be define in eazyBI import options (even if you already have them Jira). The code above knows how to exclude regular workingdays, but additonal holidays is thing that must be defined via Time options
https://docs.eazybi.com/display/EAZYBIJIRA/Jira+Issues+Import#JiraIssuesImport-Timedimension
Actually, you could try importing the report definition from below. It should create this calculated measure and final eazyBI report for you.
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Report vacation",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Count of vacation days]"],"members":[]},{"name":"Reporter","selected_set":["[Reporter].[User].Members"],"members":[],"bookmarked_members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[2018]","[Time].[2019]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time].[Day])","members":[],"bookmarked_members":[]}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Status","selected_set":["[Status].[All Statuses]"],"members":[{"depth":0,"name":"All Statuses","full_name":"[Status].[All Statuses]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Approved","full_name":"[Status].[Approved]","parent_full_name":"[Status].[All Statuses]"}],"bookmarked_members":[],"current_page_members":["[Status].[Approved]"]},{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Vacation Manager","full_name":"[Project].[Vacation Manager]","drillable":true,"key":"VACMAN","parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":[],"current_page_members":["[Project].[Vacation Manager]"]},{"name":"Time","duplicate":true,"selected_set":["[Time].[All Times]"],"members":[{"depth":0,"name":"All Times","full_name":"[Time].[All Times]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"2018","full_name":"[Time].[2018]","drillable":true,"parent_full_name":"[Time].[All Times]"},{"depth":1,"name":"2019","full_name":"[Time].[2019]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Time].[All Times]"},{"depth":2,"name":"Q1 2019","full_name":"[Time].[2019].[Q1 2019]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Time].[2019]"},{"depth":3,"name":"Jan 2019","full_name":"[Time].[2019].[Q1 2019].[Jan 2019]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Time].[2019].[Q1 2019]"}],"bookmarked_members":[],"current_page_members":["[Time].[2018]","[Time].[2019]"]}]},"options":{"nonempty":true},"view":{"current":"table","maximized":false,"table":{}}}
} ],
"calculated_members": [{"name":"Count of vacation days","dimension":"Measures","formula":"Nonzero(\nCount(\nFilter(\nDescendants([Time].CurrentHierarchyMember,[Time].[Day]),\nDateDiffWorkDays(\n [Time].CurrentHierarchyMember.StartDate,\n [Time].CurrentHierarchyMember.NextStartDate\n )>0\nAND\nDateBeforePeriodEnd(\n\nOrder(\nFilter(\n [Time].[Day].Members,\n [Measures].[Used vacation days with start date]>0\n ),\n [Time].CurrentMember.Startdate,\n BASC \n).item(0).Startdate\n,\n[Time].CurrentHierarchyMember\n)\nAND\nDateAfterPeriodEnd(\nOrder(\nFilter(\n [Time].[Day].Members,\n [Measures].[Used vacation days with end date]>0\n ),\n [Time].CurrentMember.Startdate,\n BDESC \n).item(0).Startdate\n\n\n,\n[Time].CurrentHierarchyMember.PrevMember\n)\n)\n)\n)","format_string":"#,##0"}]
}
Martins / eazyBI support
