Task #2
Updated by Rajesh Gotur 21 days ago
# MIL Power Dashboard
## Project Overview
The MIL Power Dashboard is a centralized reporting and analytics solution developed to provide real-time visibility into the operational activities of the MIL Power application. The dashboard consolidates key business metrics from multiple DocTypes into a single interface, enabling users and management to monitor business operations, track document statuses, and make informed decisions without manually navigating through multiple reports.
The dashboard eliminates the need to open individual documents or reports to understand the current business state by presenting summarized information through interactive cards, charts, tables, and KPI widgets.
---
# Objective
The primary objective of the dashboard is to:
- Provide a centralized business overview.
- Display real-time operational statistics.
- Improve management visibility.
- Reduce manual report generation.
- Monitor document workflow statuses.
- Support business decision-making.
- Improve productivity through visual analytics.
---
# Business Problem
Prior to the dashboard implementation, users had to:
- Navigate to multiple DocTypes.
- Apply filters manually.
- Generate reports individually.
- Export data for analysis.
- Calculate business metrics manually.
- Track workflow progress across multiple screens.
This process was time-consuming and made it difficult for management to obtain a consolidated view of business operations.
---
# Solution
The dashboard consolidates information from various business modules into a single interactive page.
Instead of opening multiple reports, users can instantly monitor:
- Total Documents
- Pending Documents
- Approved Documents
- Draft Records
- Completed Transactions
- Workflow Status Distribution
- Daily Activity
- Monthly Trends
- Recent Business Activity
---
# Dashboard Features
## KPI Cards
The dashboard displays important business indicators including:
- Total Bill of Entry
- Total Lot Numbers
- Total CGRN
- Pending Review
- Reviewed Documents
- Approved Records
- Completed Transactions
---
## Charts & Visualizations
The dashboard provides visual insights through:
- Status Distribution Chart
- Monthly Transaction Trend
- Daily Activity Chart
- Workflow Progress
- Warehouse Distribution
- Inventory Summary
These visualizations enable users to quickly understand business performance.
---
## Summary Tables
The dashboard displays summarized information including:
- Recent Bill of Entry
- Recent CGRN
- Recent Lot Numbers
- Pending Approvals
- Latest Transactions
---
## Filters
Users can filter dashboard information using:
- Date Range
- Warehouse
- Workflow Status
- Document Type
- Customer
- Bill of Entry
---
## Real-Time Updates
The dashboard retrieves the latest information from the database and refreshes business metrics whenever users access the page.
---
# Technical Implementation
The dashboard was developed using:
- Frappe Dashboard Components
- Query Reports
- SQL Queries
- Python Backend APIs
- JavaScript
- Charts
- Dashboard Cards
Business information is fetched directly from the application database and displayed in an optimized format.
---
# Benefits
- Centralized business monitoring
- Improved management visibility
- Faster decision making
- Real-time business analytics
- Reduced manual reporting
- Better workflow tracking
- Interactive visualizations
- Improved user experience
---
# Future Enhancements
- Export Dashboard as PDF
- Scheduled Email Reports
- Interactive Drill-down Reports
- Custom Dashboard Filters
- Role-based Dashboard Views
- Mobile Responsive Dashboard
- AI-powered Business Insights
---
# Technologies Used
- Frappe Framework
- Python
- SQL
- JavaScript
- HTML
- CSS
- Dashboard Charts
---
# Conclusion
The MIL Power Dashboard provides a centralized, real-time business monitoring solution by consolidating operational data into a single interactive interface. It enables users and management to track business performance, monitor workflow progress, and access key business metrics without navigating multiple modules, significantly improving reporting efficiency and decision-making.
# MIL Power Query Reports
## Project Overview
The MIL Power Query Reports were developed to provide users with fast, accurate, and customizable access to business data across multiple modules within the application. These reports retrieve information directly from the database using optimized SQL queries, allowing users to analyze operational data without manually searching individual records.
The reports support business operations by presenting structured information with filtering, grouping, and export capabilities for day-to-day reporting and management analysis.
---
# Objective
The primary objective of the query reports is to:
- Provide fast access to business information.
- Reduce manual data extraction.
- Generate accurate reports directly from the database.
- Support operational and management reporting.
- Enable filtering and analysis of large datasets.
- Improve reporting efficiency.
---
# Business Problem
Before implementing the query reports, users had to:
- Open individual documents.
- Search records manually.
- Apply multiple filters.
- Export data repeatedly.
- Perform calculations manually.
- Consolidate information using spreadsheets.
This approach was inefficient and became increasingly difficult as business data continued to grow.
---
# Solution
The query reports retrieve business information directly from the database using SQL queries.
Each report is designed to display meaningful business information in a structured format while supporting user-defined filters and sorting.
The reports eliminate manual data compilation and provide instant access to operational information.
---
# Reports Developed
The reporting module includes multiple business reports such as:
- Bill of Entry Reports
- Lot Number Reports
- Customer Goods Receipt Reports
- Inventory Reports
- Warehouse Reports
- Transaction Reports
- Status Reports
- Exception Reports
Each report is designed to address specific business requirements.
---
# Report Features
## Dynamic Filters
Reports support multiple filtering options including:
- Date Range
- Bill of Entry
- Warehouse
- Status
- Customer
- Product
- Transaction ID
---
## Optimized SQL Queries
The reports use optimized SQL queries to:
- Retrieve business data efficiently.
- Minimize database load.
- Handle large datasets.
- Improve report performance.
---
## Data Presentation
Reports display information in structured tables with:
- Sorting
- Searching
- Pagination
- Grouping
- Totals
- Calculated Columns
---
## Export Options
Users can export report data for further analysis in formats such as:
- Excel
- CSV
- PDF (where supported)
---
# Technical Implementation
The reports were implemented using:
- Frappe Query Reports
- SQL
- MariaDB
- Python
- Report Filters
- Dynamic Query Conditions
Query optimization techniques were applied to improve report execution time and scalability.
---
# Benefits
- Faster report generation
- Reduced manual effort
- Accurate business data
- Dynamic filtering
- Better operational visibility
- Improved reporting performance
- Scalable reporting solution
- Easy data export
---
# Future Enhancements
- Drill-down Reports
- Interactive Charts
- Scheduled Report Generation
- Email Report Distribution
- Dashboard Integration
- Custom Report Builder
- Advanced Analytics
---
# Technologies Used
- Frappe Framework
- SQL
- MariaDB
- Python
- JavaScript
- Report Builder
---
# Conclusion
The MIL Power Query Reports provide a scalable and efficient reporting solution by retrieving business information directly from the database using optimized SQL queries. The reports enable users to quickly analyze operational data, apply dynamic filters, export results, and make informed business decisions while reducing manual reporting effort.
# BOE DTA Summary Report
## Jira Story Description
### Title
**Create BOE DTA Summary Query Report with BOE-Level Consolidation and DTA Details**
---
## 1. Objective
Create a Frappe Query Report that provides a consolidated view of **Import BE (BOE)** and its associated **DTA Sale / House BE** details in a single row.
The report is designed so that:
- One Import BE is displayed as one report row.
- Multiple DTA Sales associated with the same BOE are consolidated into the same row.
- HAWB / HBL No. is used as the relationship key between BOE Shipment and DTA Sale Shipment.
- Multiple values are concatenated using `, ` as the separator.
- Repeated values, especially dates, are removed using `DISTINCT`.
- BOE and DTA dates are displayed separately with clear headers.
- The report follows the required Excel/report format.
---
## 2. Business Requirement
The requirement was to consolidate BOE and DTA Sale information because the underlying DocTypes contain one-to-many relationships.
For example, one Import BE may have multiple DTA Sales:
```text
Import BE
|
+-- DTA Sale 1
+-- DTA Sale 2
+-- DTA Sale 3
```
Instead of displaying three separate report rows, the report displays the DTA values in one row against the Import BE.
Example:
| Import BE | House BE | Request Assessment Date |
|---|---|---|
| BOE-001 | DTA-001 | 18-06-2020 |
| BOE-001 | DTA-002 | 18-06-2020 |
| BOE-001 | DTA-003 | 20-06-2020 |
Report output:
| Import BE ID | House BE No | Request Assessment Date (House BE) |
|---|---|---|
| BOE-001 | DTA-001, DTA-002, DTA-003 | 18-06-2020, 20-06-2020 |
The same date is therefore shown only once.
---
## 3. Report Type
**Frappe Query Report**
Report Name:
```text
BOE DTA Summary Report
```
The report uses SQL directly to retrieve and consolidate information from the BOE Import and DTA Sale DocTypes.
---
## 4. Main DocTypes Used
### Parent DocTypes
```text
FTWZ BOE Import
FTWZ DTA Sale
```
### Child DocTypes
```text
BOE Import Shipment
DTA Sale Shipments
```
---
## 5. Relationship Between BOE and DTA
The primary relationship used for mapping the records is:
```text
BOE Import Shipment.hawb_hbl_no
=
DTA Sale Shipments.hawb_hbl_no
```
The relationship is:
```text
FTWZ BOE Import
|
| parent
v
BOE Import Shipment
|
| HAWB / HBL No.
v
DTA Sale Shipments
|
| parent
v
FTWZ DTA Sale
```
HAWB / HBL No. is therefore used to identify the DTA Sales associated with the Import BE shipment.
---
## 6. Report Consolidation Logic
The query uses `GROUP_CONCAT()` to combine multiple related values into a single report cell.
Example:
```sql
GROUP_CONCAT(
DISTINCT d.name
ORDER BY d.name
SEPARATOR ', '
)
```
Multiple values such as:
```text
DTA-00001
DTA-00002
DTA-00003
```
are displayed as:
```text
DTA-00001, DTA-00002, DTA-00003
```
This keeps all related DTA information in the same Import BE row.
---
## 7. Duplicate Removal
Duplicate values are removed using `DISTINCT` inside `GROUP_CONCAT()`.
For example:
```sql
GROUP_CONCAT(
DISTINCT DATE_FORMAT(
d.request_assessment_date,
'%%d-%%m-%%Y'
)
ORDER BY d.request_assessment_date
SEPARATOR ', '
)
```
If multiple DTA Sales contain:
```text
18-06-2020
18-06-2020
18-06-2020
```
the report displays:
```text
18-06-2020
```
If the dates are:
```text
18-06-2020
18-06-2020
20-06-2020
```
the report displays:
```text
18-06-2020, 20-06-2020
```
This logic is applied to the repeating date/value fields included in the report.
---
## 8. DTA Aggregation
DTA records are aggregated before being joined with the BOE data.
The DTA summary is grouped by HAWB / HBL No.:
```sql
LEFT JOIN (
SELECT
...
FROM `tabFTWZ DTA Sale` d
INNER JOIN `tabDTA Sale Shipments` ds
ON ds.parent = d.name
GROUP BY
ds.hawb_hbl_no
) dta_summary
```
This allows multiple DTA Sales belonging to the same HAWB to be consolidated before the final BOE-level grouping.
---
## 9. BOE-Level Grouping
The final report is grouped at the Import BE level:
```sql
GROUP BY
boe.name,
boe.transaction_id,
boe.transaction_date,
boe.request_out_of_charge_date,
boe.request_assessment_date,
boe.request_submission_date
```
This ensures that the Import BE remains the primary report row.
Conceptually:
```text
One Import BE
|
+-- Multiple BOE shipment values
+-- Multiple HAWB values
+-- Multiple DTA Sales
+-- Multiple DTA dates
|
v
One consolidated report row
```
---
## 10. Date Formatting
Dates are displayed in:
```text
DD-MM-YYYY
```
format.
SQL used:
```sql
DATE_FORMAT(
field_name,
'%%d-%%m-%%Y'
)
```
Example:
```text
Database: 2020-06-18
Report: 18-06-2020
```
---
## 11. Final Report Columns
The report follows the required Excel/report sequence:
| # | Report Header | Source Field |
|---|---|---|
| 1 | **Import BE ID** | `boe.name` |
| 2 | **Import BE No** | `boe.transaction_id` |
| 3 | **Import BE Date** | `boe.transaction_date` |
| 4 | **HAWB / HBL No.** | `boe_ship.hawb_hbl_no` |
| 5 | **MAWB / MBL No.** | `boe_ship.mawb_mbl_no` |
| 6 | **MAWB / MBL Date** | `boe_ship.mawb_mbl_date` |
| 7 | **HAWB / HBL Date** | `boe_ship.hawb_hbl_date` |
| 8 | **IGM Date (Import BE)** | `boe_ship.igm_date` |
| 9 | **Request Out Of Charge Date (Import BE)** | `boe.request_out_of_charge_date` |
| 10 | **Request Assessment Date (Import BE)** | `boe.request_assessment_date` |
| 11 | **Request Submission Date (Import BE)** | `boe.request_submission_date` |
| 12 | **House BE No** | `dta.name` |
| 13 | **House BE Date** | `dta.transaction_date` |
| 14 | **Request Out Of Charge Date (House BE)** | `dta.request_out_of_charge_date` |
| 15 | **Request Assessment Date (House BE)** | `dta.request_assessment_date` |
| 16 | **Request Submission Date (House BE)** | `dta.request_submission_date` |
---
## 12. Frappe Link Field
The Import BE ID is configured as:
```sql
boe.name AS "Import BE ID:Link/FTWZ BOE Import"
```
This makes the Import BE ID clickable in the report and opens the corresponding **FTWZ BOE Import** document.
House BE values are returned as `Data` because multiple DTA records can be concatenated into one cell.
---
## 13. Ordering
The report uses:
```sql
ORDER BY
boe.transaction_id,
boe.name
```
This provides consistent ordering of the Import BE records.
---
## 14. Expected Output
For multiple DTA Sales belonging to the same Import BE:
```text
Import BE: BOE-00001
DTA:
DTA-00001
DTA-00002
DTA-00003
```
the report shows one row with:
```text
DTA-00001, DTA-00002, DTA-00003
```
For duplicate dates:
```text
18-06-2020
18-06-2020
20-06-2020
```
the report shows:
```text
18-06-2020, 20-06-2020
```
---
## 15. Key Technical Concepts
### `LEFT JOIN`
Ensures that an Import BE can still appear even when corresponding DTA information is unavailable.
### `INNER JOIN`
Connects DTA Sale Shipments with the FTWZ DTA Sale parent.
### `GROUP_CONCAT()`
Combines multiple related values into one report cell.
### `DISTINCT`
Removes repeated values, including repeated dates.
### `DATE_FORMAT()`
Formats dates as `DD-MM-YYYY`.
### `GROUP BY`
Consolidates DTA data by HAWB and the final report by Import BE.
### `ORDER BY`
Provides consistent ordering of concatenated values and report rows.
---
## 16. Why DTA Data Is Aggregated Before the Final Join
A direct join between BOE Shipment and DTA Shipment can multiply rows when both sides contain multiple records.
For example:
```text
BOE Shipment 1
BOE Shipment 2
DTA Shipment 1
DTA Shipment 2
```
A direct join can produce multiple combinations.
Therefore, DTA data is first summarized by HAWB:
```text
DTA Shipment
|
v
GROUP BY HAWB
|
v
DTA Summary
|
v
Join with BOE
```
This reduces unnecessary row multiplication and supports the requirement of one consolidated row per Import BE.
---
## 17. Acceptance Criteria
- [x] Query Report named **BOE DTA Summary Report** is created.
- [x] One Import BE is represented by one report row.
- [x] HAWB / HBL No. is used to relate BOE Shipment and DTA Shipment records.
- [x] Multiple DTA Sales are displayed in the same row.
- [x] Multiple House BE numbers are comma-separated.
- [x] Duplicate House BE values are removed.
- [x] Duplicate dates are removed.
- [x] Different dates are retained and comma-separated.
- [x] BOE and House BE dates are displayed separately.
- [x] BOE and House BE request dates are displayed separately.
- [x] Dates are displayed in `DD-MM-YYYY` format.
- [x] Import BE ID is a clickable Frappe Link.
- [x] Report columns follow the required Excel sequence.
- [x] No unnecessary additional report fields are included.
---
## 18. Files / Components Involved
### Report
```text
BOE DTA Summary Report
```
### Main Tables
```text
tabFTWZ BOE Import
tabBOE Import Shipment
tabFTWZ DTA Sale
tabDTA Sale Shipments
```
### Primary Relationship
```text
HAWB / HBL No.
```
---
## 19. Summary
The **BOE DTA Summary Report** was created to provide a consolidated BOE-level view of Import BE and DTA Sale information.
The implementation handles the one-to-many relationships between BOE, shipment records, and DTA Sales by aggregating related DTA information by HAWB before joining it with the BOE shipment data.
The final report provides:
- One consolidated row per Import BE.
- Multiple related DTA values in the same row.
- Comma-separated values for one-to-many relationships.
- Duplicate removal using `DISTINCT`.
- Separate BOE and House BE date columns.
- Required `DD-MM-YYYY` date format.
- Clickable Import BE ID.
- Report columns matching the required Excel sequence.