SSRS 2019, summarizing calculated expression

Question

I'm using a matrix to show salespersons orders for each month of the year. rows - state, salesperson name columns - year, month data - number of orders

for each salesperson I have the total number of orders per year. based on that I have a calculation that shows if the salesperson met his target or not.

=IIf(sum(Fields!OrderCount.Value)>=50, "met target", "below target").

I need to add a total in state level - how many salespersons met their target and how many didn't. That is, to show something like:

10 met target

5 below target

in state group total.

How can I do that?

I'm attaching an example based on my report (I can't publish the report).

Report

What I need is highlighted in yellow. For state 04 I have 3 salespersons. 2 of them met their target and 1 didn't.

=SUM(IIf(sum(Fields!OrderCount.Value)>=50, 1, 0)) & " met target"

gives me 1 met target and not 2.

edited April 1st 2020

Alan, if I understand you correctly, I need to define on which scope to calculate the sum.

This part sum(Fields!OrderCount.Value, "stateGroup")>=50 should be in the Salesperson level. so my scope is "Salesperson". The full expression is in the StateGroup total.

=SUM(IIf(sum(Fields!OrderCount.Value, "Salesperson")>=50, 1, 0)) & " met target"

But, when I try it, with either scope name, I get this error message: The value expression for the TextBox 'A' specifies a scope that is not valid for a nested aggregate. The scope must be the same name of the scope specified by the outer aggregate or the name of a group or data region that is contained in the scope specified by the outer aggregate.

So, what am I still doing wrong?

Answer 1

This is off the top of my head but I think the following will work..

=SUM(IIf(sum(Fields!OrderCount.Value)>=50, 1, 0)) & " met target"

and

=SUM(IIf(sum(Fields!OrderCount.Value)<50, , 0)) & " below target"

Update based on OP remarks

If the expressions are in a different scope then you need to specify the scope. e.g. If you States were grouped in a RowGroup called stateGroup then the expression would be something like.

=SUM(IIf(sum(Fields!OrderCount.Value, "stateGroup")>=50, 1, 0)) & " met target"