Display formatted field 0.00 conditionally in Reporting Services

Question

Display formatted field 0.00 conditionally in Reporting Services

tcote

SSC Rookie

Points: 31
More actions
May 26, 2009 at 5:21 pm

#204499

I want to display a line on a Reporting Services Report based on several conditions.
ANNDEDDSC has an alpha value =
"Your annual deductible met to date is" but sometimes is blank
ADDDEDUCT has a value > 0 sometimes, sometimes 0.
I want to display a formatted zero 0.00 when ADDDEDUCT = 0
and ANNDEDDSC has an alpha value:
"Your annual deductible met to date is 0.00"
I want to display a blank when ADDDEDUCT = 0 and ANNDEDDSC = blank
" "
I tried this but it doesn't work, any suggestions?
=fields!ANNDEDDSC.Value & " " & iif(Fields!ANNDEDUCT.Value = 0," ",Fields!ANNDEDUCT.Value)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ezytime SSCommitted Points: 1846 More actions · Answer 1

This is one expression you could use for the cell value:

=IIF(Len(Fields!ANNDEDDSC.Value) = 0 And Fields!ANNDEDUCT.Value = 0, Nothing, Fields!ANNDEDDSC.Value & " " & Format(Fields!ANNDEDUCT.Value,"N2"))

tcote SSC Rookie Points: 31 More actions · Answer 2

Thanks for your reply. I think this is very close but it still displays 0.00 when ANNDEDDSC = spaces.

I tried = " " but get a runtime #Error

=IIF(Len(Fields!ANNDEDDSC.Value) = " " And Fields!ANNDEDUCT.Value = 0, Nothing, Fields!ANNDEDDSC.Value & " " & Format(Fields!ANNDEDUCT.Value,"N2"))

tcote SSC Rookie Points: 31 More actions · Answer 3

I got it to work with this:

=IIF(Fields!ANNDEDDSC.Value = " " And Fields!ANNDEDUCT.Value = 0.00, Nothing, Fields!ANNDEDDSC.Value & " " & Format(Fields!ANNDEDUCT.Value,"N2"))

Thanks for your help!

WayneS SSC Guru Points: 95460 More actions · Answer 4

And thank you for coming back and posting what worked. This way, others with a similiar problem can see what worked.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ezytime SSCommitted Points: 1846 More actions · Answer 5

Well done. You can also make it work for both a zero-length string "" and a spaced string " " by inserting the Trim function:

=IIF(Len(Trim(Fields!ANNDEDDSC.Value)) = 0 And Fields!ANNDEDUCT.Value = 0, Nothing, Fields!ANNDEDDSC.Value & " " & Format(Fields!ANNDEDUCT.Value,"N2"))