Hello,

I want to sum up possible different values of a field if a condition is true. For example:

=Sum(IIF(Fields!status.Value = "Open",0, (IIF(Fields!status.Value = "Waiting",0, (IIF(Fields!status.Value = "Closed",0, (IIF(Fields!status.Value = "Completed",0, Fields!status.Value))))))))

I want a numeral count if the values of a field (Fields!status.Value) are Open, Waiting, Closed or Completed.

The code above is giving me an error.

Please help

Hi Sanjb,

Syntactically,the code is okay.I suspect the problem lies in different return types, depending on the outcome of the branch.

According to the code snippet, I assume that the"status"field contains string values {"Open", "Waiting","Closed","Completed"}. What would be the value in case of the last Else-condition included in the field "status"?

Only if this value is a number, you can evaluate it using aggregate functions. You should then for safety's sake add a cast to a number type (eg CInt(Fields!status.Value)).

In addition, I suggest to provideanull checking(IsNothing(Fields!status.Value)).Furthermore,it would beuseful wrapping the entire checking-conditionina calculatedfield and usethe Sum-functiononthe calculated fieldthatshould containthe number as a result of the checking-condition.

Hope this helps.

Regards,
Thomas

Thanks alot Thomas for your help,

The value in case of the last Else-condition is NULL, "Open", "Waiting","Closed","Completed" are the only possible values.

I actually used a tablix matrix to design the report. I have three row groups, Region, State and Branch.

So, the fourth column should now be a column where a count is done for all the Cases logged in each branch, that was why the calculated field became compulsory.

For example, the Northern region has 4 states which in turn has 6 branches each. In each branch, there should be a total count of cases logged in that branch, that's where the expression(calculated field) becomes compulsory.

I would really appreciate your help...

You are welcome!

Let me just make sure if I get you right...

You're trying to count a case [A] in each of its condition or [B] just in case when field "status" will be null?

For [A] I think of that:

                        Open  |  Waiting  |  Closed  |  Completed

Germany              7    |        12    |     32     |        32
-Berlin                  3    |         9    |     26     |        26
--Branch A            2    |         7    |     10     |        10
--Branch B            1    |         2    |     16     |        16

Am I get you right?

Regards,
Thomas

Thanks again Thomas,

There are four columns: Region, State, Branch and Total Cases/Branch.

The example below: North region has two states (Northern State A and Northern State B).

Northern State A has two branches: Northern State A (Branch 1)  and Northern State A (Branch 2).

Northern State A (Branch 1) has a total of 10 cases, and the breakdown is shown as:  5 open cases + 3 waiting cases + 2 closed cases + 0 completed cases.

I used a tablix matrix in the report. Region, state and branch are in the row groups.

Total Cases/Branch is the calculated field that I had to create so that I can do a sum total of all cases regardless of their status (open, waiting, closed and completed) in each branch.

That is where I get the error.

See below the structure:

Region     State                             Branch                         Total Cases/Branch

North   Northern State A   Northern State A (Branch 1)   Northern StateA (Branch 1) :    

                                                                                                 5 Open, 3 waiting, 2 closed

                                                                                                 and 0 completed cases >

                                                                                                  Total = 10.  

                                                 Northern State A (Branch 2)                                              

                                                                                               Northern StateA (Branch 2) 4

                                                                                               Open,  1 waiting, 2 closed

                                                                                               and 0 completed cases >

                                                                                                    Total = 7.

             Northern State B  

                                              Northern StateB (Branch 1)

                                              Northern StateB (Branch 2)

I want the Total Cases/Branch column to be a calculated field, it should SUM all cases in each branch if the status of the cases are Open, Waiting, Closed and Completed.

Thanks again