May 19, 2014 at 9:28 am
If I use the following expression to determine which field should be shown in the report:
=IIF((Parameters!Facility.Value = 3), Fields!Field1.Value, Fields!Field2.Value)
This should display one value (Field1), if that specific facility (3) is selected in the Facility report parameter, or else a different value (Field2) for all other facilities selected.
Only one value can be chosen for the Facility report parameter (no multiple values allowed). Unfortunately, when I run this report, I get the famous #Error message in that field in the report. Both Field1 and Field2 are numeric values that will be rounded to the nearest hundredth (2 decimal places).
Nothing is showing as an Error or Warning when trying to Build this report.
Does anyone have a suggestion on how the expression may need to be structured differently to obtain the results that I am looking for?
May 19, 2014 at 10:07 am
What is the data type of the parameter?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 19, 2014 at 11:06 am
The data type of the Facility parameter is INT. The field values of Field1 and Field2 are numeric with 2 decimal places.
I even tried the following, for a check to nothing values, and then replace it with "0.00":
=IIF((Parameters!Facility.Value = 3), IIF(Fields!Field1.Value Is Nothing, "0.00", Fields!Field1.Value), IIF(Fields!Field2.Value Is Nothing, "0.00", Fields!Field2.Value))
When testing, if all I do is put part of that expression such as:
IIF(Fields!Field1.Value Is Nothing, "0.00", Fields!Field1.Value) or IIF(Fields!Field2.Value Is Nothing, "0.00", Fields!Field2.Value)
Then it works fine on its own. The problem is coming in with the first IIF statement, to use a specific field (Field1 or Field2), based on which facility is selected in the parameter, by the user.
May 19, 2014 at 12:10 pm
If you display the value of the parameter in a text box, does it look normal?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 19, 2014 at 12:36 pm
The Facility parameter label, in a text box, shows correctly (value of 3 equals a specific Facility.Label, which is Facility Name, that is setup for the specific values in the Parameters screen).
If I just have a part of the expression to populate "0.00" where values equal IsNothing, otherwise, use the value coming in, then that field on the report works fine. For example, =IIF(Fields!Field1.Value Is Nothing, "0.00", Fields!Field1.Value).
It's getting the #Error message when I try to put a qualifier in the expression to first look at the facility that was entered, and if the value = 3, then use Field1, otherwise, use Field2. Even if I do not include a check to enter "0.00", where no values exist (IsNothing), and just set the expression to be: =IIF((Parameters!Facility.Value = 3), Fields!Field1.Value, Fields!Field2.Value), then it still shows #Error.
I hope that helps clarify your question.
May 28, 2014 at 2:35 am
A quick check...
What does below return in your textbox ?
=IIF((Parameters!Facility.Value < 3), Fields!Field1.Value, Fields!Field2.Value)
AND then also try this.
=IIF((Parameters!Facility.Value = "3"), Fields!Field1.Value, Fields!Field2.Value)
-- This should work if your Parameter is - text type.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply