Prior to the introduction of Listbox Expressions in QV11, the only additional data you could put in a listbox was Frequency. The Show Frequency option has some shortcomings:
- The meaning of frequency is dependent on the data model. For a customer name in an orders table, frequency may correctly reflect the order count. If the customer name is moved to a dimension table, the frequency is “1” .
- Frequency values show only for possible rows — green and white. Excluded gray rows show nothing in the Frequency column.
- Show Frequency is not available for Key fields.
Listbox Expressions solve these problems and more. Here are are a few tips on using Listbox Expressions.
On the Expressions tab of a listbox properties, you can add one or more expression columns, as you would in a chart. The “Dimension” will be the listbox field.
While the dialog may be similar to a chart, there are a few features that are not available, notably:
- Number format — you must do any desired formatting in the expression.
- Column labels — the workaround is to include the “label” text in the Caption.
If you want your Listbox sorted by the expression value, repeat the expression in the Sort pane “Expression” property. There is no need to include the “Num()” formatting in the Sort Expression, but no harm in leaving it in either.
Now we have a nicely sorted listbox that provides context about “customer size”.
When selections are made, excluded data shows zero, probably not what we want. Fix that up by adding a Set to the expression.
What if we want to the Sales expression to reflect other selections, like Product, but still want to see all Customers? Simple, add a set modifier to ignore Customer.
Like a chart, we can add additional expressions such as order count, days since last order, or account rep name. We also have the full range of expression representations; Image, Gauge, Mini Chart etc. We can even put pictures in the listbox.
There is no visible vertical line separating columns, but the columns may be resized by dragging at the invisible boundaries.
If you get frustrated trying to make a listbox look like a chart, take a step back. Listbox expressions are meant to guide the user in making selections, not present a finished analysis.