Hiding Null value rows

Question

Post reply

Hiding Null value rows

chaudharyabhijit

Ten Centuries

Points: 1314
More actions
November 18, 2010 at 9:49 am

#241406

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!

Viewing 12 posts - 1 through 11 (of 11 total)

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

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

Exclude it fom the dataset?

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

chaudharyabhijit Ten Centuries Points: 1314 More actions · Answer 2

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 Ten Centuries Points: 1314 More actions · Answer 3

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 Old Hand Points: 344 More actions · Answer 4

You can set the Hidden property for that cell or datarow. Like.. if(Fields!SomeField.Value = "", true, false).

chaudharyabhijit Ten Centuries Points: 1314 More actions · Answer 5

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 Old Hand Points: 328 More actions · Answer 6

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 SSCrazy Points: 2800 More actions · Answer 7

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 Mr or Mrs. 500 Points: 562 More actions · Answer 8

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 Valued Member Points: 61 More actions · Answer 9

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.

Ulysses Brown Old Hand Points: 369 More actions · Answer 10

If you have fields that contain values and not numbers then the following should work to hide columns that have only NULL values for each row in the column.

Place this code as an expression in the Column Visbility object for each column that you want to evaluate

=IIF(Count(Fields!.Value) = Cint(0), True, False)

Donna Collins SSC Enthusiast Points: 199 More actions · Answer 11

I needed to include a left join in the dataset query and create a matrix. The left join was required so I can extract the full set of values to be used for the column headers. For the records that were null I took the following steps

Right click on row of the Matrix, select Edit Group

Select Filters tab

Expression: I chose the Field value

Operator: !=

Value: =nothing

This will filter out rows with a null.