Post reply

Help: Boolean parameter in where clause?

  • October 2, 2008 at 8:49 am

    #198810

    Hi,

    I'm trying to introduce an option in my report to narrow the results by a date field. I'm want to use a boolean parameter (because of its simple radio buttons) to handle this.

    I'd like the query to return the results based on the following rules:

    1. If the boolean parameter value is 'false', return only those rows where the date field IS NULL

    OR

    2. If the boolean parameter value is 'True', return all rows.

    This is my base query, I've left the where clause empty..... Where do I go

    from here?

    DECLARE @REG_START_DATE DATETIME

    DECLARE @REG_END_DATE DATETIME

    DECLARE @CLOSED_CASES_YN VARCHAR(5)

    SET @REG_START_DATE = '2000-01-01'

    SET @REG_END_DATE = '2007-12-31'

    SET @CLOSED_CASES_YN = 'False'

    SELECT MCASE.CASE_ID,

    MCASE.TITLE,

    MCASE_APP.APP_NO,

    MCASE_APP.APP_DATE,

    MCASE_REG.REG_NO,

    MCASE_REG.REG_DATE,

    FROM MCASE

    LEFT OUTER JOIN MCASE_APP

    ON MCASE.CASE_ID = MCASE_APP.CASE_ID

    LEFT OUTER JOIN CASE_REG

    ON MCASE.CASE_ID = MCASE_REG.CASE_ID

    WHERE MCASE.CASE_CLASED = ????

    Thanks for any assistance.

    Regards

    Dan

  • October 2, 2008 at 9:34 am

    #880025

    Hi,

    You can create one parameter into RS and named it say - type.

    Now you have to design it in following way.

    Available values. It is having label and value.

    Label Value

    "=ALL" "=ALL"

    "=Y" "=Y"

    "=N" "=N"

    Now when you preview your report you can see one more parameter called type and there is drop down box with 3 options ALL, Y and N.

    In stored procedure you need to write

    where

    case when date_field is null then 'Y' else 'N' end = @type or @type = 'ALL'

    Please give your date field name instead of date_field.

    I hope it will help.

    Vijay

  • October 2, 2008 at 12:33 pm

    #880149

    Hi Vijay,

    Thanks for the advice, but I was really wanting to use a boolean parameter, the True/False radio buttons of boolean parameters will be easier for users, one click not two.

    Thanks for your help though.

    Regards

    Dan

  • January 6, 2009 at 12:11 pm

    #922033

    Don't know if you figured this out yet or not, but you could do something in your where clause similar to this

    WHERE (@Parameter = 0 AND CASE_CLOSED IS NOT NULL) OR (@Parameter = 1 AND CASE_CLOSED IS NULL)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply