problem with... parameter default value from query

  • Hello

    The following script is used to provide a default value or values to a report parameter called @IsScope. As you can see it depends on other parameter values. Namely @spec and @RefHB. When previewing the report(in BIDS), the expected default value(ReturnVal = 1 and ReturnLab = 'Scopes' in this case) is passed to the parameter and the report brings back the expected data. However, if the @spec parameter is subsequently changed by the user in preview mode(to say "C7") and then changed back to the value "C1"(the default value for @spec), the wrong default value is returned for @IsScope.:w00t: (ReturnVal = 0 and ReturnLab = 'Non-scopes')

    I am using Visual Studio 2005. Is this a known bug? Does anyone know how to do a workaround? I'm happy to explain further if necessary. Thank you in advance.

    kind regards

    Paul

    IF @spec = 'C1'

    BEGIN

    IF @RefHB IN(

    SELECT h.[HB Desc] FROM dbo.UserSSRSHealthBoardDefaultLookup d

    INNER JOIN dbo.Health_Board_Lookup h ON d.HB=h.[HB Local Code]

    WHERE DefaultIsScope = '0'

    )

    BEGIN

    SELECT

    ReturnVal = '0'

    , ReturnLab = 'Non-scopes'

    END

    IF @RefHB IN(

    SELECT h.[HB Desc] FROM dbo.UserSSRSHealthBoardDefaultLookup d

    INNER JOIN dbo.Health_Board_Lookup h ON d.HB=h.[HB Local Code]

    WHERE DefaultIsScope = '1'

    )

    BEGIN

    SELECT

    ReturnVal = '1'

    , ReturnLab = 'Scopes'

    END

    IF @RefHB IN(

    SELECT h.[HB Desc] FROM dbo.UserSSRSHealthBoardDefaultLookup d

    INNER JOIN dbo.Health_Board_Lookup h ON d.HB=h.[HB Local Code]

    WHERE DefaultIsScope = '2'

    )

    BEGIN

    SELECT

    ReturnVal = '0'

    , ReturnLab = 'Non-scopes'

    UNION

    SELECT

    ReturnVal = '1'

    , ReturnLab = 'Scopes'

    END

    END

    ELSE

    BEGIN

    SELECT ReturnVal = 0, ReturnLab = 'Not applicable'

    END

  • I believe the user just needs to click on the Refresh button - the one with the green arrows, next to the print button.

  • Let me try replicating the issue at my end. I'll revert once done.

  • Amit

    Please don't. I managed to resolve this. It was a bug in my code.

    Many thanks though.

    Happy new year.

    regards

    Paul

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

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