Nulls -conditional display of value in Visual Studio

I am trying to display one of 2 values based on an input parameter. I have
created a field which is supposed to evaluate the parameter and display the
appropriate field.
My code is as follows: =iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
Fields!Measures_M9N_MTD.Value)
When I preview my report and select "Y" as the parameter, my field displays
nothing. I have checked the data and know there is a value in the field
represented by the "true" portion of my statement.
If I change the staement to this:=iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value, 0)
I actually get the value I am expecting.
My conclusion is the data in the "false" portion of the statement is
actually null or non-existant. I think this is causing my statement to
malfunction. How do I account for this and make my statement work properly?
There will eventually be data in the field represented in the "false"
portion of the statement. The data will reside in either/or both depending
on the time of month.
Thank you... PB> My conclusion is the data in the "false" portion of the statement is
> actually null or non-existant. I think this is causing my statement to
> malfunction.
Assuming this conclusion is correct, you could use a nested IIF() test using
the ISNOTHING() function, like this:
=iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
iif(ISNOTHING( Fields!Measures_M9N_MTD.Value), 0,
Fields!Measures_M9N_MTD.Value) )
... however (still assuming your conclusion about why it's not working is
correct) it might be safer to assume that *either* value could be missing,
so you could do the test in both places:
=iif( Parameters!LastWeek.Value = "Y",
iif(ISNOTHING(Fields!Measures_M30N_MTD_Last_Week_of_Month.Value),0,
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value),
iif(ISNOTHING(Fields!Measures_M9N_MTD.Value), 0,
Fields!Measures_M9N_MTD.Value) )
... double-however <g> when you look at it this way, it might be simpler to
do the same work in your data query. IOW use ISNULL() or COALESCE() in your
SELECT statement, to provide a default value for values that might be
missing, before you get to the report level.
Regards,
>L<
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:7F96FEB5-806D-4761-8077-4C3A7B85973D@.microsoft.com...
>I am trying to display one of 2 values based on an input parameter. I have
> created a field which is supposed to evaluate the parameter and display
> the
> appropriate field.
> My code is as follows: =iif( Parameters!LastWeek.Value = "Y",
> Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
> Fields!Measures_M9N_MTD.Value)
> When I preview my report and select "Y" as the parameter, my field
> displays
> nothing. I have checked the data and know there is a value in the field
> represented by the "true" portion of my statement.
> If I change the staement to this:=iif( Parameters!LastWeek.Value = "Y",
> Fields!Measures_M30N_MTD_Last_Week_of_Month.Value, 0)
> I actually get the value I am expecting.
> My conclusion is the data in the "false" portion of the statement is
> actually null or non-existant. I think this is causing my statement to
> malfunction. How do I account for this and make my statement work
> properly?
> There will eventually be data in the field represented in the "false"
> portion of the statement. The data will reside in either/or both
> depending
> on the time of month.
> Thank you... PB