SSRS grouping + sum based on other column

Question

Hi,
We have SSSRS report where we are grouping by Type like cash, credit etc
We have group totals like 'Cash Total', 'Credit Total'. Then we have Grand total of all types.

We show like this:
ID Description Cost Value Gain
CASH
1 xxx 0.00 0.00 0.00
2 yyy 1.20 1.3 0.1
TotalCash 1.20 1,3 0.1

CREDIT
1 zzzz 0.00 8.9 10,00
2 kkk 5,00 2,2 5,00
Total Credit 5.00 11.1 15.00

My requirement is when type <> 'CASH' and original cost = 0.00, do not include value in sum of Value column and Gain column. How can i say that in group totals.
Right now I am doing sum(Fileds!Value.Value).

New requirement
so result row should look like
For CASH
TotalCash 1.20 1,3 0.1
Total Credit 5.00 2.2 5.00

Grand Total: 6.20 3.5 5.01

Answer 1

Hi @Spunny
I think you can use expression like below for the sum of Value column ,so is to Gain column

=Sum(iif(Fields!Cost.Value=0 and Fields!type.Value = "CREDIT",0,Fields!Value.Value))  

Here is the result,for test I change the data a bit:

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Spunny ,
You could use the following expressions respectively:

=SUM(IIF(Fields!Type.Value <> "CASH" and Fields!Cost.Value=0,0,Fields!Cost.Value))  
=SUM(IIF(Fields!Type.Value <> "CASH" and Fields!Cost.Value=0,0,Fields!Value.Value))  
=SUM(IIF(Fields!Type.Value <> "CASH" and Fields!Cost.Value=0,0,Fields!Gain.Value))  

Then perform the Add Total action on these three expressions respectively.

The final design is as follows:

Preview:

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.