ssrs 2008 iif expression

  • In an ssrs 2008 report, there is the following IIF expression on the visibility property of a tablix:

    =iif((CountRows("SrvyRespondents") < 1 ), True, False).

    The "SrvyRespondents" is the name of the dataset that is accessed by the tablix.

    Somehow the true and false portions determine if the tablix will display or not.

    Thus would you tell me what the iif statement means? Would you tell me what each part means so that I know what the expression means in the future?

  • =iif((CountRows("SrvyRespondents") < 1 ), True, False).

    is an odd way to express that. CountRows will return a positive number or zero. So why not

    =iif(CountRows("SrvyRespondents") = 0, True, False)

    You could do something as simple as  = ( CountRows("SrvyRespondents")<1)

    Because a boolean comparison will return either True or False anyway.

    • This reply was modified 1 week, 1 day ago by  pietlinden.
  • I think that the Visibility property has three options, Show, Hide, or Expression. If an expression is used the result determines whether to hide the object, not whether to show it. In this example SrvyRespondents is the scope of the CountRows function so the tablix will be hidden if the dataset has zero rows.

  • The IIF expression takes three parameters: (1) a condition to check, (2) a value to pass on if the condition returns a True result, (3) a value to pass on if the condition returns a False result.

    The condition to check is whether the dataset has less than 1 row. If the dataset has no rows, the condition will return True. If the dataset has at least one row, the condition will return False.

    Based on the other parameters, if the condition is True, the IIF expression returns True. If the condition is False, the IIF expression returns False.

    You said this expression is inside a Visible property of an object. So when the IIF expression returns True, the object is visible. When the IIF expression returns False, the object is invisible.

    I hope that explains it fully and clearly. By the way, since the IIF expression is merely returning exactly the same values as the comparison inside the IIF expression, the statement could probably be simplified to simply = (CountRows("SrvyRespondents")=0)

    It has been a while since I worked with expressions in this SSRS context, so there may need to be something other than the parentheses to tell it to evaluate the expression and return the result. But you can always keep the IIF expression in place if the way to take the simpler approach is not evident.

  • geoff5 wrote:

    You said this expression is inside a Visible property of an object. So when the IIF expression returns True, the object is visible. When the IIF expression returns False, the object is invisible.

    That's what I thought, but it seemed counterintuitive to hide an empty tablix so I looked it up, and when you use an expression for visibility you are setting the Hidden property.

    https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/hide-an-item-report-builder-and-ssrs?view=sql-server-ver16

    "When you specify an expression for visibility, you are setting the Hidden property of the report item... The evaluated expression shows the report item when the value is False, and hides the report item when the value is True."

  • If the property is Hidden and not Visible, then the True/False functionality is reversed, yes. I was going by memory, and I haven't worked with SSRS in a while. I do work with IFF expressions often, though, so I knew I could advise on that piece. Didn't mean to mislead you on the intent of the property using the expression.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply