Hello,
Is there anyway that we can enable drill through option for the total in the below report (or ) can you please help me to create a measure with “Total” that only distinct values. The result should be 10 and need to be able to drill through the total
Report Definition -
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “LDC - Process Automation - Use Cases - Timeline”,
“folder_name”: “LDC - BPM”,
“result_view”: “bar_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Issues Missing Due Dates]”,“[Measures].[Due Date (Global)]”,“[Measures].[Issues created]”,“[Measures].[Issues resolved]”],“members”:[{“depth”:0,“full_name”:“[Measures].[Due Date (Global)]”,“format_string”:“#,##0”,“report_specific”:true,“name”:“Due Date (Global)”,“calculated”:true},{“depth”:0,“name”:“Total Falko”,“full_name”:“[Measures].[Total Falko]”,“format_string”:“#,##0”,“report_specific”:true,“calculated”:true}]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time.Fiscal].[Month].Members”],“members”:,“bookmarked_members”:}]},“pages”:{“dimensions”:[{“name”:“Requirement Type”,“selected_set”:[“[Requirement Type].[Process Automation]”],“members”:[{“depth”:1,“name”:“Process Automation”,“full_name”:“[Requirement Type].[Process Automation]”}],“bookmarked_members”:[{“depth”:1,“name”:“Process Automation”,“full_name”:“[Requirement Type].[Process Automation]”}],“current_page_members”:[“[Requirement Type].[Process Automation]”]}]},“options”:{“nonempty”:true,“total”:“rows”},“view”:{“current”:“bar_chart”,“maximized”:false,“bar_chart”:{“stacked”:false,“vertical”:true,“swap_axes”:false,“data_labels”:true,“series_options”:{“Actual per sprint”:{“color”:“#006644”},“Completed “:{“type”:“column”,“color”:”#006644”,“dataLabelType”:false},“Due Date (Global)”:{“type”:“line”,“color”:“#274380”,“dataLabelType”:“top horizontal”},“Backlog”:{“color”:“#930F34”,“dataLabelType”:“top horizontal”},“In Progress “:{“color”:”#F24B13”,“dataLabelType”:“top horizontal”},“Screening”:{“color”:“#FFA806”,“dataLabelType”:“top horizontal”},“In Review “:{“color”:”#5DC48B”,“dataLabelType”:“top horizontal”},“Documents Closed”:{“color”:“#34AD70”,“dataLabelType”:“top horizontal”},“Documentation - Not Started”:{“color”:“#B31238”,“dataLabelType”:“top horizontal”},“Documentation - In Progress”:{“dataLabelType”:“top horizontal”},“Documentation - In Review”:{“color”:“#F24B13”,“dataLabelType”:“top horizontal”},“Due Date Missing”:{“type”:“column”}},“font_size”:“large”},“table”:{“cell_formatting”:{},“freeze_header”:true}},“calculated_members”:[{“dimension”:“Measures”,“name”:“Sprint 1 Completion”,“formula”:“[Measures].[Sprint 1 YTD]/[Measures].[Sprint 1 Planned]”,“format_string”:“#0.00%”},{“dimension”:“Measures”,“name”:“Falko Test”,“formula”:“(\n [Measures].[Sprint issues at closing],\n [Transition Status.Category].[Done],\n [Baseline Sprint].[CD Sprint 2],\n [Sprint].[CD Sprint 2]\n)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Sprint 2 Completion”,“formula”:“[Measures].[Sprint 2 YTD]/[Measures].[Sprint 2 Planned]”,“format_string”:“#0.00%”},{“dimension”:“Measures”,“name”:“Completion Rate”,“formula”:“[Measures].[Total Done]/[Measures].[Total Planned]”,“format_string”:“#0.00%”},{“dimension”:“Measures”,“name”:“Total Remaining”,“formula”:“[Measures].[Total Planned]-\n[Measures].[Total Done]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“L5s-KDDs-KSDs- Closed between Sprint 4”,“formula”:“(\n [Measures].[Sprint issues at closing],\n [Transition Status.Category].[Done],\n [Baseline Sprint].[CD Sprint 4],\n [Sprint].[CD Sprint 4]\n)+\n\nAggregate(\n[Time].[Day].DateMembersBetween(‘May 30 2022’, ‘July 01 2022’))”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Started - Yes”,“formula”:“[R2 - Started].[Yes]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Started - no”,“formula”:“[R2 - Started].[No]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Lean Review - Yes”,“formula”:“[R2 - Lean Review].[Yes]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Lean Review - No”,“formula”:“[R2 - Lean Review].[No]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Completed and Handed Over to BPM - Yes”,“formula”:“[R2 - Completed and Handed Over to BPM].[Yes]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Completed and Handed Over to BPM - No”,“formula”:“[R2 - Completed and Handed Over to BPM].[No]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Mapped in Adonis - Yes”,“formula”:“[R2 - Mapped (in Adonis)].[Yes]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Mapped in Adonis - No”,“formula”:“[R2 - Mapped (in Adonis)].[No]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Methodical Review - Yes”,“formula”:“[R2 - Methodical Review].[Yes]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - Methodical Review - No”,“formula”:“[R2 - Methodical Review].[No]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - GPO Sign Off - Yes”,“formula”:“[R2 - GPO Sign Off].[Yes]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“R2 - GPO Sign Off - No”,“formula”:“[R2 - GPO Sign Off].[No]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Document Creation (In Progress)”,“formula”:“[Document Creation].[in progress]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Document Creation (Completed)”,“formula”:“[Document Creation].[Completed]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Methodical Review (in progress)”,“formula”:“[Methodical Review].[in progress]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Methodical Review (completed)”,“formula”:“[Methodical Review].[Completed]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“GPO Review (in progress)”,“formula”:“[GPO Review].[in progress]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“GPO Review (Approved)”,“formula”:“[GPO Review].[Approved]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Final Document Link Provided (Yes)”,“formula”:“[Final Document Link Provided].[Yes]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Final Document Link Provided (no)”,“formula”:“[Final Document Link Provided].[No],\n[Final Document Link Provided].[(none)]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“GXP (Yes)”,“formula”:“[GxP Relevance].[Yes]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“GXP (No or TBD)”,“formula”:“Aggregate({\n[GxP Relevance].[(none)],\n[GxP Relevance].[TBD],\n[GxP Relevance].[no]\n})”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Document Creation (not started)”,“formula”:“[Document Creation].[(none)]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Methodical Review (not started)”,“formula”:“[Methodical Review].[(none)]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“GPO Review (not started)”,“formula”:“[GPO Review].[(none)]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Adonis linkage Completed (Yes)”,“formula”:“[Adonis linkage Completed].[Yes])]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Adonis Linkage Completed (no)”,“formula”:“[Adonis linkage Completed].[No])]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Open Items”,“formula”:“[Measures].[Issues created]-[Measures].[Issues resolved]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Percentage Completion”,“formula”:“[Measures].[Issues resolved]/\n[Measures].[Issues created]”,“format_string”:“#0%”},{“dimension”:“Measures”,“name”:“Due Date (Global)”,“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”:“”},{“dimension”:“Measures”,“name”:“Documents Closed”,“formula”:“IIf(DateDiffDays(Now(),[Time.fiscal].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Completed]),NULL)+\nIIf(DateDiffDays(Now(),[Time.fiscal].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Done]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Documentation - Not Started”,“formula”:“IIf(DateDiffDays(Now(),[Time.Fiscal].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Backlog]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Fiscal].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Scheduled]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Documentation - In Progress”,“formula”:“IIf(DateDiffDays(Now(),[Time.Fiscal].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[In Progress]),NULL)\n\n”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Documentation - In Review”,“formula”:“IIf(DateDiffDays(Now(),[Time.Fiscal].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[In Screening]),NULL)+\nIIf(DateDiffDays(Now(),[Time.Fiscal].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[In Review]),NULL)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Delay NFCM”,“formula”:“CASE WHEN\n [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember\nTHEN\n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchy.Levels("Day").CurrentDateMember),\n [Measures].[Issues with due date]\n \n )\nWHEN\n DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)\n OR\n DateInPeriod("Today", [Time].CurrentHierarchyMember)\nTHEN \n SUM(\n PreviousPeriods(\n [Time].CurrentHierarchyMember),\n [Measures].[Issues with due date]\n )\nEND”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Documents finalized”,“formula”:“[Status].[Completed]”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Issues Missing Due date Count”,“formula”:“Count(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n IsEmpty([Issue].CurrentMember.Get(‘Due date’))\n AND\n ([Measures].[Issues created],\n [Time].CurrentHierarchy.DefaultMember) \u003e 0\n )\n)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Due Date (Global) Count”,“formula”:“Count(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n NOT IsEmpty([Issue].CurrentMember.Get(‘Due date’))\n AND\n DateCompare(\n [Issue].CurrentMember.Get(‘Due date’),\n [Time].CurrentHierarchyMember.StartDate\n ) \u003c= 0\n )\n)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Total Falko”,“formula”:“[Measures].[Issues created]”,“format_string”:“”}]}
} ],
“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”:“Measures”,“name”:“Issue created date”,“format_string”:“mmm dd yyyy”,“formula”:“[Issue].CurrentHierarchyMember.get(‘Created at’)”},{“name”:“Sprint issues at closing”,“dimension”:“Measures”,“format_string”:“#,##0”,“formula”:“( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n – An issue was in a sprint at closing\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n”},{“name”:“Not Cancelled”,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { \n [Status].[Cancelled]\n }\n )\n)”,“format_string”:“”},{“name”:“CD Sprint 2”,“dimension”:“Sprint”,“formula”:“[Sprint].[Scrum Board for CDS].[CD Sprint 2]”,“format_string”:“”},{“name”:“CD Sprint 4”,“dimension”:“Sprint”,“formula”:“[Sprint].[Scrum Board for CDS].[CD Sprint 4]”,“format_string”:“”},{“name”:“Total Planned”,“dimension”:“Measures”,“formula”:“[Status].[Not Cancelled]”,“format_string”:“”},{“name”:“Sprint 1 Planned”,“dimension”:“Measures”,“formula”:“[Baseline Sprint].[CD Sprint 1]”,“format_string”:“”},{“name”:“Sprint 2 Planned”,“dimension”:“Measures”,“formula”:“[Baseline Sprint].[CD Sprint 2]”,“format_string”:“”},{“name”:“Sprint 1 YTD”,“dimension”:“Measures”,“formula”:“(\n [Baseline Sprint].[CD Sprint 1],\n [Status.Category].[Done]\n)”,“format_string”:“”},{“name”:“Sprint 2 YTD”,“dimension”:“Measures”,“formula”:“(\n [Baseline Sprint].[CD Sprint 2],\n [Status.Category].[Done]\n)”,“format_string”:“”},{“name”:“Total Done”,“dimension”:“Measures”,“formula”:“[Status.Category].[Done]”,“format_string”:“”},{“name”:“Issues Missing Due Dates”,“dimension”:“Measures”,“formula”:“Sum(\nFilter(\nDescendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),\nDateInPeriod(\n[Measures].[Issue created date],\n[Time].CurrenthierarchyMember\n)\nAND\nIsEmpty([Issue].CurrentMember.Get(‘Due date’))\n),\n[Measures].[Issues created]\n)”,“format_string”:“#,##0”}]
}