Performance of a Report

[sflindsey]

I had a complaint that a report runs tremendously long.  In reviewing the report the result set of the query comes back with six rows.

I have the following query generated when I "Show Generated SQL/MDX":
select case
       when "T10"."PLAN_CDE" = ' '   then 'ANC'
       when "T10"."PLAN_CDE" = 'PZU' then 'Continuity'
       when "T10"."PLAN_CDE" = 'PZC' then 'Continuity'
       when "T10"."PLAN_CDE" = 'PBZ' then 'Continuity'
       when "T10"."PLAN_CDE" = 'TSA' then 'Continuity'
       when "T10"."PLAN_CDE" = 'MAT' then 'Continuity'
       when "T10"."PLAN_CDE" = 'WWU' then 'Continuity'
       when "T10"."PLAN_CDE" = 'HPP' then 'Continuity'
       when "T10"."PLAN_CDE" = 'ANC' then 'Ancillary'
       else 'Other'
       end  AS "C0"
      ,case
       when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
        and "T10"."DOC_TYP_CDE" = 'I'
        and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'D'
        and "T10"."MAX_BILL_EFFRT_NBR" > '5'
       then 'T'
       when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
        and "T10"."DOC_TYP_CDE" = 'I'
        and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'G'
        and "T10"."MAX_BILL_EFFRT_NBR" > '8'
       then 'T'
       when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
        and "T10"."DOC_TYP_CDE" = 'I'
        and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'N'
        and "T10"."MAX_BILL_EFFRT_NBR" > '5'
       then 'T'
       when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
        and "T10"."DOC_TYP_CDE" = 'I'
        and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'P'
        and "T10"."MAX_BILL_EFFRT_NBR" > '8'
       then 'T'
       when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
        and "T10"."DOC_TYP_CDE" = 'I'
        and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'X'
        and "T10"."MAX_BILL_EFFRT_NBR" > '8'
       then 'T'
       when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
        and "T10"."DOC_TYP_CDE" = 'I'
        and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'A'
        and "T10"."MAX_BILL_EFFRT_NBR" > '5'
       then 'T'
       else "T10"."DOC_TYP_CDE"
       end  AS "C1"
      ,CAST( sum("T10"."BILL_DOC_BILL_AMT") AS DOUBLE PRECISION ) / nullif(sum("T10"."Bill_Document_Count"), 0) AS "C2"
      ,sum("T10"."Bill_Document_Count") AS "C3"
      ,sum("T10"."BILL_DOC_BILL_AMT") AS "C4"
      ,sum("T10"."BILL_DOC_PD_AMT") AS "C5"
      ,CAST( sum("T10"."BILL_DOC_PD_AMT") AS DOUBLE PRECISION ) / nullif(sum("T10"."BILL_DOC_BILL_AMT"), 0) AS "C6"
      ,sum("T10"."Bill_Document_Cost_Amount") AS "C7"
      ,sum("T10"."BILL_DOC_PD_AMT") - sum("T10"."Bill_Document_Cost_Amount") AS "C8"
