Here is the SQL used in one of the drill-through reports referenced by footyref above. The report is sorted in Cognos by Cnty Cd, Caseload Nbr, and Case Nbr and grouped similarly. There are also total case counts calculated by report, county, and caseload.
The primary fact table is approximately 5 million rows and is indexed on some but not all of the columns used in the detail filter.
select T1."CNTY_CD", T1."CASELOAD_NBR", T1."CASE_NBR", SUBSTR(T2."CNTY_CD", 1 , 1), SUBSTR(T2."CNTY_CD", 2 , 1) || ' - ' || T2."CNTY_DESC", T2."CNTY_CD" || ' - ' || T2."CNTY_DESC", T3."PARTICPNT_NBR", T3."PARTICIPANT_TYP_CD", T3."ACTIVE_IND", T1."INTAKE_IND", T1."LOCATE_IND", T1."PATRNTY_IND", T1."SUPPORT_IND", T1."ENFORCE_IND", T1."CASE_TYP", T1."CASE_STATUS", T4."BOW_IND", T4."ACCESS_MED_INS_IND", T4."SSA_DISABLED_IND", SUBSTR(T1."CASELOAD_NBR", 3 , 4), T4."BNKRTCY_FILED_DTE", T5."CURR_MTH_IND", T5."LAST_DY_MTH", T4."DISCHRG_BNKRCY_DTE", T4."FIRST_NAME", T4."LAST_NAME", T4."SSN_NBR", T4."GNDR_TYP_CD", T4."BIRTH_DTE", 180000 - (1 - CAST(18 AS FLOAT) / ABS(18)) / 2, T5."CHLD_SPT_PART_NBR"
from "WFDW01DP"."FACT_CHLD_SPT_CASE" T1, "WFDW01DP"."DM_COUNTY" T2, "WFDW01DP"."FACT_CS_PARTICPNT" T3, "WFDW01DP"."DM_CHLD_SPT_PERSON" T4, "WFDW01DP"."DM_MONTH" T5
where T1."CNTY_CD" = T2."CNTY_CD" and T1."CASE_NBR" = T3."CASE_NBR" and T1."PART_NBR" = T3."PART_NBR" and T3."PARTICPNT_NBR" = T4."PARTICPNT_NBR" and T1."PART_NBR" = T5."CHLD_SPT_PART_NBR" and T5."CURR_MTH_IND" = 'Y' FOR FETCH ONLY