class_="querytree"

Using <widget class_="querytree"> provides a display of hierarchical data with nested parent/child nodes. (Available as of prod-9)

Syntax

<dynamic [SEL_VAR]="" [EXP_VAR]=""
 [LIST_VAR]="">
    <widget class_="querytree" value_="@[SEL_VAR]"
     expvalue_="@[EXP_VAR]" listvalue_="[LIST_VAR]"
     trunc_="0|1" uniform_="0|1" 
     textmode_="plain|html" >
        <layer name="[LAYER_NAME_1]">
            [1010data_QUERY]
        </layer>
        <layer name="[LAYER_NAME_2]">
            [1010data_QUERY]
        </layer>
        ...
        <layer name="[LAYER_NAME_n]">
            [1010data_QUERY]
        </layer>
    </widget>
</dynamic>

The contents of a querytree widget consist of a number of <layer> elements, one for each level in the tree. Each layer must be given a name and has a [1010data_QUERY] associated with it. The query determines the nodes in that layer. Each node appears as an item in the tree. If a node is not in the final layer, then it is also expandable. Expansion of a particular node will reveal the next layer, which is populated by the query in the subsequent <layer> element.

If the [1010data_QUERY] associated with a <layer> results in a one-column table, the values in that column will be used as the labels displayed for the items in the tree as well as the values assigned when those values are selected. If the query results in a two-column table, the values of the selected items will come from the first column, and the labels for those items will come from the second column.

If the query associated with a <layer> yields three columns, the third column is construed as the number of children for each item. Including this information makes it unnecessary to issue a query to the next layer to determine whether a node has children or not, greatly improving performance when expanding nodes with a large number of children higher up in the hierarchy. (Available as of version 11.05)

Attributes

mode_
Specifies whether the querytree widget allows selection of a single value or multiple values.

Valid values are:

single
Allows you to select a single item in the querytree widget by clicking on the label of the item.
multi
Allows you to select multiple items in the querytree widget by clicking the checkbox next to the label of each item you want to select.

The default is multi.

value_
Information about selected items is stored in the dynamic variable associated with the value_ attribute.

When mode_ is set to single, the selected item is stored in the dynamic variable associated with the value_ attribute. The variable must be declared in the opening <dynamic> tag of the QuickApp (e.g., <dynamic var_name="">).

When mode_ is set to multi, information about the selected items is stored as a package in the dynamic variable specified by the value_ attribute. The dynamic variable must be initialized as a package in the opening <dynamic> tag (e.g., <dynamic this_var="{pkg()}">). The keys in the package correspond to column names, and the values correspond to the value of the elements checked in the widget.

expvalue_
Accepts a list-value dynamic variable to which the widget can assign the most recently expanded path. Each node in the path is saved as a single list element of the variable. The variable must be declared as a list-value in the opening <dynamic> tag of the QuickApp in which the widget exists (e.g., <dynamic list_var="{lst()}">).

This dynamic variable allows users to reference values of preceding layers.

pathvalue_
Provides a list of lists of packages representing the exact path to each checked value when mode_="multi".
listvalue_
Accepts a list-value dynamic variable to which the widget can assign a list of lists of pairs representing a disjunction of conjunctions of disjunctive selections.

Together with the conjsel(L) function and the expanded selection form for <sel> introduced in version 11.05, this allows efficient application of a querytree-generated selection. For more information, see conjsel(L) and <sel>.

See the example below titled Example: Using conjsel(L) with querytree widget and alternate <sel> syntax.

(Available as of version 11.06)

trunc_
Specifies whether or not subnodes of fully-selected nodes are included in the widget's value (i.e., stored in the variable(s) passed to value_ and/or pathvalue_ ). Accepts a 1 or 0.

When trunc_="1", subnodes of fully-selected nodes are not included in the widget's value.

For example, in a retail product drilldown, when trunc_="0", if a whole division is checked and the division's node is expanded, the departments underneath that division are also checked. However, when trunc_="1", the selections on individual departments are not included in the widget's value. (If, on the other hand, one or more of the departments are deselected, then all of the remaining ones are included because the entire product division is no longer selected.)

The default is 0.

(Available as of version 11.05)

uniform_
Specifies whether or not all branches in the tree are of uniform depth and there are no empty higher-level categories. Accepts a 1 or 0.

