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,
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..
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