Use Case
The client would like to see its districts ranked within their respective regions by average overall score. They would also like to know how many percentage points each district is from the “best” district in their region.
Report
Code
{|SHOWIF|OUTPUT_MODE=|EVALCONST(KNICKERS_OUTPUT_STANDARD)|}{DATATYPES.CONTROLS_START}
{DATATYPES.DEFAULT_DATE_FILTERS}
{DATATYPES.CONTROLS_END}{/|SHOWIF|}
<div class='results'>
{|VANISHIF|COUNT(DATATYPES.SHOW_DATATYPE)}
<div class='ui-state-highlight no-data'><p>No data was found to display</p></div>
{/|VANISHIF|}
{|SHOWIF|COUNT(DATATYPES.SHOW_DATATYPE)}
{|RESULT|}
<div>
#our rows are level 2 (district)
{|LOOP|RESULT.CLIENT_55_LEVEL_2(SORT=NAME)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data' id='data'>
<thead>
<tr>
<th>{|SORT_TOGGLE|CLIENT_55_LEVEL_2.ORDER_NUM}Name{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|AVG(CLIENT_55_LEVEL_2.PERCENT_SCORE)}Average Score{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|AVG(CLIENT_55_LEVEL_2.PERCENT_SCORE)}Rank in Region{/|SORT_TOGGLE|}</th>
<th>{|SORT_TOGGLE|AVG(CLIENT_55_LEVEL_2.PERCENT_SCORE)}Distance to Best in Region{/|SORT_TOGGLE|}</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{CLIENT_55_LEVEL_2.NAME}</th>
<td>{AVG(CLIENT_55_LEVEL_2.PERCENT_SCORE).NUMBERFORMAT(2)}</td>
#get the district's rank within its region
<td>{RANK(AVG(CLIENT_55_LEVEL_2.PERCENT_SCORE), DESC, 2, CLIENT_55_LEVEL_3)}</td>
#find the difference between the highest average in the region and the current district
<td>{CALC(MAX(AVG(CLIENT_55_LEVEL_2.PERCENT_SCORE), CLIENT_55_LEVEL_3) - AVG(CLIENT_55_LEVEL_2.PERCENT_SCORE)).NUMBERFORMAT(2)}</td>
</tr>
{|VANISHIF|LOOP.HASNEXT}
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
</div>
{/|RESULT|}
{/|SHOWIF|}
</div>
CSS
@import "themeroller/jquery.ui.all.css";
@import "chrome.theme.css";
@import "chrome.layout.css";
#data
{
table-layout: auto;
width: auto;
margin-top: 1em;
font-family: Papyrus, fantasy;
font-size: 1.4em;
margin-top: 1em;
font-weight: bold;
}
#data a
{
color: white;
text-decoration: underline;
}
#data tbody th
{
color: black;
background-color: #F3B3FF;
}
#data thead th
{
color: white;
background-color: #73337F;
}
#data td
{
text-align: right;
color: black;
background-color: #F3B3FF;
font-weight: bold;
}