When uniform_="1", it is assumed that all leaf nodes are at the same, final level in the hierarchy. This obviates checking whether a node has children or not (either by issuing a query or by using the third column in the layer query, if present), since that is determined solely by whether or not the node is generated by the last <layer> query.

The default is 0.

(Available as of version 11.05)

textmode_
Specifies whether the entries in the query tree are construed as plain text or as HTML.

Valid values are:

plain
Entries in the query tree are construed as plain text.
html
Entries in the query tree are construed as HTML.

The default is plain.

(Available as of version 11.06)

maxrows_
Accepts an integer value that specifies the maximum number of rows in which the query associated with the widget can result.

If the query associated with the widget results in a number of rows that exceeds the value specified to maxrows_, then the widget produces an error.

The default value is 1000.

Example

The following example creates a multi-select querytree widget that consists of three layers. The first layer is made up of the department descriptions in pub.demo.retail.prod. The second layer consists of the class descriptions for the department selected from the first layer, which is accessed via the dynamic variable associated with the expvalue_ attribute. In this example, that variable is lex, which is a list-value, and the item selected in the first layer is accessed by referencing the first item in the list-value ({@lex[1]}). The third layer consists of the item descriptions associated with both the department selected from the first layer and the class selected from the second layer.

<dynamic lex="">
  <widget class_="querytree" expvalue_="@lex" 
   mode_="multi" trunc_="1" uniform_="1">
    <layer name="one">
      <base table="pub.demo.retail.item"/>
      <link table2="pub.demo.retail.prod" col="sku" col2="sku"/>
      <tabu breaks="dept">
        <tcol source="deptdesc" fun="first" name="deptdesc"/>
      </tabu>
      <colord cols="dept,deptdesc"/>
      <sort col="deptdesc" dir="up"/>
    </layer>
    <layer name="two">
      <base table="pub.demo.retail.item"/>
      <link table2="pub.demo.retail.prod" col="sku" col2="sku"/>
      <sel value="dept={@lex[1]}"/>
      <tabu breaks="class">
        <tcol source="classdesc" fun="first" name="classdesc"/>
      </tabu>
      <sort col="classdesc" dir="up"/>
      <colord cols="class,classdesc"/>
    </layer>
    <layer name="three">
      <base table="pub.demo.retail.item"/>
      <link table2="pub.demo.retail.prod" col="sku" col2="sku"/>
      <sel value="dept={@lex[1]}"/>
      <sel value="class={@lex[2]}"/>
      <tabu breaks="sku">
        <tcol source="itemdesc" fun="first" name="itemdesc"/>
      </tabu>
      <sort col="itemdesc" dir="up"/>
      <colord cols="sku,itemdesc"/>
    </layer>
  </widget>
</dynamic>

Example: Using conjsel(L) with querytree widget and alternate <sel> syntax

The following example demonstrates how conjsel(L) can be used to create a selection expression based on the value of the variable associated with the listvalue_ attribute of the querytree widget. In this example, the dynamic variables sellist and storesel are list-value variables associated with the querytree widgets for selecting the Products and Stores, respectively. The gauge widget passes the values of these variables to the totsales block, which uses conjsel(L) in combination with the alternate syntax for <sel> to select only the selected items in the two querytree widgets.

<base table="default.lonely"/>
<defblock name="totsales" date="" todate="" 
 storesel="{lst()}" sellist="{lst()}">
  <meta>optlinksel
  </meta>
  <sel value="between(trans_date;{@date};{@todate})"/>
  <link table2="pub.doc.retail.store" col="store" col2="store" type="select">
    <sel>
      <foreach alt="{@storesel}">
        <or value="{conjsel(@alt)}"/>
      </foreach>
    </sel>
  </link>
  <sel>
    <foreach alt="{@sellist}">
      <or value="{conjsel(@alt)}"/>
    </foreach>
  </sel>
  <tabu>
    <tcol source="xsales" fun="sum" name="totsales"/>
  </tabu>
