Creating Reports

Learn how to build powerful reports, dashboards, and data visualizations in Stack9. This guide covers report types, query building, aggregations, parameters, exports, scheduling, and interactive dashboards.

What You'll Learn

✅ Report types (tabular, charts, dashboards)
✅ Building report queries with aggregations
✅ Report parameters and filters
✅ Exporting reports (PDF, Excel, CSV)
✅ Scheduling automated reports
✅ Creating interactive dashboards
✅ Visualization options and best practices

Time Required: 45-60 minutes

Prerequisites

Completed Custom Queries guide
Understanding of Entity Relationships
Entities with data in your Stack9 instance

Understanding Reports in Stack9

Reports in Stack9 are specialized screens that present data in meaningful ways:

Tabular Reports - Data grids with summaries and totals
Chart Reports - Visual representations (bar, line, pie charts)
Dashboard Reports - Multiple visualizations on one screen
Scheduled Reports - Automated report generation and delivery

Report Types

1. Tabular Reports

Display data in table format with grouping, sorting, and totals.

Use cases: Sales reports, inventory lists, transaction histories

2. Chart Reports

Visualize data with charts and graphs.

Use cases: Revenue trends, sales by region, product performance

3. Dashboard Reports

Combine multiple reports and visualizations.

Use cases: Executive dashboards, KPI tracking, real-time monitoring

4. Scheduled Reports

Automatically generate and email reports on a schedule.

Use cases: Daily sales summaries, weekly inventory reports, monthly financials

Building Your First Report

Let's build a Sales Summary Report that shows revenue by product category.

Step 1: Create the Report Query

Reports start with queries that aggregate and summarize data.

File: src/query-library/getsalessummary.json

{
  "key": "getsalessummary",
  "name": "getSalesSummary",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/sales_order/search",
    "bodyParams": "{\n  \"$select\": [\n    \"category_id\",\n    \"category.name as category_name\",\n    \"COUNT(*) as order_count\",\n    \"SUM(total_amount) as total_revenue\",\n    \"AVG(total_amount) as avg_order_value\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"status\": \"Completed\",\n    \"order_date\": {\n      \"$gte\": \"{{startDate}}\",\n      \"$lte\": \"{{endDate}}\"\n    }\n  },\n  \"$groupBy\": [\"category_id\", \"category.name\"],\n  \"$orderBy\": [\n    {\"column\": \"total_revenue\", \"order\": \"desc\"}\n  ],\n  \"$withRelated\": [\"category(notDeleted)\"]\n}",
    "queryParams": {}
  },
  "userParams": {
    "startDate": "",
    "endDate": ""
  }
}

Key Features:

COUNT(*) - Count orders
SUM(total_amount) - Total revenue
AVG(total_amount) - Average order value
$groupBy - Group by category
Parameters for date range filtering

Step 2: Create the Report Screen

File: src/screens/sales_summary_report.json

