This guide provides practical instructions for creating, configuring, and managing reports using the ObjReport system - a flexible reporting framework that generates dynamic data visualizations and summaries.
ObjReport provides a database-driven reporting system that supports multiple report types:
Reports are defined in the def_report table and can be accessed via web interfaces, APIs, or programmatically.
data.config-- Define a table report
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery,
Description
) VALUES (
'active_users',
'factory.core',
'TABLE',
'SELECT user_code, email, login_name, created_at
FROM sys_user
WHERE status = "active"
ORDER BY created_at DESC',
'List of all active users'
);
-- Define a value report (count of active users)
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery,
AsWidget,
Description
) VALUES (
'active_user_count',
'factory.core',
'VALUE',
'SELECT COUNT(*) FROM sys_user WHERE status = "active"',
'N',
'Total number of active users'
);
from ObjReport import ObjReport
# Initialize report object
report = ObjReport(0)
# Load and execute a report
report.Read("active_users")
result = report.run()
print(f"Report results: {result}")
Display data in tabular format. See detailed TABLE report guide.
Sources:
Example SQL TABLE:
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'loan_applications',
'factory.core',
'TABLE',
'SELECT
application_id,
customer_name,
loan_amount,
status,
created_at
FROM loan_applications
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC'
);
Example XML TABLE:
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'product_catalog',
'factory.core',
'TABLE',
'<?xml version="1.0"?>
<products>
<product id="P001">
<name>Product A</name>
<price>99.99</price>
<stock>150</stock>
</product>
<product id="P002">
<name>Product B</name>
<price>149.99</price>
<stock>75</stock>
</product>
</products>'
);
Display single values, metrics, or KPIs. See detailed VALUE report guide.
Types:
Example SQL VALUE:
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery,
AsWidget
) VALUES (
'total_revenue_today',
'factory.core',
'VALUE',
'SELECT FORMAT(SUM(amount), 2)
FROM transactions
WHERE DATE(created_at) = CURDATE()',
'Y' -- Render as widget with styling
);
Example EVAL VALUE:
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'credit_score_status',
'factory.core',
'VALUE',
'EVAL: "Approved" if $credit_score$ >= 650 else "Rejected"'
);
Example MARKDOWN VALUE:
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'dashboard_summary',
'factory.core',
'VALUE',
'MARKDOWN:
# Daily Summary
- **Active Users**: $active_users$
- **Revenue**: $$revenue$
- **New Signups**: $new_signups$
Status: **All Systems Operational**'
);
Parameters allow dynamic report filtering using $param_name$ syntax.
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'user_activity',
'factory.core',
'TABLE',
'SELECT
user_code,
action,
created_at
FROM user_activity_log
WHERE user_code = "$user_code$"
AND created_at >= "$start_date$"
AND created_at <= "$end_date$"
ORDER BY created_at DESC'
);
Calling with parameters:
report = ObjReport(0)
report.Read("user_activity")
# Set parameters
params = {
"user_code": "USER001",
"start_date": "2025-12-01",
"end_date": "2025-12-31"
}
# Execute with parameters
result = report.run(params=params)
-- Add parameter validation
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'sales_by_region',
'factory.core',
'TABLE',
'SELECT region, SUM(sales_amount) as total_sales
FROM sales
WHERE region = "$region$"
AND YEAR(sale_date) = $year$
GROUP BY region
HAVING total_sales > 0'
);
Validate before execution:
import re
def validate_params(params):
# Validate year (must be 4 digits)
if not re.match(r'^\d{4}$', str(params.get('year', ''))):
raise ValueError("Invalid year format")
# Validate region (alphanumeric only)
if not re.match(r'^[a-zA-Z0-9_]+$', params.get('region', '')):
raise ValueError("Invalid region format")
return True
# Use validation
params = {"region": "NORTH", "year": 2025}
validate_params(params)
result = report.run(params=params)
Set AsWidget = 'Y' to add HTML styling around values.
UPDATE def_report
SET AsWidget = 'Y'
WHERE Name = 'total_revenue_today';
Widget vs Non-Widget:
AsWidget = 'N': "15,234"
AsWidget = 'Y': "<div class='widget'>$15,234</div>"
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery,
AsWidget
) VALUES (
'kpi_dashboard',
'factory.core',
'VALUE',
'SELECT CONCAT(
"<div class=\"kpi-card\">",
"<h3>", $kpi_title$, "</h3>",
"<div class=\"kpi-value\">", FORMAT($kpi_value$, 0), "</div>",
"<div class=\"kpi-change ", IF($change$ >= 0, "positive", "negative"), "\">",
$change$, "%</div></div>"
)',
'Y'
);
-- Only show user's own data
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'my_applications',
'factory.core',
'TABLE',
'SELECT
application_id,
status,
submitted_date
FROM applications
WHERE user_code = "$current_user$"
ORDER BY submitted_date DESC'
);
Inject current user:
from ObjReport import ObjReport
report = ObjReport(0)
report.set_user("USER001") # Set current user
report.Read("my_applications")
# User code automatically injected
result = report.run()
-- Managers see all, others see only their data
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'team_performance',
'factory.core',
'TABLE',
'SELECT *
FROM performance_metrics
WHERE CASE
WHEN "$user_role$" = "manager" THEN 1=1
ELSE user_code = "$current_user$"
END
ORDER BY created_at DESC'
);
-- Create scheduled report
INSERT INTO def_scheduler (
Name,
Package,
Schedule,
Action,
ActionParams
) VALUES (
'daily_revenue_report',
'factory.core',
'0 8 * * *', -- Daily at 8 AM
'RUN_REPORT',
'{"report_name": "total_revenue_today", "email_to": "finance@company.com"}'
);
from ObjReport import ObjReport
from ObjNotify import ObjNotify
import json
def scheduled_report_export():
"""Generate and email report."""
# Generate report
report = ObjReport(0)
report.Read("monthly_sales_summary")
result = report.run()
# Format results
report_html = format_report_as_html(result)
# Email report
notify = ObjNotify(0)
notify.Run(
notify_code="REPORT_DELIVERY",
message_text=report_html
)
return {"status": "sent", "report": "monthly_sales_summary"}
-- Generate columns based on data
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'pivot_sales_by_month',
'factory.core',
'TABLE',
'SELECT
product_name,
SUM(CASE WHEN MONTH(sale_date) = 1 THEN amount ELSE 0 END) as Jan,
SUM(CASE WHEN MONTH(sale_date) = 2 THEN amount ELSE 0 END) as Feb,
SUM(CASE WHEN MONTH(sale_date) = 3 THEN amount ELSE 0 END) as Mar,
SUM(amount) as Total
FROM sales
WHERE YEAR(sale_date) = $year$
GROUP BY product_name'
);
-- Summary report
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'sales_summary',
'factory.core',
'TABLE',
'SELECT
region,
COUNT(*) as order_count,
SUM(amount) as total_sales,
CONCAT("{report:sales_detail:", region, "}") as "View Details"
FROM orders
GROUP BY region'
);
-- Detail report
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'sales_detail',
'factory.core',
'TABLE',
'SELECT
order_id,
customer_name,
amount,
order_date
FROM orders
WHERE region = "$region$"
ORDER BY order_date DESC'
);
-- Color-code based on values
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery
) VALUES (
'credit_scores',
'factory.core',
'TABLE',
'SELECT
customer_id,
customer_name,
credit_score,
CASE
WHEN credit_score >= 750 THEN "✓ Excellent"
WHEN credit_score >= 650 THEN "○ Good"
WHEN credit_score >= 550 THEN "! Fair"
ELSE "✗ Poor"
END as rating
FROM customers
ORDER BY credit_score DESC'
);
UPDATE def_report
SET cache_duration_seconds = 3600 -- Cache for 1 hour
WHERE Name = 'daily_statistics';
Caching Strategy:
import time
from ObjReport import ObjReport
class CachedReport:
def __init__(self, report_name):
self.report_name = report_name
self.cache = {}
self.cache_duration = 3600 # 1 hour
def get_report(self, params=None):
cache_key = f"{self.report_name}_{str(params)}"
# Check cache
if cache_key in self.cache:
cached_data, cached_time = self.cache[cache_key]
if time.time() - cached_time < self.cache_duration:
return cached_data
# Generate report
report = ObjReport(0)
report.Read(self.report_name)
result = report.run(params=params)
# Store in cache
self.cache[cache_key] = (result, time.time())
return result
Pattern: {scope}_{subject}_{type}
Examples:
- "global_revenue_summary"
- "user_activity_detail"
- "department_expense_kpi"
- "monthly_sales_pivot"
-- Bad: Full table scan
SELECT * FROM transactions WHERE customer_id = "$customer_id$"
-- Good: Indexed lookup with specific columns
SELECT
transaction_id,
amount,
transaction_date
FROM transactions
WHERE customer_id = "$customer_id$"
AND transaction_date >= DATE_SUB(NOW(), INTERVAL 90 DAY)
ORDER BY transaction_date DESC
LIMIT 100
def sanitize_param(value):
"""Sanitize report parameters to prevent SQL injection."""
# Remove dangerous characters
sanitized = re.sub(r'[^\w\s\-@.]', '', str(value))
return sanitized
params = {
"customer_id": sanitize_param(user_input_id),
"start_date": sanitize_param(user_input_date)
}
def safe_report_execution(report_name, params=None):
"""Execute report with error handling."""
try:
report = ObjReport(0)
report.Read(report_name)
result = report.run(params=params)
return {"success": True, "data": result}
except Exception as e:
return {
"success": False,
"error": str(e),
"report": report_name
}
# Use safe execution
result = safe_report_execution("sales_summary", {"region": "NORTH"})
if result["success"]:
print(result["data"])
else:
print(f"Report failed: {result['error']}")
-- Always include descriptive comments
INSERT INTO def_report (
Name,
Package,
ReportType,
ReportQuery,
Description
) VALUES (
'churn_risk_customers',
'factory.core',
'TABLE',
'-- Identifies customers at risk of churning
-- Criteria: No activity in 90 days + declining engagement
SELECT
customer_id,
customer_name,
last_activity_date,
engagement_score,
DATEDIFF(NOW(), last_activity_date) as days_inactive
FROM customers
WHERE last_activity_date < DATE_SUB(NOW(), INTERVAL 90 DAY)
AND engagement_score < 30
ORDER BY days_inactive DESC',
'High-risk churn customers requiring intervention'
);
Check query syntax:
-- Test query independently
SELECT * FROM def_report WHERE Name = 'my_report';
-- Copy ReportQuery and test in MySQL
SELECT ... (paste query here)
Verify parameters:
# Debug parameter substitution
report = ObjReport(0)
report.Read("my_report")
print(f"Query before params: {report.ReportQuery}")
params = {"user_id": "USER001"}
result = report.run(params=params)
print(f"Query after params: {report.get_executed_query()}")
Optimize query:
-- Add indexes
CREATE INDEX idx_created_at ON transactions(created_at);
CREATE INDEX idx_customer_id ON transactions(customer_id);
-- Add query limits
SELECT * FROM transactions
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 1000;
Check parameter syntax:
-- Correct
WHERE user_code = "$user_code$"
-- Incorrect
WHERE user_code = "${user_code}" -- Wrong syntax
WHERE user_code = "%user_code%" -- Wrong syntax