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?

Cortex

@Matt Thrower It shows #Error in matrix data cell and not the calculated value... – Cortex

Ah, so this is an excel question? Will change the tags if so, as all three are wrong in that case. – Enchiridion

Answer 1

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.

Polymerize

Thank you! The conversion to the same types makes a whole world of difference – Dramaturgy

Answer 2

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

Scotfree

Answer 3

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

Thetes

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. – Wingard

While this code may solve the question, including an explanation 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. – Asyut