Hi Team,
Can you please help me on the below.
When I perform the search with JQL as in the below screenshot the items in the Backlog are 27
As in below screenshot the backlog items in the eazybi report are 14
Can you please help me if I am filtering anything wrong as per JQL.
Below is the report definition.
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “FS Commitment Curve-Std Filters”,
“folder_name”: “LDC - Design - All Releases”,
“result_view”: “line_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Completed ]”,“[Measures].[In Review/PQM Check/Completed]”,“[Measures].[In Review ]”,“[Measures].[In Progress ]”,“[Measures].[On Hold]”,“[Measures].[Backlog]”,“[Measures].[Baseline]”,“[Measures].[Revised Target]”],“members”:}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time.Weekly].[2023]”,“[Time.Weekly].[2024]”],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Time.Weekly].[Week])”,“members”:,“bookmarked_members”:}],“filter_by”:{“conditions”:[{“expression”:“[Time.Weekly].CurrentHierarchyMember”,“operator”:“between”,“value”:“May 01 2023,Apr 29 2024”,“value_type”:“date”}],“others”:false}},“pages”:{“dimensions”:[{“name”:“Time”,“duplicate”:true,“selected_set”:[“[Time.Weekly].[All Times]”,“[Time.Weekly].[Current week]”,“[Time.Weekly].[Previous week]”,“[Time.Weekly].[Last 4 weeks]”,“[Time.Weekly].[Last 12 weeks]”],“members”:[{“depth”:0,“name”:“All Times”,“full_name”:“[Time.Weekly].[All Times]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“2023”,“full_name”:“[Time.Weekly].[2023]”,“drillable”:true,“parent_full_name”:“[Time.Weekly].[All Times]”},{“depth”:1,“name”:“2024”,“full_name”:“[Time.Weekly].[2024]”,“drillable”:true,“parent_full_name”:“[Time.Weekly].[All Times]”}],“bookmarked_members”:,“current_page_members”:[“[Time.Weekly].[2023]”,“[Time.Weekly].[2024]”]},{“name”:“Workstream”,“selected_set”:[“[Workstream].[Commercial ]”,“[Workstream].[Finance ]”,“[Workstream].[Procurement ]”,“[Workstream].[Operations ]”,“[Workstream].[LDC-Workstreams ]”],“members”:[{“depth”:0,“name”:“LDC-Workstreams “,“full_name”:”[Workstream].[LDC-Workstreams ]”,“calculated”:true,“drillable”:true,“dimension”:“Workstream”}],“bookmarked_members”:,“current_page_members”:[“[Workstream].[LDC-Workstreams ]”]},{“name”:“Status”,“selected_set”:[“[Status].[Status Work Item]”,“[Status].[All Statuses]”],“members”:[{“depth”:0,“name”:“Status Work Item”,“full_name”:“[Status].[Status Work Item]”,“calculated”:true,“drillable”:true,“dimension”:“Status”,“expanded”:true,“drilled_into”:false,“removed”:true}],“bookmarked_members”:,“current_page_members”:[“[Status].[Status Work Item]”]},{“name”:“Work Item type”,“selected_set”:[“[Work Item type].[All Work Item types]”,“[Work Item type].[Functional Specification]”],“members”:[{“depth”:0,“name”:“All Work Item types”,“full_name”:“[Work Item type].[All Work Item types]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:0,“name”:“Functional Specification”,“full_name”:“[Work Item type].[Functional Specification]”,“calculated”:true,“dimension”:“Work Item type”,“formula”:“[Work Item type].[Design]”,“format_string”:“”,“drillable”:null,“annotations”:null,“dependent_calculated_members_count”:1,“dependent_report_ids”:[1192690,1606503,1768131,1793543,1763570,1784980,1186977,1380586,1316318,1797262,1187007,1781129,1175699,1274856,1523973,1380585,1916491,1328603,1660076,1175539,1704541,1175574,1177302,1554544,1177313,1175538,1175572,1177297,1554541,1177311,1704582,1795577,1186999,1495920,1495919,1242124,1242182,1495804,1495803,1198989,1214055,1211637]}],“bookmarked_members”:,“current_page_members”:[“[Work Item type].[Functional Specification]”]},{“name”:“Issue Subtype”,“selected_set”:[“[Issue Subtype].[All Issue Subtypes]”],“members”:[{“depth”:0,“name”:“All Issue Subtypes”,“full_name”:“[Issue Subtype].[All Issue Subtypes]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:,“current_page_members”:[“[Issue Subtype].[All Issue Subtypes]”]},{“name”:“Complexity”,“selected_set”:[“[Complexity].[All Complexities]”],“members”:[{“depth”:0,“name”:“All Complexities”,“full_name”:“[Complexity].[All Complexities]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:,“current_page_members”:[“[Complexity].[All Complexities]”]},{“name”:“Release_Version”,“selected_set”:[“[Release_Version].[All Release_Versions]”,“[Release_Version].[R1]”,“[Release_Version].[R2]”],“members”:[{“depth”:0,“name”:“All Release_Versions”,“full_name”:“[Release_Version].[All Release_Versions]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“R2.0”,“full_name”:“[Release_Version].[R2.0]”,“parent_full_name”:“[Release_Version].[All Release_Versions]”},{“depth”:1,“name”:“R2.5”,“full_name”:“[Release_Version].[R2.5]”,“parent_full_name”:“[Release_Version].[All Release_Versions]”}],“bookmarked_members”:,“current_page_members”:[“[Release_Version].[R2.0]”,“[Release_Version].[R2.5]”]}]},“options”:{},“view”:{“current”:“line_chart”,“maximized”:false,“line_chart”:{“area”:true,“stacked”:true,“swap_axes”:false,“data_labels”:false,“series_options”:{“Actual Completion”:{“type”:“column”,“color”:“#00875A”},“Baseline”:{“type”:“line”,“color”:“#F24B13”,“symbol”:“circle”,“dataLabelType”:false},“Revised Target”:{“type”:“line-Dash”,“color”:“#6F2DAA”,“symbol”:“diamond”,“dataLabelType”:“top horizontal”},“In Progress “:{“type”:“column”,“color”:”#E74B71”},“Scheduled”:{“type”:“column”},“In Review “:{“type”:“column”,“color”:”#86DBA6”},“Backlog”:{“type”:“column”,“color”:“#274380”,“dataLabelType”:false},“Issues due”:{“type”:“column”},“Backlog New”:{“color”:“#930F34”,“type”:“column”},“Issues last updated”:{“type”:“column”},“Issues created”:{“type”:“column”},“Completed”:{“type”:“column”,“color”:“#006644”},“On Hold”:{“type”:“column”},“On Hold “:{“type”:“column”,“color”:”#FFD04D”},“In Review / Completed”:{“type”:“area”},“In Review/Completed “:{“type”:“area”,“color”:”#F24B13”},“Actual Completed”:{“color”:“#006644”,“type”:“column”},“Completed “:{“color”:”#006644”,“type”:“column”},“In Review/PQM Check/Completed”:{“color”:“#F24B13”},“On Hold”:{“color”:“#FFBC29”,“type”:“column”}},“x_axis”:{“0”:{“labelOrientation”:“vertical”}}},“table”:{“freeze_header”:false}},“calculated_members”:[{“dimension”:“Measures”,“name”:“Backlog New”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Backlog]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“In Review / Completed”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[In Review]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Completed]),NULL)”,“format_string”:“”}],“description”:“Total to be used to filter through different options\nBaseline and Revised Target will only show full numbers once the planning by the individual functions is completed\nNote- Please select "S/4HANA Total" to get the total count of S/4HANA Eco System, S/4HANA Report, S/4HANA Report - EA or Select the options individually to get individual count”}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issues history”,“format_string”:“#,##0”,“formula”:“Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n [Measures].[Transitions to]\n - [Measures].[Transitions from]\n ))\n + [Measures].[Transitions to]\n - [Measures].[Transitions from]\n)”},{“dimension”:“Time”,“name”:“Current week”,“format_string”:“”,“formula”:“Aggregate({\n [Time.Weekly].[Week].CurrentDateMember\n})”,“dimension_hierarchy”:“Weekly”},{“dimension”:“Time”,“name”:“Previous week”,“format_string”:“”,“formula”:“Aggregate({\n [Time.Weekly].[Week].CurrentDateMember.PrevMember\n})”,“dimension_hierarchy”:“Weekly”},{“dimension”:“Time”,“name”:“Last 4 weeks”,“format_string”:“”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(‘4 weeks ago’, ‘today’)\n)”,“dimension_hierarchy”:“Weekly”},{“dimension”:“Time”,“name”:“Last 12 weeks”,“format_string”:“”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(‘12 weeks ago’, ‘today’)\n)”,“dimension_hierarchy”:“Weekly”},{“name”:“Completed “,“dimension”:“Measures”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Completed]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Done]),NULL)”,“format_string”:””},{“name”:“Baseline”,“dimension”:“Measures”,“formula”:“/cumulativeSum([Measures].[Issues with Target end])/\n\nSum(\n { PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember },\n [Measures].[Issues with Target end]\n)”,“format_string”:“”},{“name”:“Revised Target”,“dimension”:“Measures”,“formula”:“/CumulativeSum([Measures].[Issues with due date])/\n\nSum(\n { PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember },\n [Measures].[Issues with due date]\n)”,“format_string”:“”},{“name”:“Operations “,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Operations],\n[Workstream].[DAT-Material \u0026 Operations],\n[Workstream].[DAT-Product \u0026 Brand],\n[Workstream].[OPSP-Pillar: Manufacturing],\n[Workstream].[OPSP-Pillar: Quality],\n[Workstream].[OPSP-Pillar: Supply Chain],\n[Workstream].[OPSP-Pillar: TRD],\n[Workstream].[OPS-Manufacturing - Asset Management],\n[Workstream].[OPS-Manufacturing - Manufacturing],\n[Workstream].[OPS-SCM - External Manufacturing],\n[Workstream].[OPS-SCM - Intercompany Order Management / Customs and Trade Compliance],\n[Workstream].[OPS-SCM - Lifecycle Management and Implementation],\n[Workstream].[OPS-SCM - Logistics/Warehousing],\n[Workstream].[OPS-SCM - Long Term, Demand and Supply Planning],\n[Workstream].[OPS-SCM - Production planning],\n[Workstream].[OPS-SCM - Supply Planning],\n[Workstream].[OPS-TRD - Clinical Supply (CTSM)],\n[Workstream].[OPS-TRD - Embedded],\n[Workstream].[OPS-Quality (incl. OBBR)],\n[Workstream].[OPS-TRD - Clinical Supply]\n})”,“format_string”:””},{“name”:“Backlog”,“dimension”:“Measures”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Backlog]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Scheduled]),NULL)”,“format_string”:“”},{“name”:“In Review “,“dimension”:“Measures”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[In Review]),NULL)\n”,“format_string”:””},{“name”:“In Progress “,“dimension”:“Measures”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n ([Measures].[Issues history],[Transition Status].[In Progress]),NULL)\n”,“format_string”:””},{“name”:“Functional Specification”,“dimension”:“Work Item type”,“formula”:“[Work Item type].[Design]”,“format_string”:“”},{“name”:“Status Work Item”,“dimension”:“Status”,“formula”:“Aggregate({\n [Status].[Backlog],\n [Status].[Scheduled],\n [Status].[In Progress],\n [Status].[Screening],\n [Status].[In Screening],\n [Status].[In Review],\n [Status].[Testing],\n [Status].[PQM Check],\n [Status].[Completed],\n [Status].[Done],\n [Status].[On Hold]\n})”,“format_string”:“”},{“name”:“Commercial “,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Commercial],\n[Workstream].[COM-Customer 360],\n[Workstream].[COM-Evidence-to-Value],\n[Workstream].[COM-Lead-to-Quote],\n[Workstream].[COM-Order-to-Release],\n[Workstream].[COMP-Pillar: Commercial],\n[Workstream].[COM-Quote-to-Contract],\n[Workstream].[COM-Release-to-Invoice],\n[Workstream].[COM-Strategy-to-Demand],\n[Workstream].[DAT-Commercial]})”,“format_string”:””},{“name”:“Finance “,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[ALX-R\u0026A Finance \u0026 Procurement],\n[Workstream].[DAT-Finance \u0026 Controlling],\n[Workstream].[DAT-Customer],\n[Workstream].[FIN-Asset Accounting and Projects],\n[Workstream].[FIN-FC\u0026C (incl. Analytics)],\n[Workstream].[FIN-General Ledger and Period End],\n[Workstream].[FIN-I2C - Invoice 2 Cash],\n[Workstream].[FIN-Operations Finance],\n[Workstream].[FIN-Intercompany],\n[Workstream].[FIN-Revenue Management],\n[Workstream].[FIN-Tax],\n[Workstream].[FIN-Treasury and Cash-Management],\n[Workstream].[FIN-RDF]\n})”,“format_string”:””},{“name”:“Procurement “,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[DAT-Supplier \u0026 Procurement],\n[Workstream].[PUR-Accounts Payable and Supplier Experience (SLP)],\n[Workstream].[PUR-CE and Transactional Direct Procurement],\n[Workstream].[PUR-CE and Transactional Indirect Procurement and Front door],\n[Workstream].[PUR-Strategic Procurement and S2C],\n[Workstream].[PUR-S2P],\n[Workstream].[SEP-Supplier Exp- Supplier Data Quality],\n[Workstream].[SEP-Supplier Exp - Supplier Information Mgmt]\n})”,“format_string”:””},{“name”:“Test On Hold”,“dimension”:“Status”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[On Hold]),NULL)”,“format_string”:“”},{“name”:“On Hold”,“dimension”:“Measures”,“formula”:“–IsEmpty([Issue].CurrentHierarchyMember.get(‘Original estimated hours’))\n([Status].[Test On Hold],\n[Measures].[Issues with Original estimated hours])”,“format_string”:“”},{“name”:“In Review/PQM Check/Completed”,“dimension”:“Measures”,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[In Review]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[PQM Check]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Completed]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Done]),NULL)”,“format_string”:“”},{“name”:“LDC-Workstreams “,“dimension”:“Workstream”,“formula”:“Aggregate({\n[Workstream].[Commercial ],\n[Workstream].[Finance ],\n[Workstream].[Procurement ],\n[Workstream].[Operations ]\n})”,“format_string”:””}]
}
Thanks in advance