locked
Sub Total only when All prices have value in Matrix Tablix RRS feed

  • Question

  • Hi,

    I am trying to subtotal the MTRC_INV_P as shown below. But I need to apply some rules while summing them all.

    1. ADD only when CODE = 0 and display at bottom.

    This rule is done by using the simple iff condition: =SUM(IIf(Fields!CODE.Value = 0,Fields!MTRC_INV_P.Value,0))

    2. Don not add the total when any one of the MTRC_INV_P has a value of 0.0 or Null.

    I am not sure how to include the second rule with the 1st one.

    Any help is highly appreciated.

    Thanks.

    Wednesday, May 8, 2013 9:40 AM

Answers

  • The below expression worked for me. However I need to test little more. :)

    =SUM(IIF(Sum(IIF(IsNothing(Fields!MTRC_INV_P.Value) AND (Fields!CODE.Value = 0 OR Fields!CODE.Value = 1),1,0)) >0,0,IIf(Fields!CODE.Value = 0,Fields!MTRC_INV_P.Value,0)))

    • Proposed as answer by Harsh Kumar Thursday, May 9, 2013 10:00 AM
    • Marked as answer by Rikz Monday, May 13, 2013 9:38 AM
    Thursday, May 9, 2013 7:59 AM

All replies

  • Hi Rikz,

    Try this expression:

    =SUM(IIf(Fields!CODE.Value = 0 AND ISNUMERIC(Fields!MTRC_INV_P.Value) = TRUE,Fields!MTRC_INV_P.Value,0))


    Regards Harsh

    Wednesday, May 8, 2013 11:06 AM
  • Thanks Harsh.

    But it din't work out and may be i couldn't explain the problem. I will try to explain it properly. The color coding as shown below is handled with a CODE value if white (0) then add if gray(1) then do not add in the total (neglect the black (2) ).

    Thus the total for T1 is 100 +100 = 200

    The next thing is if any of the cells with white background doesn't have price value then do not go for summation for that column and display 0. make sense?? :(

    m totally blank on the 2nd part.

    Thanks in advance.

    Wednesday, May 8, 2013 1:18 PM
  • Just found out InScope function but not sure if I have understood it properly.

    Can we use InScope in this scenario?

    Thanks

    Thursday, May 9, 2013 5:00 AM
  • The below expression worked for me. However I need to test little more. :)

    =SUM(IIF(Sum(IIF(IsNothing(Fields!MTRC_INV_P.Value) AND (Fields!CODE.Value = 0 OR Fields!CODE.Value = 1),1,0)) >0,0,IIf(Fields!CODE.Value = 0,Fields!MTRC_INV_P.Value,0)))

    • Proposed as answer by Harsh Kumar Thursday, May 9, 2013 10:00 AM
    • Marked as answer by Rikz Monday, May 13, 2013 9:38 AM
    Thursday, May 9, 2013 7:59 AM