CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2014
alex5p
alex5p

Reputation: 9

SSRS: Trying to hide a Textbox and keep getting a data aggregation error

I am basically trying to hide this placeholder/text box based off the condition of the preapproval field i am using in this report. If the field has the value of "Yes" leave the textbox there and if it is "No" hide the textbox from the user. I have tried using

=iif( Fields!Preapproval.Value = "Yes", false,true)

and the ssrs report will error saying

The Hidden expression for the text box ‘Textbox86’ refers directly to the field ‘Preapproval’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.

I have seen some posts saying to use code similar to this

=First(Fields!Preapproval.Value, "TestDateSet") to get around the error I am currently receiving.

Anyone have any ideas on what I need to do to get the visibility working correctly for this textbox?

Upvotes: 0

Views: 691

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

It sound like you are trying to hide the textbox but the textbox is not inside a table/matrix that is bound to the dataset.

If the textbox was, for example, in a row in a table that was bound to your dataset then what you have tried would work.

If the textbox is in a table/matrix but not in the details row group, you can specify the row group name as the scope, something like

=IIF(FIRST(Fields!Preapproval.Value, "myRowGroupName") = "Yes", False, True)

Assuming the textbox is just sat on it's own on the report somewhere then depending on what your dataset contains you would have to decide what approach to use.

If you dataset only contains 1 record then you can use

=IIF(FIRST(Fields!Preapproval.Value, "myDataSetName") = "Yes", False, True)

If you dataset contains more than one records then you can still use the above or you could use a different aggregate function such as MIN() or MAX() instead of FIRST().

NOTE: The scope name (in this case your dataset name) is case sensitive and must be enclosed in double quotes.

BTW: You could simplify the hidden expression to something like

=FIRST(Fields!Preapproval.Value, "myDataSetName") <> "Yes"

as this will return true or false anyway, so no need for an IIF

Upvotes: 2

Related Questions