Dashboard

// chat pattern variable for form OutputAggregated
 
ensure var VarParagraphAggregatedOrderChatPattern kind: paragraph 
  deploy: fixedOnDeploy 
  value: "Hello ${p1}, Aggregated order amount: ₹${p2}, Dated ${p3}." 
  paramSet: ["p1", "p2", "p3"]
 

// function variable used to fetch the month from number
 
ensure var VarFunctionGetMonthName kind: function 
  deploy: fixedOnDeploy 
  value: "function(MonthInNumber : number) : text	
    {
	    const monthNames = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']; 
        return monthNames[MonthInNumber - 1];	
    }"
 

// this form will be used for filter out the dashboard information
 
ensure form FilterAggregated 
ensure composite Details kind: section 
ensure field DateRange kind: dateRange 
  required: true 
  fromDefault: "startOfMonth" 
  toDefault: "endOfMonth"
 

// this form will be used to show output of aggregated information
 
ensure form OutputAggregated allowToPrintForm: true
ensure grid: SalesByCustomer
ensure field Office kind: text 
ensure field TotalTea kind: number 
ensure field TotalCoffee kind: number 
ensure field TotalAmount kind: number 
  prefixVar: {
      'var': 'VarTextRupeeSymbol'
    }
 
ensure section: Summary
ensure field Date kind: date 
  defaultValue: "now"
ensure field DateRange kind: dateRange 
ensure field TotalSales kind: number 
  prefixVar: {
      'var': 'VarTextRupeeSymbol'
    }
ensure field TotalTea kind: number 
ensure field TotalCoffee kind: number 
 
ensure form OutputAggregated
  chatPatternVar: {
      'var': 'VarParagraphAggregatedOrderChatPattern',
      'paramSet': [
        '${f:SalesByCustomer.Office}',
        '${f:SalesByCustomer.TotalAmount}',
        '${f:Summary.Date}'
      ]
    }
 
ensure formula TotalAmountFormula 
  assignToField: TotalSales 
  formula: "SUM(${f:SalesByCustomer.TotalAmount})"
 
ensure formula TotalCoffeeFormula 
  assignToField: Summary.TotalCoffee 
  formula: "SUM(${f:SalesByCustomer.TotalCoffee})"
 
ensure formula TotalTeaFormula 
  assignToField: Summary.TotalTea 
  formula: "SUM(${f:SalesByCustomer.TotalTea})"
 

// this form will be used for show the dashboard information
 
ensure form OutputDashboard 
ensure grid: TeaVsCoffee
ensure field Date kind: date 
ensure field MonthInNumber kind: number 
ensure field Month kind: text 
ensure field TotalTea kind: number 
  label: "Tea" 
  prefixVar: {
      'var': 'VarTextRupeeSymbol'
    }
ensure field TotalCoffee kind: number 
  label: "Coffee" 
  prefixVar: {
      'var': 'VarTextRupeeSymbol'
    }
ensure field TotalSales kind: number 
  label: "Total"
  prefixVar: {
      'var': 'VarTextRupeeSymbol'
    } 
 
ensure grid: PendingPayments
ensure field InvoiceId kind: text 
ensure field InvoiceDate kind: date
  defaultValue: "now"
ensure field OfficeName kind: text
ensure field OfficeNo kind: text 
ensure field Amount kind: number 
  prefixVar: {
      'var': 'VarTextRupeeSymbol'
    }
 
ensure grid: TopCustomerSet
ensure field Name kind: text 
ensure field Amount kind: number 
  prefixVar: {
      'var': 'VarTextRupeeSymbol'
    }
 
ensure section: Details
ensure field ShowAggregatedReport kind: button 
  buttonKind: normal 
  targetType: triggerAction 
 
ensure formula FormulatUon29 
  assignToField: TeaVsCoffee.Month 
  formula: "${var:VarFunctionGetMonthName}(${f:MonthInNumber})"
  

// this reports fetches aggregated summary for OrderBook for given date range
 
ensure report AggregatedSummary kind: query 
  inputForm: FilterAggregated 
  outputForm: OutputAggregated 
  fromSpreadsheets: [OrderBook] 
  neoQL: "select
        { 'from' : ${in:Details.DateRange.from}, 'to' : ${in:Details.DateRange.to} } as ${out:Summary.DateRange},
        MILLIS(CLOCK_LOCAL()) as ${out:Summary.Date}
    from ${ss}
    where ${ctx:row.type} = ${ss:s2}
    and ${ss:s2.Order.Date} >= ${in:Details.DateRange.from}
    and ${ss:s2.Order.Date} <= ${in:Details.DateRange.to}
    group by ${ss:s2.Office.Name}"
 

// this report feches aggregated sales group by on customers from OrderBook
 
