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

Changing Field Value With Parameter Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 2:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 14, 2014 10:54 AM
Points: 91, Visits: 475
Hi,

I'd like to change a field value in my main data source query using a parameter. I cannot figure this out.

I'd like to be able to use a parameter to change the field 'DueDays' to 'AgeDays' and vice versa if I choose. I would specify my parameter values as the coordinating field names which are;

AgeDays
DueDays

Select Last,First,SiteName,DueDays 
From My Table

Seems like such an easy thing to accomplish but apparently not for me. Thanks
Post #1432332
Posted Monday, March 18, 2013 10:02 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:57 PM
Points: 443, Visits: 822
mbrady5 (3/18/2013)
Hi,

I'd like to change a field value in my main data source query using a parameter. I cannot figure this out.

I'd like to be able to use a parameter to change the field 'DueDays' to 'AgeDays' and vice versa if I choose. I would specify my parameter values as the coordinating field names which are;

AgeDays
DueDays

Select Last,First,SiteName,DueDays 
From My Table

Seems like such an easy thing to accomplish but apparently not for me. Thanks


There's a couple of ways you could do this. I would recommend against a dynamic SQL route, which could be accomplished either by using a stored proc (pass a parameter telling the proc which of those fields you want, and then using an IF/ ELSE in the proc to call one query or another) or in an expression by constructing SQL from a string:

="Select Last,First,SiteName, " + @AgeOrDueDays + "
From My Table"

Don't do it that way though.


Better to return both fields to the report and choose which you want in there

Select Last,First,SiteName,DueDays, AgeDays
From My Table

In the tablix, you can use an expression for both the column heading and the cell value.

The @AgeorDueDays parameter can have 2 available values
"AgeDays"
"DueDays"
You could use the same values as the labels.

The expression in the table would be
=IIF(Parameters!@AgeorDueDays.value = "AgeDays", Fields!AgeDays.value, Fields!DueDays.value)

And the heading expression:
=IIF(Parameters!@AgeorDueDays.value = "AgeDays", "AgeDays", "DueDays")
Post #1432464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse