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

Question

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

Here are the columns:

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

I want to summarize qty where skuno starts with report “6” in the rdlc report.

I use this expression, but I get an error:

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

Where is the problem and how to 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 before aggregation you need to convert all possible values ​​to the same type (CInt for Integer, CDec for decimal numbers, CDbl for Double, etc.).