Subsets : Picklists

Picklist filter helps you choose and order specific elements and is especially helpful in a combo box. You can manually add elements from the element picker on the filter, or refer to spreadsheet or variable values for the elements:


The above selection will deliver a combo box with 3 elements: 2008, 2009, 2010. I could also have achieved the same by using a formula in the combo box properties:


This is one usage of Picklists. Another use is to position specific elements in the combo box. Check out the example here. You can see that on the first worksheet, we have a grid of 4 cells where you can enter in valid elements. The drop list will be filtered based on the pick list behavior that you select. The picklist behavior allows you to insert the elements at the front, back, merge (their normal position in the dimension) or subtract (show only those elements you have specified).

This enables you do things like selecting a default element that you want users to see when they open the report:


A more common approach is to use  picklist to insert a default element into a combo box. For example, you may want the current month to always show up as the first element in the list. See the second worksheet in the example you downloaded. Here, we calculate the current month (using a now() function wrapped in a text() function) and pass this value into the picklist subset that dives the combo box. The result is that the user always sees the current month as the first element in the drop list when they open the report.


There one other trick required to get this to work. If you want the report to default to this value, you need to pass it into a variable that you can set when the report is opened. This requires a small macro on the __open function of the report:

function __open() {
$value = ActiveSheet()->Range('D10')->value;
define_variable('Month', $value);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s