Actions

Feature #130

closed

RG RG

Feature #90: SLC CANOFY Report Creation , Chart Creation and Designing the Dashboards and Workspaces

Implementation of Query Reports, charts, dashboards, and workspaces from eapx.canofy.tech to lax.canofy.tech

Feature #130: Implementation of Query Reports, charts, dashboards, and workspaces from eapx.canofy.tech to lax.canofy.tech

Added by Rajesh Gotur 10 days ago.

Status:

Closed

Priority:

Normal

Assignee:

Rajesh Gotur

Target version:

Backlogs

Start date:

10/05/2026

Due date:

% Done:

Estimated time:

Spent time:

16:00 h

Description

SLC CANOFY – Reports, Charts, Dashboards, Workspaces & Interactive Workspace Banner¶

Project Overview¶

The SLC CANOFY reporting and dashboard implementation was carried out to provide users and management with a centralized view of operational data available within the CANOFY application.

The work involved creating and modifying Query Reports, adapting SQL logic for the Frappe reporting environment, creating visual charts, assembling Dashboards, and organizing reporting functionality into structured Workspaces. The work also included bringing the reporting and workspace setup available on eapx.canofy.tech into lax.canofy.tech, so the corresponding reports, charts, dashboards, workspaces, and related components were available in the LAX environment.

In addition, an interactive Workspace Quick-Access Banner was created as a Custom HTML Block. The banner presents shortcuts to the available workspaces in one place, allowing users to select a workspace directly instead of navigating through each workspace separately.

The overall objective was to make operational reporting accessible, consistent, and easier to navigate while preserving the required report logic and output.

Objective¶

The main objectives of this implementation were:

Create Query Reports for SLC CANOFY business requirements.
Convert existing SQL/report logic into Frappe-compatible Query Reports.
Retrieve data directly from the relevant CANOFY DocTypes and database tables.
Add required report filters such as date ranges.
Maintain the required report columns and business calculations.
Make reports reusable for different importers and business users.
Create charts based on Query Report data.
Design dashboards using the created reports and charts.
Organize related reports and dashboards into appropriate Workspaces.
Recreate/provide the reporting setup from eapx.canofy.tech in lax.canofy.tech, including the required reports, charts, dashboards, workspaces, and related components.
Create an interactive Custom HTML Block that presents shortcuts to all relevant workspaces in a single banner.
Provide users with a centralized interface for operational monitoring, analysis, and workspace navigation.

Business Requirement¶

The SLC CANOFY application contains a large amount of operational data across different modules and DocTypes.

Previously, users had to navigate through individual documents or rely on manually generated reports to obtain the required information.

The requirement was therefore to create a reporting layer where users could:

Open a report.
Select the required filters.
Retrieve the relevant operational data.
Analyze the results in tabular form.
View important information through charts.
Access related reports from a centralized dashboard/workspace.

Overall Implementation Flow¶

Business Requirement
        │
        ▼
Identify Required Data
        │
        ▼
Prepare SQL Query
        │
        ▼
Convert / Adapt Query for Frappe
        │
        ▼
Create Query Report
        │
        ▼
Add Report Filters
        │
        ▼
Validate Report Output
        │
        ▼
Create Charts
        │
        ▼
Design Dashboard
        │
        ▼
Organize Workspace Setup on EAPX
        │
        ▼
Recreate Reporting Components on LAX
        │
        ▼
Reports, Charts, Dashboards & Workspaces
        │
        ▼
Interactive Workspace Quick-Access Banner
(Custom HTML Block)
        │
        ▼
User Access & Operational Reporting

1. Requirement Analysis¶

The first stage was to understand what information was required from the SLC CANOFY application.

For each reporting requirement, the following were identified:

Required output fields.
Source DocTypes/tables.
Relationships between records.
Required calculations.
Required filters.
Required date conditions.
Grouping requirements.
Sorting requirements.
Business-specific conditions.

The existing SQL/report logic was used as the basis for creating the Frappe Query Reports.

2. SQL Query Preparation¶

SQL queries were prepared to retrieve the required business information directly from the CANOFY database.

The queries were designed around:

Relevant DocTypes.
Parent-child relationships.
Item-level records.
Document-level records.
Date fields.
Status fields.
Business-specific fields.

The required columns were explicitly selected so that the Query Report output matched the expected business format.

3. Conversion to Frappe Query Reports¶

The SQL logic was adapted for use inside the Frappe Query Report environment.

During this process, attention was given to:

Frappe SQL compatibility.
Correct table names.
Correct field names.
Parent-child relationships.
Report filter syntax.
Query parameters.
Required output aliases.

The resulting SQL was implemented as a Query Report within the CANOFY application.

4. Dynamic Report Filters¶

