Hi, I have created report with Calculated measure “Open Delivery Raids Count”:
COUNT(
FILTER(([Issue].[Issue].GetMembersByKeys(
[Issue].CurrentHierarchyMember.get('All links')
)),
[Measures].[Issue Type] = "Delivery RAID" AND [Measures].[Issue Status] <> "Closed"
))
In Issues row I use hierarchy:
And in the report result I try to sort issues by this calculated measure:
But it is not sorting it correctly. Why and how it is sorting it like this?
Replicating yours into my eazybi, with a “default” type for Formatting on this custom formula, it is ordered properly (with the type).
Are you applying the Format of Integer?
However, if you want to set the order. you can wrap it like:
Order(
<your formula>
, DESC)
Hi @Nacho,
The sorting should work as you expected unless there is other sorting already applied. The report screenshot doesn’t disclose the report toolbar, and we don’t see other dimensions involved in the report if there are any.
To see what may cause the sorting to work not in the expected way, please export and share the definition of the report - Export and import report definitions. You can share it here or to the support@eazybi.com email and reference this conversation.
Kind regards,
Roberts // support@eazybi.com
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Linked issues Only Count",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Issues created]","[Measures].[Open Delivery Raids Count]"],"members":[{"depth":0,"full_name":"[Measures].[Open Delivery Raids Count]","format_string":"#,##0","report_specific":true,"name":"Open Delivery Raids Count","calculated":true,"id":"[Measures].[Open Delivery Raids Count]"}]}]},"rows":{"dimensions":[{"name":"Issue","selected_set":["[Issue.Advanced Roadmaps].[Capability].Members"],"selected_set_action":[null],"members":[{"depth":3,"name":"WPB-96143","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-96143]","key":"WPB-96143","expanded":true},{"depth":3,"name":"WPB-128855","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-128855]","key":"WPB-128855","expanded":true},{"depth":3,"name":"WPB-156617","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-156617]","key":"WPB-156617","expanded":true},{"depth":3,"name":"WPB-171144","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-171144]","key":"WPB-171144","expanded":true},{"depth":3,"name":"WPB-171145","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-171145]","key":"WPB-171145","expanded":true},{"depth":3,"name":"WPB-171146","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-171146]","key":"WPB-171146","expanded":true},{"depth":3,"name":"WPB-171149","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-171149]","key":"WPB-171149","expanded":true},{"depth":3,"name":"WPB-178767","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-178767]","key":"WPB-178767","expanded":true},{"depth":3,"name":"WPB-186495","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-186495]","key":"WPB-186495","expanded":true},{"depth":3,"name":"WPB-195037","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-195037]","key":"WPB-195037","expanded":true},{"depth":3,"name":"WPB-203974","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-203974]","key":"WPB-203974","expanded":true},{"depth":3,"name":"WPB-203991","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-203991]","key":"WPB-203991","expanded":true},{"depth":3,"name":"WPB-203996","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-203996]","key":"WPB-203996","expanded":true},{"depth":3,"name":"WPB-216134","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-216134]","key":"WPB-216134","expanded":true},{"depth":3,"name":"WPB-227614","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-227614]","key":"WPB-227614","expanded":true},{"depth":3,"name":"WPB-228352","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-228352]","key":"WPB-228352","expanded":true},{"depth":3,"name":"WPB-228818","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-228818]","key":"WPB-228818","expanded":true},{"depth":3,"name":"WPB-230409","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-230409]","key":"WPB-230409","expanded":true},{"depth":3,"name":"WPB-230904","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-230904]","key":"WPB-230904","expanded":true},{"depth":3,"name":"WPB-231930","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-231930]","key":"WPB-231930","expanded":true},{"depth":3,"name":"WPB-232686","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-232686]","key":"WPB-232686","expanded":true},{"depth":3,"name":"WPB-233954","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-233954]","key":"WPB-233954","expanded":true},{"depth":3,"name":"WPB-235566","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-235566]","key":"WPB-235566","expanded":true},{"depth":3,"name":"WPB-236664","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-236664]","key":"WPB-236664","expanded":true},{"depth":3,"name":"WPB-240985","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-240985]","key":"WPB-240985","expanded":true},{"depth":3,"name":"WPB-241297","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-241297]","key":"WPB-241297","expanded":true},{"depth":3,"name":"WPB-242518","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-242518]","key":"WPB-242518","expanded":true},{"depth":3,"name":"WPB-246785","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-246785]","key":"WPB-246785","expanded":true},{"depth":3,"name":"WPB-246788","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-246788]","key":"WPB-246788","expanded":true},{"depth":3,"name":"WPB-246791","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-246791]","key":"WPB-246791","expanded":true},{"depth":3,"name":"WPB-246792","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-246792]","key":"WPB-246792","expanded":true},{"depth":3,"name":"WPB-247828","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-247828]","key":"WPB-247828","expanded":true},{"depth":3,"name":"WPB-250530","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-250530]","key":"WPB-250530","expanded":true},{"depth":3,"name":"WPB-258257","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-258257]","key":"WPB-258257","expanded":true},{"depth":3,"name":"WPB-263022","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-263022]","key":"WPB-263022","expanded":true},{"depth":3,"name":"WPB-275048","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-275048]","key":"WPB-275048","expanded":true},{"depth":3,"name":"WPB-277072","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-277072]","key":"WPB-277072","expanded":true},{"depth":3,"name":"WPB-279317","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-279317]","key":"WPB-279317","expanded":true},{"depth":3,"name":"WPB-288926","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-288926]","key":"WPB-288926","expanded":true},{"depth":3,"name":"WPB-292790","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-292790]","key":"WPB-292790","expanded":true},{"depth":3,"name":"WPB-300946","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-300946]","key":"WPB-300946","expanded":true},{"depth":3,"name":"WPB-304723","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-304723]","key":"WPB-304723","expanded":true,"drillable":true},{"depth":3,"name":"WPB-305935","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-305935]","key":"WPB-305935","expanded":true},{"depth":3,"name":"WPB-310351","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-310351]","key":"WPB-310351","expanded":true},{"depth":3,"name":"WPB-316070","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-316070]","key":"WPB-316070","expanded":true},{"depth":3,"name":"WPB-316201","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-316201]","key":"WPB-316201","expanded":true},{"depth":3,"name":"WPB-316308","full_name":"[Issue.Advanced Roadmaps].[(none)].[(none)].[WPB-316308]","key":"WPB-316308","expanded":true},{"depth":3,"name":"WPB-65086","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-20747].[WPB-65086]","key":"WPB-65086","expanded":true},{"depth":3,"name":"WPB-315414","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-249686].[WPB-315414]","key":"WPB-315414","expanded":true},{"depth":3,"name":"WPB-234103","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-254804].[WPB-234103]","key":"WPB-234103","expanded":true},{"depth":3,"name":"WPB-234246","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-254804].[WPB-234246]","key":"WPB-234246","expanded":true},{"depth":3,"name":"WPB-316010","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-254804].[WPB-316010]","key":"WPB-316010","expanded":true},{"depth":3,"name":"WPB-279247","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-254842].[WPB-279247]","key":"WPB-279247","expanded":true},{"depth":3,"name":"WPB-314788","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-314552].[WPB-314788]","key":"WPB-314788","expanded":true},{"depth":3,"name":"WPB-316247","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-314552].[WPB-316247]","key":"WPB-316247","expanded":true},{"depth":3,"name":"WPB-315765","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315765]","key":"WPB-315765","expanded":true},{"depth":3,"name":"WPB-315766","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315766]","key":"WPB-315766","expanded":true},{"depth":3,"name":"WPB-315767","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315767]","key":"WPB-315767","expanded":true},{"depth":3,"name":"WPB-315768","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315768]","key":"WPB-315768","expanded":true},{"depth":3,"name":"WPB-315770","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315770]","key":"WPB-315770","expanded":true},{"depth":3,"name":"WPB-315771","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315771]","key":"WPB-315771","expanded":true},{"depth":3,"name":"WPB-315772","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315772]","key":"WPB-315772","expanded":true},{"depth":3,"name":"WPB-315773","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315773]","key":"WPB-315773","expanded":true},{"depth":3,"name":"WPB-315774","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315774]","key":"WPB-315774","expanded":true},{"depth":3,"name":"WPB-315775","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315775]","key":"WPB-315775","expanded":true},{"depth":3,"name":"WPB-315776","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315776]","key":"WPB-315776","expanded":true},{"depth":3,"name":"WPB-315777","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315777]","key":"WPB-315777","expanded":true},{"depth":3,"name":"WPB-315778","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315778]","key":"WPB-315778","expanded":true},{"depth":3,"name":"WPB-315779","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315779]","key":"WPB-315779","expanded":true},{"depth":3,"name":"WPB-315780","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315780]","key":"WPB-315780","expanded":true},{"depth":3,"name":"WPB-315781","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315781]","key":"WPB-315781","expanded":true},{"depth":3,"name":"WPB-315782","full_name":"[Issue.Advanced Roadmaps].[(none)].[WPB-315729].[WPB-315782]","key":"WPB-315782","expanded":true}],"bookmarked_members":[]}],"order_by":{"expression":["[Measures].[Open Delivery Raids Count]"],"order":"DESC"},"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Value and Sub-Value Stream","selected_set":["[Value and Sub-Value Stream].[All Value and Sub-Value Streams]"],"members":[{"depth":0,"name":"All Value and Sub-Value Streams","full_name":"[Value and Sub-Value Stream].[All Value and Sub-Value Streams]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"North Star","full_name":"[Value and Sub-Value Stream].[North Star]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Value and Sub-Value Stream].[All Value and Sub-Value Streams]"}],"bookmarked_members":[],"current_page_members":["[Value and Sub-Value Stream].[All Value and Sub-Value Streams]"]},{"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}],"bookmarked_members":[],"current_page_members":["[Project].[All Projects]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[{"dimension":"Measures","name":"linked test","formula":"Generate(\n-- get a list of block issues not in Done status\n Filter(\n [Issue].[Issue].GetMembersByKeys([Issue].CurrentHierarchyMember.get('Blocked by')),\n [Measures].[Issue Status] \u003c\u003e \"Done\"),\n-- show those issues by key\n cast([Issue].CurrentHierarchyMember.Key as string), \",\"\n)","format_string":""},{"dimension":"Measures","name":"test1","formula":"CASE WHEN\n[Issue].CurrentHierarchyMember.level.Name = \"Issue\"\nAND\nNot IsEmpty([Issue].CurrentHierarchyMember.get('Blocked by'))\nTHEN\nGenerate(\nFilter(\n[Issue].[Issue].Getmembersbykeys(\n [Issue].CurrentHierarchyMember.get('Blocked by')\n),\n[Measures].[Issue status] = \"Done\"\n),\nCast ([Issue].CurrentMember.Key as string), ', '\n)\nEND","format_string":""},{"dimension":"Measures","name":"Open_Delivery_Raids","formula":"Generate(\n\nFILTER(([Issue].[Issue].GetMembersByKeys(\n [Issue].CurrentHierarchyMember.get('All links')\n)),\n[Measures].[Issue Type] = \"Delivery RAID\" AND [Measures].[Issue Status] \u003c\u003e \"Closed\"\n)\n,\n\n\"[*\"\n||cast([Issue].CurrentHierarchyMember.Key as string) \n||\"*]\"\n||\"(https://wpb-jira.systems.uk.hsbc/browse/\"\n||cast([Issue].CurrentHierarchyMember.Key as string)\n||\")\" \n|| \" - \" ||\ncast([Issue].CurrentHierarchyMember.get('Summary') as string) \n||\"\u003cbr\u003e\"\n||\"\u003cbr\u003e\"\n)","format_string":"MarkdownFormatter"},{"dimension":"Measures","name":"test4","formula":"-- annotations.group = 4 Issue links and hierarchies\n\nGenerate(\n Filter(\n [Status].[Status].Members,\n Count(\n Filter(\n [Issue].CurrentHierarchy.GetLinkedmembers('All links'),\n [Measures].[Issue status] = [Status].CurrentMember.name)\n )\u003e0\n ),\n [Status].CurrentMember.name\n || \" - \" ||\n Cast(Cast( Count(\n Filter(\n [Issue].CurrentHierarchy.GetLinkedmembers('All links'),\n [Measures].[Issue status] = [Status].CurrentMember.name)\n ) as integer) as string),\n chr(10)\n \n)","format_string":""},{"dimension":"Measures","name":"Open Delivery Raids Count","formula":"COUNT(\nFILTER(([Issue].[Issue].GetMembersByKeys(\n [Issue].CurrentHierarchyMember.get('All links')\n)),\n[Measures].[Issue Type] = \"Delivery RAID\" AND [Measures].[Issue Status] \u003c\u003e \"Closed\"\n))\n","format_string":"#,##0"},{"dimension":"Measures","name":"Delivery_Raid - Risk","formula":"Generate(\n\nFILTER(([Issue].[Issue].GetMembersByKeys(\n [Issue].CurrentHierarchyMember.get('All links')\n)),\n[Measures].[Issue Type.] = \"Risk\" AND [Measures].[Issue Type] = \"Delivery RAID\" AND [Measures].[Issue Status] \u003c\u003e \"Closed\"\n)\n,\n\n\"[*\"\n||cast([Issue].CurrentHierarchyMember.Key as string) \n||\"*]\"\n||\"(https://wpb-jira.systems.uk.hsbc/browse/\"\n||cast([Issue].CurrentHierarchyMember.Key as string)\n||\")\" \n||\"\u003cbr\u003e\"\n)","format_string":"MarkdownFormatter"},{"dimension":"Measures","name":"test2","formula":"Generate(\n\nFILTER(([Issue].[Issue].GetMembersByKeys(\n [Issue].CurrentHierarchyMember.get('All links')\n)),\n[Measures].[Issue Type] = \"Delivery RAID\" AND [Measures].[Issue Status] \u003c\u003e \"Closed\"\n)\n,\ncast([Issue].CurrentHierarchyMember.Key as string) \n)","format_string":""},{"dimension":"Measures","name":"Delivery_Raid - Keys","formula":"Generate(\n\nFILTER(([Issue].[Issue].GetMembersByKeys(\n [Issue].CurrentHierarchyMember.get('All links')\n)),\n[Measures].[Issue Type] = \"Delivery RAID\" AND [Measures].[Issue Status] \u003c\u003e \"Closed\"\n)\n,\n\n\"[*\"\n||cast([Issue].CurrentHierarchyMember.Key as string) \n||\"*]\"\n||\"(https://wpb-jira.systems.uk.hsbc/browse/\"\n||cast([Issue].CurrentHierarchyMember.Key as string)\n||\")\" \n||\"\u003cbr\u003e\"\n)","format_string":"MarkdownFormatter"},{"dimension":"Measures","name":"Delivery_Raid - Dependency","formula":"Generate(\n\nFILTER(([Issue].[Issue].GetMembersByKeys(\n [Issue].CurrentHierarchyMember.get('All links')\n)),\n[Measures].[Issue Type.] = \"Dependency\" AND [Measures].[Issue Type] = \"Delivery RAID\" AND [Measures].[Issue Status] \u003c\u003e \"Closed\"\n)\n,\n\n\"[*\"\n||cast([Issue].CurrentHierarchyMember.Key as string) \n||\"*]\"\n||\"(https://wpb-jira.systems.uk.hsbc/browse/\"\n||cast([Issue].CurrentHierarchyMember.Key as string)\n||\")\" \n||\"\u003cbr\u003e\"\n)","format_string":"MarkdownFormatter"},{"dimension":"Measures","name":"Delivery_Raid - Blocker","formula":"Generate(\n\nFILTER(([Issue].[Issue].GetMembersByKeys(\n [Issue].CurrentHierarchyMember.get('All links')\n)),\n[Measures].[Issue Type.] = \"Blocker\" AND [Measures].[Issue Type] = \"Delivery RAID\" AND [Measures].[Issue Status] \u003c\u003e \"Closed\"\n)\n,\n\n\"[*\"\n||cast([Issue].CurrentHierarchyMember.Key as string) \n||\"*]\"\n||\"(https://wpb-jira.systems.uk.hsbc/browse/\"\n||cast([Issue].CurrentHierarchyMember.Key as string)\n||\")\" \n||\"\u003cbr\u003e\"\n)","format_string":"MarkdownFormatter"},{"dimension":"Measures","name":"Delivery_Raid - Assumption","formula":"Generate(\n\nFILTER(([Issue].[Issue].GetMembersByKeys(\n [Issue].CurrentHierarchyMember.get('All links')\n)),\n[Measures].[Issue Type.] = \"Assumption\" AND [Measures].[Issue Type] = \"Delivery RAID\" AND [Measures].[Issue Status] \u003c\u003e \"Closed\"\n)\n,\n\n\"[*\"\n||cast([Issue].CurrentHierarchyMember.Key as string) \n||\"*]\"\n||\"(https://wpb-jira.systems.uk.hsbc/browse/\"\n||cast([Issue].CurrentHierarchyMember.Key as string)\n||\")\" \n||\"\u003cbr\u003e\"\n)","format_string":"MarkdownFormatter"}]}
} ],
"calculated_members": [{"dimension":"Measures","name":"Issue type","format_string":"","formula":"[Issue Type].[Issue Type].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get('Issue type ID')\n)"},{"dimension":"Measures","name":"Issue status","format_string":"","formula":"[Status].[Status].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get('Status ID')\n)"},{"name":"Issue Type.","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('Type.')","format_string":""}]
}
This is exported definition of the report
Don’t know what was wrong there, but created new report again and sorting is working now with that measure. Topic can be closed
Hi @Konrad,
Thank you for the report definition. I am happy you got it working in a new report.
The only aspect that caught my attention in the report definition is the multitude of expanded members for the “(none)” member of the Advanced Roadmaps “Capability” level. Other than that, I really can’t put my finger on it.
Best,
Roberts // support@eazybi.com