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:

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:

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¶

FTWZ BOE Import
FTWZ DTA Sale

Child DocTypes¶

BOE Import Shipment
DTA Sale Shipments

---

5. Relationship Between BOE and DTA¶

The primary relationship used for mapping the records is:

BOE Import Shipment.hawb_hbl_no
            =
DTA Sale Shipments.hawb_hbl_no

The relationship is:

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:

GROUP_CONCAT(
    DISTINCT d.name
    ORDER BY d.name
    SEPARATOR ', '
)

Multiple values such as:

DTA-00001
DTA-00002
DTA-00003

are displayed as:

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:

GROUP_CONCAT(
    DISTINCT DATE_FORMAT(
        d.request_assessment_date,
        '%%d-%%m-%%Y'
    )
    ORDER BY d.request_assessment_date
    SEPARATOR ', '
)

If multiple DTA Sales contain:

18-06-2020
18-06-2020
18-06-2020

the report displays:

18-06-2020

If the dates are:

18-06-2020
18-06-2020
20-06-2020

the report displays:

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.:

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:

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:

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:

DD-MM-YYYY

format.

SQL used:

DATE_FORMAT(
    field_name,
    '%%d-%%m-%%Y'
)

Example:

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:

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:

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:

Import BE: BOE-00001

DTA:
DTA-00001
DTA-00002
DTA-00003

the report shows one row with:

DTA-00001, DTA-00002, DTA-00003

For duplicate dates:

18-06-2020
18-06-2020
20-06-2020

the report shows:

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:

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:

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¶

• Query Report named BOE DTA Summary Report is created.
• One Import BE is represented by one report row.
• HAWB / HBL No. is used to relate BOE Shipment and DTA Shipment records.
• Multiple DTA Sales are displayed in the same row.
• Multiple House BE numbers are comma-separated.
• Duplicate House BE values are removed.
• Duplicate dates are removed.
• Different dates are retained and comma-separated.
• BOE and House BE dates are displayed separately.
• BOE and House BE request dates are displayed separately.
• Dates are displayed in DD-MM-YYYY format.
• Import BE ID is a clickable Frappe Link.
• Report columns follow the required Excel sequence.
• No unnecessary additional report fields are included.

---

18. Files / Components Involved¶

Report¶

BOE DTA Summary Report

Main Tables¶

tabFTWZ BOE Import
tabBOE Import Shipment
tabFTWZ DTA Sale
tabDTA Sale Shipments

Primary Relationship¶

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.