ensure report AggregatedSalesByCustomer kind: query 
  inputForm: FilterAggregated 
  outputForm: OutputAggregated 
  fromSpreadsheets: [OrderBook] 
  neoQL: "select
        ${ss:s2.Office.Name} as ${out:SalesByCustomer.Office},
        sum(${ss:s2.Order.TeaCount}) as ${out:SalesByCustomer.TotalTea},
        sum(${ss:s2.Order.CoffeeCount}) as ${out:SalesByCustomer.TotalCoffee},
        sum(${ss:s2.Order.Total}) as ${out:SalesByCustomer.TotalAmount}
    from ${ss}
    where ${ctx:row.type} = ${ss:s2}
    and ${ss:s2.Order.Date} >= ${in:Details.DateRange.from}
    and ${ss:s2.Order.Date} <= ${in:Details.DateRange.to}
    group by ${ss:s2.Office.Name}"
 

// this reports combines AggregatedSalesByCustomer and AggregatedSummary reports
 
ensure report AggregatedReport kind: composite 
  inputForm: FilterAggregated 
  outputForm: OutputAggregated
  mergeReports: [AggregatedSalesByCustomer, AggregatedSummary]
 

// this report feches the top customer information
 
ensure report DashboardTopCustomers kind: query 
  outputForm: OutputDashboard 
  fromSpreadsheets: [OrderBook] 
  neoQL: "select
        ${ss:s2.Office.Name} as ${out:TopCustomerSet.Name},
        sum(${ss:s2.Order.Total}) as ${out:TopCustomerSet.Amount}
    from ${ss}
    where ${ctx:row.type} = ${ss:s2}
    group by
    ${ss:s2.Office.Name}
    order by sum(${ss:s2.Order.Total}) DESC
    LIMIT 5"
 

// this report feches the monthly sale data
 
ensure report DashboardMonthlySale kind: query 
  outputForm: OutputDashboard 
  fromSpreadsheets: [OrderBook] 
  neoQL: "select
${ss:s2.Order.Date} as ${out:TeaVsCoffee.Date},
${ss:s2.Order.Total} as ${out:TeaVsCoffee.TotalSales},
(${ss:s2.Office.TeaPrice} * ${ss:s2.Order.TeaCount}) as ${out:TeaVsCoffee.TotalTea},
(${ss:s2.Office.CoffeePrice} * ${ss:s2.Order.CoffeeCount}) as ${out:TeaVsCoffee.TotalCoffee},
DATE_PART_MILLIS(${ss:s2.Order.Date}, 'month') as ${out:TeaVsCoffee.MonthInNumber}
FROM ${ss}
where ${ctx:row.type} = ${ss:s2}
ORDER BY ${ss:s2.Order.Date} ASC;"
    

// this report feches the pending payment data
 
ensure var VarMappingPendingPayment kind: mapping 
  deploy: fixedOnDeploy
  fromForm: OutputInvoice 
  toForm: OutputDashboard 
  toGrid: PendingPayments 
  fieldMappingMap: {
      'map': {
        '${f:Details.InvoiceDate}': 'PendingPayments.InvoiceDate',
        '${f:Details.OfficeName}': 'PendingPayments.OfficeName',
        '${f:Details.OfficeNo}': 'PendingPayments.OfficeNo',
        '${f:Summary.GrandTotal}': 'PendingPayments.Amount',
        '${f:Details.InvoiceId}': 'PendingPayments.InvoiceId'
      }
    }
 
ensure var VarConditionPendingPaymentReport kind: condition 
  deploy: fixedOnDeploy
  sourceForm: OutputInvoice
  value: "<root>
      <stmt>${f:Summary.PaymentStatus} != ${d:PaymentStatus.paid}</stmt>
    </root>" 
 
ensure report DashboardPendingPayments kind: spreadsheet 
  outputForm: OutputDashboard 
  fromSpreadsheet: InvoiceHistory 
  filterConditionVar: VarConditionPendingPaymentReport 
  outputFormMappingVar: VarMappingPendingPayment
    

// this report combines
// DashboardMonthlySale, 
// DashboardPendingPayments, 
// DashboardTopCustomers
 
ensure report Dashboard kind: composite 
  outputForm: OutputDashboard
  mergeReports: [DashboardMonthlySale, DashboardPendingPayments, DashboardTopCustomers]
    

// layouts of OutputDashboard form
 
ensure form OutputDashboard
ensure grid: TeaVsCoffee
 
ensure layoutGrid Calendar kind: calendar 
  fromDateField: TeaVsCoffee.Date 
  titleField: TeaVsCoffee.Month
  showFields: [Date, TotalSales, MonthInNumber, TotalTea, TotalCoffee, Month]
 
ensure layoutGrid LineChart kind: xyChartBarGraph 
  xAxis: TeaVsCoffee.Month
ensure xAxis AxisX 
ensure yAxis AxisY 
  color.value: red 
  color.shade: s500
  field: TeaVsCoffee.TotalSales
 
