sql server reporting services: Nulls -conditional display of value in Visual Studio -- sql server reporting services

Nulls -conditional display of value in Visual Studio

ppbedz
4/5/2007 11:54:04 AM

sql server reporting services:

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.

Re: Nulls -conditional display of value in Visual Studio

Lisa Slater Nicholls
4/5/2007 9:00:00 PM

[quoted text, click to view]

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,

[quoted text, click to view]

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.