• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

SQL Reporting zero out negative numbers

I have a SQL report that has a table in it. Column C is Colum A - Column B which sometimes produces negative numbers. Is there an expression I can use to just have column C show a zero instead of negative numbers?

Column C current expression =Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value
0
bkpierce
Asked:
bkpierce
2 Solutions
 
Patrick MatthewsCommented:
=IIF(Fields!User_Define_1.Value > Fields!Act_Labor_Units_TTD.Value, Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value, 0)
0
 
TyugCommented:
=IIF(Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value >0, Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value, 0  )
0
 
Paul_Harris_FusionCommented:
From memory, you can use IIF as follows:

Iif (Fields|User_Define_1.Value < Fields!Act_Labor_Units_TTD.Value, 0  , Fields!User_Define_1.Value - Fields!Act_Labor_Units_TTD.Value)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Patrick MatthewsCommented:
bkpierce,

I am puzzled.  I posted a syntactically and arithmetically identical suggestion in http:#a30119550, and posted that first, and yet you selected http:#a30119599 as the answer.

I would have been pleased with a split, but I do feel the current disposition is unfair.

Patrick
0
 
bkpierceAuthor Commented:
You are correct my apologies, you should have received the points as your post was first, I must have accidentally over looked it. I am not sure how to undo and assign the points to you. I'm guessing a moderator would have to be contacted?
0
 
bkpierceAuthor Commented:
I have contacted a moderator and asked that they make the change.
0
 
Patrick MatthewsCommented:
Thanks :)  I really would be pleased with a split, as chances are pretty good that Tyug and I were typing simultaneously.
0
 
planoczCommented:
Most customers want to see the neg numbers (If we are talking about money) We use brackets around our anwers in our reports to show that it is neg.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now