Where reports required user-selected filtering, dynamic filters were incorporated into the Query Reports.

Typical filters included:

From Date
To Date
Document-specific filters
Importer-related filters
Other business-specific parameters

The filters were connected directly to the SQL query so that the report dynamically returned data based on the user's selection.

For reports requiring a date range, the report was structured so that the required date filters were passed into the query rather than relying on hardcoded dates.

5. Removing Hardcoded Business Restrictions¶

During the report conversion, existing SQL logic containing a hardcoded importer restriction was identified.

For example, a condition specifically restricting the report to:

SMARTAGE PROJECTS PRIVATE LIMITED

was not suitable for the final reusable report.

The hardcoded importer restriction was removed while preserving:

Existing output fields.
Existing joins.
Existing calculations.
Existing business conditions.
Existing report logic.

This allowed the report to return information for different importers instead of being restricted to a single company.

6. Query Report Validation¶

After creating each Query Report, the output was validated against the expected business data.

The validation process included checking:

Report execution.
Filter functionality.
Date range behaviour.
Number of records.
Column names.
Column order.
Field values.
Calculated values.
Parent-child relationships.
Duplicate records.
Missing records.

The SQL output was compared against the expected source data to ensure that the report was returning the correct information.

7. Report Output Design¶

The Query Reports were designed to provide business-readable output rather than exposing raw database fields.

Column aliases were used to provide meaningful names to users.

For example:

SELECT
    boe.p_1d_inwdate AS `Inward Date`

This allows the technical database field to be presented using a business-friendly report label.

The final report structure was kept aligned with the required business output.

8. Chart Creation¶

After the Query Reports were completed and validated, charts were created using the report data.

Charts were designed to provide a visual representation of important operational information.

Depending on the report requirement, the chart could represent:

Record counts.
Status distribution.
Date-wise activity.
Importer-wise information.
Warehouse-wise information.
Transaction trends.
Other report-level business metrics.

The chart configuration was based on the underlying Query Report data.

9. Dashboard Design¶

The created Query Reports and charts were then organized into dashboards.

The dashboard was designed to provide a consolidated view of important SLC CANOFY information.

The dashboard structure brings together:

Key Business Information
        │
        ├── Query Reports
        │
        ├── Charts
        │
        ├── Summary Information
        │
        └── Operational Data

This reduces the need for users to open each report independently.

10. Dashboard Layout¶

The dashboard was arranged to make frequently required information easily accessible.

The design approach was based on:

Important information at the top.
Frequently used reports easily accessible.
Charts positioned for quick visual analysis.
Related reports grouped together.
Logical navigation between reporting components.

The dashboard therefore acts as a central reporting interface rather than simply being a collection of independent reports.

11. Environment Implementation — EAPX to LAX¶

The reporting and workspace implementation was not limited to a single CANOFY environment. The reporting setup available on eapx.canofy.tech was used as the reference for making the corresponding functionality available on lax.canofy.tech.

The work included creating/providing the related components in the LAX environment:

Query Reports
Charts based on report data
Dashboards containing relevant reporting components
Workspaces that organize the reporting functionality
Related workspace and dashboard components required for navigation

The intent was to make the reporting experience available in LAX consistent with the setup established in EAPX. This description records the environment-to-environment implementation; it does not imply an automated migration mechanism unless one was separately configured.

eapx.canofy.tech
      │
      │ Reference reporting and workspace setup
      ▼
Review required components
      │
      ▼
Create / configure in lax.canofy.tech
      │
      ├── Query Reports
      ├── Charts
      ├── Dashboards
      └── Workspaces
      │
      ▼
Verify access and navigation in LAX

Validation focus¶

Confirm the required reports are available in LAX.
Confirm the report filters and output work as expected.
Confirm charts use the intended report data.
Confirm dashboards display the required charts and reports.
Confirm the workspaces expose the relevant reporting components.
Confirm the interactive workspace banner links to the intended workspaces.

12. Interactive Workspace Quick-Access Banner¶

An interactive banner was created as a Custom HTML Block to provide a compact, centralized shortcut to the workspaces.

Instead of opening workspaces one by one through the standard navigation, users can view the workspace shortcuts together in one banner and select the workspace they need.

Purpose¶

Present shortcuts to all relevant workspaces in one place.
Make workspace navigation quicker and more convenient.
Provide a single entry point to the organized workspace collection.
Reduce the effort required to locate a particular workspace.

Conceptual layout¶

┌───────────────────────────────────────────────────────────────┐
│                 CANOFY WORKSPACE QUICK ACCESS                 │
├────────────────┬────────────────┬────────────────┬────────────┤
│   Workspace 1  │   Workspace 2  │   Workspace 3  │ Workspace N│
│    Open ↗      │    Open ↗      │    Open ↗      │   Open ↗   │
└────────────────┴────────────────┴────────────────┴────────────┘
          Interactive shortcuts rendered in a Custom HTML Block

