none
How to combine conditions of a paramter without the query planner breaking? RRS feed

  • Question

  • I'm attempting to make a drop down list of filters for my repoprt. 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 fileter, second filter, and one for ALL RECORDS ignoring both of the filters. I want only one of these options to be selected at once. My problem is when I attempt to run one of the filters the query runs until visual studio becomes unusable and I have to restart the application.

    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 (@param IS NULL) OR (jodrtg.fdescnum <> inmastx.fbin1
     AND @param = 1) OR (fcomp_date<=GETDATE()-2 AND @param = 2) 
    
    
    ) Q
    WHERE  R = 1 



    • Edited by David9501 Monday, October 29, 2018 3:06 PM
    Monday, October 29, 2018 3:05 PM

All replies

  • Hi David9501

    According to your description , due to the complex sql query and the large data count (might be),seems it could be a time-out issue .

    You could check in the following aspects.

    1 make sure you have set the correct parameter setting and the report structure. If possible , you could offer the snapshot about the report structure ,parameter

    Setting , report preview (only the parameter part) to us for more precise research.

    2 make sure your sql query works fine , you could running the sql query in ssms to check if the sql query get the excepted resultset.

    3 whether you got the same result or not when you choose the 3 different options?

    4 is there any error messages when the issue occurred , you could check the vs log file for more information.

    5 try to run the sql report with a few data in database and see if it works or not. If the issue is about the sql query time out , you could get help from the below links:

    Dataset Timeout expired error in SSRS

    Setting Time-out Values for Report and Shared Dataset Processing (SSRS)

    6 for the query, seems it is fine, if possible , you could judge the @param at the beginning of the query . you could try if it works if you make the sql query more optimized. 

    Ps: you could post the image in other website or cloud disk and attach the url here.

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.



    Tuesday, October 30, 2018 3:08 AM
  • Thank you for the insight. I will look into these options.
    Tuesday, October 30, 2018 1:10 PM
  • Hi

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Wednesday, October 31, 2018 3:06 AM