How to get Sum of column with absolute value in Matrix in SSRS

1
votes

I am new in SSRS and I am creating a report in which I need sum of column with absolute value as Gross. I have following database structure : 

ExpCat  long_short  Qty
USA     Long        10
USA     Long        30
USA     Long        -15
USA     Short       5 
USA     Short       -1
Japan   Long        50
Japan   Long        10
Japan   Short       -2

I used long_short column in row group and ExpCat colum as column group in matrix.

Current Output : 

Entity  Japan   USA
 Long   60      25
 Short  -2      4
 Gross  58      29

Desired Output: 

Entity  Japan   USA
Long    60      25
Short   -2      4
Gross   62      29

Here Gross is absolute sum of Long and Short respective to Japan and USA.

I am tried following expression to calculate Gross: 

=IIf(InScope("ExpCat"),Sum(Field!Qty),0)

enter image description here

Any help will be appreciated. Thanks.

1
sql-serversql-server-2008reporting-servicesssmsssms-2014
I'm sure =iff(InScope..' is a typo, but just to make sure... Don't you want IIF ? – BJones
Yes that a typo, it should be IIF, I used correct syntax in my SSRS report – Saurabh Saxena

1 Answers

2
votes

I SSRS you should be able to build an expression. The expression should be something like: =Abs(iff(InScope("ExpCat"),Sum(Field!Qty),0)) that should give you the value you are looking for.