{
  "head": {
    "title": "Sales Summary Report",
    "key": "sales_summary_report",
    "route": "sales-summary-report",
    "app": "reports",
    "icon": "BarChartOutlined",
    "description": "Revenue summary by product category"
  },
  "screenType": "listView",
  "listQuery": "getsalessummary",
  "reportMode": true,
  "columnsConfiguration": [
    {
      "field": "category_name",
      "label": "Category",
      "value": "{{category_name}}",
      "renderAs": "Text"
    },
    {
      "field": "order_count",
      "label": "Orders",
      "value": "{{order_count}}",
      "renderAs": "Number",
      "options": {
        "format": "0,0"
      }
    },
    {
      "field": "total_revenue",
      "label": "Total Revenue",
      "value": "{{total_revenue}}",
      "renderAs": "Currency",
      "options": {
        "currency": "USD",
        "decimals": 2
      }
    },
    {
      "field": "avg_order_value",
      "label": "Avg Order Value",
      "value": "{{avg_order_value}}",
      "renderAs": "Currency",
      "options": {
        "currency": "USD",
        "decimals": 2
      }
    }
  ],
  "filters": [
    {
      "field": "startDate",
      "label": "Start Date",
      "type": "date",
      "required": true
    },
    {
      "field": "endDate",
      "label": "End Date",
      "type": "date",
      "required": true
    }
  ],
  "actions": [
    {
      "key": "export_pdf",
      "label": "Export PDF",
      "type": "export",
      "format": "pdf"
    },
    {
      "key": "export_excel",
      "label": "Export Excel",
      "type": "export",
      "format": "xlsx"
    },
    {
      "key": "export_csv",
      "label": "Export CSV",
      "type": "export",
      "format": "csv"
    }
  ],
  "totals": {
    "enabled": true,
    "columns": [
      {
        "field": "order_count",
        "type": "sum"
      },
      {
        "field": "total_revenue",
        "type": "sum"
      },
      {
        "field": "avg_order_value",
        "type": "avg"
      }
    ]
  }
}

Key Features:

reportMode: true - Enables report-specific features
Date range filters
Export actions (PDF, Excel, CSV)
Column totals

Aggregation Functions

Stack9 supports SQL aggregation functions in queries:

COUNT - Count Records

{
  "$select": [
    "status",
    "COUNT(*) as total_count"
  ],
  "$groupBy": ["status"]
}

SUM - Total Values

{
  "$select": [
    "product_id",
    "SUM(quantity) as total_quantity",
    "SUM(line_total) as total_sales"
  ],
  "$groupBy": ["product_id"]
}

AVG - Average Values

{
  "$select": [
    "category_id",
    "AVG(price) as avg_price",
    "AVG(rating) as avg_rating"
  ],
  "$groupBy": ["category_id"]
}

MIN/MAX - Minimum/Maximum

{
  "$select": [
    "product_id",
    "MIN(price) as lowest_price",
    "MAX(price) as highest_price"
  ],
  "$groupBy": ["product_id"]
}

Multiple Aggregations

{
  "$select": [
    "sales_rep_id",
    "sales_rep.name",
    "COUNT(*) as order_count",
    "SUM(total_amount) as total_sales",
    "AVG(total_amount) as avg_order_value",
    "MIN(order_date) as first_order",
    "MAX(order_date) as last_order"
  ],
  "$groupBy": ["sales_rep_id", "sales_rep.name"]
}

Grouping Data

Single-Level Grouping

Group by one field:

{
  "$select": [
    "status",
    "COUNT(*) as count"
  ],
  "$groupBy": ["status"]
}

Multi-Level Grouping

Group by multiple fields:

{
  "$select": [
    "category_id",
    "status",
    "COUNT(*) as count",
    "SUM(total_amount) as revenue"
  ],
  "$groupBy": ["category_id", "status"],
  "$orderBy": [
    {"column": "category_id", "order": "asc"},
    {"column": "revenue", "order": "desc"}
  ]
}

Grouping with Date Functions

{
  "$select": [
    "DATE_TRUNC('month', order_date) as month",
    "COUNT(*) as order_count",
    "SUM(total_amount) as monthly_revenue"
  ],
  "$groupBy": ["DATE_TRUNC('month', order_date)"],
  "$orderBy": [
    {"column": "month", "order": "asc"}
  ]
}

Report Parameters

Date Range Parameters

Query with Date Parameters:

{
  "key": "getdailysalesreport",
  "name": "getDailySalesReport",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/sales_order/search",
    "bodyParams": "{\n  \"$select\": [\n    \"DATE(order_date) as sale_date\",\n    \"COUNT(*) as order_count\",\n    \"SUM(total_amount) as daily_revenue\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"order_date\": {\n      \"$gte\": \"{{startDate}}\",\n      \"$lte\": \"{{endDate}}\"\n    }\n  },\n  \"$groupBy\": [\"DATE(order_date)\"],\n  \"$orderBy\": [{\"column\": \"sale_date\", \"order\": \"asc\"}]\n}"
  },
  "userParams": {
    "startDate": "",
    "endDate": ""
  }
}

