Create a Custom Reporting Dashboard from Scratch
Create a custom reporting dashboard in DenchClaw from scratch. Use DuckDB queries and the App Builder to build live charts and reports on your CRM data.
Create a Custom Reporting Dashboard from Scratch
The best CRM reporting dashboard is the one built for your actual questions — not a set of default charts someone at a software company decided were important. DenchClaw gives you two paths to custom reporting: ask the AI to generate reports inline, or build a persistent dashboard app.
This guide covers both, starting with the fastest approach and working toward a production-quality dashboard.
Path 1: Inline Reports (Fastest)#
For quick analysis, ask directly in the chat:
Show me closed won deals by industry this quarter as a bar chart
DenchClaw runs a DuckDB query and emits a report-json block that the UI renders as an interactive chart. These appear inline in the chat.
To save a report:
Save this chart as "Q1 Win by Industry" and add it to my sidebar
It appears as a saved report in your workspace — you can reopen it anytime.
Useful report prompts to try:
Show me total pipeline by stage as a funnel chart
Show me contacts added per week for the last 12 weeks as a line chart
What's the average days to close by deal source? Show as a horizontal bar chart.
Show me win rate by owner, with deals closed in the last 90 days
These run instantly on your local DuckDB — no API calls, no loading screens.
Path 2: Persistent Dashboard App#
For a dashboard that lives in your sidebar and auto-refreshes, build a .dench.app.
Option A: AI-Generated Dashboard#
Build a sales reporting dashboard with these sections:
1. KPI row: total pipeline, closed won this month, win rate, average deal size
2. Pipeline by stage — horizontal bar chart
3. Revenue trend — monthly closed won for last 12 months, line chart
4. Top performers — deals closed by owner, bar chart
5. Deal sources — pie chart of deals by source
6. Stale deals alert — table of deals with no update in 14+ days
Use Chart.js. Dark theme. Auto-refresh every 10 minutes. Show "Last updated" timestamp.
DenchClaw builds this as a complete .dench.app folder. Open it from your sidebar.
Option B: Manual Dashboard Build#
Create the app folder:
mkdir ~/.openclaw-dench/workspace/apps/sales-dashboard.dench.app.dench.yaml:
name: Sales Dashboard
icon: trending-up
version: 1.0.0
permissions:
- db:read
display: tab
refresh_interval: 600000index.html — Key sections:
<!DOCTYPE html>
<html>
<head>
<title>Sales Dashboard</title>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<style>
body { font-family: -apple-system, sans-serif; background: #111; color: #fff; padding: 20px; }
.kpis { display: grid; grid-template-columns: repeat(4, 1fr); gap: 12px; margin-bottom: 20px; }
.kpi { background: #1e1e1e; padding: 16px; border-radius: 8px; }
.kpi-num { font-size: 28px; font-weight: 700; color: #22c55e; }
.kpi-label { font-size: 11px; color: #777; margin-top: 4px; text-transform: uppercase; }
.charts { display: grid; grid-template-columns: 1fr 1fr; gap: 12px; margin-bottom: 20px; }
.chart-box { background: #1e1e1e; padding: 16px; border-radius: 8px; }
.chart-title { font-size: 12px; color: #999; margin-bottom: 12px; }
.stale { background: #1e1e1e; padding: 16px; border-radius: 8px; }
table { width: 100%; border-collapse: collapse; }
td, th { padding: 8px 10px; border-bottom: 1px solid #2a2a2a; font-size: 12px; }
th { color: #666; font-size: 11px; }
.updated { font-size: 11px; color: #555; text-align: right; margin-top: 12px; }
</style>
</head>
<body>
<div class="kpis">
<div class="kpi"><div class="kpi-num" id="k-pipeline">—</div><div class="kpi-label">Open Pipeline</div></div>
<div class="kpi"><div class="kpi-num" id="k-won" style="color:#3b82f6">—</div><div class="kpi-label">Closed Won (MTD)</div></div>
<div class="kpi"><div class="kpi-num" id="k-winrate" style="color:#f59e0b">—</div><div class="kpi-label">Win Rate (90d)</div></div>
<div class="kpi"><div class="kpi-num" id="k-avg" style="color:#a855f7">—</div><div class="kpi-label">Avg Deal Size</div></div>
</div>
<div class="charts">
<div class="chart-box"><div class="chart-title">Pipeline by Stage</div><canvas id="c-stages"></canvas></div>
<div class="chart-box"><div class="chart-title">Monthly Revenue</div><canvas id="c-revenue"></canvas></div>
<div class="chart-box"><div class="chart-title">Closed by Owner</div><canvas id="c-owners"></canvas></div>
<div class="chart-box"><div class="chart-title">Deal Sources</div><canvas id="c-sources"></canvas></div>
</div>
<div class="stale">
<div class="chart-title">⚠️ Stale Deals (No Update 14+ Days)</div>
<table><thead><tr><th>Deal</th><th>Stage</th><th>Value</th><th>Owner</th></tr></thead>
<tbody id="t-stale"></tbody></table>
</div>
<div class="updated" id="updated-at"></div>
<script>
const fmt = v => '$' + ((v||0)/1000).toFixed(0) + 'k';
const colors = ['#3b82f6','#22c55e','#f59e0b','#ef4444','#a855f7','#06b6d4'];
async function load() {
const [pipeline, won, wr, avg] = await Promise.all([
dench.db.query(`SELECT SUM(CAST("Value" AS DOUBLE)) t FROM v_deals WHERE "Stage" NOT IN ('Closed Won','Closed Lost')`),
dench.db.query(`SELECT SUM(CAST("Value" AS DOUBLE)) t FROM v_deals WHERE "Stage"='Closed Won' AND CAST("Close Date" AS DATE)>=date_trunc('month',CURRENT_DATE)`),
dench.db.query(`SELECT ROUND(100.0*SUM(CASE WHEN "Stage"='Closed Won' THEN 1 ELSE 0 END)/NULLIF(COUNT(*),0),1) r FROM v_deals WHERE "Stage" IN ('Closed Won','Closed Lost') AND CAST("Close Date" AS DATE)>=CURRENT_DATE-90`),
dench.db.query(`SELECT AVG(CAST("Value" AS DOUBLE)) a FROM v_deals WHERE "Stage"='Closed Won'`)
]);
document.getElementById('k-pipeline').textContent = fmt(pipeline[0]?.t);
document.getElementById('k-won').textContent = fmt(won[0]?.t);
document.getElementById('k-winrate').textContent = (wr[0]?.r||0)+'%';
document.getElementById('k-avg').textContent = fmt(avg[0]?.a);
const stages = await dench.db.query(`SELECT "Stage",SUM(CAST("Value" AS DOUBLE)) v FROM v_deals WHERE "Stage" NOT IN ('Closed Won','Closed Lost') GROUP BY "Stage" ORDER BY v DESC`);
new Chart(document.getElementById('c-stages'),{type:'bar',data:{labels:stages.map(s=>s.Stage),datasets:[{data:stages.map(s=>s.v||0),backgroundColor:colors}]},options:{indexAxis:'y',plugins:{legend:{display:false}},scales:{x:{ticks:{color:'#888',callback:fmt}},y:{ticks:{color:'#888'}}}}});
const rev = await dench.db.query(`SELECT strftime(CAST("Close Date" AS DATE),'%Y-%m') m,SUM(CAST("Value" AS DOUBLE)) v FROM v_deals WHERE "Stage"='Closed Won' AND CAST("Close Date" AS DATE)>=CURRENT_DATE-365 GROUP BY m ORDER BY m`);
new Chart(document.getElementById('c-revenue'),{type:'line',data:{labels:rev.map(r=>r.m),datasets:[{data:rev.map(r=>r.v||0),borderColor:'#22c55e',fill:true,backgroundColor:'rgba(34,197,94,0.1)',tension:0.3}]},options:{plugins:{legend:{display:false}},scales:{x:{ticks:{color:'#888'}},y:{ticks:{color:'#888',callback:fmt}}}}});
const owners = await dench.db.query(`SELECT "Owner",SUM(CAST("Value" AS DOUBLE)) v FROM v_deals WHERE "Stage"='Closed Won' AND CAST("Close Date" AS DATE)>=CURRENT_DATE-90 GROUP BY "Owner" ORDER BY v DESC`);
new Chart(document.getElementById('c-owners'),{type:'bar',data:{labels:owners.map(o=>o.Owner||'Unassigned'),datasets:[{data:owners.map(o=>o.v||0),backgroundColor:colors}]},options:{plugins:{legend:{display:false}},scales:{x:{ticks:{color:'#888'}},y:{ticks:{color:'#888',callback:fmt}}}}});
const sources = await dench.db.query(`SELECT "Deal Source",COUNT(*) c FROM v_deals GROUP BY "Deal Source"`);
new Chart(document.getElementById('c-sources'),{type:'pie',data:{labels:sources.map(s=>s['Deal Source']||'Unknown'),datasets:[{data:sources.map(s=>s.c),backgroundColor:colors}]},options:{plugins:{legend:{position:'bottom',labels:{color:'#888'}}}}});
const stale = await dench.db.query(`SELECT "Deal Name","Stage","Value","Owner" FROM v_deals WHERE "Stage" NOT IN ('Closed Won','Closed Lost') LIMIT 10`);
document.getElementById('t-stale').innerHTML = stale.map(d=>`<tr><td>${d['Deal Name']||'—'}</td><td>${d.Stage||'—'}</td><td>${fmt(d.Value)}</td><td>${d.Owner||'—'}</td></tr>`).join('');
document.getElementById('updated-at').textContent = 'Last updated: ' + new Date().toLocaleTimeString();
}
load();
setInterval(load, 600000);
</script>
</body>
</html>Adding Custom Sections#
Cohort analysis:
const cohorts = await dench.db.query(`
SELECT date_trunc('month', CAST("date" AS DATE)) as cohort,
COUNT(*) as customers
FROM v_people WHERE "Status" = 'Customer'
GROUP BY cohort ORDER BY cohort
`);Funnel conversion rates:
const funnel = await dench.db.query(`
SELECT "Stage", COUNT(*) as count
FROM v_deals GROUP BY "Stage"
ORDER BY CASE "Stage"
WHEN 'Discovery' THEN 1 WHEN 'Proposal' THEN 2
WHEN 'Negotiation' THEN 3 WHEN 'Closed Won' THEN 4 ELSE 5 END
`);Forecast vs. actuals:
const forecast = await dench.db.query(`
SELECT strftime(CAST("Close Date" AS DATE), '%Y-%m') as month,
SUM(CAST("Value" AS DOUBLE)) as forecast,
SUM(CASE WHEN "Stage"='Closed Won' THEN CAST("Value" AS DOUBLE) ELSE 0 END) as actual
FROM v_deals WHERE CAST("Close Date" AS DATE) >= CURRENT_DATE - 90
GROUP BY month ORDER BY month
`);AI-Assisted Interpretation#
Add an AI summary section to your dashboard:
const summary = await dench.chat.oneShot(`
Analyze this pipeline data and highlight the top 2 concerns and 1 opportunity:
${JSON.stringify({stages, rev, owners})}
Keep it to 3 bullet points.
`);
document.getElementById('ai-insight').textContent = summary;This gives you a machine-written weekly summary of your pipeline health — trends a human might miss in the raw numbers.
For more on DenchClaw's App Builder, see the build a custom analytics app guide and the full DenchClaw overview.
Frequently Asked Questions#
Can I export my dashboard as a PDF for stakeholders?#
Yes. Use the browser's print-to-PDF function, or ask DenchClaw: "Export the sales dashboard as a PDF." The here-now skill can also publish it to a temporary web URL.
How do I add filters to the dashboard (e.g., filter by owner)?#
Add a <select> element and pass its value into your SQL query: `WHERE "Owner" = '${selectedOwner}'`. Re-run charts on the select's change event.
Can I embed external data (e.g., from Google Sheets) in my dashboard?#
Yes. Use dench.http.fetch() to pull from external APIs, then merge with DuckDB data in your JavaScript.
What chart types are available?#
Any library you can load via CDN: Chart.js (bar, line, pie, doughnut, scatter, radar, funnel), D3 (anything), Plotly (interactive 3D, heatmaps). DenchClaw doesn't restrict which JS libraries you use.
How does the dashboard auto-refresh work?#
Set refresh_interval in .dench.yaml (milliseconds) and call setInterval(load, interval) in your JS. The app reloads data without a full page refresh.
Ready to try DenchClaw? Install in one command: npx denchclaw. Full setup guide →
