Count Distinct Summaries

[mrcool]

Hi All,

Is there anyway to force cognos to write "partition by" for subtotals for the columns with aggregate type count distinct?
Cognos is writing separate query for every subtotal in the report.
This is how it is generating the query:

SELECT   "T2"."C0" (NAMED "Yearkey" ) , "T2"."C2" (NAMED "Year0" ) ,
      "T2"."C1" (NAMED "Monthkey" ) , "T2"."C3" (NAMED "Month0" ) ,
      "T1"."C2" (NAMED "Billing_Account" )
, "T0"."C1" (NAMED "Billing_Account1" )
FROM   (
SELECT   "Approved_Calendar"."Cstm_Year_Id" (NAMED "C0" ) , COUNT ( DISTINCT "Compensation_Payable_Fact"."BAccnt_Num" ) (NAMED "C1" )
FROM   "EDWIT1E_VW40_DLR_COMP"."Cstm_Clndr_Dt" "Approved_Calendar" ,
      "EDWIT1E_VW40_DLR_COMP"."COMP_PAYABLE_FACT" "Compensation_Payable_Fact"
WHERE   "Approved_Calendar"."Clndr_Dt" = "Compensation_Payable_Fact"."Comp_Payable_Apprd_Dt"
   AND   "Approved_Calendar"."Cstm_Clndr_Tp_Id" = "Compensation_Payable_Fact"."Cstm_Week_Tp_Id"
   AND   "Approved_Calendar"."Cstm_Year_Id" = 2010
GROUP   BY "Approved_Calendar"."Cstm_Year_Id" ) "T0" ,

(
SELECT   "Approved_Calendar"."Cstm_Year_Id" (NAMED "C0" ) , "Approved_Calendar"."Cstm_Month_Id" (NAMED "C1" ) ,
      COUNT ( DISTINCT "Compensation_Payable_Fact"."BAccnt_Num" ) (NAMED "C2" )
FROM   "EDWIT1E_VW40_DLR_COMP"."Cstm_Clndr_Dt" "Approved_Calendar" ,
      "EDWIT1E_VW40_DLR_COMP"."COMP_PAYABLE_FACT" "Compensation_Payable_Fact"
WHERE   "Approved_Calendar"."Clndr_Dt" = "Compensation_Payable_Fact"."Comp_Payable_Apprd_Dt"
   AND   "Approved_Calendar"."Cstm_Clndr_Tp_Id" = "Compensation_Payable_Fact"."Cstm_Week_Tp_Id"
   AND   "Approved_Calendar"."Cstm_Year_Id" = 2010
GROUP   BY "Approved_Calendar"."Cstm_Year_Id" , "Approved_Calendar"."Cstm_Month_Id" ) "T1" ,
   
      (
SELECT   "Approved_Calendar"."Cstm_Year_Id" (NAMED "C0" ) , "Approved_Calendar"."Cstm_Month_Id" (NAMED "C1" ) ,
      "Approved_Calendar"."Cstm_Year_Name" (NAMED "C2" ) , "Approved_Calendar"."Cstm_Month_Name" (NAMED "C3" )
FROM   "EDWIT1E_VW40_DLR_COMP"."Cstm_Clndr_Dt" "Approved_Calendar" ,
      "EDWIT1E_VW40_DLR_COMP"."COMP_PAYABLE_FACT" "Compensation_Payable_Fact"
WHERE   "Approved_Calendar"."Clndr_Dt" = "Compensation_Payable_Fact"."Comp_Payable_Apprd_Dt"
   AND   "Approved_Calendar"."Cstm_Clndr_Tp_Id" = "Compensation_Payable_Fact"."Cstm_Week_Tp_Id"
   AND   "Approved_Calendar"."Cstm_Year_Id" = 2010
GROUP   BY "Approved_Calendar"."Cstm_Year_Id" , "Approved_Calendar"."Cstm_Month_Id" ,
      "Approved_Calendar"."Cstm_Year_Name" , "Approved_Calendar"."Cstm_Month_Name" ) "T2"
WHERE   ( "T2"."C0" = "T0"."C0"
   OR   "T2"."C0" IS NULL
   AND   "T0"."C0" IS NULL )
   AND   ( "T2"."C0" = "T1"."C0"
   OR   "T2"."C0" IS NULL
   AND   "T1"."C0" IS NULL )
   AND   ( "T2"."C1" = "T1"."C1"
   OR   "T2"."C1" IS NULL
   AND   "T1"."C1" IS NULL )

Thanks,
mc

[BigChris]

I'm not quite sure what you're asking (forgive my ignorance). Are you trying to make Cognos alter the SQL that you've posted, or do you want to change the text in the total row in your report?

I'm assuming (hoping) it's the latter, in which case you can do that quite simply by unlocking the report and changing the text.

[mrcool]

Hi Chris,

If you observe the above query, cognos is generating separate query for each level and joining at the end for sub totals in the report.
My fact table is huge and it is being few times for sub totals,because of this it is impacting my report performance.
If cognos can generate partition performance would be much better.

[usjse]

Has anyone found a solution to this problem?  I've been trying to figure out why when I summarize the column for distinct counts my SQL increases 4 to 5 times.   This causes the report to run for over an hour.  I've tried using the summary tool for distinct counts at the top of report studio menu and I've tried choosing distinct count in my aggregate in the properties for the data item value.  I've even tried to create a calculated agrregate function and it still generates an SQL that runs over an hour.  Any help on this will be greatly appreciated.  I am writing the report in report studio with 7 columns and 1 query.  Thanks.  jse :(