Skip to main content

Bucket-Based Drill-Down Report Pattern

Overview​

A bucket-based report shows one aggregate number per bucket (for example per month and per resource type) and lets the user click a number to see the records behind it. It is built from two parts that work together:

  1. A custom report Program that counts the records itself and returns one row per bucket. Each row also carries the filter needed to list the records of that bucket.
  2. A custom widget that draws those rows as a pivot table (or a chart) and, when a number is clicked, fetches the records live and shows them in a paged drill-down popup.

Use this instead of the standard report when the counts cannot be produced by the platform's own grouping, or when loading all the raw rows just to count them is too slow. Only the numbers are computed up front; the records are fetched one page at a time, when needed.

This is different from the Report Layout Custom Visualization pattern, where the platform pre-aggregates data and totalData and the widget uses the built-in ShowDrillDownData. Here the widget builds its own drill-down from the filter columns of the Program.

When to Use​

  • The numbers come from a Program that runs count queries (for example a cumulative count per month).
  • The raw rows are too many to load into the browser.
  • You want the report to look and behave like a standard report (same grid, footer, drill-down header, export).

Applicable Layout Types​

TargetNotes
ReportLayoutThe widget reads the Program's rows from pageContext.totalData.
DashboardLayoutWorks the same when the report is shown as a dashboard section.

Pattern Structure​

1. The Program returns one row per bucket and category​

Besides the number, every row has the columns the widget needs for the drill-down. Choose names once and use them in the Program and the widget:

ColumnExampleUsed for
ProjectTypeDevice <br/> ClassesThe category: one pivot column. <br/> makes a two-line title.
EventDateThe start of the monthThe bucket: one pivot row. Its month is shown with formatDate.
InstanceCount15842The number shown in the cell.
DrillDownInstanceTypeNameDeviceClassThe type whose instances are listed.
DrillDownAttributeNameCreatedThe attribute of the filter.
DrillDownOperatorLessThanOrEqualThe filter condition.
DrillDownValue2026-09-30T18:30:00.000ZThe filter value. The drill-down lists the same records the count was taken from.

Compute the bucket boundaries in the tenant time zone (convert each month start with TimeZoneInfo.ConvertTimeToUtc on the local date) so that the labels shown in the widget are the right month on every tenant. See the custom report Program sample for the Program structure.

2. The widget pivots the rows and shows them in ReportPivotGrid​

Read pageContext.totalData, group the rows by bucket, and give each cell a reference to its own row, so that a click can build the filter from that exact row.

3. A click fetches the records live​

Call instances?... with useProtrakApi and protrakApiClient. useProtrakApi gives your promiseFn an authContext; pass it as the third argument of protrakApiClient, because the client adds the Authorization header only from it. Without it the request is sent without a login and fails. Use the row's DrillDown* values as the attribute filter, and the current page and sort. Show the result in MUIGrid with paginationMode="server".

Use GridHeaderLayout with an ExportCSVButton on the left and VirtualizedGridStatus on the right, so the header matches the standard drill-down.

Code Example​