Screen with Date Filters:

{
  "filters": [
    {
      "field": "startDate",
      "label": "From Date",
      "type": "date",
      "required": true,
      "defaultValue": "{{today - 30 days}}"
    },
    {
      "field": "endDate",
      "label": "To Date",
      "type": "date",
      "required": true,
      "defaultValue": "{{today}}"
    }
  ]
}

{
  "filters": [
    {
      "field": "category_id",
      "label": "Category",
      "type": "dropdown",
      "entityKey": "category",
      "multiple": false
    },
    {
      "field": "status",
      "label": "Status",
      "type": "dropdown",
      "options": ["Pending", "Processing", "Completed", "Cancelled"],
      "multiple": true
    }
  ]
}

Numeric Range Parameters

{
  "filters": [
    {
      "field": "minAmount",
      "label": "Minimum Amount",
      "type": "number",
      "placeholder": "0.00"
    },
    {
      "field": "maxAmount",
      "label": "Maximum Amount",
      "type": "number",
      "placeholder": "10000.00"
    }
  ]
}

Chart Visualizations

Creating a Chart Report

File: src/screens/monthly_revenue_chart.json

{
  "head": {
    "title": "Monthly Revenue Chart",
    "key": "monthly_revenue_chart",
    "route": "monthly-revenue-chart",
    "app": "reports",
    "icon": "LineChartOutlined"
  },
  "screenType": "chartView",
  "listQuery": "getmonthlyrevenue",
  "chartConfiguration": {
    "type": "line",
    "xAxis": {
      "field": "month",
      "label": "Month"
    },
    "yAxis": [
      {
        "field": "revenue",
        "label": "Revenue",
        "format": "currency"
      }
    ],
    "options": {
      "smooth": true,
      "showPoints": true,
      "showGrid": true,
      "legend": {
        "position": "top"
      }
    }
  },
  "filters": [
    {
      "field": "year",
      "label": "Year",
      "type": "dropdown",
      "options": ["2023", "2024", "2025"]
    }
  ]
}

Chart Types

Bar Chart

{
  "chartConfiguration": {
    "type": "bar",
    "xAxis": {
      "field": "category_name",
      "label": "Category"
    },
    "yAxis": [
      {
        "field": "total_sales",
        "label": "Sales",
        "format": "currency"
      }
    ],
    "options": {
      "horizontal": false,
      "stacked": false
    }
  }
}

Pie Chart

{
  "chartConfiguration": {
    "type": "pie",
    "valueField": "revenue",
    "labelField": "category_name",
    "options": {
      "showPercentage": true,
      "showLegend": true,
      "donut": false
    }
  }
}

Line Chart

{
  "chartConfiguration": {
    "type": "line",
    "xAxis": {
      "field": "date",
      "label": "Date",
      "format": "date"
    },
    "yAxis": [
      {
        "field": "revenue",
        "label": "Revenue",
        "format": "currency"
      },
      {
        "field": "order_count",
        "label": "Orders",
        "format": "number"
      }
    ],
    "options": {
      "smooth": true,
      "area": false
    }
  }
}

Multi-Series Chart

{
  "chartConfiguration": {
    "type": "line",
    "xAxis": {
      "field": "month",
      "label": "Month"
    },
    "series": [
      {
        "field": "online_sales",
        "label": "Online Sales",
        "color": "#1890ff"
      },
      {
        "field": "retail_sales",
        "label": "Retail Sales",
        "color": "#52c41a"
      },
      {
        "field": "wholesale_sales",
        "label": "Wholesale Sales",
        "color": "#faad14"
      }
    ]
  }
}

Creating Dashboards

Dashboards combine multiple reports and visualizations on a single screen.