</defblock>
<dynamic lex="" slex="" sellist="{lst()}" storesel="{lst()}" 
 date="20140115" todate="20150115" store="">
  <layout type_="splitter" width_="1200" height_="700">
    <layout type_="splitter" width_="700">
      <layout label_="Products" height_="75%">
        <widget class_="querytree" listvalue_="@sellist" expvalue_="@lex" 
         mode_="multi" trunc_="1" uniform_="1" 
         maxrows_="10000" textmode_="html">
          <base table="pub.doc.retail.product"/>
          <layer name="dept">
            <tabu breaks="dept">
              <tcol source="deptdesc" fun="first" name="desc"/>
              <tcol fun="cnt" name="count"/>
            </tabu>
            <willbe name="label" 
             value="splice('<B>' desc '</B> (<I>' count ' items</I>)';'')"/>
            <colord cols="dept,label,count"/>
            <sort col="label" dir="up"/>
          </layer>
          <layer name="group">
            <sel value="dept={@lex[1]}"/>
            <tabu breaks="group">
              <tcol source="groupdesc" fun="first" name="desc"/>
              <tcol fun="cnt" name="count"/>
            </tabu>
            <willbe name="label" 
             value="splice('<B>' desc '</B> (<I>' count ' items</I>)';'')"/>
            <colord cols="group,label,count"/>
            <sort col="label" dir="up"/>
          </layer>
          <layer name="sku">
            <sel value="dept={@lex[1]}"/>
            <sel value="group={@lex[2]}"/>
            <tabu breaks="sku">
              <tcol source="description" fun="first" name="desc"/>
              <tcol fun="cnt" name="count"/>
            </tabu>
            <sort col="desc" dir="up"/>
            <willbe name="label" value="splice(sku ': <B>' desc '</B>';'')"/>
            <colord cols="sku,label,count"/>
          </layer>
        </widget>
      </layout>
      <layout label_="Stores">
        <widget class_="querytree" listvalue_="@storesel" expvalue_="@slex" 
         mode_="multi" trunc_="1" uniform_="1" 
         maxrows_="10000" textmode_="html">
          <base table="pub.doc.retail.store"/>
          <layer name="division">
            <tabu breaks="division">
              <tcol source="divisiondesc" fun="first" name="desc"/>
              <tcol fun="cnt" name="count"/>
            </tabu>
            <willbe name="label" 
             value="splice('<B>Division ' division '</B> - ' desc ' (<I>' count ' stores</I>)';'')"/>
            <colord cols="division,label,count"/>
            <sort col="division" dir="up"/>
          </layer>
          <layer name="sdiv">
            <sel value="division={@slex[1]}"/>
            <tabu breaks="subdivision">
              <tcol source="subdivisiondesc" fun="first" name="sdivdesc"/>
              <tcol fun="cnt" name="count"/>
            </tabu>
            <willbe name="label" 
             value="splice('<B>Subdivision ' subdivision '</B> - ' sdivdesc ' (<I>' count ' stores</I>)';'')"/>
            <colord cols="subdivision,label,count"/>
            <sort col="subdivision" dir="up"/>
          </layer>
          <layer name="store">
            <sel value="division={@slex[1]}"/>
            <sel value="subdivision={@slex[2]}"/>
            <willbe name="citystate" value="splice(city state;', ')"/>
            <tabu breaks="store">
              <tcol source="citystate" fun="first" name="loc"/>
              <tcol fun="cnt" name="count"/>
            </tabu>
            <willbe name="label" 
             value="splice('<B>Store ' store'</B> - ' loc;'')"/>
            <colord cols="store,label,count"/>
            <sort col="store" dir="up"/>
          </layer>
        </widget>
      </layout>
    </layout>
    <layout label_="SALES % CHANGE YEAR OVER YEAR" arrange_="v">
      <layout arrange_="h">
        <widget class_="date" label_="Dates from:" 
         value_="@date" tolabel_="to:" tovalue_="@todate" inputwidth_="150"/>
      </layout>
      <widget class_="gauge" type_="radial" base_="default.lonely" 
       min_="-100" max_="100" visible_="{@date<>''&@todate<>''}" 
       width_="460" height_="400">
        <layer name="reading">
          <if test="{@date<>''&@todate<>''}">
            <base table="pub.doc.retail.salesdetail"/>
            <call block="totsales" 
             date="{@date}" todate="{@todate}" 
             sellist="{@sellist}" storesel="{@storesel}"/>
            <link table2="pub.doc.retail.salesdetail" suffix="_ly">
              <call block="totsales" 
               date="{shift(int(@date);-365)}" 
               todate="{shift(int(@todate);-365)}" 
               sellist="{@sellist}" storesel="{@storesel}"/>
            </link>
            <willbe name="yoychg" 
             value="100*((totsales-totsales_ly)/totsales_ly)"/>
            <colord cols="yoychg"/>
          </if>
        </layer>
        <layer name="ranges">
          <table>-100,5,red; -5,5,yellow; 5,100,green;
          </table>
        </layer>
      </widget>
    </layout>
  </layout>
