How to hide null values from the "Drill-Down" Report of SSRS?

one of my colleague asked me this, how to hide null values in the "Drill Down" report of SSRS?

For doing this scenario, i have created the table with below script.


CREATE TABLE Test_Report ( Category Varchar(100), SubCategory Varchar(100) Null, Product Varchar(100) Null, OrderQty int Null, OrderYear int Null)
INSERT INTO Test_Report VALUES
 ( 'Bikes',NULL,NULL,NULL,Null)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)           
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
and output for the table looks like below

I given intentionally Null values  for "SubCategory", "Product", "OrderQty" and "OrderYear" , in order to show drill down report.

Now we have to design  the Report,
Step1: take one report and create a datasource with the above created table. Like Below.

Here i am showing part of the data for understanding purpose.

Step 2: Drag "Table" from the "toolbox" to designer tab of the report and create 3 groups with "Category", "SubCategory", "Product" like below

Step3: Now , if you see the O/P, it looks like below. If you see closely, it displaying "Bikes" also which has "NULL" data.

Step4:  Now the important part comes, we want to hide the data, if it has "NULL" values ( means, we don't want to display "Bikes"  in drill down". For this we need to select entire row of groups and select "rowvisibility" like below.

Step 5:  After Selecting Row visibilty, Select "Show or Hide based on expression " and Click on "Fx' button and write below query
=IIF(ISNOTHING(Fields!SubCategory.Value),True,False)
Like below

Step6: Now , if you see the preview, "Bikes" Data is hidden.

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.