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?

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.