Sum based on multiple conditions

Question

Sum based on multiple conditions

Eshwar 216

Hi,
I am trying to get total based on multiple conditions and it is giving error.
=sum(IIF(Fields!Field1.Value = "One", IIF(Fields!Field2.Value = 3, Fields!Amnt1.Value, Fields!Amnt2.Value),0))
Thanks,
Eshwar.

Eshwar 216 Reputation points

2022-01-28T07:23:48.173+00:00

Tried this which is also not working
=sum(IIF(Fields!Field1.Value = "One", IIF(Fields!Field2.Value = 3, CDec(Fields!Amnt1.Value), CDec(Fields!Amnt2.Value)),0))

Clng works but it is rounding off the decimal values
=sum(IIF(Fields!Field1.Value = "One", IIF(Fields!Field2.Value = 3, CLng(Fields!Amnt1.Value), CLng(Fields!Amnt2.Value)),0))

Thanks,
Eshwar.
Eshwar 216 Reputation points

2022-01-28T08:01:25.183+00:00

This is happening because of different data types getting warning:
The Value expression for the textrun ‘Textbox102.Paragraphs[0].TextRuns[0]’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

This seems to be working:
=sum(IIF(Fields!Field1.Value = "One", IIF(Fields!Field2.Value = 3, CDec(Fields!Amnt1.Value), CDec(Fields!Amnt2.Value)),CDec(0.00)))

Thanks,
Eshwar.

1 answer

Your answer

Eshwar 216 Reputation points

2022-01-28T07:23:48.173+00:00

Tried this which is also not working
=sum(IIF(Fields!Field1.Value = "One", IIF(Fields!Field2.Value = 3, CDec(Fields!Amnt1.Value), CDec(Fields!Amnt2.Value)),0))

Clng works but it is rounding off the decimal values
=sum(IIF(Fields!Field1.Value = "One", IIF(Fields!Field2.Value = 3, CLng(Fields!Amnt1.Value), CLng(Fields!Amnt2.Value)),0))

Thanks,
Eshwar.
Eshwar 216 Reputation points

2022-01-28T08:01:25.183+00:00

This is happening because of different data types getting warning:
The Value expression for the textrun ‘Textbox102.Paragraphs[0].TextRuns[0]’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

This seems to be working:
=sum(IIF(Fields!Field1.Value = "One", IIF(Fields!Field2.Value = 3, CDec(Fields!Amnt1.Value), CDec(Fields!Amnt2.Value)),CDec(0.00)))

Thanks,
Eshwar.

Answer 1

Hi @Eshwar

I test the expression and it runs ok.

Here is my test table :

CREATE TABLE TEST1  
(ID INTEGER IDENTITY(1,1),  
 Field1 VARCHAR(20),  
 Field2 INTEGER,  
 Amnt1 INTEGER,  
 Amnt2 INTEGER)  
  
  INSERT INTO TEST1  
  SELECT 'One',3,1,1  
    INSERT INTO TEST1  
  SELECT 'One',3,1,2

The textbox expression:

And the preview result:

Best Regards,
Isabella

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Sum based on multiple conditions

1 answer

Your answer