SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problem with... parameter default value from query


problem with... parameter default value from query

Author
Message
p.gibson
p.gibson
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 56
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


budbeth
budbeth
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 463
I believe the user just needs to click on the Refresh button - the one with the green arrows, next to the print button.



Amit Raut
Amit Raut
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 342
Let me try replicating the issue at my end. I'll revert once done.
p.gibson
p.gibson
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 56
Amit

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

Many thanks though.

Happy new year.

regards
Paul
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search