Dashboard Structure

File: src/screens/sales_dashboard.json

{
  "head": {
    "title": "Sales Dashboard",
    "key": "sales_dashboard",
    "route": "sales-dashboard",
    "app": "reports",
    "icon": "DashboardOutlined"
  },
  "screenType": "dashboard",
  "layout": {
    "columns": 12,
    "rowHeight": 60
  },
  "widgets": [
    {
      "key": "total_revenue",
      "title": "Total Revenue",
      "type": "metric",
      "query": "gettotalrevenue",
      "position": {
        "x": 0,
        "y": 0,
        "w": 3,
        "h": 2
      },
      "configuration": {
        "valueField": "total_revenue",
        "format": "currency",
        "trend": {
          "field": "revenue_change",
          "format": "percentage"
        },
        "icon": "DollarOutlined",
        "color": "green"
      }
    },
    {
      "key": "order_count",
      "title": "Total Orders",
      "type": "metric",
      "query": "gettotalorders",
      "position": {
        "x": 3,
        "y": 0,
        "w": 3,
        "h": 2
      },
      "configuration": {
        "valueField": "order_count",
        "format": "number",
        "icon": "ShoppingCartOutlined",
        "color": "blue"
      }
    },
    {
      "key": "avg_order_value",
      "title": "Avg Order Value",
      "type": "metric",
      "query": "getavgordervalue",
      "position": {
        "x": 6,
        "y": 0,
        "w": 3,
        "h": 2
      },
      "configuration": {
        "valueField": "avg_value",
        "format": "currency",
        "icon": "RiseOutlined",
        "color": "purple"
      }
    },
    {
      "key": "conversion_rate",
      "title": "Conversion Rate",
      "type": "metric",
      "query": "getconversionrate",
      "position": {
        "x": 9,
        "y": 0,
        "w": 3,
        "h": 2
      },
      "configuration": {
        "valueField": "conversion_rate",
        "format": "percentage",
        "icon": "FunnelPlotOutlined",
        "color": "orange"
      }
    },
    {
      "key": "revenue_chart",
      "title": "Revenue Trend",
      "type": "chart",
      "query": "getrevenuetrend",
      "position": {
        "x": 0,
        "y": 2,
        "w": 8,
        "h": 4
      },
      "configuration": {
        "chartType": "line",
        "xAxis": "date",
        "yAxis": "revenue"
      }
    },
    {
      "key": "top_products",
      "title": "Top Products",
      "type": "table",
      "query": "gettopproducts",
      "position": {
        "x": 8,
        "y": 2,
        "w": 4,
        "h": 4
      },
      "configuration": {
        "columns": [
          {"field": "product_name", "label": "Product"},
          {"field": "sales", "label": "Sales", "format": "currency"}
        ],
        "limit": 5
      }
    },
    {
      "key": "sales_by_category",
      "title": "Sales by Category",
      "type": "chart",
      "query": "getsalesbycategory",
      "position": {
        "x": 0,
        "y": 6,
        "w": 6,
        "h": 4
      },
      "configuration": {
        "chartType": "pie",
        "valueField": "revenue",
        "labelField": "category_name"
      }
    },
    {
      "key": "recent_orders",
      "title": "Recent Orders",
      "type": "table",
      "query": "getrecentorders",
      "position": {
        "x": 6,
        "y": 6,
        "w": 6,
        "h": 4
      },
      "configuration": {
        "columns": [
          {"field": "order_number", "label": "Order #"},
          {"field": "customer_name", "label": "Customer"},
          {"field": "total", "label": "Total", "format": "currency"},
          {"field": "status", "label": "Status"}
        ],
        "limit": 10
      }
    }
  ],
  "refreshInterval": 300000,
  "filters": [
    {
      "field": "dateRange",
      "label": "Date Range",
      "type": "dateRange",
      "defaultValue": "last30days"
    }
  ]
}