from (
      select "T3376_CONTINUITY_BILL_DOCUMENT"."ACCT_NBR" AS "ACCT_NBR"
            ,"T3376_CONTINUITY_BILL_DOCUMENT"."BILL_DOC_NBR" AS "BILL_DOC_NBR"
            ,min("T3376_CONTINUITY_BILL_DOCUMENT"."BILL_DT") AS "BILL_DT"
            ,min("T3376_CONTINUITY_BILL_DOCUMENT"."MAX_BILL_BILL_GROUP_CDE") AS "MAX_BILL_BILL_GROUP_CDE"
            ,min("T3376_CONTINUITY_BILL_DOCUMENT"."DOC_TYP_CDE") AS "DOC_TYP_CDE"
            ,min("T3376_CONTINUITY_BILL_DOCUMENT"."MAX_BILL_EFFRT_NBR") AS "MAX_BILL_EFFRT_NBR"
            ,min("T3376_CONTINUITY_BILL_DOCUMENT"."PLAN_CDE") AS "PLAN_CDE"
            ,min("T3376_CONTINUITY_BILL_DOCUMENT"."BILL_DOC_BILL_AMT") AS "BILL_DOC_BILL_AMT"
            ,min("T3376_CONTINUITY_BILL_DOCUMENT"."BILL_DOC_PD_AMT") AS "BILL_DOC_PD_AMT"
            ,min(1) AS "Bill_Document_Count"
            ,min(case 
                 when "T3376_CONTINUITY_BILL_DOCUMENT"."BILL_DT" between
                      CAST( datepart(mm, "T3386_BILL_DOC_TYP_COST8"."BILL_COST_EFF_FROM_DT") AS VARCHAR( 2 ) )+'/'+CAST( datepart(dd, "T3386_BILL_DOC_TYP_COST8"."BILL_COST_EFF_FROM_DT") AS VARCHAR( 2 ) )+'/'+CAST( datepart(yy, "T3386_BILL_DOC_TYP_COST8"."BILL_COST_EFF_FROM_DT") AS VARCHAR( 10 ) )
                  and CAST( datepart(mm, "T3386_BILL_DOC_TYP_COST8"."BILL_COST_EFF_TO_DT") AS VARCHAR( 2 ) )+'/'+CAST( datepart(dd, "T3386_BILL_DOC_TYP_COST8"."BILL_COST_EFF_TO_DT") AS VARCHAR( 2 ) )+'/'+CAST( datepart(yy, "T3386_BILL_DOC_TYP_COST8"."BILL_COST_EFF_TO_DT") AS VARCHAR( 10 ) )
                 then "T3386_BILL_DOC_TYP_COST8"."BILL_DOC_TYP_COST_AMT"
                 else NULL
                 end ) AS "Bill_Document_Cost_Amount"
      from "DHMD03P_Marketing_Database_Report"."dbo"."T3376_CONTINUITY_BILL_DOCUMENT" "T3376_CONTINUITY_BILL_DOCUMENT"
          ,"DHMD03P_Marketing_Database_Report"."dbo"."T3387_MAJOR_SOURCE" "T3387_MAJOR_SOURCE"
          ,"DHMD03P_Marketing_Database_Report"."dbo"."T3389_PRODUCT_MEDIA_CC" "coguda20"
          ,"DHMD03P_Marketing_Database_Report"."dbo"."T3388_COST_CENTER" "coguda21"
          ,"DHMD03P_Marketing_Database_Report"."dbo"."T3388_COST_CENTER" "T3388_COST_CENTER"
          ,"DHMD03P_Marketing_Database_Report"."dbo"."T3386_BILL_DOC_TYP_COST" "T3386_BILL_DOC_TYP_COST8"
          ,"DHMD03P_Marketing_Database_Report"."dbo"."T3382_MEDIA" "T3382_MEDIA9"
      where "T3382_MEDIA9"."MEDIA_CDE" = "T3376_CONTINUITY_BILL_DOCUMENT"."MAX_BILL_MEDIA_CDE"
        and "T3387_MAJOR_SOURCE"."MAJOR_SOURCE_CDE" = "T3382_MEDIA9"."MAJOR_SOURCE_CDE"
        and "coguda20"."MEDIA_CDE" = "T3376_CONTINUITY_BILL_DOCUMENT"."MAX_BILL_MEDIA_CDE"
        and "coguda20"."PLAN_CDE" = "T3376_CONTINUITY_BILL_DOCUMENT"."PLAN_CDE"
        and "T3388_COST_CENTER"."COST_CENTER_NBR" = "coguda20"."COST_CENTER_NBR"
        and "T3386_BILL_DOC_TYP_COST8"."DOC_TYP_CDE" = "T3376_CONTINUITY_BILL_DOCUMENT"."DOC_TYP_CDE"
        and "T3386_BILL_DOC_TYP_COST8"."PLAN_CDE" = "T3376_CONTINUITY_BILL_DOCUMENT"."PLAN_CDE"
        and "coguda21"."COST_CENTER_NBR" = "coguda20"."COST_CENTER_NBR"
      group by "T3376_CONTINUITY_BILL_DOCUMENT"."ACCT_NBR"
              ,"T3376_CONTINUITY_BILL_DOCUMENT"."BILL_DOC_NBR"
     ) "T10"
    ,"DHMD03P_Marketing_Database_Report"."dbo"."T3381_BILL_GROUP" "T3381_BILL_GROUP11"
