CODEWITHSUNDEEP
reporting-services
JustJohn
JustJohn

Reputation: 1460

Error when trying to use Where clause in SSRS expression

This SO link, WHERE Caluse in SSRS expression, verifies that my syntax is correct yet I get this error:

"An error occurred during local report processing. The definition of the report is invalid. The Value expression for the text box 'txt1A_EMA' uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one dataset."

My expression:

=Sum(IIF((Fields!COU_EMA.Value, "CA_Summary") = 1, (Fields!Amount.Value, "CA_Summary"), 0))

There are 3 Datasets in my report. The one I want is "CA_Summary" and the expression above was built with the expression builder.

The field, (Fields!COU_EMA.Value, "CA_Summary"), is a boolean (1 or 0) and I get error either with a 1 or a "true" (no quotes).

I probably could fix it if I knew what the definition of "scope" is. . . maybe.

Upvotes: 0

Views: 759

Answers (1)

BJones
BJones

Reputation: 2460

The scope is where you want to look in order to get the values you're looking for. It could be a data set, group, etc. In your expression it appears CA_Summary is the scope.

I would try modifying the expression a bit. Since you say COU_EMA is bit field, maybe use TRUE. This is also assuming the matrix is using the data set CA_Summary.

=Sum(IIF(Fields!COU_EMA.Value = TRUE, Fields!Amount.Value, 0))

If your matrix isn't using that data set and it still doesn't work try

=Sum(IIF(Fields!COU_EMA.Value = TRUE, Fields!Amount.Value, 0),"CA_Summary")

Upvotes: 1

Related Questions