SSRS expression IIF() and sum

Question

I have this error message that pops up when trying to run a report. It is a SSRS Report for Dynamics AX 2012.

System.Web.Services.Protocols.SoapException: The Visibility.Hidden expression for the text box ‘Textbox183’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset. at Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, Guid batchId, Warning[]& Warnings) at Microsoft.ReportingServices.WebServer.ReportingService2005.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, Warning[]& Warnings)*

This expression below is set on a textbox's visibility.

Format(sum(IIF(Fields!InventOnHand.Value <= Parameters!CutOff.Value, Sum(Fields!InventOnHand.Vallue), 0 )), "#, ##0.00")

Please help me understand why that error above shows and how to fix this expression.

I agree with the posted answer. Also if you wish to have the text in the box either show the sum or 0, then you would set the text box expression to =Format(sum(IIF(Fields!InventOnHand.Value <= Parameters!CutOff.Value, Sum(Fields!InventOnHand.Vallue), 0 )), "#, ##0.00") — B. Seberle
The "Vallue" typo could be leading to a strange error message like this, depending on how the compiler interprets this. — Wesley Marshall

Hannover Fist Hannover Fist · Accepted Answer · 2019-06-28T18:12:46

When do you want to see your text box? Your expression is calculating a dollar amount and not resulting in a True/False condition need for Visibility.

Since the error is asking about your dataset, I assume it's not in a table - a text box in a table wouldn't need a dataset sine the table is associated with a dataset.

Assuming that you only want to see the text box when the SUM of Inventory is greater than the CutOff parameter, you'd want:

=IIF(SUM(Fields!InventOnHand.Value, "Dataset1") <= Parameters!CutOff.Value, True, False)

SSRS expression IIF() and sum

1 Answers