locked
summing across columns RRS feed

  • Question

  • Hi,

    I have two calculated field columns, each with a numeric value.  I've created a third column to sum across the two created columns.  However, this is not working.  Seems very simple but for some reason it's not summing the two values across.  

    So, an example of my three calculated columns:

    col1  |  col2 | total

    2           3        5

    Wednesday, July 20, 2011 6:56 PM

Answers

  • Hi vba123,

    try below expression under col3(Total):-

    = CInt ( IIf ( Lookup ( Fields!key1.value, Fields!key2.value, Fields!colx.value, "First" ) = 0.569, 3, 0 ) )
    +
    CInt ( IIf ( Lookup ( Fields!key1.value, Fields!key2.value, Fields!colx.value, "Second" ) = 2.36, 5, 0 ) )

     

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    • Marked as answer by vba123 Thursday, July 21, 2011 8:25 PM
    Thursday, July 21, 2011 5:23 PM

All replies

  • Hello vba123,

    try below expression under thrird column detailed row,

    = CInt ( Fields!col1.Value ) + CInt ( Fields!col2.Value )


    Thanks

    Kumar


    KG, MCTS
    Wednesday, July 20, 2011 8:03 PM
  • I've actually already tried that.  Doesn't work.   I've also tried CDbl() as well. 
    Wednesday, July 20, 2011 8:06 PM
  • Hi,

    What is the DataType of col1 & col2 fields?

    Thanks

    Kumar


    KG, MCTS
    Wednesday, July 20, 2011 8:10 PM
  • Just integers. 
    Wednesday, July 20, 2011 8:14 PM
  • Also, there's logic behind Col1 and Col2.  Based on an IIF statement it displays the number 3 or 5 or other numbers.  However, when I right click on Col1 --> Field Properties, under the expression it only shows =Fields!Col1.Value.  But on the actual table layout of the report I have the logic written and it works. 
    Wednesday, July 20, 2011 8:24 PM
  • Hello,

    Please check out my example it works perfectly, "Summation.rdl"

    https://skydrive.live.com/#!/?cid=15e0e13bd0545504&sc=documents&uc=1&id=15E0E13BD0545504%21137

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Wednesday, July 20, 2011 8:31 PM
  • Hmm..getting a service error when I use the link above. 
    Thursday, July 21, 2011 12:24 PM
  • Ok, got the file.  However, I'm still running into the issue that my  =CInt ( Fields!col1.Value ) + CInt ( Fields!col2.Value ), is not displaying any values.  I have the query fields set up exactly like your example.  Weird...
    Thursday, July 21, 2011 2:36 PM
  • Hi vba123,

    Is it possible if you can create new example from scratch by creating new rdl file? I'm still thinking you might be doing some minor mistake otherwise I'm unable to predict anything at present.

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, July 21, 2011 2:44 PM
  • So, my col1 and col2 columns are displaying numeric values (based on built in IIF statements).  The problem that I'm running into is the last Total field that I have created to do the following:  =CInt ( Fields!col1.Value ) + CInt ( Fields!col2.Value ),  is just not displaying data.  Everything is null for that column. 
    Thursday, July 21, 2011 3:12 PM
  • Are you having IIF statement under col1 & col2 fields too, aren't you displaying straight without any expression?


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, July 21, 2011 3:23 PM
  • No, I'm not displaying straight expressions.  There is IIF statements in col1 and in col2.   And those are coming over correctly, it's when I go to the last "total" column to add them up, when it displays no values. 
    Thursday, July 21, 2011 4:44 PM
  • Hi vba123,

    I'm confused you have two different statments in your last reply:-

    "No, I'm not displaying straight expressions."

    AND

    "There is IIF statements in col1 and in col2."

     

    If you have IIf statements under col1 & col2 please paste here those expressions.

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, July 21, 2011 5:03 PM
  • Here are my iif statements in col1 and col2, respectively.  

     

    =iif(Lookup(Fields!key1.value,Fields!key2.value,Fields!colx.value, "First") = 0.569, 3,0)

     

    =iif(Lookup(Fields!key1.value,Fields!key2.value,Fields!colx.value, "Second") = 2.36, 5,0)

     

    And then the last field is trying to total the to columns across. 

    Thursday, July 21, 2011 5:09 PM
  • Hi vba123,

    try below expression under col3(Total):-

    = CInt ( IIf ( Lookup ( Fields!key1.value, Fields!key2.value, Fields!colx.value, "First" ) = 0.569, 3, 0 ) )
    +
    CInt ( IIf ( Lookup ( Fields!key1.value, Fields!key2.value, Fields!colx.value, "Second" ) = 2.36, 5, 0 ) )

     

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    • Marked as answer by vba123 Thursday, July 21, 2011 8:25 PM
    Thursday, July 21, 2011 5:23 PM
  • Well that worked.  By doing it this way I no longer need those columns, since I'm throwing it all in the "Total" column.  Thanks.
    • Marked as answer by vba123 Thursday, July 21, 2011 5:44 PM
    • Unmarked as answer by vba123 Thursday, July 21, 2011 7:41 PM
    Thursday, July 21, 2011 5:44 PM
  • Hi,

    Please marked the correct reply as "Answered"

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, July 21, 2011 5:54 PM
  • Hi vba123,

    try below expression under col3(Total):-

    = CInt ( IIf ( Lookup ( Fields!key1.value, Fields!key2.value, Fields!colx.value, "First" ) = 0.569, 3, 0 ) )
    
    +
    
    CInt ( IIf ( Lookup ( Fields!key1.value, Fields!key2.value, Fields!colx.value, "Second" ) = 2.36, 5, 0 ) )

     

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Actually, sorry, your suggestion doesn't work the way it should.  Because we are putting everything in one experssion, it never sums up correctly.  For example, when it sees the first value of .569 it gives it a 3, then it goes to the second line and it sees 2.36, but doesn't see .569, so it assigns a 0.  And eventually never sums up correctly. 
    Thursday, July 21, 2011 7:44 PM
  • Hello vba123,

    Can you please specific your req. correctly with a well defined example? It's giving me hard time to understand your example. :(

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, July 21, 2011 8:15 PM
  • Ok.  I think I got it to work.  I had to add a Group variable (group by key1) and the logic specified above. 
    Thursday, July 21, 2011 8:25 PM