SSRS - How do you hide columns based on drill down level

I thought the built in expression Level() would show me be drill down level. I can't see to get that to work. I'd like to determine my drill down level so I can hide columns based on that.

Any ideas?

Thank you,
September 10th, 2015 11:26am

Hi KLNGroup,


For example, I have the date source named AdventureWorksDW2008 in the report. In my dataset, there are five fields which are Product, ProductCategory, ProductSubcategory, CalendarYear, and SalesAmount available. To archive your requirement, please refer to the following steps:

 1. In the Toolbox window, drag the Matrix to the Design surface. 
2. Drag the CalendarYear to the second row of the first column.
3. Drag the Product field to the first row of the second column, and then drag the SalesAmount field to the second row of the second column.
4. Right the Product in the Column Groups pane, select Add Group and click Parent Group
5. In the drop-down menu of Group by:, select ProductSubcategory. Click OK.
6. Repeat the above two steps to add ProductCategory column group.
7. Right the Product in the Column Groups pane, select Group Properties.
8. In the Group Visibility dialog box, select Hide.
9. Select the Display Can Be Toggled By This Report Item check box, and then select the ProductSubcategory field in the drop down list. Click OK.

10. Repeat step 7, 8 and 9 to add the drill drown function on the ProductSubcategory column group.

Tyr it..

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 3:01am

Hi KLN, 

According to your description, you want to hide columns based on the value which returned by Level() function, right? 

In Reporting Services, Level() function is used for a hierarchy and it can be used to provide indentation in a recursive hierarchy, such as an employee list. In your scenario, if you want to use Level() to control the visibility of the records, you need to create a Recursive Hierarchy Group firstly. How to create it, please refer to this blog: Using Recursive Hierarchy Group in SSRS 2012

For your requirement, since the Recursive Hierarchy Group is created, you can the following expression to control the row visibility. The report only display the records which level is 2. 

=IIf(Level() = 2, false, true)

The result is like below: 

If you have any other question, please feel free to ask. 

Regards,
Shrek Li

September 11th, 2015 3:14am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics