Reports

Backend

1. ReportMonthlySales

a. FilterMonthly

Create a FilterMonthly form to function as an input form for the MonthlySales report.

ensure form FilterMonthly
ensure section: Details
ensure field LabelFilterDate kind: label
  label: "Filter Date"
  bold: true
  textPattern: "Filter Date"
ensure field FromDate kind: date
  defaultValue: "startOfMonth"
ensure field ToDate kind: date
  defaultValue: "endOfMonth"

b. OutputMonthlySales

Create an OutputMonthlySales form to function as an output form for the MonthlySales report.

ensure form OutputMonthlySales

// details grid
ensure grid: Details
ensure field CustomerName kind: text label: "Customer Name"
ensure field NumberOfOrder kind: number label: "No. of Order"
ensure field TotalBill kind: number label: "Total Bill" prefix: "₹ "

ensure layoutGrid MonthlySalesTable kind: table
  columnSizeSet: ["Flex", "AutoSize"]
  showComps: [CustomerName, NumberOfOrder, TotalBill]

Add a Summary section to the OutputMonthlySales form to capture and present the overall, aggregated data of the monthly sales.

// summary section
ensure section: Summary
ensure field TotalSale kind: number prefix: "₹ "
ensure field TotalOrders kind: number
ensure field OrdersCancelled kind: number
ensure field NewCustomers kind: number
ensure field LastMonthSale kind: number prefix: "₹ "
ensure field TotalAmount kind: decimal

Set a formula to compute the TotalAmount of the monthly sales

// formula for total amount
ensure formula TotalAmount
  assignToField: Summary.TotalAmount
  formula: "SUM(${f:Details.TotalBill})"

Apply a ReportLayout to the OutputMonthlySales form to display it in the report.

// report layout
ensure layout ReportLayout kind: content
  direction: vertical
  contentPadding: thick
  showPaddingSet: ["bottom"]
  flexCenter.showPaddingSet: ["top", "bottom", "left", "right"]
  flexCenter.padding: thick
  end.fields: [
    TotalSale,
    TotalOrders,
    OrdersCancelled,
    NewCustomers,
    LastMonthSale,
    TotalAmount
  ]
  end.showBorderSet: ["top"]
  end.showPaddingSet: ["top", "bottom", "left", "right"]
  end.padding: thick
  flexCenter.gridLayouts: [Details.MonthlySalesTable]

c. MonthlyOrders

Create a ReportMonthlyOrders report to retrieve a detailed overview of monthly orders.

// fill relevant columns of OutputMonthlySales
ensure report ReportMonthlyOrders kind: query
  label: "Monthly Order Report"
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "SELECT
      ${ss:OrderBook.Details.CustomerName} AS ${out:Details.CustomerName},
      COUNT(*) as ${out:Details.NumberOfOrder},
      SUM(${ss:OrderBook.Summary.TotalBill}) AS ${out:Details.TotalBill}
    FROM ${ss}
    WHERE ${ctx:row.type} = ${ss:OrderBook}
      AND ${ss:OrderBook.Details.Date} >= ${in:Details.FromDate}
      AND ${ss:OrderBook.Details.Date} <= ${in:Details.ToDate}
    GROUP BY ${ss:OrderBook.Details.CustomerName}"

d. LastMonthSale

Create a MonthlySalesLastMonthSale report to retrieve comprehensive data regarding sales from the previous month.

ensure report MonthlySalesLastMonthSale kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "Select
      COALESCE(sum(${ss:OrderBook.Summary.TotalBill}), 0)  as ${out:Summary.LastMonthSale}
    from ${ss}
    where ${ctx:row.type} = ${ss:OrderBook}
      and ${ctx:row.createdOn} >= DATE_ADD_MILLIS(${in:Details.FromDate} , -30, 'day')
      and ${ctx:row.createdOn} < ${in:Details.FromDate}"

e. NewCustomers

Create a MonthlySalesNewCustomers report to retrieve comprehensive data about orders placed by new customers from the previous month.

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesNewCustomers kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [VisitBook, OrderBook]
  neoQL: "Select
      count(*) as ${out:Summary.NewCustomers}
    from ${ss} as main
    where ${ctx:row.type} = ${ss:OrderBook}
      and ${ctx:row.createdOn} >= ${in:Details.FromDate}
      and ${ctx:row.createdOn} <= ${in:Details.ToDate}
      and ${ss:OrderBook.Details.VisitRowId} NOT IN  (select distinct ${ctx:row.id}
    from ${ss} as sub
    where ${ctx:row.type} = ${ss:VisitBook}
      and ${ctx:row.createdOn} <= ${in:Details.ToDate})
    group by ${ss:OrderBook.Details.VisitRowId}"

f. OrderCancelled

Create a MonthlySalesOrderCancelled report to retrieve comprehensive data about cancelled orders.

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesOrderCancelled kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "Select
      count(*) as ${out:Summary.OrdersCancelled}
    from ${ss}
    where ${ctx:row.type} = ${ss:OrderBook}
    and ${ss:OrderBook.Details.Date} >= ${in:Details.FromDate}
    and ${ss:OrderBook.Details.Date} <= ${in:Details.ToDate}
    and ${ss:OrderBook.Summary.OrderStatus.optionId} == 'keyCancelled'"

g. TotalOrders

Create a MonthlySalesTotalOrders report to retrieve comprehensive data about all orders placed during the previous month.

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesTotalOrders kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "Select
      count(*) as ${out:Summary.TotalOrders}
    from ${ss}
    where ${ctx:row.type} = ${ss:OrderBook} and ${ss:OrderBook.Details.Date} >= ${in:Details.FromDate}
    and ${ss:OrderBook.Details.Date} <= ${in:Details.ToDate}"

h. TotalAmount

Create a MonthlySalesTotalAmount report to retrieve comprehensive data about the total revenue from sales.

// fill relevant columns of OutputMonthlySales
ensure report MonthlySalesTotalAmount kind: query
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  fromSpreadsheets: [OrderBook]
  neoQL: "Select
      sum(${ss:OrderBook.Summary.TotalBill}) as ${out:Summary.TotalSale}
    from ${ss}
    where ${ctx:row.type} = ${ss:OrderBook} and ${ss:OrderBook.Details.Date} >= ${in:Details.FromDate}
    and ${ss:OrderBook.Details.Date} <= ${in:Details.ToDate}"

i. Composite report

Create a MonthlySales report designed to function as a holistic and detailed amalgamation, bringing together the full scope of information provided by the previously mentioned reports into one unified presentation.

// combine all the reports into OutputMonthlySales
ensure report MonthlySales kind: composite
  inputForm: FilterMonthly
  outputForm: OutputMonthlySales
  mergeReports: [
    MonthlySalesTotalAmount,
    MonthlySalesTotalOrders,
    MonthlySalesOrderCancelled,
    MonthlySalesNewCustomers,
    MonthlySalesLastMonthSale,
    ReportMonthlyOrders
  ]

Frontend

1. ReportMonthlySales

Implement a ReportMonthlySales action to gather the sales data corresponding to the previous month.

ensure action ReportMonthlySales kind: report
  label: "Report Monthly Sales"
  icon: "ListAltRounded"
  report: MonthlySales
  outputFormContentLayout: ReportLayout

2. Group actions

Group all these actions into a MyVisits section for more convenient access.

ensure group MyVisits
  pinnedActions: [AddVisit, AddOrder, EditOrderBook, ReportVisit, ReportMonthlySales]
  pinnedActionSetMobile: [AddVisit, AddOrder]
  actionPermission: {
      'ReportMonthlySales': {
        'menuGroup': '3',
        'roles': [
          'Owner'
        ]
      }
    }

Spreadsheet Collection Sheet

Reports

Backend

1. ReportMonthlySales

a. FilterMonthly

b. OutputMonthlySales

c. MonthlyOrders

d. LastMonthSale

e. NewCustomers

f. OrderCancelled

g. TotalOrders

h. TotalAmount

i. Composite report

Frontend

1. ReportMonthlySales

2. Group actions

Showcase

Platform

Community

About us