Using like operator in SSRS Expression

Question

I have a tablix with following results.

Since i am learning SSRS. i wonder how to Sum line total with respect to product name. Since product name has duplicate values but it has only M and Xl difference. If i use row group it won't total like i expected since it has M and Xl difference. I wonder how to write an expression for the total. The desired result set

May 31 2011     S043659      Long-Sleeve Logo jerse M     3      $86.52
                             Long-Sleeve Logo jersey XL    1      $28.84
                                                           Total  $115.36
                             mountain bike socks M        6       $34.20

i used this expression but giving me an error.

 `IIF((Fields!Product.value = Previous(Fields!Product.value),Sum(Fields!linetotal.value))`

Hannover Fist Hannover Fist · Accepted Answer · 2019-11-20T17:13:58

There's actually a few things wrong with your expression.

The IIF doesn't have a 3rd argument for the ELSE value. In this case, you'd want to use 0. So the expression would be IIF the fields match then LineTotal Else 0.

You want to have the SUM on the outside of the IIF, otherwise it will only SUM one row.

The matching without the size is trickier. I have it trim off the last 4 characters to exclude the size for a match - it may not work depending on your other Product names.

=SUM(IIF(LEFT(Fields!Product.value, LEN(Fields!Product.value) - 4) = LEFT(Previous(Fields!Product.value), LEN(Fields!Product.value) - 4), Fields!linetotal.value, 0))

The expression reads the SUM of (IF the Product matches the Previous Product then the Line Total else 0).

All this being said, it would actually be easy to crate a parent group and GROUP BY on the parent product. Unfortunately, your data uses a comma to separate one type (jersey) by size but a space in another (socks) so I don't see how to do it. If they all had a comma, I would create a Calculated Field on the dataset to use the product-line up to the comma.

=LEFT(Fields!Product.value, INSTR(Fields!Product.value, ",") - 1)

IF your Product line is either a comma or space to separate, you might be able to use this for your Calculated Field:

=LEFT(Fields!Product.value, 
    IIF(INSTR(Fields!Product.value, ",") > 0, 
        INSTR(Fields!Product.value, ",") - 1,
        InStrRev(Fields!Product.value, " ") - 1) )

Using like operator in SSRS Expression

1 Answers