Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

problem with... parameter default value from query Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 2:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 2:11 AM
Points: 4, Visits: 31
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. (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

Post #1514056
Posted Monday, December 09, 2013 4:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 7:16 AM
Points: 40, Visits: 402
I believe the user just needs to click on the Refresh button - the one with the green arrows, next to the print button.


Post #1521349
Posted Monday, January 06, 2014 2:22 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 6:12 PM
Points: 827, Visits: 342
Let me try replicating the issue at my end. I'll revert once done.
Post #1528255
Posted Tuesday, January 07, 2014 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 2:11 AM
Points: 4, Visits: 31
Amit

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

Many thanks though.

Happy new year.

regards
Paul
Post #1528383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse