I have a report that has it's grouping linked to a dynamic grouping filter which I want to do a custom sort on. Basically, the table and field are a Shift Type Name that sorts in alphabetical order. What sort formula should I use? OR_LOC_SHIFT_TIMES.SHIFT_NAME that is linked to another table ZC_SHIFT_TYPE.TITLE (HCGH MAIN, HCGH TCAS, SH MAIN, SMH MAIN, SMH MOPS, SMH SASC). When you select a value from ZC_SHIFT_TYPE.TITLE (i.e. ?Shift Type) in parameters then the OR_LOC_SHIFT_TIMES.SHIFT_NAME will display as a dynamic group (i.e. ?Group 3 = Shift).
//Depending on the specified third item to group by, populated with
//data from any of a number of items. If sorting by date, populates
//in difficult to read but easily-sortable format
if {?Group 3}="Day of Week" then {@Day # of Week} else
If {?Group 3}="Month" then {@Snapshot Month} else
if {?Group 3}="None" then "None" else
If {?Group 3}="Location" then {@Location Name} else
If {?Group 3}="Date" then {@DateSort} else
If {?Group 3}="Room" then {@Room Name} else
If {?Group 3}="Shift" then {@Shift Name}OR_LOC_SHIFT_TIMES.SHIFT_NAME
OR_LOC_SHIFT_TIMES.SHIFT_NAME
1st PM
2nd PM
3rd PM
4th PM
Afternoon
AM
Day/Prime
Early AM
First Eve
Fourth Eve
Morning
Night
OC Night
OC PM
Second Eve
Third Eve
Finally, I created a running total for ?group 3 on Room_ID to find out how many rooms the report is looking at for that shift but now I want to sum those totals in the other two groups (group 1 and group 2) for a grand total (group 1) and a location total (group 2). How would I go about that?
Thank you,
Gary
P.S.
This is the SQL for the report:
SELECT "CLARITY_LOC"."LOC_NAME", "CLARITY_SER_room"."PROV_NAME", "OR_UTIL_ROOM"."LOG_ID", "OR_UTIL_ROOM"."CASE_ID", "OR_UTIL_ROOM"."SNAPSHOT_DATE", "OR_UTIL_ROOM"."SLOT_TYPE", "OR_UTIL_ROOM"."ROOM_ID", "OR_UTIL_ROOM"."PROC_TIME_YN", "OR_UTIL_ROOM"."SLOT_LENGTH", "OR_UTIL_ROOM"."START_TIME", "OR_UTIL_ROOM"."END_TIME", "OR_UTIL_ROOM"."SNAPSHOT_NUMBER", "OR_SER_SURG_SRVC"."LOC_ID", "OR_LOG_ALL_SURG"."LINE", "OR_CASE_ALL_SURG"."LINE", "OR_CASE"."ADD_ON_CASE_YN", "OR_LOC_SHIFT_TIMES"."SHIFT_NAME", "OR_LOC_SHIFT_TIMES"."SHIFT_START_TIME", "OR_LOC_SHIFT_TIMES"."SHIFT_END_TIME", "OR_LOC_SHIFT_TIMES"."FACILITY_ID", "ZC_SHIFT_TYPE"."TITLE", "DATE_DIMENSION"."HOLIDAY_YN", "DATE_DIMENSION"."WEEKEND_YN", "OR_LOG_ALL_SURG"."SURG_ID", "CLARITY_SER_log_phys"."PROV_NAME", "OR_CASE_ALL_SURG"."SURG_ID", "CLARITY_SER_case_phys"."PROV_NAME", "CLARITY_SER_log_phys"."CLINICIAN_TITLE", "CLARITY_SER_case_phys"."CLINICIAN_TITLE", /********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/
case when "OR_LOG_ALL_SURG"."LOG_ID" is null then null
else
(select min(allsurg.LINE)
from OR_LOG_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.LOG_ID = "OR_LOG_ALL_SURG"."LOG_ID"
)
end, /********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/
case when "OR_CASE_ALL_SURG"."OR_CASE_ID" is null then null
else
(select min(allsurg.LINE)
from OR_CASE_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.OR_CASE_ID = "OR_CASE_ALL_SURG"."OR_CASE_ID"
)
end
FROM {oj (((((((((("CLARITY"."dbo"."OR_UTIL_ROOM" "OR_UTIL_ROOM" LEFT OUTER JOIN "CLARITY"."dbo"."OR_SER_SURG_SRVC" "OR_SER_SURG_SRVC" ON "OR_UTIL_ROOM"."ROOM_ID"="OR_SER_SURG_SRVC"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_room" ON "OR_UTIL_ROOM"."ROOM_ID"="CLARITY_SER_room"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_CASE_ALL_SURG" "OR_CASE_ALL_SURG" ON "OR_UTIL_ROOM"."CASE_ID"="OR_CASE_ALL_SURG"."OR_CASE_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_LOG_ALL_SURG" "OR_LOG_ALL_SURG" ON "OR_UTIL_ROOM"."LOG_ID"="OR_LOG_ALL_SURG"."LOG_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_CASE" "OR_CASE" ON "OR_UTIL_ROOM"."CASE_ID"="OR_CASE"."OR_CASE_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_LOC_SHIFT_TIMES" "OR_LOC_SHIFT_TIMES" ON ("OR_UTIL_ROOM"."START_TIME"<"OR_LOC_SHIFT_TIMES"."SHIFT_END_TIME") AND ("OR_UTIL_ROOM"."END_TIME">"OR_LOC_SHIFT_TIMES"."SHIFT_START_TIME")) LEFT OUTER JOIN "CLARITY"."dbo"."DATE_DIMENSION" "DATE_DIMENSION" ON "OR_UTIL_ROOM"."SNAPSHOT_DATE"="DATE_DIMENSION"."CALENDAR_DT") LEFT OUTER JOIN "CLARITY"."dbo"."ZC_SHIFT_TYPE" "ZC_SHIFT_TYPE" ON "OR_LOC_SHIFT_TIMES"."SHIFT_TYPE_C"="ZC_SHIFT_TYPE"."SHIFT_TYPE_C") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_case_phys" ON "OR_CASE_ALL_SURG"."SURG_ID"="CLARITY_SER_case_phys"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_log_phys" ON "OR_LOG_ALL_SURG"."SURG_ID"="CLARITY_SER_log_phys"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_LOC" "CLARITY_LOC" ON "OR_SER_SURG_SRVC"."LOC_ID"="CLARITY_LOC"."LOC_ID"}
WHERE ("OR_UTIL_ROOM"."SNAPSHOT_DATE">={ts '2014-10-01 00:00:00'} AND "OR_UTIL_ROOM"."SNAPSHOT_DATE"<{ts '2014-11-01 00:00:00'}) AND "OR_UTIL_ROOM"."SNAPSHOT_NUMBER"=4 AND ("OR_UTIL_ROOM"."LOG_ID" IS NULL OR "OR_LOG_ALL_SURG"."LINE"=/********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/
case when "OR_LOG_ALL_SURG"."LOG_ID" is null then null
else
(select min(allsurg.LINE)
from OR_LOG_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.LOG_ID = "OR_LOG_ALL_SURG"."LOG_ID"
)
end) AND ("OR_UTIL_ROOM"."CASE_ID" IS NULL OR "OR_CASE_ALL_SURG"."LINE"=/********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/
case when "OR_CASE_ALL_SURG"."OR_CASE_ID" is null then null
else
(select min(allsurg.LINE)
from OR_CASE_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.OR_CASE_ID = "OR_CASE_ALL_SURG"."OR_CASE_ID"
)
end) AND "OR_SER_SURG_SRVC"."LOC_ID"=110303 AND "OR_UTIL_ROOM"."ROOM_ID"<>'107420' AND "CLARITY_SER_room"."PROV_NAME"<>'107420' AND "ZC_SHIFT_TYPE"."TITLE"='HCGH Main' AND "OR_LOC_SHIFT_TIMES"."FACILITY_ID"=1
//Depending on the specified third item to group by, populated with
//data from any of a number of items. If sorting by date, populates
//in difficult to read but easily-sortable format
if {?Group 3}="Day of Week" then {@Day # of Week} else
If {?Group 3}="Month" then {@Snapshot Month} else
if {?Group 3}="None" then "None" else
If {?Group 3}="Location" then {@Location Name} else
If {?Group 3}="Date" then {@DateSort} else
If {?Group 3}="Room" then {@Room Name} else
If {?Group 3}="Shift" then {@Shift Name}OR_LOC_SHIFT_TIMES.SHIFT_NAME
OR_LOC_SHIFT_TIMES.SHIFT_NAME
1st PM
2nd PM
3rd PM
4th PM
Afternoon
AM
Day/Prime
Early AM
First Eve
Fourth Eve
Morning
Night
OC Night
OC PM
Second Eve
Third Eve
Finally, I created a running total for ?group 3 on Room_ID to find out how many rooms the report is looking at for that shift but now I want to sum those totals in the other two groups (group 1 and group 2) for a grand total (group 1) and a location total (group 2). How would I go about that?
Thank you,
Gary
P.S.
This is the SQL for the report:
SELECT "CLARITY_LOC"."LOC_NAME", "CLARITY_SER_room"."PROV_NAME", "OR_UTIL_ROOM"."LOG_ID", "OR_UTIL_ROOM"."CASE_ID", "OR_UTIL_ROOM"."SNAPSHOT_DATE", "OR_UTIL_ROOM"."SLOT_TYPE", "OR_UTIL_ROOM"."ROOM_ID", "OR_UTIL_ROOM"."PROC_TIME_YN", "OR_UTIL_ROOM"."SLOT_LENGTH", "OR_UTIL_ROOM"."START_TIME", "OR_UTIL_ROOM"."END_TIME", "OR_UTIL_ROOM"."SNAPSHOT_NUMBER", "OR_SER_SURG_SRVC"."LOC_ID", "OR_LOG_ALL_SURG"."LINE", "OR_CASE_ALL_SURG"."LINE", "OR_CASE"."ADD_ON_CASE_YN", "OR_LOC_SHIFT_TIMES"."SHIFT_NAME", "OR_LOC_SHIFT_TIMES"."SHIFT_START_TIME", "OR_LOC_SHIFT_TIMES"."SHIFT_END_TIME", "OR_LOC_SHIFT_TIMES"."FACILITY_ID", "ZC_SHIFT_TYPE"."TITLE", "DATE_DIMENSION"."HOLIDAY_YN", "DATE_DIMENSION"."WEEKEND_YN", "OR_LOG_ALL_SURG"."SURG_ID", "CLARITY_SER_log_phys"."PROV_NAME", "OR_CASE_ALL_SURG"."SURG_ID", "CLARITY_SER_case_phys"."PROV_NAME", "CLARITY_SER_log_phys"."CLINICIAN_TITLE", "CLARITY_SER_case_phys"."CLINICIAN_TITLE", /********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/
case when "OR_LOG_ALL_SURG"."LOG_ID" is null then null
else
(select min(allsurg.LINE)
from OR_LOG_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.LOG_ID = "OR_LOG_ALL_SURG"."LOG_ID"
)
end, /********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/
case when "OR_CASE_ALL_SURG"."OR_CASE_ID" is null then null
else
(select min(allsurg.LINE)
from OR_CASE_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.OR_CASE_ID = "OR_CASE_ALL_SURG"."OR_CASE_ID"
)
end
FROM {oj (((((((((("CLARITY"."dbo"."OR_UTIL_ROOM" "OR_UTIL_ROOM" LEFT OUTER JOIN "CLARITY"."dbo"."OR_SER_SURG_SRVC" "OR_SER_SURG_SRVC" ON "OR_UTIL_ROOM"."ROOM_ID"="OR_SER_SURG_SRVC"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_room" ON "OR_UTIL_ROOM"."ROOM_ID"="CLARITY_SER_room"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_CASE_ALL_SURG" "OR_CASE_ALL_SURG" ON "OR_UTIL_ROOM"."CASE_ID"="OR_CASE_ALL_SURG"."OR_CASE_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_LOG_ALL_SURG" "OR_LOG_ALL_SURG" ON "OR_UTIL_ROOM"."LOG_ID"="OR_LOG_ALL_SURG"."LOG_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_CASE" "OR_CASE" ON "OR_UTIL_ROOM"."CASE_ID"="OR_CASE"."OR_CASE_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_LOC_SHIFT_TIMES" "OR_LOC_SHIFT_TIMES" ON ("OR_UTIL_ROOM"."START_TIME"<"OR_LOC_SHIFT_TIMES"."SHIFT_END_TIME") AND ("OR_UTIL_ROOM"."END_TIME">"OR_LOC_SHIFT_TIMES"."SHIFT_START_TIME")) LEFT OUTER JOIN "CLARITY"."dbo"."DATE_DIMENSION" "DATE_DIMENSION" ON "OR_UTIL_ROOM"."SNAPSHOT_DATE"="DATE_DIMENSION"."CALENDAR_DT") LEFT OUTER JOIN "CLARITY"."dbo"."ZC_SHIFT_TYPE" "ZC_SHIFT_TYPE" ON "OR_LOC_SHIFT_TIMES"."SHIFT_TYPE_C"="ZC_SHIFT_TYPE"."SHIFT_TYPE_C") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_case_phys" ON "OR_CASE_ALL_SURG"."SURG_ID"="CLARITY_SER_case_phys"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_log_phys" ON "OR_LOG_ALL_SURG"."SURG_ID"="CLARITY_SER_log_phys"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_LOC" "CLARITY_LOC" ON "OR_SER_SURG_SRVC"."LOC_ID"="CLARITY_LOC"."LOC_ID"}
WHERE ("OR_UTIL_ROOM"."SNAPSHOT_DATE">={ts '2014-10-01 00:00:00'} AND "OR_UTIL_ROOM"."SNAPSHOT_DATE"<{ts '2014-11-01 00:00:00'}) AND "OR_UTIL_ROOM"."SNAPSHOT_NUMBER"=4 AND ("OR_UTIL_ROOM"."LOG_ID" IS NULL OR "OR_LOG_ALL_SURG"."LINE"=/********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/
case when "OR_LOG_ALL_SURG"."LOG_ID" is null then null
else
(select min(allsurg.LINE)
from OR_LOG_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.LOG_ID = "OR_LOG_ALL_SURG"."LOG_ID"
)
end) AND ("OR_UTIL_ROOM"."CASE_ID" IS NULL OR "OR_CASE_ALL_SURG"."LINE"=/********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/
case when "OR_CASE_ALL_SURG"."OR_CASE_ID" is null then null
else
(select min(allsurg.LINE)
from OR_CASE_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.OR_CASE_ID = "OR_CASE_ALL_SURG"."OR_CASE_ID"
)
end) AND "OR_SER_SURG_SRVC"."LOC_ID"=110303 AND "OR_UTIL_ROOM"."ROOM_ID"<>'107420' AND "CLARITY_SER_room"."PROV_NAME"<>'107420' AND "ZC_SHIFT_TYPE"."TITLE"='HCGH Main' AND "OR_LOC_SHIFT_TIMES"."FACILITY_ID"=1
