locked
Get the negative money value RRS feed

  • Question

  •  I set the currency format in a Field.
     Some values should be negative via executing the query.


    But the displaying result is always positive.
    The strange thing is when I set the field format as numeric, the negative values will be shown correctly.


    Why?

    Thanks
    Thursday, October 30, 2008 1:14 AM

All replies

  •  can you show us the expression your using in the field, and the results based on the different ways you've tried to implement it
    Living my life at 123mph in 11.15 seconds
    Thursday, October 30, 2008 1:47 PM
  • begin
        declare @@rc int
    ...
     create table #TmpData(
           ....  AMT      money,
      .....
    )

    exec('insert #TmplData

    select ....
    AMT = CASE T.TRAN_CD
        WHEN ''21'' THEN L.AMT
        WHEN ''25'' THEN L.AMT
        WHEN ''22'' THEN (L.AMT * -1)
        WHEN ''26'' THEN (L.AMT * -1)
        END,
    select * from #TmplData
     return @@rc

    • Edited by ardmore Thursday, October 30, 2008 10:19 PM
    Thursday, October 30, 2008 10:18 PM
  • were you asking about the resultset in SQL? or the result in a report?

    Because I was referring to the .rdl or .rdlc file your sending the results to

    I think in your sql statement, your on the right track to casting it as a numeric value, since I thought a string value was implied when using case statements, but I could be wrong.
    Living my life at 123mph in 11.15 seconds
    Friday, October 31, 2008 4:09 AM

  • I mean the result in a report. 

    You know, inside the field.
    The value is assigned as "=Field!AMT.Value".

    For ex, if AMT=-$100.00 via query, it should display a negative value in the report but it is not.
    It always positive.

    However, it can be correct shown as -100.00 if I set the field format as numeric.
    • Edited by ardmore Friday, October 31, 2008 1:35 PM
    Friday, October 31, 2008 1:34 PM
  •  click on the field to display, and then bring up the properties, set the Format value to C

    this will format it as currency automatically.

    I'm guessing that when you pass the value in, it's coming in as a string.

    Make sure you run your query over in SQL Managment studio, and verify the resultset over there, just to be sure of the values your passing in.
    Living my life at 123mph in 11.15 seconds
    Friday, October 31, 2008 1:37 PM
  • I do set the Format value to C.
    And I run my query over in SQL Managment studio, the result is correct.

    But it is wrong in the report.
    Friday, October 31, 2008 3:38 PM
  • weird, I deal with negative values everyday of the week and never have a problem with it.  I don't even do anything special to make them show correctly.

    However, I know my values are already numeric in the .RDLC, I don't have to do any conversion.  

    Make sure the datatype in the .rdlc for your field is the correct type. 
    Living my life at 123mph in 11.15 seconds
    Friday, October 31, 2008 4:35 PM
  • Finally  I figure it out, the negative can be obtained but displayed as ($abc.yz).

    How can I remove the paraness and express it as minus sign?
    Monday, November 3, 2008 10:43 PM