function BucketReportWidget(pageContext) {
const { protrakUtils, protrakComponents } = React.useContext(customWidgetContext);
const { useProtrakApi, protrakApiClient, formatDate } = protrakUtils;
const {
ReportPivotGrid, ReportPivotGridLinkCell, SanitizedHTML, Text,
Modal, Grid, Box, H4, Button, ButtonEnums, CancelIcon, BorderBox,
MUIGrid, CellContent, GridHeaderLayout, VirtualizedGridStatus, ExportCSVButton,
} = protrakComponents;

const rows = (pageContext && pageContext.totalData) || [];
const timeZone = pageContext?.settings?.dateTimeFormat?.timeZoneId;

// 1. Pivot: month rows x category columns. Each cell keeps its own source row.
const { months, categories, cells } = React.useMemo(() => {
const monthMap = new Map();
const categorySet = new Set();
const cellMap = new Map();
rows.forEach((row) => {
const date = new Date(row.eventDate);
const key = date.getUTCFullYear() + '-' + String(date.getUTCMonth() + 1).padStart(2, '0');
if (!monthMap.has(key)) {
monthMap.set(key, { key, date, label: formatDate(row.eventDate, 'MMM-yyyy', timeZone) });
}
categorySet.add(row.projectType);
cellMap.set(key + '||' + row.projectType, row);
});
return {
months: [...monthMap.values()].sort((a, b) => a.date - b.date),
categories: [...categorySet],
cells: cellMap,
};
}, [rows]);

// 2. Live, paged drill-down for the clicked cell.
const [bucket, setBucket] = React.useState(null);
const [skip, setSkip] = React.useState(0);
const [take, setTake] = React.useState(50);

const fetchPage = React.useCallback(
async ({ bucket, skip, take, protrakApiClient, authContext }) =>
protrakApiClient(
'instances?instanceTypeName=' + encodeURIComponent(bucket.drillDownInstanceTypeName) +
'&skip=' + skip + '&take=' + take +
'&sortBy=Created&isSortByDescending=true' +
'&AttributeFilterExpressions%5B0%5D.AttributeFilterConditions%5B0%5D.AttributeName=' + encodeURIComponent(bucket.drillDownAttributeName) +
'&AttributeFilterExpressions%5B0%5D.AttributeFilterConditions%5B0%5D.Condition=' + encodeURIComponent(bucket.drillDownOperator) +
'&AttributeFilterExpressions%5B0%5D.AttributeFilterConditions%5B0%5D.FirstValue=' + encodeURIComponent(bucket.drillDownValue),
{},
authContext // required: this is where the Authorization header comes from
),
[]
);
const { state, run } = useProtrakApi({ promiseFn: fetchPage }, { defer: true });

React.useEffect(() => {
if (bucket) run({ bucket, skip, take, protrakApiClient });
}, [bucket, skip, take, run, protrakApiClient]);

const columns = [
{ key: 'month', dataKey: 'month', title: 'Month', frozen: 'left' },
...categories.map((category) => ({
key: category,
dataKey: category,
title: (
<Text style={{ fontWeight: 'bold' }}>
<SanitizedHTML html={category} />
</Text>
),
cellRenderer: ({ rowData }) => {
const cell = rowData[category + '__bucket'];
return cell ? (
<ReportPivotGridLinkCell
value={cell.instanceCount}
onClick={() => { setSkip(0); setBucket(cell); }}
/>
) : '-';
},
})),
];

const data = months.map((month) => {
const row = { id: month.key, month: month.label };
categories.forEach((category) => {
const cell = cells.get(month.key + '||' + category);
row[category] = cell ? cell.instanceCount : '-';
row[category + '__bucket'] = cell || null;
});
return row;
});

const items = (state.data && state.data.items) || [];
const totalCount = (state.data && state.data.totalCount) || 0;

return (
<Box direction="column" style={{ display: 'block' }}>
<ReportPivotGrid columns={columns} data={data} rowKey="id" paginate />
<Modal open={Boolean(bucket)} background>
{bucket && (
<Grid direction="column" width="80vw" maxHeight="80vh" backgroundColor="SURFACE" borderRadius="5px" rows={['auto', '1fr']} areas={[['header'], ['main']]} columns={['1fr']}>
<Box padding="0 10px" alignItems="center">
<H4>Drilldown Report</H4>
<Button appearance={ButtonEnums.Appearance.TransparentWithHover} onClick={() => setBucket(null)} size={ButtonEnums.Size.Small}>
<CancelIcon />
</Button>
</Box>
<Box style={{ flexDirection: 'column', overflow: 'hidden' }}>
<GridHeaderLayout
leftHeader={<ExportCSVButton data={items} headers={[{ label: 'Name', key: 'name', attributeType: 'Text' }]} filename="drilldown" exportDisabled={!items.length} />}
rightHeader={<VirtualizedGridStatus totalRecords={totalCount} />}
/>
<Box height="60vh">
<BorderBox style={{ height: '100%', width: '100%' }} display="block">
<MUIGrid
key={state.isLoading ? 'loading' : 'loaded'}
data={items}
columns={[{
field: 'name', headerName: 'Name', flex: 1,
renderCell: (params) => (
<CellContent style={{ width: '100%', height: '100%', padding: 5 }}>
<Text>{params.row.name || '-'}</Text>
</CellContent>
),
}]}
isLoading={state.isLoading}
paginationMode="server"
paginationContext={{ skip, take }}
totalCount={totalCount}
initialPageSize={take}
getPage={(newSkip) => setSkip(newSkip)}
setPageSize={(newTake) => setTake(newTake)}
onPageSizeChange={() => setSkip(0)}
/>
</BorderBox>
</Box>
</Box>
</Grid>
)}
</Modal>
</Box>
);
}

window['BucketReportWidget'] = BucketReportWidget;

Notes​

  • Use the key on MUIGrid. The grid is mounted while the request is loading (fixed row height) and then switches to automatic row height. Without the key, the first rows can keep a taller height until the user scrolls. The key makes the grid mount again when the data arrives.
  • Keep the month of a bucket stable. Group and sort by the bucket date, and show the label with formatDate(value, 'MMM-yyyy', timeZone). Do not build the label from the browser's locale.
  • The count and the drill-down must use the same filter. The Program counts with DrillDownAttributeName / DrillDownOperator / DrillDownValue, and the widget sends exactly those values, so the records listed always match the number clicked.
  • Sort by a field that exists on every type. Use a system attribute such as Name or Created for the drill-down sort. A custom attribute name may not exist on every instance type.
  • CSV export of all rows. ExportCSVButton exports the data it is given. To export every record of a bucket and not only the current page, make one more request with take set to the total count when the widget allows export.
  • Export of the main report. The Export to CSV and Export to Excel buttons in the report header are the platform's. Their default file is built from the Program's report configuration, which adds up cumulative counts over the months. Give the header the right rows with registerExport: see Custom Report Export.
  • Pagination of the pivot. paginate pages the rows in the browser. Use it when the report has many buckets.

Components Used​

ReportPivotGrid, GridHeaderLayout, CellContent, CancelIcon, SanitizedHTML, LineChartRenderer, getDefaultColors, useTheme, ExportCSVButton, Modal.