Help…I am trying to create a report with the Issues Counts per Assignee, with the number of Provisioned Users on each Issue, Then columns with Averages for the Completed Issues MTD divided by the number of Workdays in the Month.
I can get the # of Issues Closed MTD and the # Provisioned Users Closed but I can’t get the Averages.
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “SAA Monthly Fulfillment Recap”,
“folder_name”: “MSR”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Issues closed]”,“[Measures].[# Provisioned Users closed]”,“[Measures].[Average completed daily]”,“[Measures].[Ave Provisioned Users MTD]”],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Assignee”,“selected_set”:[“[Assignee].[Anne Poelker]”,“[Assignee].[Gene Sullivant]”,“[Assignee].[Marisol Badillo]”,“[Assignee].[Patrick Rach]”,“[Assignee].[Susan Abdel-Ghani]”],“members”:[],“bookmarked_members”:[{“depth”:1,“name”:“Gene Sullivant”,“full_name”:“[Assignee].[Gene Sullivant]”,“key”:“5ed6a8f18063980c18f407e2”},{“depth”:1,“name”:“Susan Abdel-Ghani”,“full_name”:“[Assignee].[Susan Abdel-Ghani]”,“key”:“5cf16f6ff34cf30f24aaf98e”},{“depth”:1,“name”:“Patrick Rach”,“full_name”:“[Assignee].[Patrick Rach]”,“key”:“5ca4f7f13270b449b4fec421”},{“depth”:1,“name”:“Anne Poelker”,“full_name”:“[Assignee].[Anne Poelker]”,“key”:“61a69b623618cd006fe265d7”},{“depth”:1,“name”:“Marisol Badillo”,“full_name”:“[Assignee].[Marisol Badillo]”,“key”:“61a69b63977c5b007212455f”}]},{“name”:“Status”,“selected_set”:[“[Status].[Completed]”],“members”:[],“bookmarked_members”:[{“depth”:1,“name”:“Completed”,“full_name”:“[Status].[Completed]”},{“depth”:2,“name”:“Canceled”,“full_name”:“[Status.Category].[Done].[Canceled]”},{“depth”:2,“name”:“Rejected”,“full_name”:“[Status.Category].[Done].[Rejected]”},{“depth”:2,“name”:“Completed”,“full_name”:“[Status.Category].[Done].[Completed]”}]}]},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:[“[Project].[MSR - Mortgage Security Requests]”],“members”:[{“depth”:1,“name”:“MSR - Mortgage Security Requests”,“full_name”:“[Project].[MSR - Mortgage Security Requests]”,“drillable”:true,“key”:“MSR”}],“bookmarked_members”:[{“depth”:1,“name”:“MSR - Mortgage Security Requests”,“full_name”:“[Project].[MSR - Mortgage Security Requests]”,“drillable”:true,“key”:“MSR”}],“current_page_members”:[“[Project].[MSR - Mortgage Security Requests]”]},{“name”:“Issue Type”,“selected_set”:[“[Issue Type.By name].[Submit a request]”],“members”:[{“depth”:1,“name”:“Submit a request”,“full_name”:“[Issue Type.By name].[Submit a request]”,“drillable”:true}],“bookmarked_members”:[{“depth”:1,“name”:“Service Request”,“full_name”:“[Issue Type.By name].[Service Request]”,“drillable”:true},{“depth”:1,“name”:“Submit a request”,“full_name”:“[Issue Type.By name].[Submit a request]”,“drillable”:true}],“current_page_members”:[“[Issue Type.By name].[Submit a request]”]},{“name”:“Time”,“selected_set”:[“[Time].[Current month]”,“[Time].[Current year]”],“members”:[{“depth”:0,“name”:“Current month”,“full_name”:“[Time].[Current month]”,“annotations”:{“group”:“Default”,“predefined”:“true”},“calculated”:true,“drillable”:true,“dimension”:“Time”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:0,“name”:“Current year”,“full_name”:“[Time].[Current year]”,“annotations”:{“group”:“Default”,“predefined”:“true”},“calculated”:true,“drillable”:true,“dimension”:“Time”}],“bookmarked_members”:[],“current_page_members”:[“[Time].[Current year]”]},{“name”:“Status”,“duplicate”:true,“selected_set”:[“[Status].[Completed]”,“[Status].[Submitted]”,“[Status].[Submitted with Approval]”,“[Status].[Assigned]”,“[Status].[In_Process]”],“members”:[{“depth”:1,“name”:“Completed”,“full_name”:“[Status].[Completed]”}],“bookmarked_members”:[{“depth”:1,“name”:“Submitted”,“full_name”:“[Status].[Submitted]”},{“depth”:1,“name”:“Submitted with Approval”,“full_name”:“[Status].[Submitted with Approval]”},{“depth”:1,“name”:“Assigned”,“full_name”:“[Status].[Assigned]”},{“depth”:1,“name”:“In_Process”,“full_name”:“[Status].[In_Process]”}],“current_page_members”:[“[Status].[Completed]”]}]},“options”:{“total”:“rows”},“view”:{“current”:“table”,“maximized”:false,“table”:{“row_dimension_headers”:{“Assignee”:true},“cell_formatting”:{},“freeze_header”:true}},“calculated_members”:[],“description”:“SAA Monthly Fulfillment Recap (Current Month 2022)\n=================================”}
} ],
“calculated_members”: [{“dimension”:“Time”,“name”:“Current month”,“format_string”:“”,“formula”:“Aggregate({\n [Time].[Month].CurrentDateMember\n})”},{“dimension”:“Time”,“name”:“Current year”,“format_string”:“”,“formula”:“Aggregate({\n [Time].[Year].CurrentDateMember\n})”},{“name”:“Average completed daily”,“dimension”:“Measures”,“formula”:“CASE WHEN [Measures].[Issues closed] \u003e 0 THEN\n [Measures].[Total Issues closed count] / [Time].[Issues closing days]\n END”,“format_string”:“#,##0.00”},{“name”:“Ave Provisioned Users MTD”,“dimension”:“Measures”,“formula”:“CASE WHEN [Measures].[# Provisioned Users closed] \u003e 0 THEN\n[Measures].[Total # Provisioned Users closed count] / [Measures].[Issues closing days]\nEND”,“format_string”:“#,##0.00”}]
}