I am using two of the EazyBI report templates for my dashbaord: created vs resolved issues (timeline) and created issues over time by current status.
I added new calculated members, aggregating via start and end date (sprint).
These new members work for all of my reports. But not for the two mentioned above.
Why that? Can anybody help me please?!
Hi @ANM
It would very much depend on the way and formula for your calculated member.
Calculated members can change the report context significantly and it seems your calculated member won’t display results on “Time” dimension members.
Please export and share the definition for your report.
Martins / eazyBI support
Report Definition (Created issues over time by current status):
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “[MGMT] Created issues over time by current status “,
“result_view”: “bar_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[”[Measures].[Issues created]”],“members”:[]},{“name”:“Status”,“selected_set”:["[Status.Category].[Done]","[Status.Category].[In Progress]","[Status.Category].[To Do]"],“members”:[],“bookmarked_members”:[]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time].[Last 12 months]"],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Time].[Month])”,“members”:[],“bookmarked_members”:[]}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Time”,“duplicate”:true,“selected_set”:["[Time].[All Times]","[Time].[Last 12 months]","[Time].[Current year]","[Time].[Previous year]","[Time].[Last 30 days]","[Time].[21 days ago]","[Time].[S2044.1]","[Time].[S2044.2]","[Time].[S2044.3]","[Time].[S2044.4]","[Time].[S2105.2]","[Time].[S2105.3]","[Time].[S2105.4]","[Time].[S2105.1]"],“members”:[{“depth”:0,“full_name”:"[Time].[Last 12 months]",“name”:“Last 12 months”,“calculated”:true,“drillable”:true}],“bookmarked_members”:[],“current_page_members”:["[Time].[Last 12 months]"]},{“name”:“Project”,“selected_set”:["[Project].DefaultMember"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Project].[All Projects]"]},{“name”:“Issue Type”,“selected_set”:["[Issue Type].DefaultMember"],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:"[Issue Type].[All Issue Types]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Issue Type].[All Issue Types]"]},{“name”:“Priority”,“selected_set”:["[Priority].DefaultMember"],“members”:[{“depth”:0,“name”:“All Priorities”,“full_name”:"[Priority].[All Priorities]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Priority].[All Priorities]"]},{“name”:“Assignee”,“selected_set”:["[Assignee].DefaultMember"],“members”:[{“depth”:0,“name”:“All Assignees”,“full_name”:"[Assignee].[All Assignees]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Assignee].[All Assignees]"]}]},“options”:{},“view”:{“current”:“bar_chart”,“maximized”:false,“bar_chart”:{“stacked”:true,“vertical”:true,“swap_axes”:false,“data_labels”:false,“series_options”:{“To Do”:{“color”:"#344563"},“In Progress”:{“color”:"#2F579C"},“Done”:{“color”:"#00875A"}}},“table”:{}},“calculated_members”:[]}
} ],
“calculated_members”: [{“dimension”:“Time”,“name”:“Last 12 months”,“format_string”:"",“formula”:“Aggregate(\n [Time].[Month].DateMembersBetween(‘12 months ago’, ‘today’)\n)”},{“dimension”:“Time”,“name”:“Last 30 days”,“format_string”:"",“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘30 days ago’, ‘today’)\n)”},{“dimension”:“Time”,“name”:“Current year”,“format_string”:"",“formula”:“Aggregate({\n [Time].[Year].CurrentDateMember\n})”},{“dimension”:“Time”,“name”:“Previous year”,“format_string”:"",“formula”:“Aggregate({\n [Time].[Year].CurrentDateMember.PrevMember\n})”},{“name”:“21 days ago”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘21 days ago’, ‘today’)\n)”,“format_string”:""},{“name”:“S2044.2”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-08-31’, ‘2020-09-18’)\n)”,“format_string”:""},{“name”:“S2044.1”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-08-10’, ‘2020-08-28’)\n)”,“format_string”:""},{“name”:“S2044.3”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-09-21’, ‘2020-10-09’)\n)”,“format_string”:""},{“name”:“S2044.4”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-10-12’, ‘2020-10-30’)\n)”,“format_string”:""},{“name”:“S2105.1”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-11-02’, ‘2020-11-20’)\n)”,“format_string”:""},{“name”:“S2105.2”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-11-23’, ‘2020-12-11’)\n)”,“format_string”:""},{“name”:“S2105.3”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-12-14’, ‘2021-01-15’)\n)”,“format_string”:""},{“name”:“S2105.4”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2021-01-18’, ‘2021-02-05’)\n)”,“format_string”:""}]
}
calculated member that does not work:
Aggregate(
[Time].[Day].DateMembersBetween(‘2020-08-10’, ‘2020-08-28’)
)
Hi,
Do you have any issues created or resolved in that project in the period which is defined in your calculated member?
Does this report show results when you select any other calculated members from “Time” dimension?
Otherwise report might seem empty for that period.
If that is so please switch to table view and disable “Nonempty” cross join for report rows.
The measure “Open issues” should show results for the period defined in your calculated member.
Martins / eazyBI reports
it shows data for
last 12 months
current year
all times
it does not show data for day based members
and yes, there are issues created/ resolved in these spefic day based periods/ calculated members
Hi,
Try the following way to define them.
Aggregate(
[Time].[Day].DateMembersBetween('Aug 10 2020','Aug 28 2020')
)
It would aggregate members between 10th of Aug and 28th of Aug
Martins
that does not work either 
the same calculated member works for this report:
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “[MGMT] Open issues per component”,
“result_view”: “pie_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Priority”,“selected_set”:["[Priority].[All Priorities]"],“members”:[],“bookmarked_members”:[]},{“name”:“Measures”,“selected_set”:["[Measures].[Open issues]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Project].[Component])”,“members”:[],“bookmarked_members”:[]}],“extreme_by”:{“expression”:["[Priority].[All Priorities]","[Measures].[Open issues]"],“extreme”:“top_count”,“limit”:5,“others”:true},“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Project”,“duplicate”:true,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Project].[All Projects]"]},{“name”:“Priority”,“duplicate”:true,“selected_set”:["[Priority].[All Priorities]"],“members”:[{“depth”:0,“name”:“All Priorities”,“full_name”:"[Priority].[All Priorities]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Priority].[All Priorities]"]},{“name”:“Issue Type”,“selected_set”:["[Issue Type].[All Issue Types]"],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:"[Issue Type].[All Issue Types]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Issue Type].[All Issue Types]"]},{“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}],“bookmarked_members”:[],“current_page_members”:["[Status].[All Statuses]"]},{“name”:“Time”,“selected_set”:["[Time].[All Times]","[Time].[21 days ago]","[Time].[Last 12 months]","[Time].[Last 30 days]","[Time].[Current year]","[Time].[Previous year]","[Time].[S2044.1]","[Time].[S2044.2]","[Time].[S2044.3]","[Time].[S2044.4]","[Time].[S2105.1]","[Time].[S2105.2]","[Time].[S2105.3]","[Time].[S2105.4]"],“members”:[{“depth”:0,“name”:“All Times”,“full_name”:"[Time].[All Times]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Time].[All Times]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“pie_chart”,“maximized”:false,“pie_chart”:{“swap_axes”:false,“donut”:false,“show_legend”:false,“show_labels”:true,“data_labels”:“values”,“relative_size”:false,“series_options”:{}},“table”:{}},“calculated_members”:[]}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Open issues”,“format_string”:"#,##0",“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name \u003c\u003e ‘Issue’ THEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues resolved])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues resolved]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Created at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Resolved at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND”},{“dimension”:“Time”,“name”:“Last 12 months”,“format_string”:"",“formula”:“Aggregate(\n [Time].[Month].DateMembersBetween(‘12 months ago’, ‘today’)\n)”},{“dimension”:“Time”,“name”:“Last 30 days”,“format_string”:"",“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘30 days ago’, ‘today’)\n)”},{“dimension”:“Time”,“name”:“Current year”,“format_string”:"",“formula”:“Aggregate({\n [Time].[Year].CurrentDateMember\n})”},{“dimension”:“Time”,“name”:“Previous year”,“format_string”:"",“formula”:“Aggregate({\n [Time].[Year].CurrentDateMember.PrevMember\n})”},{“name”:“21 days ago”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘21 days ago’, ‘today’)\n)”,“format_string”:""},{“name”:“S2044.2”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-08-31’, ‘2020-09-18’)\n)”,“format_string”:""},{“name”:“S2044.1”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-08-10’, ‘2020-08-28’)\n)”,“format_string”:""},{“name”:“S2044.3”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-09-21’, ‘2020-10-09’)\n)”,“format_string”:""},{“name”:“S2044.4”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-10-12’, ‘2020-10-30’)\n)”,“format_string”:""},{“name”:“S2105.1”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-11-02’, ‘2020-11-20’)\n)”,“format_string”:""},{“name”:“S2105.2”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-11-23’, ‘2020-12-11’)\n)”,“format_string”:""},{“name”:“S2105.3”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2020-12-14’, ‘2021-01-15’)\n)”,“format_string”:""},{“name”:“S2105.4”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time].[Day].DateMembersBetween(‘2021-01-18’, ‘2021-02-05’)\n)”,“format_string”:""}]
}
Please try using “Time” dimension calculated members in rows to check if any of them return value for “Issues created”?
See attached image.
Hi,
Great that you found the explanation for your behavior in your sample reports.
Indeed, if you create a calculated member that aggregates “Time” dimension members at the day level (for a specific time range), it would work only for “Day” level members in the report.
If you want to see the total result for the calculated member, you can’t use it both in pages and in rows.
Try changing the layout and show other dimensions in rows, but keep “Time” dimension in pages only.
Martins / eazyBI