ensure layoutGrid BarGraph kind: xyChartBarGraph 
  xAxis: TeaVsCoffee.Month
ensure xAxis AxisX 
ensure yAxis AxisY1 
  field: TeaVsCoffee.TotalTea
ensure yAxis AxisY2 
  field: TeaVsCoffee.TotalCoffee
ensure yAxis AxisY3 
  field: TeaVsCoffee.TotalSales
 
ensure layoutGrid XYChart kind: xyChartBarGraph 
  xAxis: TeaVsCoffee.Month
ensure yAxis AxisY 
  field: TeaVsCoffee.TotalSales
 
ensure grid: PendingPayments
ensure layoutGrid Table kind: table columnSizeSet: ["Flex"] renderingMode: flex
  showComps: [InvoiceId, InvoiceDate, OfficeName, OfficeNo, PendingPayments.Amount]
  
ensure grid: TopCustomerSet
ensure layoutGrid TableLayout kind: table columnSizeSet: ["AutoSize"]
  showComps: [Name, TopCustomerSet.Amount]
 
ensure form OutputDashboard
ensure layout Header kind: content 
  direction: horizontal 
  contentPadding: thick 
  showPaddingSet: ["top"] 
  end.fields: [ShowAggregatedReport]
 
ensure layout TeaVsCoffeeChart kind: content 
  displayLabel: "Tea vs Coffee" 
  direction: vertical 
  showBorderSet: ["bottom"] 
  contentPadding: thin 
  showPaddingSet: ["top", "bottom"] 
  flexCenter.gridLayouts: [TeaVsCoffee.BarGraph] 
 
ensure layout PendingPayments kind: content 
  displayLabel: "Pending Payments" 
  direction: vertical 
  showBorderSet: ["bottom"] 
  contentPadding: thick 
  showPaddingSet: ["top", "bottom"]
  flexCenter.gridLayouts: [PendingPayments.Table]
 
ensure layout TopCustomers kind: content 
  flexCenter.gridLayouts: [TopCustomerSet.TableLayout] 
  displayLabel: "Top 5 Customers" 
  direction: vertical 
  contentPadding: thin 
  showPaddingSet: ["top", "bottom"] 
 
ensure layout Dashboard kind: content 
  direction: vertical
  start.formLayouts: [Header] 
  flexCenter.formLayouts: [TeaVsCoffeeChart, PendingPayments, TopCustomers] 
 
ensure layout DashboardTemplate kind: template 
  paperSize: a4Size
 

// this action runs the Dashboard report
 
ensure action ShowDashboard kind: report 
  icon: "AnalyticsRounded" 
  report: Dashboard 
  outputFormContentLayout: Dashboard
  

// layouts for OutputAggregated form
 
ensure form OutputAggregated
ensure grid: SalesByCustomer
ensure layoutGrid Table kind: table 
  columnSizeSet: ["Flex", "AutoSize"]
  showComps: [Office, SalesByCustomer.TotalCoffee, SalesByCustomer.TotalTea, SalesByCustomer.TotalAmount]
 
ensure form OutputAggregated
ensure layout ReportSection1 kind: content 
  direction: vertical 
  contentPadding: thick 
  start.fields: [Summary.Date, Summary.DateRange]
 
ensure layout ReportSection2 kind: content 
  direction: vertical 
  contentPadding: thick 
  start.fields: [Summary.TotalTea, Summary.TotalCoffee, Summary.TotalSales]
 
ensure layout ReportSection kind: content
  direction: horizontal 
  contentPadding: thick
  start.formLayouts: [ReportSection1] 
  end.formLayouts: [ReportSection2]
 
ensure layout Report kind: content 
  direction: vertical 
  start.padding: thick 
  start.showBorderSet: ["bottom"] 
  start.formLayouts: [ReportSection]
  flexCenter.gridLayouts: [SalesByCustomer.Table]
 
ensure layout Template kind: template paperSize: a4Size
 

// this action runs the AggregatedReport
 
ensure action ShowAggregatedReport kind: report 
  icon: "AnalyticsRounded" 
  report: AggregatedReport 
  outputFormContentLayout: Report 
  outputFormTemplateLayout: Template 
  sendMessageToInbox: false
  
ensure form OutputDashboard 
ensure section: Details
ensure field ShowAggregatedReport 
  action: ShowAggregatedReport

// this group has all the actions those can be performed for dashboard 
 
ensure group Dashboard 
  allowPromptAssistant: false 
  hideActionMenu: true
  pinnedActions: [ShowDashboard, ShowAggregatedReport] 
  defaultAction: ShowDashboard 
  actionPermission: {
      'ShowDashboard': {
        'menuGroup': 'Report',
        'roles': [
          'Owner'
        ]
      },
      'ShowAggregatedReport': {
        'roles': [
          'Owner'
        ]
      }
    }