Clickable sorting headers can allow report users to reorder results on demand.
Using clickable sorting requires that you have a controls section somewhere on your report.
{DATATYPES.CONTROLS_START}
...
{DATATYPES.CONTROLS_END}
There are three types of sort controls:
- SORT_TOGGLE - Sorts ascending on the first click and descending when clicked a second time
- SORT_ASCENDING - Sorts ascending on every click
- SORT_DESCENDING - Sorts descending on every click
Each sort has 3 parts:
- Opening sort command, including the value to sort by when clicked
- Content of the sort link (typically the column header text)
- Closing sort command
Datapoint Sort
Datapoints can be sorted by ID, property value, and group name, ID, or order_num.
{|RESULT|}<table class='data'>
{|LOOP|RESULT.DATAPOINTS}
{|SHOWIF|LOOP.ISFIRST}
<thead>
<tr>
<th>{|SORT_TOGGLE|ID}ID{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|TRENDING_TIMESTAMP}Date{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|MONTH_OF_YEAR.ORDER_NUM}Month{/|SORT_TOGGLE|}</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{DATAPOINTS.ID}</th>
<td>{DATAPOINTS.TRENDING_TIMESTAMP}</td>
<td>{DATAPOINTS.MONTH_OF_YEAR.NAME}</td>
</tr>
{|VANISHIF|LOOP.HASNEXT}
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
{/|RESULT|}
Dynamic property sorting can be added with the use of EVALTAG.
{|RESULT|}
{|LOOP|RESULT.DATAPOINTS(LIMIT=50)}
{|SHOWIF|LOOP.ISFIRST}
<table>
<thead>
<tr>
<th>{|SORT_TOGGLE|DATAPOINT_ID}ID{/|SORT_TOGGLE|}</th>
{|LOOP|DATAPOINTS.GROUPS(LIMIT=5,SORT=NAME)}
<th>
#this dynamically builds the sort control for each group
{|SORT_TOGGLE||EVALTAG(GROUPS.GROUP_ID)|.NAME}
{GROUPS.GROUP_NAME}
{/|SORT_TOGGLE|}
</th>
{/|LOOP|}
{|LOOP|DATAPOINTS.PROPERTIES(LIMIT=5,SORT=NAME)}
<th>
#this dynamically builds the sort control for each property name
{|SORT_TOGGLE||EVALTAG(PROPERTIES.PROPERTY_ID)|}
{PROPERTIES.NAME}
{/|SORT_TOGGLE|}
</th>
{/|LOOP|}
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{DATAPOINTS.DATAPOINT_ID}</th>
{|LOOP|DATAPOINTS.GROUPS(LIMIT=5,SORT=NAME)}
<td>{GROUPS.NAME}</td>
{/|LOOP|}
{|LOOP|DATAPOINTS.PROPERTIES(LIMIT=5,SORT=NAME)}
<td>{DATAPOINTS.PROPERTIES.VALUE}</td>
{/|LOOP|}
</tr>
{|VANISHIF|LOOP.HASNEXT}
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
{/|RESULT|}
Result Sort
Simple data tables can be sorted by group member attributes as well as aggregate values.
{|RESULT|}<div>
<h2>Level 3</h2>
<table>
{|LOOP|RESULT.CLIENT_10_LEVEL_3(SORT=COUNT(CLIENT_10_LEVEL_3,CLIENT_10_LEVEL_3))}
{|SHOWIF|LOOP.ISFIRST}
<thead>
<tr>
<th>{|SORT_TOGGLE|CLIENT_10_LEVEL_3.NAME}{DATATYPES.SHOW_DATATYPE.CLIENT_10_LEVEL_3.GROUP.NAME}{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|COUNT(CLIENT_10_LEVEL_3)}Count{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|AVG(CLIENT_10_LEVEL_3.PERCENT_SCORE)}Avg Score{/|SORT_TOGGLE|}</th>
#dynamic property looping can also be done at the aggregate level {|LOOP|CLIENT_10_LEVEL_3.PROPERTIES(FILTER=type|eq|major_sectional_percent_score,SORT=ORDER_NUM,LIMIT=5)}
<th>
{|SORT_TOGGLE|AVG(CLIENT_10_LEVEL_3.|EVALTAG(PROPERTIES.PROPERTY_ID)|)}
{PROPERTIES.NAME}
{/|SORT_TOGGLE|}
</th>
{/|LOOP|}
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{CLIENT_10_LEVEL_3.NAME}</th>
<td>{COUNT(CLIENT_10_LEVEL_3)}</td>
<td>{AVG(CLIENT_10_LEVEL_3.PERCENT_SCORE).NUMBERFORMAT(2)}</td>
{|LOOP|CLIENT_10_LEVEL_3.PROPERTIES(FILTER=type|eq|major_sectional_percent_score,SORT=ORDER_NUM,LIMIT=5)}
<td>
{AVG(CLIENT_10_LEVEL_3.PROPERTIES.VALUE).ROUND}
</td>
{/|LOOP|}
</tr>
{/|LOOP|}
</tbody>
</table>
{|RESULT|}
<div>
<h2>Level 3</h2>
<table>
{|LOOP|RESULT.CLIENT_10_LEVEL_3(SORT=COUNT(CLIENT_10_LEVEL_3,CLIENT_10_LEVEL_3))}
{|SHOWIF|LOOP.ISFIRST}
<thead>
<tr>
<th>
{DATATYPES.SHOW_DATATYPE.CLIENT_10_LEVEL_3.GROUP.NAME}
{|SORT_ASCENDING|CLIENT_10_LEVEL_3.NAME}[asc]{/|SORT_ASCENDING|}
{|SORT_DESCENDING|CLIENT_10_LEVEL_3.NAME}[desc]{/|SORT_DESCENDING|}
</th>
<th>
Count
{|SORT_ASCENDING|COUNT(CLIENT_10_LEVEL_3)}[asc]{/|SORT_ASCENDING|}
{|SORT_DESCENDING|COUNT(CLIENT_10_LEVEL_3)}[desc]{/|SORT_DESCENDING|}
</th>
<th>
Avg Score
{|SORT_ASCENDING|AVG(CLIENT_10_LEVEL_3.PERCENT_SCORE)}[asc]{/|SORT_ASCENDING|}
{|SORT_DESCENDING|AVG(CLIENT_10_LEVEL_3.PERCENT_SCORE)}[desc]{/|SORT_DESCENDING|}
</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{CLIENT_10_LEVEL_3.NAME}</th>
<td>{COUNT(CLIENT_10_LEVEL_3)}</td>
<td>{AVG(CLIENT_10_LEVEL_3.PERCENT_SCORE).NUMBERFORMAT(2)}</td>
</tr>
{/|LOOP|}
</tbody>
</table>
{/|RESULT|}
Crosstab Sorting
Crosstab sorting is more complex, because you’re sorting by a specific column in the results, which requires the use of POS and EVALTAG in combination with INDEX.
{|CROSSTAB|}{|LOOP|CROSSTAB.YEAR(SORT=ORDER_NUM)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data'>
<thead>
<tr>
<th>{|SORT_TOGGLE|YEAR.NAME}Name{/|SORT_TOGGLE|}</th>
{|LOOP|YEAR.MONTH_OF_YEAR(SORT=ORDER_NUM)}
#Using POS tells ToolZ that we want to sort by a specific column position
#We then use EVALTAG and INDEX to tell it which position to sort by
<th>{|SORT_TOGGLE|POS(COUNT(MONTH_OF_YEAR),|EVALTAG(MONTH_OF_YEAR.INDEX)|)}{MONTH_OF_YEAR.NAME}{/|SORT_TOGGLE|}</th>
{/|LOOP|}
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{YEAR.NAME}</th>
{|LOOP|YEAR.MONTH_OF_YEAR}
<td>{COUNT(MONTH_OF_YEAR)}</td>
{/|LOOP|}
</tr>
{|VANISHIF|LOOP.HASNEXT}
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
{/|CROSSTAB|}
Nested Sorting
Nested sorting is much more complex, as sorting by certain nested columns would actually require an entire restructuring of the nested query. As such, only sorting on the innermost nested group is supported currently. If you have the need to do more elaborate nested sorting, please submit a feature request so we can prioritize the functionality.
{|NESTED|PAGING_GROUP=DATAPOINTS}<table>
<thead>
{|LOOP|NESTED.CLIENT_10_LEVEL_4(SORT=NAME)}
{|SHOWIF|LOOP.ISFIRST}
<tr>
<th>
{DATATYPES.SHOW_DATATYPE.CLIENT_10_LEVEL_4.NAME}
</th>
<th>{|SORT_TOGGLE|ID}Shop ID{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|CLIENT_10_LEVEL_3.NAME}{DATATYPES.SHOW_DATATYPE.CLIENT_10_LEVEL_3.NAME}{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|PERCENT_SCORE}Score{/|SORT_TOGGLE|}</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{CLIENT_10_LEVEL_4.NAME}</th>
<td>{COUNT(CLIENT_10_LEVEL_4,CLIENT_10_LEVEL_4)}</td>
<td></td>
<td></td>
</tr>
{|LOOP|CLIENT_10_LEVEL_4.DATAPOINTS(SORT=ID,LIMIT=10)}
<tr>
<th></th>
<td>{DATAPOINTS.ID}</td>
<td>{DATAPOINTS.CLIENT_10_LEVEL_3.NAME}</td>
<td>{DATAPOINTS.PERCENT_SCORE}</td>
</tr>
{/|LOOP|}
{/|LOOP|}
</tbody>
</table>
{/|NESTED|}