</dynamic>

Example

The following example creates a single-selection querytree widget that displays the hierarchical relationship between supervisors and their employees. It uses the <table> op to provide the data as in inline table within the query associated with the <widget>. The widget defines two layers, using a <layout> element for each. The first layer, layer_0, consists of all the unique values from the supervisor column. The second layer, layer_1, selects those rows where the value in the supervisor column is equal to the first value in the list associated with the dynamic variable exp_path and uses <colord> to get the list of the employees associated with that supervisor.

<dynamic selected_item="" exp_path="">
  <widget class_="querytree" mode_="single" disabled_="0" 
   value_="@selected_item" expvalue_="@exp_path">
    <table title="Org Chart" cols="supervisor,employee" 
     labels="Supervisor,Employee">
     Greta,Arthur;Greta,Al;Greta,Jane;
     Arthur,Otto;Arthur,Dana;Arthur,Chris;
     Otto,John;Otto,Sally;Otto,Mike;Otto,Stan;
     Dana,Alan;Dana,Emily;Dana,Simon;
     Simon,Patty;Simon,Kirk;
     Chris,Jack;Chris,Manny;
    </table>
    <layer name="layer_0">
      <sel value="g_first1(supervisor;;)"/>
      <colord cols="supervisor"/>
    </layer>
    <layer name="layer_1">
      <sel value="supervisor='{@exp_path.1}'"/>
      <colord cols="employee"/>
    </layer>
  </widget>
  <widget class_="text" text_="Selected employee: {@selected_item}" 
   background_="lightgray" border_="1" width_="300" relpos_="200,10"/>
</dynamic>

Example

<dynamic selected_item="" exp_path="" num_layers="" top_node="Greta">
  <do on_="init" value1_="@num_layers" row1_="1" col1_="1">
    <table title="Org Chart" cols="supervisor,employee" 
     labels="Supervisor,Employee">
     Greta,Arthur;Greta,Al;Greta,Jane;
     Arthur,Otto;Arthur,Dana;Arthur,Chris;
     Otto,John;Otto,Sally;Otto,Mike;Otto,Stan;
     Dana,Alan;Dana,Emily;Dana,Simon;
     Simon,Patty;Simon,Kirk;
     Chris,Jack;Chris,Manny;
    </table>
    <willbe name="top" value="supervisor='{@top_node}'"/>
    <willbe name="table_depth" 
     value="g_chase(;;supervisor;employee;top;'depth')"/>
    <colord cols="table_depth"/>
    <sort col="table_depth" dir="down"/>
  </do>
  <widget class_="querytree" mode_="single" 
   value_="@selected_item" expvalue_="@exp_path">
    <table title="Org Chart" cols="supervisor,employee" 
     labels="Supervisor,Employee">
     Greta,Arthur;Greta,Al;Greta,Jane;
     Arthur,Otto;Arthur,Dana;Arthur,Chris;
     Otto,John;Otto,Sally;Otto,Mike;Otto,Stan;
     Dana,Alan;Dana,Emily;Dana,Simon;
     Simon,Patty;Simon,Kirk;
     Chris,Jack;Chris,Manny;
    </table>
    <layer name="layer_0">
      <sel value="supervisor='{@top_node}'"/>
      <sel value="g_first1(supervisor;;)"/>
      <colord cols="supervisor"/>
    </layer>
    <for i="1" to="{@num_layers+1}">
      <layer name="layer_{@i}">
        <sel value="supervisor='{@exp_path.{@i}}'"/>
        <colord cols="employee"/>
      </layer>
    </for>
  </widget>
  <widget class_="text" text_="Selected employee: {@selected_item}" 
   background_="lightgray" border_="1" width_="300" relpos_="200,10"/>
</dynamic>
Note: Because this example uses an inline <table>, it is included in the query associated with the <do> clause as well as the query associated with the <widget> element for the querytree. Typically, you would reference the table in both the <do> clause and the <widget> element using the base_ attribute to specify the path to the 1010data table.