SSRS expression based on Field value

Question

I have a matrix report. i am calculating by using this expression,

=switch(Fields!Type.Value = "ATHLETE",Sum(Fields!JnlAmt.Value)
,Fields!Type.Value = "Type",0.8*Sum(Fields!JnlAmt.Value)
,Fields!Type.Value = "VENUE", Sum(Fields!JnlAmt.Value))

Now i would like to find the difference of Athlete and Type

=Sum(Fields!JnlAmt.Value) [from Athlete] - 0.8*Sum(Fields!JnlAmt.Value) [from Type]

I need help to write an expression for this. If you need more information, let me know. Please any help, any taught is welocme.

how is your report template, how you are achieving these in `matrix` - dataset mapping and some sample data would help to understand the problem better — Abhishek, Mar 09 '16 at 07:03

score 3 · Accepted Answer · answered Mar 09 '16 at 14:18

3

I think you can achieve that by using a conditionally Sum(). Despite I know nothing about grouping and data arrangement in your matrix, I think you are looking for this expression:

=Sum(IIF(Fields!Type.Value="ATHLETE",Fields!JnlAmt.Value,0))-
0.8*Sum(IIF(Fields!Type.Value="Type",Fields!JnlAmt.Value,0))

I've created a simple matrix for example purpose.

6 - (0.8 * 15) = -6

Hopefully this what you are looking for, let me know if this helps.

answered Mar 09 '16 at 14:18

alejandro zuleta

13,962
3
28
48

Hey, i tried by using the above expression. I am just getting #error message. – Hari Mar 09 '16 at 17:44
@Hari, share a screenshot or a description of your matrix, the groups and the expressions in it. – alejandro zuleta Mar 09 '16 at 18:31

Hari · Answer 2 · 2016-08-22T18:39:34.530

0

I used nothing instead of 0, it's worked for me. Thanks for your help.

=Sum(IIF(Fields!Type.Value="ATHLETE",Fields!JnlAmt.Value,nothing))-
0.8*Sum(IIF(Fields!Type.Value="Type",Fields!JnlAmt.Value,nothing))

edited Aug 22 '16 at 18:39

answered Mar 14 '16 at 16:44

Hari

192
2
12

SSRS expression based on Field value

2 Answers2