Single-value KPI displays:

{
  "type": "metric",
  "configuration": {
    "valueField": "total_revenue",
    "format": "currency",
    "trend": {
      "field": "change",
      "format": "percentage"
    },
    "icon": "DollarOutlined",
    "color": "green"
  }
}

Visual data representations:

{
  "type": "chart",
  "configuration": {
    "chartType": "bar",
    "xAxis": "category",
    "yAxis": "sales"
  }
}

Tabular data displays:

{
  "type": "table",
  "configuration": {
    "columns": [
      {"field": "name", "label": "Name"},
      {"field": "value", "label": "Value"}
    ],
    "limit": 10
  }
}

Exporting Reports

Export Formats

Stack9 supports multiple export formats:

CSV Export

{
  "actions": [
    {
      "key": "export_csv",
      "label": "Export CSV",
      "type": "export",
      "format": "csv",
      "options": {
        "delimiter": ",",
        "includeHeaders": true,
        "encoding": "utf-8"
      }
    }
  ]
}

Excel Export

{
  "actions": [
    {
      "key": "export_excel",
      "label": "Export Excel",
      "type": "export",
      "format": "xlsx",
      "options": {
        "sheetName": "Sales Report",
        "includeCharts": true,
        "formatting": true
      }
    }
  ]
}

PDF Export

{
  "actions": [
    {
      "key": "export_pdf",
      "label": "Export PDF",
      "type": "export",
      "format": "pdf",
      "options": {
        "orientation": "landscape",
        "pageSize": "A4",
        "includeHeader": true,
        "includeFooter": true,
        "headerText": "Sales Report - {{company_name}}",
        "footerText": "Generated on {{current_date}}"
      }
    }
  ]
}

Custom Export Templates

Create custom export templates for PDF reports:

File: src/document-templates/sales_report_template.html

