As promised - here is the code for generating the asymmetric drill down with the membership count of the next layer:
Example - College to Department, clicking a college expands the departments for just that member. Rolled up displays the number of departments.
1. Create a Set from College Description, we'll call it Selected Colleges
2. Create a new calculated field, we'll call it College Drilldown
- My thinking was first that I wanted to get the discount count of the Department Descriptions. To do so, I needed to aggregate along College Description. Since we need the aggregated count of departments, we'll also need to aggregate the result of the Set expression. Since the Set expression is calculated on College Description, any aggregate grouped by College Description should evaluate nicely using the call to ATTR().
This calculation expresses the idea. If the College Description isn't in the set (after setting up the action this just means the user hasn't click on it or removed all values by clicking again), then return '+', concatenate the count distinct the departments, and return the friendly string. Otherwise, just return '-' to mimic the standard collapse notation.
IF NOT ATTR([Selected Colleges]) THEN '+' + STR(COUNTD([Department Description])) + ' Depts...' ELSE '-' END
3. Create a new calculated field for the conditional department description, since we want an empty string unless the College Description is in the set (shown as expanded), we'll call it College Drill Title (that's not a good name, but it's what I called it during the demo, so for consistency, I'll stick with it)
IF [Selected Colleges] THEN [Department Description] ELSE '' END
4. Build the view. Use whatever measure suits you for columns, I used distinct count of course reference numbers in the demo. Add to the rows in order: Selected Colleges (remove header, just for sorting selection to top), College Description, College Drilldown, College Drill Title.
5. Add the Set Action to the worksheet, use Selected Colleges as the target set, removing all values when the selection is cleared.
And there you have it! Asymmetric drill down with a little fancy text and count! Hope you enjoy it!
