CODEWITHSUNDEEP
reporting-servicesssrs-2012ssrs-expression
LD9
LD9

Reputation: 11

SSRS expression IIF() and sum

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.

Upvotes: 1

Views: 532

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

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)

Upvotes: 1

Related Questions