Queries

• 8
  Expressions:

  

  Data Items

  

  Detail Filters
• Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model

• 

  

  Query-All Cases

  Data Items

  4

  Collective Agreement Group	Case #sq($[Language_lookup]{$runLocale})# When 'EN' then Case WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-939', 'ST-940', 'ST-941', 'ST-942') THEN 'FS' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-614', 'ST-913', 'ST-814', 'ST-815', 'ST-816', 'ST-817') THEN 'AV' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-931', 'ST-932') THEN 'CAP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-620', 'ST-719', 'ST-720', 'ST-721', 'ST-722', 'ST-723', 'ST-724', 'ST-726', 'ST-727', 'ST-770', 'ST-771', 'ST-905', 'ST-906', 'ST-725', 'ST-772', 'ST-773', 'ST-774', 'ST-874', 'ST-875', 'ST-876', 'ST-877') THEN 'CS' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-925') THEN 'CSN' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-927', 'ST-936', 'ST-928') THEN 'CX' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-623', 'ST-728', 'ST-729', 'ST-730', 'ST-732', 'ST-735', 'ST-736', 'ST-776', 'ST-850', 'ST-851', 'ST-852', 'ST-853', 'ST-888', 'ST-889', 'ST-890', 'ST-891', 'ST-892', 'ST-731', 'ST-733', 'ST-734') THEN 'EB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-622', 'ST-664', 'ST-665', 'ST-666', 'ST-667', 'ST-670', 'ST-672', 'ST-673', 'ST-674', 'ST-911', 'ST-668', 'ST-669', 'ST-671', 'ST-675', 'ST-700', 'ST-830', 'ST-831', 'ST-832', 'ST-833') THEN 'EC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-737', 'ST-738', 'ST-739', 'ST-740', 'ST-742', 'ST-743', 'ST-775', 'ST-907', 'ST-628', 'ST-741', 'ST-854', 'ST-855', 'ST-856', 'ST-857') THEN 'EL' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-613', 'ST-914', 'ST-915', 'ST-810', 'ST-811', 'ST-812', 'ST-813') THEN 'FI' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-924') THEN 'HOC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-933') THEN 'LP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-934','ST-962') THEN 'NAR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-929') THEN 'NFB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-879', 'ST-880', 'ST-881', 'ST-882', 'ST-883', 'ST-884', 'ST-885', 'ST-886', 'ST-893', 'ST-894', 'ST-895', 'ST-896', 'ST-897', 'ST-898', 'ST-899', 'ST-878','ST-963') THEN 'NR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-923') THEN 'NRC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-701') THEN 'Overpayments *' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-616', 'ST-704', 'ST-705', 'ST-706', 'ST-707', 'ST-708', 'ST-709', 'ST-710', 'ST-711', 'ST-712', 'ST-713', 'ST-714', 'ST-715', 'ST-717', 'ST-718', 'ST-782', 'ST-903', 'ST-904', 'ST-716', 'ST-846', 'ST-847', 'ST-848', 'ST-849') THEN 'PA' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-781') THEN 'PH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-627') THEN 'PR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-922') THEN 'RCMP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-654', 'ST-655', 'ST-658', 'ST-660', 'ST-661', 'ST-662', 'ST-918', 'ST-919', 'ST-617', 'ST-653', 'ST-656', 'ST-657', 'ST-659', 'ST-663', 'ST-834', 'ST-835', 'ST-836', 'ST-837') THEN 'RE' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-618', 'ST-642', 'ST-643', 'ST-644', 'ST-647', 'ST-649', 'ST-650', 'ST-651', 'ST-917', 'ST-645', 'ST-646', 'ST-648', 'ST-652', 'ST-822', 'ST-823', 'ST-824', 'ST-825') THEN 'RO' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-760', 'ST-761', 'ST-762', 'ST-763', 'ST-764', 'ST-767', 'ST-768', 'ST-769', 'ST-778', 'ST-779', 'ST-783', 'ST-784', 'ST-785', 'ST-786', 'ST-787', 'ST-788', 'ST-789', 'ST-920', 'ST-921', 'ST-930', 'ST-619', 'ST-765', 'ST-766', 'ST-870', 'ST-871', 'ST-872', 'ST-873') THEN 'SH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-615', 'ST-625', 'ST-912', 'ST-818', 'ST-819', 'ST-820', 'ST-821') THEN 'SP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-744', 'ST-745', 'ST-746', 'ST-747', 'ST-748', 'ST-751', 'ST-752', 'ST-753', 'ST-754', 'ST-755', 'ST-757', 'ST-759', 'ST-780', 'ST-908', 'ST-909', 'ST-910') THEN 'SR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-935') THEN 'SSH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-621', 'ST-688', 'ST-689', 'ST-690', 'ST-691', 'ST-694', 'ST-696', 'ST-697', 'ST-698', 'ST-703', 'ST-900', 'ST-901', 'ST-902', 'ST-692', 'ST-693', 'ST-695', 'ST-699', 'ST-842', 'ST-843', 'ST-844', 'ST-845') THEN 'SV' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-624', 'ST-676', 'ST-677', 'ST-678', 'ST-679', 'ST-682', 'ST-684', 'ST-685', 'ST-686', 'ST-702', 'ST-838', 'ST-839', 'ST-840', 'ST-841', 'ST-680', 'ST-681', 'ST-683', 'ST-687') THEN 'TC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-626', 'ST-632', 'ST-633', 'ST-634', 'ST-635', 'ST-636', 'ST-638', 'ST-639', 'ST-640', 'ST-916', 'ST-637', 'ST-641', 'ST-826', 'ST-827', 'ST-828', 'ST-829') THEN 'TR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-944', 'ST-945', 'ST-946') THEN 'SO' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-952') THEN 'FB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-790', 'ST-791', 'ST-792', 'ST-793', 'ST-796', 'ST-797', 'ST-798', 'ST-799', 'ST-800', 'ST-801', 'ST-802', 'ST-803', 'ST-804', 'ST-805', 'ST-806', 'ST-807', 'ST-808', 'ST-809', 'ST-926', 'ST-957', 'ST-794', 'ST-795', 'ST-954', 'ST-955', 'ST-956') THEN 'Excluded (TBS)' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-953') THEN 'NBC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-629', 'ST-756', 'ST-858', 'ST-859', 'ST-860', 'ST-861') THEN 'SR-E' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-630', 'ST-749', 'ST-750', 'ST-862', 'ST-863', 'ST-864', 'ST-865') THEN 'SR-W' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-631', 'ST-758', 'ST-866', 'ST-867', 'ST-868', 'ST-869') THEN 'SR-C' else 'Uncategorized Sub-Types**' End When 'FR' then Case WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-939', 'ST-940', 'ST-941', 'ST-942') THEN 'FS' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-614', 'ST-913', 'ST-814', 'ST-815', 'ST-816', 'ST-817') THEN 'AV' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-931', 'ST-932') THEN 'CAP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-620', 'ST-719', 'ST-720', 'ST-721', 'ST-722', 'ST-723', 'ST-724', 'ST-726', 'ST-727', 'ST-770', 'ST-771', 'ST-905', 'ST-906', 'ST-725', 'ST-772', 'ST-773', 'ST-774', 'ST-874', 'ST-875', 'ST-876', 'ST-877') THEN 'CS' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-925') THEN 'CSN' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-927', 'ST-936', 'ST-928') THEN 'CX' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-623', 'ST-728', 'ST-729', 'ST-730', 'ST-732', 'ST-735', 'ST-736', 'ST-776', 'ST-850', 'ST-851', 'ST-852', 'ST-853', 'ST-888', 'ST-889', 'ST-890', 'ST-891', 'ST-892', 'ST-731', 'ST-733', 'ST-734') THEN 'EB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-622', 'ST-664', 'ST-665', 'ST-666', 'ST-667', 'ST-670', 'ST-672', 'ST-673', 'ST-674', 'ST-911', 'ST-668', 'ST-669', 'ST-671', 'ST-675', 'ST-700', 'ST-830', 'ST-831', 'ST-832', 'ST-833') THEN 'EC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-737', 'ST-738', 'ST-739', 'ST-740', 'ST-742', 'ST-743', 'ST-775', 'ST-907', 'ST-628', 'ST-741', 'ST-854', 'ST-855', 'ST-856', 'ST-857') THEN 'EL' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-613', 'ST-914', 'ST-915', 'ST-810', 'ST-811', 'ST-812', 'ST-813') THEN 'FI' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-924') THEN 'HOC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-933') THEN 'LP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-934', 'ST-962') THEN 'NAR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-929') THEN 'NFB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-879', 'ST-880', 'ST-881', 'ST-882', 'ST-883', 'ST-884', 'ST-885', 'ST-886', 'ST-893', 'ST-894', 'ST-895', 'ST-896', 'ST-897', 'ST-898', 'ST-899', 'ST-878','ST-962') THEN 'NR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-923') THEN 'NRC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-701') THEN 'Trop-payés *' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-616', 'ST-704', 'ST-705', 'ST-706', 'ST-707', 'ST-708', 'ST-709', 'ST-710', 'ST-711', 'ST-712', 'ST-713', 'ST-714', 'ST-715', 'ST-717', 'ST-718', 'ST-782', 'ST-903', 'ST-904', 'ST-716', 'ST-846', 'ST-847', 'ST-848', 'ST-849') THEN 'PA' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-781') THEN 'PH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-627') THEN 'PR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-922') THEN 'RCMP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-654', 'ST-655', 'ST-658', 'ST-660', 'ST-661', 'ST-662', 'ST-918', 'ST-919', 'ST-617', 'ST-653', 'ST-656', 'ST-657', 'ST-659', 'ST-663', 'ST-834', 'ST-835', 'ST-836', 'ST-837') THEN 'RE' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-618', 'ST-642', 'ST-643', 'ST-644', 'ST-647', 'ST-649', 'ST-650', 'ST-651', 'ST-917', 'ST-645', 'ST-646', 'ST-648', 'ST-652', 'ST-822', 'ST-823', 'ST-824', 'ST-825') THEN 'RO' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-760', 'ST-761', 'ST-762', 'ST-763', 'ST-764', 'ST-767', 'ST-768', 'ST-769', 'ST-778', 'ST-779', 'ST-783', 'ST-784', 'ST-785', 'ST-786', 'ST-787', 'ST-788', 'ST-789', 'ST-920', 'ST-921', 'ST-930', 'ST-619', 'ST-765', 'ST-766', 'ST-870', 'ST-871', 'ST-872', 'ST-873') THEN 'SH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-615', 'ST-625', 'ST-912', 'ST-818', 'ST-819', 'ST-820', 'ST-821') THEN 'SP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-744', 'ST-745', 'ST-746', 'ST-747', 'ST-748', 'ST-751', 'ST-752', 'ST-753', 'ST-754', 'ST-755', 'ST-757', 'ST-759', 'ST-780', 'ST-908', 'ST-909', 'ST-910') THEN 'SR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-935') THEN 'SSH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-621', 'ST-688', 'ST-689', 'ST-690', 'ST-691', 'ST-694', 'ST-696', 'ST-697', 'ST-698', 'ST-703', 'ST-900', 'ST-901', 'ST-902', 'ST-692', 'ST-693', 'ST-695', 'ST-699', 'ST-842', 'ST-843', 'ST-844', 'ST-845') THEN 'SV' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-624', 'ST-676', 'ST-677', 'ST-678', 'ST-679', 'ST-682', 'ST-684', 'ST-685', 'ST-686', 'ST-702', 'ST-838', 'ST-839', 'ST-840', 'ST-841', 'ST-680', 'ST-681', 'ST-683', 'ST-687') THEN 'TC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-626', 'ST-632', 'ST-633', 'ST-634', 'ST-635', 'ST-636', 'ST-638', 'ST-639', 'ST-640', 'ST-916', 'ST-637', 'ST-641', 'ST-826', 'ST-827', 'ST-828', 'ST-829') THEN 'TR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-944', 'ST-945', 'ST-946') THEN 'SO' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-952') THEN 'FB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-790', 'ST-791', 'ST-792', 'ST-793', 'ST-796', 'ST-797', 'ST-798', 'ST-799', 'ST-800', 'ST-801', 'ST-802', 'ST-803', 'ST-804', 'ST-805', 'ST-806', 'ST-807', 'ST-808', 'ST-809', 'ST-926', 'ST-957', 'ST-794', 'ST-795', 'ST-954', 'ST-955', 'ST-956') THEN 'Excluses (SCT)' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-953') THEN 'NBC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-629', 'ST-756', 'ST-858', 'ST-859', 'ST-860', 'ST-861') THEN 'SR-E' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-630', 'ST-749', 'ST-750', 'ST-862', 'ST-863', 'ST-864', 'ST-865') THEN 'SR-W' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-631', 'ST-758', 'ST-866', 'ST-867', 'ST-868', 'ST-869') THEN 'SR-C' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-962') THEN 'NAR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-963') THEN 'NR' else 'Sous-types non catégorisés**' End END
  Transaction Type	[Presentation].[Sub Type].[TRANSACTION_TYPE]
  Case Count	[Presentation].[Case].[Case Count]	Aggregate count Rollup summarize label Count of Case Number
  ALL-PRI	[Presentation].[Case].[PRI]	Rollup summarize label Distinct Count of PRI

  • Detail Filters
    1
  • 

    [Presentation].[Sub Type].[TRANSACTION_TYPE] in ( '12: CAI Overpayments / ICC Trop-payés' , '13: CAI Revisions / ICC Révisions', '14: CAI Post Validation / ICC Post Vérification' )
    
