Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hiding Null value rows


Hiding Null value rows

Author
Message
chaudharyabhijit
chaudharyabhijit
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 252
Hello All,

I have 5 rows in a directory and when i output them i see a peculiar problem. Not all the rows always have values, some are null and they show as blank spaces.

Example:
DIRECTORY:

NAME1
ADD1
CITY1
STATE1
ZIP1

NAME2

CITY2
STATE2
ZIP2

NAME3
ADD3

STATE3
ZIP3

In the above example for the second listing of Name 2, Address 2 is missing and thus shows as a blank space. Is there any property is SSRS where i can specify if the field value is null then hide the row?

Thanks!
nigelrivett
nigelrivett
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 211
Exclude it fom the dataset?


Cursors never.
DTS - only when needed and never to control.

chaudharyabhijit
chaudharyabhijit
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 252
Hello Nigel,

Thanks for you reply, but what do you mean by exclude it from the data set. I was hoping there would be a visibility option where i can may be write an expression or may be there is setting which i can enable so if the value is null then it does not show in the output.

Thanks!
chaudharyabhijit
chaudharyabhijit
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 252
To reiterate my example:
NAME1
ADD1
CITY1
STATE1
ZIP1

NAME2

CITY2
STATE2
ZIP2

NAME3
ADD3

STATE3
ZIP3


For this given example i want the rows in the output to show like this:
NAME1
ADD1
CITY1
STATE1
ZIP1

NAME2
CITY2
STATE2
ZIP2

NAME3
ADD3
STATE3
ZIP3

Thanks!
SSRSDev
SSRSDev
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 32
You can set the Hidden property for that cell or datarow. Like.. if(Fields!SomeField.Value = "", true, false).
chaudharyabhijit
chaudharyabhijit
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 252
Hello SSRS Dev,

Thank you for your suggestion but the iif statement does not work in the Visibility expression. It still shows that empty cell.

Any other suggestions on how i can hide the empty cell??
anisha.nair03
anisha.nair03
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 47
Hi,

I have the same issue. I am able to put an IIF condition and hide the data for value = 0. However, instead of data, now report is displaying a blank row.

Is there any way i can turn off/hide that blank row as well??

Thanks in advance.
Anisha Nair
sdvoranchik
sdvoranchik
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 298
I just tested this as well and it worked fine for me. In SSRS 2005, first make sure you select the entire row, not just a single cell or textbox. In the properties pane expand Visibility and in the Hidden property create an expression. This is the expression I used utilizing AdventureWorks:

=IIF(IsNothing(Fields!AddressLine2.Value),True,False)

My entire address line 2 row was hidden and the rest of the text was collapsed togther.



rexyrexin
rexyrexin
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 61
I have done it in my report. But it was on columns.

iif(Fields!abc.IsMissing,True,False)

If the field doesn't has data then the corresponding column was hided. leaving no traces. You have to try it on Visibility property of the column. You can try it.
myepifan
myepifan
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
sdvoranchik (6/1/2011)
I just tested this as well and it worked fine for me. In SSRS 2005, first make sure you select the entire row, not just a single cell or textbox. In the properties pane expand Visibility and in the Hidden property create an expression. This is the expression I used utilizing AdventureWorks:

=IIF(IsNothing(Fields!AddressLine2.Value),True,False)

My entire address line 2 row was hidden and the rest of the text was collapsed togther.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search