The labels above illustrate the concept only; the actual banner uses the workspace shortcuts configured in the CANOFY environment.

How it fits into the reporting experience¶

Reports + Charts + Dashboards
              │
              ▼
         Workspaces
              │
              ▼
  Custom HTML Quick-Access Banner
              │
              ▼
 User selects the required workspace
              │
              ▼
  Navigate to the selected workspace

Implementation notes¶

The banner is implemented as a Custom HTML Block.
Its role is navigation and quick access; it does not replace the underlying Query Reports, charts, dashboards, or workspaces.
The workspace shortcuts should point to the correct workspace destinations in the LAX environment.
After any workspace changes, verify the corresponding shortcut still opens the intended destination.

13. Workspace Design¶

Workspaces were designed to organize the reporting functionality within the SLC CANOFY application.

The Workspace provides users with a structured entry point to access:

Relevant reports.
Dashboards.
Query Reports.
Charts.
Related operational functionality.

The purpose of the Workspace design was to improve navigation and ensure that users could reach the required reporting functionality without searching through individual modules.

14. Report-to-Chart Relationship¶

The implementation follows a relationship between the underlying Query Report and its visualization.

Database
   │
   ▼
SQL Query
   │
   ▼
Query Report
   │
   ├──────────────► Tabular Data
   │
   ▼
Chart
   │
   ▼
Dashboard
   │
   ▼
Workspace

This approach keeps the report as the underlying data source while the chart and dashboard provide a visual presentation of the same business information.

15. Testing and Verification¶

Testing was performed after creating the reports and dashboard components.

The following areas were verified:

Query Report¶

SQL executes successfully.
Filters work correctly.
Date filters return expected results.
Output columns are correct.
Values match source records.
No unintended hardcoded restrictions remain.

Charts¶

Correct report is used as the source.
Chart values correspond to report data.
Categories and counts are displayed correctly.
Chart provides meaningful visual representation.

Dashboard¶

Required charts are displayed.
Reports are accessible.
Dashboard components load correctly.
Information is presented in a logical order.

Workspace¶

Reports are accessible from the intended Workspace.
Dashboard/report navigation works correctly.
Related reporting components are grouped appropriately.

16. Key Implementation Considerations¶

During implementation, the following principles were followed:

Preserve existing business logic.
Avoid unnecessary changes to existing SQL logic.
Keep report output aligned with the original requirement.
Replace hardcoded business restrictions where the report should be reusable.
Use dynamic filters where user input is required.
Validate report results before creating visualizations.
Use Query Reports as the underlying source for reporting visualizations.
Organize related reporting components through dashboards and Workspaces.

17. Benefits¶

The SLC CANOFY reporting implementation provides:

Centralized operational reporting.
Faster access to business information.
Reduced manual data extraction.
Reusable Query Reports.
Dynamic report filtering.
Business-friendly report outputs.
Visual representation through charts.
Centralized dashboards.
Better Workspace navigation.
A single interactive banner with shortcuts to relevant workspaces.
Consistent availability of the reporting setup in the LAX environment based on the EAPX setup.
Improved management visibility.
Reduced dependency on manually prepared reports.

18. End-to-End Reporting Architecture¶

The completed implementation can be represented as:

                SLC CANOFY DATABASE
                         │
                         ▼
                  SQL / Business Logic
                         │
                         ▼
                  Frappe Query Reports
                         │
              ┌──────────┴──────────┐
              │                     │
              ▼                     ▼
        Tabular Reports          Charts
              │                     │
              └──────────┬──────────┘
                         ▼
                     Dashboard
                         │
                         ▼
                     Workspace
                         │
                         ▼
                      End User

Conclusion¶

The SLC CANOFY reporting implementation involved creating a reporting and visualization layer consisting of Query Reports, Charts, Dashboards, and Workspaces. The Query Reports provide structured business data through Frappe-compatible SQL and dynamic filters; charts present important report information visually; and dashboards consolidate reports and charts for monitoring.

The reporting and workspace setup was also implemented from eapx.canofy.tech into lax.canofy.tech, covering the required reports, charts, dashboards, workspaces, and related components. To improve navigation, an interactive Workspace Quick-Access Banner was created as a Custom HTML Block, bringing shortcuts to the relevant workspaces together in one place.

The implementation also included adapting SQL logic to Frappe, validating report outputs, and removing unnecessary hardcoded importer restrictions where reports needed to be reusable across importers.

Overall, the work made operational reports and visualizations available through a structured workspace experience in LAX, with a single interactive banner for faster workspace navigation.

Spent time