• 

  

  Summary All Cases

  Data Items

  5

  Collective Agreement Group	[Query-All Cases].[Collective Agreement Group]
  Transaction Type	[Query-All Cases].[Transaction Type]
  Case Count	[Query-All Cases].[Case Count]	Aggregate total
  PRI Count	[Query-All Cases].[ALL-PRI]	Aggregate countDistinct Rollup calculated
  fake-join	1

• 

  

  Query-Active

  Data Items

  4

  Collective Agreement Group	Case #sq($[Language_lookup]{$runLocale})# When 'EN' then Case WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-939', 'ST-940', 'ST-941', 'ST-942') THEN 'FS' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-614', 'ST-913', 'ST-814', 'ST-815', 'ST-816', 'ST-817') THEN 'AV' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-931', 'ST-932') THEN 'CAP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-620', 'ST-719', 'ST-720', 'ST-721', 'ST-722', 'ST-723', 'ST-724', 'ST-726', 'ST-727', 'ST-770', 'ST-771', 'ST-905', 'ST-906', 'ST-725', 'ST-772', 'ST-773', 'ST-774', 'ST-874', 'ST-875', 'ST-876', 'ST-877') THEN 'CS' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-925') THEN 'CSN' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-927', 'ST-936', 'ST-928') THEN 'CX' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-623', 'ST-728', 'ST-729', 'ST-730', 'ST-732', 'ST-735', 'ST-736', 'ST-776', 'ST-850', 'ST-851', 'ST-852', 'ST-853', 'ST-888', 'ST-889', 'ST-890', 'ST-891', 'ST-892', 'ST-731', 'ST-733', 'ST-734') THEN 'EB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-622', 'ST-664', 'ST-665', 'ST-666', 'ST-667', 'ST-670', 'ST-672', 'ST-673', 'ST-674', 'ST-911', 'ST-668', 'ST-669', 'ST-671', 'ST-675', 'ST-700', 'ST-830', 'ST-831', 'ST-832', 'ST-833') THEN 'EC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-737', 'ST-738', 'ST-739', 'ST-740', 'ST-742', 'ST-743', 'ST-775', 'ST-907', 'ST-628', 'ST-741', 'ST-854', 'ST-855', 'ST-856', 'ST-857') THEN 'EL' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-613', 'ST-914', 'ST-915', 'ST-810', 'ST-811', 'ST-812', 'ST-813') THEN 'FI' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-924') THEN 'HOC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-933') THEN 'LP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-934','ST-962') THEN 'NAR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-929') THEN 'NFB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-879', 'ST-880', 'ST-881', 'ST-882', 'ST-883', 'ST-884', 'ST-885', 'ST-886', 'ST-893', 'ST-894', 'ST-895', 'ST-896', 'ST-897', 'ST-898', 'ST-899', 'ST-878','ST-963') THEN 'NR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-923') THEN 'NRC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-701') THEN 'Overpayments *' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-616', 'ST-704', 'ST-705', 'ST-706', 'ST-707', 'ST-708', 'ST-709', 'ST-710', 'ST-711', 'ST-712', 'ST-713', 'ST-714', 'ST-715', 'ST-717', 'ST-718', 'ST-782', 'ST-903', 'ST-904', 'ST-716', 'ST-846', 'ST-847', 'ST-848', 'ST-849') THEN 'PA' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-781') THEN 'PH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-627') THEN 'PR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-922') THEN 'RCMP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-654', 'ST-655', 'ST-658', 'ST-660', 'ST-661', 'ST-662', 'ST-918', 'ST-919', 'ST-617', 'ST-653', 'ST-656', 'ST-657', 'ST-659', 'ST-663', 'ST-834', 'ST-835', 'ST-836', 'ST-837') THEN 'RE' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-618', 'ST-642', 'ST-643', 'ST-644', 'ST-647', 'ST-649', 'ST-650', 'ST-651', 'ST-917', 'ST-645', 'ST-646', 'ST-648', 'ST-652', 'ST-822', 'ST-823', 'ST-824', 'ST-825') THEN 'RO' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-760', 'ST-761', 'ST-762', 'ST-763', 'ST-764', 'ST-767', 'ST-768', 'ST-769', 'ST-778', 'ST-779', 'ST-783', 'ST-784', 'ST-785', 'ST-786', 'ST-787', 'ST-788', 'ST-789', 'ST-920', 'ST-921', 'ST-930', 'ST-619', 'ST-765', 'ST-766', 'ST-870', 'ST-871', 'ST-872', 'ST-873') THEN 'SH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-615', 'ST-625', 'ST-912', 'ST-818', 'ST-819', 'ST-820', 'ST-821') THEN 'SP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-744', 'ST-745', 'ST-746', 'ST-747', 'ST-748', 'ST-751', 'ST-752', 'ST-753', 'ST-754', 'ST-755', 'ST-757', 'ST-759', 'ST-780', 'ST-908', 'ST-909', 'ST-910') THEN 'SR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-935') THEN 'SSH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-621', 'ST-688', 'ST-689', 'ST-690', 'ST-691', 'ST-694', 'ST-696', 'ST-697', 'ST-698', 'ST-703', 'ST-900', 'ST-901', 'ST-902', 'ST-692', 'ST-693', 'ST-695', 'ST-699', 'ST-842', 'ST-843', 'ST-844', 'ST-845') THEN 'SV' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-624', 'ST-676', 'ST-677', 'ST-678', 'ST-679', 'ST-682', 'ST-684', 'ST-685', 'ST-686', 'ST-702', 'ST-838', 'ST-839', 'ST-840', 'ST-841', 'ST-680', 'ST-681', 'ST-683', 'ST-687') THEN 'TC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-626', 'ST-632', 'ST-633', 'ST-634', 'ST-635', 'ST-636', 'ST-638', 'ST-639', 'ST-640', 'ST-916', 'ST-637', 'ST-641', 'ST-826', 'ST-827', 'ST-828', 'ST-829') THEN 'TR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-944', 'ST-945', 'ST-946') THEN 'SO' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-952') THEN 'FB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-790', 'ST-791', 'ST-792', 'ST-793', 'ST-796', 'ST-797', 'ST-798', 'ST-799', 'ST-800', 'ST-801', 'ST-802', 'ST-803', 'ST-804', 'ST-805', 'ST-806', 'ST-807', 'ST-808', 'ST-809', 'ST-926', 'ST-957', 'ST-794', 'ST-795', 'ST-954', 'ST-955', 'ST-956') THEN 'Excluded (TBS)' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-953') THEN 'NBC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-629', 'ST-756', 'ST-858', 'ST-859', 'ST-860', 'ST-861') THEN 'SR-E' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-630', 'ST-749', 'ST-750', 'ST-862', 'ST-863', 'ST-864', 'ST-865') THEN 'SR-W' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-631', 'ST-758', 'ST-866', 'ST-867', 'ST-868', 'ST-869') THEN 'SR-C' else 'Uncategorized Sub-Types**' End When 'FR' then Case WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-939', 'ST-940', 'ST-941', 'ST-942') THEN 'FS' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-614', 'ST-913', 'ST-814', 'ST-815', 'ST-816', 'ST-817') THEN 'AV' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-931', 'ST-932') THEN 'CAP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-620', 'ST-719', 'ST-720', 'ST-721', 'ST-722', 'ST-723', 'ST-724', 'ST-726', 'ST-727', 'ST-770', 'ST-771', 'ST-905', 'ST-906', 'ST-725', 'ST-772', 'ST-773', 'ST-774', 'ST-874', 'ST-875', 'ST-876', 'ST-877') THEN 'CS' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-925') THEN 'CSN' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-927', 'ST-936', 'ST-928') THEN 'CX' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-623', 'ST-728', 'ST-729', 'ST-730', 'ST-732', 'ST-735', 'ST-736', 'ST-776', 'ST-850', 'ST-851', 'ST-852', 'ST-853', 'ST-888', 'ST-889', 'ST-890', 'ST-891', 'ST-892', 'ST-731', 'ST-733', 'ST-734') THEN 'EB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-622', 'ST-664', 'ST-665', 'ST-666', 'ST-667', 'ST-670', 'ST-672', 'ST-673', 'ST-674', 'ST-911', 'ST-668', 'ST-669', 'ST-671', 'ST-675', 'ST-700', 'ST-830', 'ST-831', 'ST-832', 'ST-833') THEN 'EC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-737', 'ST-738', 'ST-739', 'ST-740', 'ST-742', 'ST-743', 'ST-775', 'ST-907', 'ST-628', 'ST-741', 'ST-854', 'ST-855', 'ST-856', 'ST-857') THEN 'EL' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-613', 'ST-914', 'ST-915', 'ST-810', 'ST-811', 'ST-812', 'ST-813') THEN 'FI' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-924') THEN 'HOC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-933') THEN 'LP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-934', 'ST-962') THEN 'NAR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-929') THEN 'NFB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-879', 'ST-880', 'ST-881', 'ST-882', 'ST-883', 'ST-884', 'ST-885', 'ST-886', 'ST-893', 'ST-894', 'ST-895', 'ST-896', 'ST-897', 'ST-898', 'ST-899', 'ST-878','ST-962') THEN 'NR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-923') THEN 'NRC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-701') THEN 'Trop-payés *' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-616', 'ST-704', 'ST-705', 'ST-706', 'ST-707', 'ST-708', 'ST-709', 'ST-710', 'ST-711', 'ST-712', 'ST-713', 'ST-714', 'ST-715', 'ST-717', 'ST-718', 'ST-782', 'ST-903', 'ST-904', 'ST-716', 'ST-846', 'ST-847', 'ST-848', 'ST-849') THEN 'PA' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-781') THEN 'PH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-627') THEN 'PR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-922') THEN 'RCMP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-654', 'ST-655', 'ST-658', 'ST-660', 'ST-661', 'ST-662', 'ST-918', 'ST-919', 'ST-617', 'ST-653', 'ST-656', 'ST-657', 'ST-659', 'ST-663', 'ST-834', 'ST-835', 'ST-836', 'ST-837') THEN 'RE' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-618', 'ST-642', 'ST-643', 'ST-644', 'ST-647', 'ST-649', 'ST-650', 'ST-651', 'ST-917', 'ST-645', 'ST-646', 'ST-648', 'ST-652', 'ST-822', 'ST-823', 'ST-824', 'ST-825') THEN 'RO' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-760', 'ST-761', 'ST-762', 'ST-763', 'ST-764', 'ST-767', 'ST-768', 'ST-769', 'ST-778', 'ST-779', 'ST-783', 'ST-784', 'ST-785', 'ST-786', 'ST-787', 'ST-788', 'ST-789', 'ST-920', 'ST-921', 'ST-930', 'ST-619', 'ST-765', 'ST-766', 'ST-870', 'ST-871', 'ST-872', 'ST-873') THEN 'SH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-615', 'ST-625', 'ST-912', 'ST-818', 'ST-819', 'ST-820', 'ST-821') THEN 'SP' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-744', 'ST-745', 'ST-746', 'ST-747', 'ST-748', 'ST-751', 'ST-752', 'ST-753', 'ST-754', 'ST-755', 'ST-757', 'ST-759', 'ST-780', 'ST-908', 'ST-909', 'ST-910') THEN 'SR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-935') THEN 'SSH' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-621', 'ST-688', 'ST-689', 'ST-690', 'ST-691', 'ST-694', 'ST-696', 'ST-697', 'ST-698', 'ST-703', 'ST-900', 'ST-901', 'ST-902', 'ST-692', 'ST-693', 'ST-695', 'ST-699', 'ST-842', 'ST-843', 'ST-844', 'ST-845') THEN 'SV' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-624', 'ST-676', 'ST-677', 'ST-678', 'ST-679', 'ST-682', 'ST-684', 'ST-685', 'ST-686', 'ST-702', 'ST-838', 'ST-839', 'ST-840', 'ST-841', 'ST-680', 'ST-681', 'ST-683', 'ST-687') THEN 'TC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-626', 'ST-632', 'ST-633', 'ST-634', 'ST-635', 'ST-636', 'ST-638', 'ST-639', 'ST-640', 'ST-916', 'ST-637', 'ST-641', 'ST-826', 'ST-827', 'ST-828', 'ST-829') THEN 'TR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-944', 'ST-945', 'ST-946') THEN 'SO' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-952') THEN 'FB' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-790', 'ST-791', 'ST-792', 'ST-793', 'ST-796', 'ST-797', 'ST-798', 'ST-799', 'ST-800', 'ST-801', 'ST-802', 'ST-803', 'ST-804', 'ST-805', 'ST-806', 'ST-807', 'ST-808', 'ST-809', 'ST-926', 'ST-957', 'ST-794', 'ST-795', 'ST-954', 'ST-955', 'ST-956') THEN 'Excluses (SCT)' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-953') THEN 'NBC' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-629', 'ST-756', 'ST-858', 'ST-859', 'ST-860', 'ST-861') THEN 'SR-E' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-630', 'ST-749', 'ST-750', 'ST-862', 'ST-863', 'ST-864', 'ST-865') THEN 'SR-W' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-631', 'ST-758', 'ST-866', 'ST-867', 'ST-868', 'ST-869') THEN 'SR-C' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-962') THEN 'NAR' WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-963') THEN 'NR' else 'Sous-types non catégorisés**' End END
  Transaction Type	[Presentation].[Sub Type].[TRANSACTION_TYPE]
  Active Case Count	[Presentation].[Case].[Case Count]	Aggregate count Rollup summarize label Count of Active Case Number
  Active-PRI	[Presentation].[Case].[PRI]	label Distinct Count of Active PRI

  • Detail Filters
    2
  • 

    [Presentation].[Sub Type].[TRANSACTION_TYPE] in ( '12: CAI Overpayments / ICC Trop-payés' , '13: CAI Revisions / ICC Révisions', '14: CAI Post Validation / ICC Post Vérification' )
    
  • 

    ([Presentation].[State].[NAME] in ('Assigned', 'Bring_Forward', 'Open', 'Pending', 'Reassigned')) and
    ([Presentation].[Case].[TIME_IN_ASSIGNED] is null and [Presentation].[Case].[TIME_OUT_ASSIGNED] is null
    and [Presentation].[Case].[TIME_IN_REASSIGNED] is null and [Presentation].[Case].[TIME_OUT_REASSIGNED] is null)
    
