Search code examples
sqlreporting-servicesssrs-2008

How to combine paramters in SSRS?


I'm attempting to make a drop down list of choices for my report using 2 parameters. One is for when jodrtg.fdescnum <> inmastx.fbin1 and the other is for when fcomp_date<=GETDATE()-2. There's supposed to be 3 options for the filters/parameters in the drop down list the first parameter, second parameter, and one for ALL RECORDS ignoring both of the parameters. I want only one of these options to be selected at once.I have the first parameter jodrtg.fdescnum <> inmastx.fbin1 working in my dataset as well as the option to show ALL RECORDS. My problem is getting fcomp_date<=GETDATE()-2 to work as an option in the same parameter drop down list.

     SELECT
    R,
    Stack,
        RTRIM(MoldNo) as MoldNo,
        Press,
        JobNo,
        Status,
        RelDate,
        StartByDate,
        Priority,
        RTRIM(WH) as WH,
        MoldLoc,
        foperno,
        flschedule,
        RTRIM(WC) as WC,
        fcomp_date,
        flastlab,
        RTRIM(MoldDesc) as MoldDesc,
        Cavitation,
        MoldDims,
        Type,
        fddDueDate,
        fDueDate,
        foperqty,
        fnqty_comp,
        fnqty_togo,
        fnqty_move
    FROM
    (
    SELECT
        jodbom.fbompart as MoldNo, 
        jodrtg.fdescnum AS Press,
        jomast.fjobno as JobNo,
          jomast.fstatus as Status,
        jomast.frel_dt as RelDate,
          jomast.ftduedate as StartByDate,
        jomast.fschdprior as Priority,
        inmastx.flocate1 AS WH,
        inmastx.fbin1 AS MoldLoc,
        jodrtg.foperno as foperno,
        jodrtg.flschedule as flschedule,
        jodrtg.fpro_id as WC,
        jodrtg.fcomp_date as fcomp_date,
        jomast.flastlab as flastlab ,
        inmastx.fdescript AS MoldDesc,
          inmastx.fcusrchr2 AS Cavitation,
        inmastx.fcusrchr3 AS MoldDims,
        jomast.ftype as Type,
        jomast.fddue_date as fddDueDate,
        sorels.fduedate as fDueDate,
        jodrtg.foperqty as foperqty,
        jodrtg.fnqty_comp as fnqty_comp ,
        jodrtg.fnqty_togo as fnqty_togo,
        jodrtg.fnqty_move as fnqty_move,
        ROW_NUMBER() OVER (PARTITION BY jodbom.fbompart ORDER BY jomast.frel_dt,jomast.fschdprior,jomast.fjobno) R,
        ROW_NUMBER() OVER (PARTITION BY jodrtg.fdescnum ORDER BY jodrtg.fdescnum,jomast.frel_dt,jomast.fschdprior,jomast.fjobno) Stack

        FROM jodbom 
        INNER JOIN jomast ON jodbom.fjobno =  jomast.fjobno
        INNER JOIN jodrtg ON jomast.fjobno = jodrtg.fjobno 
        INNER JOIN inmastx ON jodbom.fbompart = inmastx.fpartno
        LEFT JOIN sorels ON jomast.fsono+jomast.fkey = sorels.fsono+sorels.finumber+sorels.frelease 
        WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1 

and (jodrtg.fdescnum <> inmastx.fbin1
    OR @Setup is null)



    ) Q
    WHERE  R = 1 

Solution

  • Answered with help from the comments.

    ( (jodrtg.fcomp_date <= DATEADD(dd,-2,GETDATE()) And @Setup=1) Or (@Setup is null) Or (jodrtg.fdescnum <> inmastx.fbin1 AND @Setup =2) )