<!DOCTYPE html>
<html>
<head>
  <style>
    body { font-family: Arial, sans-serif; }
    .header { text-align: center; margin-bottom: 20px; }
    .summary { margin: 20px 0; }
    table { width: 100%; border-collapse: collapse; }
    th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
    th { background-color: #4CAF50; color: white; }
  </style>
</head>
<body>
  <div class="header">
    <h1>Sales Summary Report</h1>
    <p>Period: {{startDate}} to {{endDate}}</p>
  </div>

  <div class="summary">
    <h2>Summary</h2>
    <p>Total Revenue: {{totalRevenue | currency}}</p>
    <p>Total Orders: {{totalOrders | number}}</p>
    <p>Average Order Value: {{avgOrderValue | currency}}</p>
  </div>

  <h2>Sales by Category</h2>
  <table>
    <thead>
      <tr>
        <th>Category</th>
        <th>Orders</th>
        <th>Revenue</th>
      </tr>
    </thead>
    <tbody>
      {{#each data}}
      <tr>
        <td>{{category_name}}</td>
        <td>{{order_count}}</td>
        <td>{{total_revenue | currency}}</td>
      </tr>
      {{/each}}
    </tbody>
  </table>
</body>
</html>

Reference in Screen:

{
  "actions": [
    {
      "key": "export_custom_pdf",
      "label": "Export Report",
      "type": "export",
      "format": "pdf",
      "template": "sales_report_template"
    }
  ]
}

Scheduling Reports

Automated Report Generation

File: src/automations/daily_sales_report.json

{
  "key": "daily_sales_report",
  "name": "Daily Sales Report",
  "triggerType": "schedule",
  "schedule": {
    "cron": "0 8 * * *",
    "timezone": "America/New_York"
  },
  "actions": [
    {
      "type": "generateReport",
      "reportKey": "sales_summary_report",
      "parameters": {
        "startDate": "{{yesterday}}",
        "endDate": "{{yesterday}}"
      },
      "exportFormat": "pdf",
      "outputPath": "/reports/daily/sales-{{date}}.pdf"
    },
    {
      "type": "sendEmail",
      "to": ["sales-team@company.com"],
      "subject": "Daily Sales Report - {{date}}",
      "body": "Please find attached the daily sales report.",
      "attachments": [
        {
          "path": "/reports/daily/sales-{{date}}.pdf",
          "name": "DailySalesReport.pdf"
        }
      ]
    }
  ]
}

Schedule Configuration

Daily Reports

{
  "schedule": {
    "cron": "0 8 * * *",
    "timezone": "America/New_York"
  }
}

Weekly Reports

{
  "schedule": {
    "cron": "0 8 * * 1",
    "timezone": "America/New_York"
  }
}

Monthly Reports

{
  "schedule": {
    "cron": "0 8 1 * *",
    "timezone": "America/New_York"
  }
}

Advanced Report Patterns

Pattern 1: Drill-Down Reports

Master report with links to detail reports:

{
  "columnsConfiguration": [
    {
      "field": "category_name",
      "label": "Category",
      "value": "{{category_name}}",
      "renderAs": "Text",
      "options": {
        "linkProp": "/reports/category-detail-report?category_id={{category_id}}"
      }
    }
  ]
}

Pattern 2: Conditional Formatting

Highlight values based on conditions:

{
  "field": "revenue",
  "label": "Revenue",
  "value": "{{revenue}}",
  "renderAs": "Currency",
  "conditionalFormatting": [
    {
      "condition": "{{revenue}} >= 100000",
      "style": {
        "backgroundColor": "#d4edda",
        "color": "#155724"
      }
    },
    {
      "condition": "{{revenue}} < 50000",
      "style": {
        "backgroundColor": "#f8d7da",
        "color": "#721c24"
      }
    }
  ]
}

Pattern 3: Calculated Fields

Add calculated columns:

{
  "field": "profit_margin",
  "label": "Profit Margin",
  "value": "{{((revenue - cost) / revenue * 100).toFixed(2)}}%",
  "renderAs": "Text"
}

Pattern 4: Running Totals

Show cumulative values:

{
  "key": "getrunningtotals",
  "queryTemplate": {
    "method": "post",
    "path": "/sales_order/search",
    "bodyParams": "{\n  \"$select\": [\n    \"order_date\",\n    \"total_amount\",\n    \"SUM(total_amount) OVER (ORDER BY order_date) as running_total\"\n  ],\n  \"$orderBy\": [{\"column\": \"order_date\", \"order\": \"asc\"}]\n}"
  }
}

Report Performance Optimization

1. Use Indexed Fields

{
  "$where": {
    "order_date": {
      "$gte": "{{startDate}}"
    },
    "status": "Completed"
  }
}

Ensure order_date and status are indexed in entity definition.

2. Limit Result Sets

{
  "$limit": 1000,
  "$offset": 0
}

3. Select Only Needed Fields

{
  "$select": ["id", "name", "total"],
  // ❌ Don't use: "$select": ["*"]
}

4. Cache Report Results

{
  "caching": {
    "enabled": true,
    "ttl": 300,
    "key": "sales_summary_{{startDate}}_{{endDate}}"
  }
}

5. Use Materialized Views

For complex reports, create materialized views:

File: src/database/views/sales_summary_view.sql

CREATE MATERIALIZED VIEW sales_summary_by_category AS
SELECT
  c.id as category_id,
  c.name as category_name,
  COUNT(so.id) as order_count,
  SUM(so.total_amount) as total_revenue,
  AVG(so.total_amount) as avg_order_value
FROM categories c
LEFT JOIN sales_orders so ON so.category_id = c.id
WHERE so._is_deleted = false AND so.status = 'Completed'
GROUP BY c.id, c.name;

Query the view instead of calculating on-the-fly.

Best Practices

1. Use Meaningful Report Names

{
  "title": "Monthly Sales by Region Report"  // ✅ Clear
  "title": "Report 1"                        // ❌ Unclear
}

2. Provide Default Parameters

{
  "filters": [
    {
      "field": "startDate",
      "defaultValue": "{{today - 30 days}}"
    }
  ]
}

3. Include Summary Totals

{
  "totals": {
    "enabled": true,
    "columns": [
      {"field": "revenue", "type": "sum"},
      {"field": "orders", "type": "sum"}
    ]
  }
}

4. Add Export Options

Always provide CSV/Excel export for tabular reports.

5. Use Appropriate Visualizations

Tables - Detailed data, comparisons
Bar Charts - Category comparisons
Line Charts - Trends over time
Pie Charts - Part-to-whole relationships
Metrics - Single KPI values

6. Test with Large Datasets

Ensure reports perform well with production data volumes.

Troubleshooting

Report Query Returns No Data

Problem: Report shows "No data available"

Solutions:

Check date range parameters
Verify $where conditions
Check data exists in database
Test query directly in API

Aggregation Not Working

Problem: SUM/COUNT returns incorrect values

Solutions:

Verify $groupBy includes all non-aggregated fields
Check for null values
Ensure proper data types
Test query in SQL directly

Export Fails

Problem: PDF/Excel export fails or times out

Solutions:

Reduce result set with $limit
Remove heavy calculations
Check memory limits
Use background job for large exports

Problem: Widget shows loading spinner indefinitely

Solutions:

Check widget query exists
Verify query parameters
Check browser console for errors
Ensure query returns expected data structure

Complete Example: Sales Analytics Dashboard

Let's build a complete sales analytics system.

Queries

File: src/query-library/getdashboardmetrics.json

{
  "key": "getdashboardmetrics",
  "name": "getDashboardMetrics",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/sales_order/search",
    "bodyParams": "{\n  \"$select\": [\n    \"COUNT(*) as total_orders\",\n    \"SUM(total_amount) as total_revenue\",\n    \"AVG(total_amount) as avg_order_value\",\n    \"COUNT(DISTINCT customer_id) as unique_customers\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"status\": \"Completed\",\n    \"order_date\": {\n      \"$gte\": \"{{startDate}}\",\n      \"$lte\": \"{{endDate}}\"\n    }\n  }\n}"
  },
  "userParams": {
    "startDate": "",
    "endDate": ""
  }
}

File: src/query-library/getsalestrend.json

{
  "key": "getsalestrend",
  "name": "getSalesTrend",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/sales_order/search",
    "bodyParams": "{\n  \"$select\": [\n    \"DATE(order_date) as date\",\n    \"COUNT(*) as orders\",\n    \"SUM(total_amount) as revenue\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"order_date\": {\n      \"$gte\": \"{{startDate}}\",\n      \"$lte\": \"{{endDate}}\"\n    }\n  },\n  \"$groupBy\": [\"DATE(order_date)\"],\n  \"$orderBy\": [{\"column\": \"date\", \"order\": \"asc\"}]\n}"
  },
  "userParams": {
    "startDate": "",
    "endDate": ""
  }
}

Dashboard Screen

File: src/screens/sales_analytics_dashboard.json

Complete dashboard combining all elements discussed above.

Summary

You now understand:

✅ Report Types - Tabular, charts, dashboards ✅ Aggregations - COUNT, SUM, AVG, MIN, MAX ✅ Grouping - Single and multi-level grouping ✅ Parameters - Date ranges, dropdowns, filters ✅ Visualizations - Bar, line, pie charts ✅ Dashboards - Multi-widget layouts ✅ Exports - PDF, Excel, CSV formats ✅ Scheduling - Automated report generation ✅ Optimization - Performance best practices

Next Steps

Performance Optimization - Optimize report queries
Building Workflows - Automate report distribution
Document Templates - Custom report templates
Custom Queries - Advanced query techniques

What You'll Learn​

Prerequisites​

Understanding Reports in Stack9​

Report Types​

1. Tabular Reports​

2. Chart Reports​

3. Dashboard Reports​

4. Scheduled Reports​

Building Your First Report​

Step 1: Create the Report Query​

Step 2: Create the Report Screen​

Aggregation Functions​

COUNT - Count Records​

SUM - Total Values​

AVG - Average Values​

MIN/MAX - Minimum/Maximum​

Multiple Aggregations​

Grouping Data​

Single-Level Grouping​

Multi-Level Grouping​

Grouping with Date Functions​

Report Parameters​

Date Range Parameters​

Dropdown Parameters​

Numeric Range Parameters​

Chart Visualizations​

Creating a Chart Report​

Chart Types​

Bar Chart​

Pie Chart​

Line Chart​

Multi-Series Chart​

Creating Dashboards​

Dashboard Structure​

Widget Types​

Metric Widget​

Chart Widget​

Table Widget​

Exporting Reports​

Export Formats​

CSV Export​

Excel Export​

PDF Export​

Custom Export Templates​

Scheduling Reports​

Automated Report Generation​

Schedule Configuration​

Daily Reports​

Weekly Reports​

Monthly Reports​

Advanced Report Patterns​

Pattern 1: Drill-Down Reports​

Pattern 2: Conditional Formatting​

Pattern 3: Calculated Fields​

Pattern 4: Running Totals​

Report Performance Optimization​

1. Use Indexed Fields​

2. Limit Result Sets​

3. Select Only Needed Fields​

4. Cache Report Results​

5. Use Materialized Views​

Best Practices​

1. Use Meaningful Report Names​

2. Provide Default Parameters​

3. Include Summary Totals​

4. Add Export Options​

5. Use Appropriate Visualizations​

6. Test with Large Datasets​

Troubleshooting​

Report Query Returns No Data​

Aggregation Not Working​

Export Fails​

Dashboard Widget Not Loading​

Complete Example: Sales Analytics Dashboard​

Queries​

Dashboard Screen​

Summary​

Next Steps​

What You'll Learn

Prerequisites

Understanding Reports in Stack9

Report Types

1. Tabular Reports

2. Chart Reports

3. Dashboard Reports

4. Scheduled Reports

Building Your First Report

Step 1: Create the Report Query

Step 2: Create the Report Screen

Aggregation Functions

COUNT - Count Records

SUM - Total Values

AVG - Average Values

MIN/MAX - Minimum/Maximum

Multiple Aggregations

Grouping Data

Single-Level Grouping

Multi-Level Grouping

Grouping with Date Functions

Report Parameters

Date Range Parameters

Dropdown Parameters

Numeric Range Parameters

Chart Visualizations

Creating a Chart Report

Chart Types

Bar Chart

Pie Chart

Line Chart

Multi-Series Chart

Creating Dashboards

Dashboard Structure

Widget Types

Metric Widget

Chart Widget

Table Widget

Exporting Reports

Export Formats

CSV Export

Excel Export

PDF Export

Custom Export Templates

Scheduling Reports

Automated Report Generation

Schedule Configuration

Daily Reports

Weekly Reports

Monthly Reports

Advanced Report Patterns

Pattern 1: Drill-Down Reports

Pattern 2: Conditional Formatting

Pattern 3: Calculated Fields

Pattern 4: Running Totals

Report Performance Optimization

1. Use Indexed Fields

2. Limit Result Sets

3. Select Only Needed Fields

4. Cache Report Results

5. Use Materialized Views

Best Practices

1. Use Meaningful Report Names

2. Provide Default Parameters

3. Include Summary Totals

4. Add Export Options

5. Use Appropriate Visualizations

6. Test with Large Datasets

Troubleshooting

Report Query Returns No Data

Aggregation Not Working

Export Fails

Dashboard Widget Not Loading

Complete Example: Sales Analytics Dashboard

Queries

Dashboard Screen

Summary

Next Steps