I have a SSRS report that has several columns. Some of those columns may contain NULL data for the row some may have data in this column or that column. I want to hide all rows that have NULL data in across 5 or 6 columns of the 10 columns I am selecting. If one of the columns have data in it, I want that row to show. I try the hidden property in details on the column, but if the column was NULL it made the whole roll disappear. How do I change the report to hide the row if all columns are NULL.
I tried to do this in the query, but the query is very doing a lot of convulted joins and when I put in the is NOT NULL for each column, it eliminates all rows even when it shouldn't so I am trying to handle this on the reporting side.
Please provide sample data and point out which row should show and which should not
Easier to understand that way
On the SQL side - create a new column using a CASE expression:
CASE WHEN firstColumn IS NOT NULL THEN 1
WHEN secondColumn IS NOT NULL THEN 1
WHEN thirdColumn IS NOT NULL THEN 1
WHEN fourthColumn IS NOT NULL THEN 1
WHEN fifthColumn IS NOT NULL THEN 1
WHEN sixthColumn IS NOT NULL THEN 1
ELSE 0
END
Now you can use that new column to hide the row.
If you don't want to do that in SQL - add a computed column to the dataset and do the same type of check in that computed column. Then use the computed column to determine when to show the row.
I'd do it in the query. No reason to futz about trying to remove the row on the reporting side.
WHERE ...other_conditions... AND NOT (column_1 IS NULL AND column_2 IS NULL AND column_3 IS NULL AND column_4 IS NULL AND column_5 IS NULL AND column_6 IS NULL)