Changing Field Value With Parameter

• mbrady5

  SSCrazy

  Points: 2835

  More actions

  March 18, 2013 at 2:14 pm

  #272265

  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

• davoscollective

  SSCertifiable

  Points: 6355

  More actions

  March 18, 2013 at 10:02 pm

  #1598283

  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")