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