Report Builder - Don't display empty values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Report Builder - Don't display empty values

Author

Topic

Shane2077
Starting Member

4 Posts

Posted - 2012-10-19 : 16:27:18

Hi all
Sorry if this question has been asked before. I am a "newbie" to Reporting Services Report Builder and am having difficulties in creating an expression to exclude empty values in a single name/address text box.

I have 7 fields in my database:
Name1
Name2
Name3
Address
City
State
Postcode

An example is:
Name1 field: Professor Fred Flintstone
Name2 field: Director of Technology
Name3 field: University of New South Wales
Address field: 100 Test Road
City: Sydney
State: NSW
Postcode: 2000

But some addresses might also be:
Name1 field: Professor Barney Rubble
Name2 field: no value
Name3 field: no value
Address field: PO Box 1234
City: Sydney
State: NSW
Postcode: 2000

In Report Builder I want to the address field to not display where there is no value.

I am using this expression in the text box:

=IIF(IsNothing(First(Fields!NAME1.Value, "DataSet2")),"",First(Fields!NAME1.Value, "DataSet2") + Chr(13) + Chr(10) ) + IIF(IsNothing(First(Fields!NAME2.Value, "DataSet2")),"",First(Fields!NAME2.Value, "DataSet2") + Chr(13) + Chr(10) ) + IIF(IsNothing(First(Fields!NAME3.Value, "DataSet2")),"",First(Fields!NAME3.Value, "DataSet2") + Chr(13) + Chr(10) ) + First(Fields!ADDRESS.Value, "DataSet2") + Chr(13) + Chr(10) + First(Fields!CITY.Value, "DataSet2")+ " " +First(Fields!STATE.Value, "DataSet2") + " " + First(Fields!PCODE.Value, "DataSet2")

The formatted address where all fields have a value is:

Professor Fred Flintstone
Director of Technology
University of New South Wales
100 Test Road
Sydney NSW 2000

The fields NAME2 and NAME3 don't always have a value and where there is no value the report is displaying the address field with empty rows, e.g.

Professor Barney Rubble

PO Box 1234
Sydney NSW 2000

What I want is the address to display with the empty rows not displayed where NAME2 and NAME3 don't have a value, e.g.

Professor Barney Rubble
PO Box 1234
Sydney NSW 2000

Any assistance would be greatly appreciated.
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-19 : 23:05:43

i dont understand why you're using First() in your expression. do you know that it gives you always a single value set?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-20 : 09:01:20

It may be that the column is not really null (IsNothing checks for NULL value), instead it could be an empty string. If that is the case, change the expression to account for that also - for example, the first IIF expression to:

=IIF(IsNothing(First(Fields!NAME1.Value, "DataSet2")) OR FIRST(Fields!NAME1.Value, "DataSet2") = "",
"",First(Fields!NAME1.Value, "DataSet2")

Shane2077
Starting Member

4 Posts

Posted - 2012-10-21 : 18:23:57

Thanks so much Snitabeck! hat is what the problem was, the fields weren't null, simply empty, so I removed the IsNothing and it worked fine

=IIF(First(Fields!NAME1.Value, "DataSet2") = "","",First(Fields!NAME1.Value, "DataSet2") + Chr(13) + Chr(10))

your assistance is very appreciated

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-21 : 20:46:51

You are very welcome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources