How to sum a field based on a condition in another field in RDLC report?

Question

I have an SQL database with skuno and qty columns, with the varchar(50) data type and an int data type respectively.

Here are the columns :

 skuno       qty
 -----------------
 78654   -   100  
 65495   -   120  
 10564   -   67  
 64389   -   20

I want to sum qty where skuno begins with a "6" in rdlc report.

I am using this expression but getting an error :

=Sum(iif(Fields!skuno.Value like "6*", Fields!qty.Value, 0))

Where is the problem and how can I fix it?

@Matt Thrower It shows `#Error` in matrix data cell and not the calculated value... — Muhammad Tarique, Apr 15 '15 at 11:52
Ah, so this is an excel question? Will change the tags if so, as all three are wrong in that case. — Bob Tway, Apr 15 '15 at 12:51

score 19 · Answer 1 · answered Apr 15 '15 at 13:32

19

You can use an expression like this:

=Sum(CInt(IIf(Left(Fields!skuno.Value, 1) = "6", Fields!qty.Value, 0)))

Please note that you have to convert every possible values to the same type (CInt for Integer, CDec for Decimal, CDbl for Double, etc.) before aggregation.

answered Apr 15 '15 at 13:32

tezzo

10,858
1
25
48

3

Thank you! The conversion to the same types makes a whole world of difference – MPaul Feb 09 '16 at 15:44

Aladein · Answer 2 · 2022-01-03T18:07:15.903

1

=Sum(IIf(Fields!HeaderModel.Value = 1 , cdbl(Fields!HeaderTAX.Value), 0), "DataSet2")

edited Jan 03 '22 at 18:07

answered Jan 03 '22 at 17:45

Aladein

184
2
13

score 0 · Answer 3 · edited Feb 16 '22 at 16:02

0

=Sum(IIf(Fields!HeaderModel.Value = 1 , cdbl(Fields!HeaderTAX.Value), cdbl(0)), "DataSet2")

edited Feb 16 '22 at 16:02

Syscall

19,327
10
37
52

answered Feb 16 '22 at 14:40

Ciro Capasso

1

1

Your answer could be improved with additional supporting information. Please edit your answer to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](https://stackoverflow.com/help/how-to-answer). – Syscall Feb 16 '22 at 16:02
While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Feb 24 '22 at 22:05

How to sum a field based on a condition in another field in RDLC report?

3 Answers3

Linked