where "T10"."BILL_DT" between {ts '2011-09-01 00:00:00.000'} and {ts '2011-09-12 23:59:59.998'}
  and "T10"."MAX_BILL_BILL_GROUP_CDE" <> ' '
  and (case
       when ("T10"."PLAN_CDE" = ' ')   then 'ANC'
       when ("T10"."PLAN_CDE" = 'PZU') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'PZC') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'PBZ') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'TSA') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'MAT') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'WWU') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'HPP') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'ANC') then 'Ancillary'
       else 'Other'
       end  = 'ANC'
   and "T3381_BILL_GROUP11"."BILL_GROUP_DESC" in ('Ancillary', 'Donor Invoices', 'Undeliverable')
    or case
       when ("T10"."PLAN_CDE" = ' ')   then   'ANC'
       when ("T10"."PLAN_CDE" = 'PZU') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'PZC') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'PBZ') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'TSA') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'MAT') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'WWU') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'HPP') then 'Continuity'
       when ("T10"."PLAN_CDE" = 'ANC') then 'Ancillary'
       else 'Other'
       end  <> 'ANC')
   and "T3381_BILL_GROUP11"."BILL_GROUP_CDE" = "T10"."MAX_BILL_BILL_GROUP_CDE"
group by case
         when "T10"."PLAN_CDE" = ' '   then 'ANC'
         when "T10"."PLAN_CDE" = 'PZU' then 'Continuity'
         when "T10"."PLAN_CDE" = 'PZC' then 'Continuity'
         when "T10"."PLAN_CDE" = 'PBZ' then 'Continuity'
         when "T10"."PLAN_CDE" = 'TSA' then 'Continuity'
         when "T10"."PLAN_CDE" = 'MAT' then 'Continuity'
         when "T10"."PLAN_CDE" = 'WWU' then 'Continuity'
         when "T10"."PLAN_CDE" = 'HPP' then 'Continuity'
         when "T10"."PLAN_CDE" = 'ANC' then 'Ancillary'
         else 'Other'
         end
        ,case 
         when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
          and "T10"."DOC_TYP_CDE" = 'I'
          and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'D'
          and "T10"."MAX_BILL_EFFRT_NBR" > '5'
         then 'T'
         when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
          and "T10"."DOC_TYP_CDE" = 'I'
          and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'G'
          and "T10"."MAX_BILL_EFFRT_NBR" > '8'
         then 'T'
         when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
          and "T10"."DOC_TYP_CDE" = 'I'
          and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'N'
          and "T10"."MAX_BILL_EFFRT_NBR" > '5'
         then 'T'
         when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
          and "T10"."DOC_TYP_CDE" = 'I'
          and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'P'
          and "T10"."MAX_BILL_EFFRT_NBR" > '8'
         then 'T'
         when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
          and "T10"."DOC_TYP_CDE" = 'I'
          and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'X'
          and "T10"."MAX_BILL_EFFRT_NBR" > '8'
         then 'T'
         when "T10"."BILL_DT" between '11/16/10' and '12/31/11'
          and "T10"."DOC_TYP_CDE" = 'I'
          and "T10"."MAX_BILL_BILL_GROUP_CDE" = 'A'
          and "T10"."MAX_BILL_EFFRT_NBR" > '5'
         then 'T'
         else "T10"."DOC_TYP_CDE"
         end
order by 1 asc , 2 asc

When I run the SQL code by itself it took 1 hour, 14 minutes and 2 seconds.  As you can see the query uses a subquery to produce an intermediate result set.  I noticed that the date parameter ("T10"."BILL_DT" between {ts '2011-09-01 00:00:00.000'} and {ts '2011-09-12 23:59:59.998'}) was being applied after the subquery and not before. 

I moved the date parameter to be part of the subquery and it ran in 58 seconds.

How can I get Report Studio to move the date parameter to be part of the subquery?  I have tried a few of the query parameters but the query does not change when I regenerate the query.

Thanks in advance!
Scott Lindsey