Use Case
The client would like to see score trending information in a drilldown format. They would like to their levels broken out by month. They would also like upper-level scores to be maintained as the report is drilled down.
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)}
#if we have no group, show region by default
{|VANISHIF|DATATYPES.REPORT_CONFIG.SHOW_GROUP_ID}
{|CROSSTAB|}
<div>
{|LOOP|CROSSTAB.CLIENT_49_LEVEL_3(SORT=NAME,CROSSTAB_FILTER=NO_BLANKS)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data'>
<thead>
<tr>
<th>Name</th>
{|LOOP|CLIENT_49_LEVEL_3.MONTH_YEAR(SORT=ORDER_NUM,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
<th>{MONTH_YEAR.NAME}</th>
{/|LOOP|}
<th>All Months</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th><a href="../xjump/DrilldownTrending1392835806874?datatype={DATATYPES.REPORT_CONFIG.DATATYPE}&show_group_id=client_49_level_2&filterby=client_49_level_3_id,eq,{CLIENT_49_LEVEL_3.ID}&trending_date_after={DATATYPES.REPORT_CONFIG.TRENDING_DATE_AFTER.FORMATTED(Y-m-d)}&trending_date_before={DATATYPES.REPORT_CONFIG.TRENDING_DATE_BEFORE.FORMATTED(Y-m-d)}&MISC_VARS___LEV3ID={CLIENT_49_LEVEL_3.ID}">
{CLIENT_49_LEVEL_3.NAME}</a></th>
{|LOOP|CLIENT_49_LEVEL_3.MONTH_YEAR}
<td>{AVG(MONTH_YEAR.PERCENT_SCORE).ROUND}</td>
{/|LOOP|}
<td>{AVG(CLIENT_49_LEVEL_3.PERCENT_SCORE,CLIENT_49_LEVEL_3).ROUND}</td>
</tr>
{|VANISHIF|LOOP.HASNEXT}
<tr>
<th>Company</th>
{|LOOP|CLIENT_49_LEVEL_3.MONTH_YEAR}
<td>{AVG(MONTH_YEAR.PERCENT_SCORE,MONTH_YEAR).ROUND}</td>
{/|LOOP|}
<td>{AVG(CLIENT_49_LEVEL_3.PERCENT_SCORE,OVERALL).ROUND}</td>
</tr>
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
</div>
{/|CROSSTAB|}
{/|VANISHIF|}
#group is specified
{|SHOWIF|DATATYPES.REPORT_CONFIG.SHOW_GROUP_ID}
#level 2 or 1
{|VANISHIF|DATATYPES.REPORT_CONFIG.SHOW_GROUP_ID=datapoints}
{|CROSSTAB|}
<div>
{|LOOP|CROSSTAB.SHOW_GROUP(SORT=NAME,CROSSTAB_FILTER=NO_BLANKS)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data'>
<thead>
<tr>
<th>Name</th>
{|LOOP|SHOW_GROUP.MONTH_YEAR(SORT=ORDER_NUM,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
<th>{MONTH_YEAR.NAME}</th>
{/|LOOP|}
<th>All Months</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>
{|SHOWIF|DATATYPES.REPORT_CONFIG.SHOW_GROUP_ID=client_49_level_2}
<a href="../xjump/DrilldownTrending1392835806874?datatype={DATATYPES.REPORT_CONFIG.DATATYPE}&show_group_id=client_49_level_1&filterby=client_49_level_2_id,eq,{SHOW_GROUP.ID};client_49_level_3_id,eq,{MISC_VARS.LEV3ID}&trending_date_after={DATATYPES.REPORT_CONFIG.TRENDING_DATE_AFTER.FORMATTED(Y-m-d)}&trending_date_before={DATATYPES.REPORT_CONFIG.TRENDING_DATE_BEFORE.FORMATTED(Y-m-d)}&MISC_VARS___LEV3ID={MISC_VARS.LEV3ID}&MISC_VARS___LEV2ID={SHOW_GROUP.ID}">
{SHOW_GROUP.NAME}</a>
{/|SHOWIF|}
{|SHOWIF|DATATYPES.REPORT_CONFIG.SHOW_GROUP_ID=client_49_level_1}
<a href="../xjump/DrilldownTrending1392835806874?datatype={DATATYPES.REPORT_CONFIG.DATATYPE}&show_group_id=datapoints&filterby=client_49_level_1_id,eq,{SHOW_GROUP.ID};client_49_level_3_id,eq,{MISC_VARS.LEV3ID};client_49_level_2_id,eq,{MISC_VARS.LEV2ID}&trending_date_after={DATATYPES.REPORT_CONFIG.TRENDING_DATE_AFTER.FORMATTED(Y-m-d)}&trending_date_before={DATATYPES.REPORT_CONFIG.TRENDING_DATE_BEFORE.FORMATTED(Y-m-d)}&MISC_VARS___LEV3ID={MISC_VARS.LEV3ID}&MISC_VARS___LEV2ID={MISC_VARS.LEV2ID}">
{SHOW_GROUP.NAME}</a>
{/|SHOWIF|}
</th>
{|LOOP|SHOW_GROUP.MONTH_YEAR}
<td>{AVG(MONTH_YEAR.PERCENT_SCORE).ROUND}</td>
{/|LOOP|}
<td>{AVG(SHOW_GROUP.PERCENT_SCORE,SHOW_GROUP).ROUND}</td>
</tr>
{/|LOOP|}
{/|CROSSTAB|}
{/|VANISHIF|}
#datapoints
{|SHOWIF|DATATYPES.REPORT_CONFIG.SHOW_GROUP_ID=datapoints}
<div>
<table id='datapoint-table' class='data'>
<thead>
<tr>
<th></th>
{|LOOP|DATATYPES.SHOW_DATATYPE.MONTH_YEAR.MEMBERS(SORT=ORDER_NUM,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
<th>{MEMBERS.GROUP_MEMBER_NAME}</th>
{/|LOOP|}
<th>All Months</th>
</tr>
</thead>
<tbody>
{|CROSSTAB|}
{|LOOP|CROSSTAB.MONTH_YEAR(SORT=ORDER_NUM|DESC,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
{|LOOP|MONTH_YEAR.DATAPOINTS}
<tr>
<td><a href="http://www.sassieshop.com/2su/clients/ClientShopView.php?JobID={DATAPOINTS.ID}" target="_blank">{DATAPOINTS.ID}</a></td>
{|LOOP|DATATYPES.SHOW_DATATYPE.MONTH_YEAR.MEMBERS(SORT=ORDER_NUM,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
<td>{|SHOWIF|MEMBERS.GROUP_MEMBER_ID=|EVALTAG(MONTH_YEAR.ID)|}{DATAPOINTS.PERCENT_SCORE}{/|SHOWIF|}</td>
{/|LOOP|}
<td>{DATAPOINTS.PERCENT_SCORE}</td>
</tr>
{/|LOOP|}
{/|LOOP|}
{/|CROSSTAB|}
{/|SHOWIF|}
{|SHOWIF|DATATYPES.REPORT_CONFIG.SHOW_GROUP_ID=datapoints}
#district values
{|CROSSTAB|KEEP_ONLY_FILTERS=TRENDING_TIMESTAMP;CLIENT_49_LEVEL_2_ID;CLIENT_49_LEVEL_3_ID;CLIENT_49_LEVEL_1_ID}
{|LOOP|CROSSTAB.CLIENT_49_LEVEL_1(SORT=NAME,CROSSTAB_FILTER=NO_BLANKS)}
<tr>
<th>{CLIENT_49_LEVEL_1.NAME}</th>
{|LOOP|CLIENT_49_LEVEL_1.MONTH_YEAR(SORT=ORDER_NUM,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
<td>{AVG(MONTH_YEAR.PERCENT_SCORE).ROUND}</td>
{/|LOOP|}
<td>{AVG(CLIENT_49_LEVEL_1.PERCENT_SCORE,CLIENT_49_LEVEL_1).ROUND}</td>
</tr>
{/|LOOP|}
{/|CROSSTAB|}
{/|SHOWIF|}
{|VANISHIF|DATATYPES.REPORT_CONFIG.SHOW_GROUP_ID=client_49_level_2}
#district values
{|CROSSTAB|KEEP_ONLY_FILTERS=TRENDING_TIMESTAMP;CLIENT_49_LEVEL_2_ID;CLIENT_49_LEVEL_3_ID}
{|LOOP|CROSSTAB.CLIENT_49_LEVEL_2(SORT=NAME,CROSSTAB_FILTER=NO_BLANKS)}
<tr>
<th>{CLIENT_49_LEVEL_2.NAME}</th>
{|LOOP|CLIENT_49_LEVEL_2.MONTH_YEAR(SORT=ORDER_NUM,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
<td>{AVG(MONTH_YEAR.PERCENT_SCORE).ROUND}</td>
{/|LOOP|}
<td>{AVG(CLIENT_49_LEVEL_2.PERCENT_SCORE,CLIENT_49_LEVEL_2).ROUND}</td>
</tr>
{/|LOOP|}
{/|CROSSTAB|}
{/|VANISHIF|}
#region values
{|CROSSTAB|KEEP_ONLY_FILTERS=TRENDING_TIMESTAMP;CLIENT_49_LEVEL_3_ID}
{|LOOP|CROSSTAB.CLIENT_49_LEVEL_3(SORT=NAME,CROSSTAB_FILTER=NO_BLANKS)}
<tr>
<th>{CLIENT_49_LEVEL_3.NAME}</th>
{|LOOP|CLIENT_49_LEVEL_3.MONTH_YEAR(SORT=ORDER_NUM,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
<td>{AVG(MONTH_YEAR.PERCENT_SCORE).ROUND}</td>
{/|LOOP|}
<td>{AVG(CLIENT_49_LEVEL_3.PERCENT_SCORE,CLIENT_49_LEVEL_3).ROUND}</td>
</tr>
{/|LOOP|}
{/|CROSSTAB|}
#company values
{|CROSSTAB|KEEP_ONLY_FILTERS=TRENDING_TIMESTAMP}
<tr>
<th>Company</th>
{|LOOP|CROSSTAB.MONTH_YEAR(SORT=ORDER_NUM,CROSSTAB_FILTER=WITH_BLANKS_IN_RANGE)}
<td>{AVG(MONTH_YEAR.PERCENT_SCORE).ROUND}</td>
{|VANISHIF|LOOP.HASNEXT}
<td>{AVG(MONTH_YEAR.PERCENT_SCORE,OVERALL).ROUND}</td>
{/|VANISHIF|}
{/|LOOP|}
</tr>
{/|CROSSTAB|}
</tbody>
</table>
</div>
{/|SHOWIF|}
{/|SHOWIF|}
</div>
CSS
/* css for CompanyOverviewDrilldown1350400998759 */
@import "themeroller/jquery.ui.all.css";
@import "chrome.theme.css";
@import "chrome.layout.css";
#data
{
width: auto;
margin: auto;
}
/***** clean *****/
body {
background: #99bbcc;
}
.wrap {
padding: 2em 0;
}
header.main, .header.main {
background: transparent;
border: 0;
padding: 0;
-moz-box-shadow: none;
-webkit-box-shadow: none;
box-shadow: none;
}
#content {
background: #ecf4f9;
border: 9px solid #fff;
padding: 0;
-moz-border-radius: 9px;
-webkit-border-radius: 9px;
border-radius: 9px;
-moz-box-shadow: 0px 0px 12px #3a6478;
-webkit-box-shadow: 0px 0px 12px #3a6478;
box-shadow: 0px 0px 12px #3a6478;
}
fieldset {
background: #ecf4f9;
border: 0;
padding: 1em;
text-align: right;
}
fieldset > div, fieldset .level-input {
float: left;
width: 25%;
text-align: left;
}
fieldset .date-input {
width: 50%;
}
fieldset .date-input div {
float: left;
margin-left: 15px;
}
fieldset .controls, fieldset .ui-controls {
float: right;
margin: 18px 18px 0 0;
text-align: right;
width: 40%;
}
fieldset .controls button {
background: #138bbf; /* Old browsers */
background: -moz-linear-gradient(top, #138bbf 0%, #72c1e5 50%, #2982a5 51%, #138bbf 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#138bbf), color-stop(50%,#72c1e5), color-stop(51%,#2982a5), color-stop(100%,#138bbf)); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, #138bbf 0%,#72c1e5 50%,#2982a5 51%,#138bbf 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, #138bbf 0%,#72c1e5 50%,#2982a5 51%,#138bbf 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, #138bbf 0%,#72c1e5 50%,#2982a5 51%,#138bbf 100%); /* IE10+ */
background: linear-gradient(to bottom, #138bbf 0%,#72c1e5 50%,#2982a5 51%,#138bbf 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#138bbf', endColorstr='#138bbf',GradientType=0 ); /* IE6-9 */
border: 1px solid #4095bf;
color: #fff;
font-size: 1.1em;
float: right;
margin-left: 1em;
padding: .25em 1em;
-moz-border-radius: 6px;
-webkit-border-radius: 6px;
border-radius: 6px;
}
fieldset .controls button:hover {
background: #2982a5;
border: 2px solid #111;
color: #fff;
-moz-box-shadow: 0px 0px 12px #fff;
-webkit-box-shadow: 0px 0px 12px #fff;
box-shadow: 0px 0px 12px #fff;
border: 1px solid #fff;
}
.results {
background: #fff;
border: 1px solid #ccc;
margin: 2em;
padding: 1em 2em 2em 2em;
-moz-border-radius: 9px;
-webkit-border-radius: 9px;
border-radius: 9px;
-moz-box-shadow: 0px 0px 12px #ccc;
-webkit-box-shadow: 0px 0px 12px #ccc;
box-shadow: 0px 0px 12px #ccc;
}
.results:before,
.results:after {
content:"";
display:table;
}
.results:after {
clear:both;
}
table#data {
border: 1px solid #bbb;
float: none;
width: 100%;
-moz-border-radius: 6px;
-webkit-border-radius: 6px;
border-radius: 6px;
}
thead th {
background: #138bbf; /* Old browsers */
background: -moz-linear-gradient(top, #138bbf 0%, #72c1e5 50%, #2982a5 51%, #138bbf 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#138bbf), color-stop(50%,#72c1e5), color-stop(51%,#2982a5), color-stop(100%,#138bbf)); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, #138bbf 0%,#72c1e5 50%,#2982a5 51%,#138bbf 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, #138bbf 0%,#72c1e5 50%,#2982a5 51%,#138bbf 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, #138bbf 0%,#72c1e5 50%,#2982a5 51%,#138bbf 100%); /* IE10+ */
background: linear-gradient(to bottom, #138bbf 0%,#72c1e5 50%,#2982a5 51%,#138bbf 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#138bbf', endColorstr='#138bbf',GradientType=0 ); /* IE6-9 */
color: #fff;
}
table tbody tr:nth-child(odd) {
background: #eef4f6;
color: #4095bf;
}
table tbody tr:nth-child(even) {
background: #fff;
color: #4095bf;
}
table tbody tr th {
background: transparent
}
table tbody tr:hover {
background: orange;
color: #fff;
}
table tbody tr:hover .cleanliness-value-1 {
background-color: #00aa00;
background-color: #88cc00;
color: #fff;
}
table tbody tr:hover .cleanliness-value-2 {
background-color: #aaff22;
background-color: #dfff80;
color: #4095bf;
}
table tbody tr:hover .cleanliness-value-3 {
background-color: #ffff22;
background-color: #fff266;
color: #4095bf;
}
table tbody tr:hover .cleanliness-value-4 {
background-color: #ff1111;
background-color: #ff8c66;
color: #fff;
}
#summary-graph .grapher {
margin: 0 auto;
text-align: center;
}
/***** print mode *****/
body.output-mode-print,
.output-mode-print #content,
.output-mode-print .results
{
background: #fff;
border: 0;
margin: 0 auto;
padding: 0;
-moz-box-shadow: none;
-webkit-box-shadow: none;
box-shadow: none;
}
.output-mode-print .results {
margin: 2em;
}