Complex IIF statement not working

I am not understanding what my issue is on this one! I can look at the data
returned from the dataset in Data view, but when I attempt to show a table
based on at least one value being true (from several) it says:
"The hidden expression for the table 'table1' refers to the field
'SSFLunchRegular'. Report item expressions can only refer to fields within
the current data set scope..."
I can turn right around and show/hide a row of the table based on the exact
same field name and it works. The structure of my IIF statment appears to be
fine because the same structure works in another report. Is it the number of
conditions? Is there a limit?
Here are the conditions placed on the Hidden statement:
=IIF(
(FIELDS!SLPLunchRegular.Value <> 0) and
(FIELDS!SLPLunchProv1.Value <> 0) and
(FIELDS!SLPLunchProv2.Value <> 0) and
(FIELDS!SLPLunchProv3.Value <> 0) and
(FIELDS!SLPBkfstRegular.Value <> 0) and
(FIELDS!SLPBkfstProv1.Value <> 0) and
(FIELDS!SLPBkfstProv2.Value <> 0) and
(FIELDS!SLPBkfstProv3.Value <> 0) and
(FIELDS!SLPSevereRegular.Value <> 0) and
(FIELDS!SLPSevereProv1.Value <> 0) and
(FIELDS!SLPSevereProv2.Value <> 0) and
(FIELDS!SLPSevereProv3.Value <> 0) and
(FIELDS!SLPSnackArea.Value <> 0) and
(FIELDS!SLPSnackNonArea.Value <> 0), False, True
)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1oops, I wrote 'SSFLunchRegular', it should have been 'SLPLunchRegular'. The
misspelling was in this thread, not my code so the problem is not solved..
Rick wrote:
>I am not understanding what my issue is on this one! I can look at the data
>returned from the dataset in Data view, but when I attempt to show a table
>based on at least one value being true (from several) it says:
>"The hidden expression for the table 'table1' refers to the field
>'SSFLunchRegular'. Report item expressions can only refer to fields within
>the current data set scope..."
>I can turn right around and show/hide a row of the table based on the exact
>same field name and it works. The structure of my IIF statment appears to be
>fine because the same structure works in another report. Is it the number of
>conditions? Is there a limit?
>Here are the conditions placed on the Hidden statement:
>=IIF(
>(FIELDS!SLPLunchRegular.Value <> 0) and
>(FIELDS!SLPLunchProv1.Value <> 0) and
>(FIELDS!SLPLunchProv2.Value <> 0) and
>(FIELDS!SLPLunchProv3.Value <> 0) and
>(FIELDS!SLPBkfstRegular.Value <> 0) and
>(FIELDS!SLPBkfstProv1.Value <> 0) and
>(FIELDS!SLPBkfstProv2.Value <> 0) and
>(FIELDS!SLPBkfstProv3.Value <> 0) and
>(FIELDS!SLPSevereRegular.Value <> 0) and
>(FIELDS!SLPSevereProv1.Value <> 0) and
>(FIELDS!SLPSevereProv2.Value <> 0) and
>(FIELDS!SLPSevereProv3.Value <> 0) and
>(FIELDS!SLPSnackArea.Value <> 0) and
>(FIELDS!SLPSnackNonArea.Value <> 0), False, True
>)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1|||The problem seems to be that you're attempting to hide a whole table based
on one single record. That wouldn't be logical. You'd need to aggregate the
values somehow, something like
=IIF(
(Sum(FIELDS!SLPLunchRegular.Value) <> 0) and
(Sum(FIELDS!SLPLunchProv1.Value) <> 0) and
...
...
Hope this helps.
--
Robert Jeppesen
Durius
http://www.durius.com/
"Rick via SQLMonster.com" <u15024@.uwe> wrote in message
news:5773f7edc9ca2@.uwe...
>I am not understanding what my issue is on this one! I can look at the
>data
> returned from the dataset in Data view, but when I attempt to show a table
> based on at least one value being true (from several) it says:
> "The hidden expression for the table 'table1' refers to the field
> 'SSFLunchRegular'. Report item expressions can only refer to fields
> within
> the current data set scope..."
> I can turn right around and show/hide a row of the table based on the
> exact
> same field name and it works. The structure of my IIF statment appears to
> be
> fine because the same structure works in another report. Is it the number
> of
> conditions? Is there a limit?
> Here are the conditions placed on the Hidden statement:
> =IIF(
> (FIELDS!SLPLunchRegular.Value <> 0) and
> (FIELDS!SLPLunchProv1.Value <> 0) and
> (FIELDS!SLPLunchProv2.Value <> 0) and
> (FIELDS!SLPLunchProv3.Value <> 0) and
> (FIELDS!SLPBkfstRegular.Value <> 0) and
> (FIELDS!SLPBkfstProv1.Value <> 0) and
> (FIELDS!SLPBkfstProv2.Value <> 0) and
> (FIELDS!SLPBkfstProv3.Value <> 0) and
> (FIELDS!SLPSevereRegular.Value <> 0) and
> (FIELDS!SLPSevereProv1.Value <> 0) and
> (FIELDS!SLPSevereProv2.Value <> 0) and
> (FIELDS!SLPSevereProv3.Value <> 0) and
> (FIELDS!SLPSnackArea.Value <> 0) and
> (FIELDS!SLPSnackNonArea.Value <> 0), False, True
> )
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1