• 

  

  Summary Active Cases

  Data Items

  5

  Collective Agreement Group	[Query-Active].[Collective Agreement Group]
  Transaction Type	[Query-Active].[Transaction Type]
  Active Case Count	[Query-Active].[Active Case Count]	Aggregate total
  Active PRI Count	[Query-Active].[Active-PRI]	Aggregate countDistinct Rollup calculated
  fake-join	1

• 

  

  All and Active

  

  [Query-All Cases].[Collective Agreement Group] = [Query-Active].[Collective Agreement Group] and
  [Query-All Cases].[Transaction Type] = [Query-Active].[Transaction Type] and
  [Query-All Cases].[ALL-PRI] = [Query-Active].[Active-PRI]

  

  Query-All Cases

  1:1

  

  Query-Active

  0:1

  Data Items

  6

  Collective Agreement Group	[Query-All Cases].[Collective Agreement Group]
  Transaction Type	[Query-All Cases].[Transaction Type]
  Case Count	[Query-All Cases].[Case Count]	Aggregate total Rollup total
  PRI Count	[Query-All Cases].[ALL-PRI]
  Active Case Count	[Query-Active].[Active Case Count]	Aggregate total
  Active PRI Count	[Query-Active].[Active-PRI]

• 

  

  Final

  Data Items

  11

  Collective Agreement Group	[All and Active].[Collective Agreement Group]
  Transaction Type	[All and Active].[Transaction Type]
  Case Count	[All and Active].[Case Count]	Aggregate total
  PRI Count	[All and Active].[PRI Count]	Aggregate countDistinct Rollup summarize
  Active Case Count	coalesce( [All and Active].[Active Case Count], 0 )	Aggregate summarize
  Active PRI Count	[All and Active].[Active PRI Count]	Aggregate countDistinct Rollup summarize
  Case Progress	[Case Count] - [Active Case Count]	Aggregate calculated Rollup calculated
  PRI Progress	coalesce([PRI Count], 0) - coalesce( [Active PRI Count], 0)	Aggregate calculated Rollup calculated
  Case %	[Case Progress] / [Case Count]	Aggregate calculated Rollup calculated
  PRI %	coalesce([PRI Progress], 0) / coalesce([PRI Count], 0)	Aggregate calculated Rollup calculated
  fake-join	1

• 

  

  Query-Fake-join

  Data Items

  1

  Fake-join	1

• 

  

  Load Last Date Query

  Data Items

  1

  Latest Valid Data Load Date	[Presentation].[Data Load Archive].[Latest Valid Data Load Date]

Layouts