Configure a List Billboard Parameter

List parameters create selection criteria for multiple or single values from a list. The item query returns the list of values from which the selection is made. The setting parameters are used for creating criteria for parameters. For example, the user could select a location and then select a stockpile from that location.

select Name, Capacity, CountryOrigin 'Country of Origin'
from Vessel
where VesselID in ({Vessels})

In the example above, for the multi-select {Vessels} list parameter:

• The Key Field is VesselID.
• The Item Query is:
  Copy

  select Name, VesselID
  from Vessel;

select
    distinct org.Name 'Organisation',
    ct.Name 'Contract',
    ct.ContractID,
    do.Name 'Despatch Order',
    do.DespatchOrderID,
    d.Name 'Despatch Name'
from DespatchOrder do
    join Contract ct on do.ContractID = ct.ContractID
    left join DespatchDespOrders dods on do.DespatchOrderID = dods.DespatchOrderID
    left join Despatch d on dods.DespatchID = d.DespatchID
    left join Organisation org on do.Organisation = org.OrganisationID
where do.despatchorderid in ({DOList})

In the example above, for the multi-select {DOList} list parameter:

• The Setting Parameters includes the {ConList} list parameter.
• The Key Field is despatchorderid.
• The Item Query is:
  Copy

  select do.name 'Despatch Order', 
      do.despatchorderid
  from DespatchOrder do
  where do.ContractID in ({ConList})
  order by do.name

For the multi-select {ConList} list parameter:

• The Key Field is contractid.
• The Item Query is:
  Copy

  select distinct ct.name 'Contract Name',
      ct.contractid
  from Contract ct
  order by ct.name

Security Note: You need the Allow billboard maintenance security right in the Billboards domain security rights group for this activity.

Activity Steps

1. Open the Billboard (configuration).
2. Select the Parameters tab.
3. Right-click in the Parameters field group and select New » List Parameter from the menu.
4. Complete the following fields.
   • Name—Case-sensitive name of the parameter that is used in the query.
   • Caption—Caption used when prompting for the parameter value.
   • Required—Whether the parameter must be defined before data can be retrieved.
   • Force Refresh—Whether the parameter is refreshed if its value is dynamic.
   • Default Value—Default value of the parameter. For single-select lists, this is a single value. For multi-select lists, this can be a list of values, separated by a semicolon. The default value(s) supplied correspond to values in the Key Field column that are returned by the list’s SQL query. The maximum length of the default value for list parameters is 4000 characters.
   • Database Connection
   • Multi Select—If checked, the list table includes a Selected column with checkboxes for selecting multiple list items. If unchecked, the Selected column does not display.
5. If Setting Parameters are referenced in the Item Query:
   1. To add a setting parameter, right-click in Setting Parameters and select Add Billboard Parameter » [selected parameter] from the menu.
   2. To use an existing setting parameter in the item query, right-click in Setting Parameters and select Add Billboard Parameter » [selected parameter] from the menu.

      Note: The query string itself does not have to include quotes around the parameter (that is, {Parameter} and not '{Parameter}') because the quotes are automatically added when the actual value is substituted.

6. Enter the following details for the query.
   • Item Query
   • Key Field—Name of the key field to be returned as the list item. The key field should be included in the select clause of the item query.
   • Show Key Field—Whether the key field displays when selecting a value or values for the list parameter. If checked, the key field is included as a column in the list table. If unchecked, the key field does not display.
7. Click Save.