Dashboard
Backend
1. Variables
1. Dashboard report
ensure var VarTextPatternDateRange kind: text
modules: ["QuickBooks"]
deploy: fixedOnDeploy
value: "From: ${P1} To: ${P2}"
ensure var VarSetOfTextMonths kind: setOfText
modules: ["QuickBooks"]
deploy: fixedOnDeploy
value: "<root>
<node key='1'>January</node>
<node key='2'>February</node>
<node key='3'>March</node>
<node key='4'>April</node>
<node key='5'>May</node>
<node key='6'>June</node>
<node key='7'>July</node>
<node key='8'>August</node>
<node key='9'>September</node>
<node key='10'>October</node>
<node key='11'>November</node>
<node key='12'>December</node>
</root>"
a. Input form
ensure form FilterDashboard modules: ["QuickBooks"]
ensure section: Details
ensure field From kind: date
required: true
defaultValue: "startOfMonth"
ensure field To kind: date
required: true
defaultValue: "endOfMonth"
b. Output form
ensure form OutputDashboard
modules: ["QuickBooks"]
ensure section: DateRange
ensure field From kind: date
ensure field To kind: date
ensure field DateRangeLabel kind: label
textSize: h6
justifyText: center
textPatternVar: {
'var': 'VarTextPatternDateRange',
'paramSet': [
'${f:DateRange.From}',
'${f:DateRange.To}'
]
}
// composite, section, NetAssets
ensure section: NetAssets
ensure field CurrentNetAssets kind: decimal
ensure field CurrentNetAssetsLabel kind: label textSize: h6 justifyText: center
// composite, grid, MonthlyCashflow
ensure grid: MonthlyCashflow
ensure field TransactionType kind: pickText
sourceVar: VarSetOfTextTransaction
ensure field Amount kind: decimal
prefix: "₹ "
numberOfDigitsAfterPeriod: 2
numberFormat: "#,##,##0"
// composite, grid, DebitBreakdown
ensure grid: DebitBreakdown
ensure field Target kind: pickText
sourceVar: VarSetOfTextTarget
ensure field Amount kind: decimal
prefix: "₹ "
numberOfDigitsAfterPeriod: 2
numberFormat: "#,##,##0"
// composite, grid, CreditBreakdown
ensure grid: CreditBreakdown
ensure field Source kind: pickText
sourceVar: VarSetOfTextSource
ensure field Amount kind: decimal
prefix: "₹ "
numberOfDigitsAfterPeriod: 2
numberFormat: "#,##,##0"
// composite, grid, TransactionHistory
ensure grid: TransactionHistory
ensure field Item kind: text
ensure field Amount kind: decimal
prefix: "₹ "
numberOfDigitsAfterPeriod: 2
numberFormat: "#,##,##0"
ensure field Date kind: dateTime
ensure field Transaction kind: pickText
sourceVar: VarSetOfTextTransaction
ensure field Category kind: text
ensure field Verified kind: text
// composite, grid, YearlyCashflow
ensure grid: YearlyCashflow
ensure field Month kind: pickText sourceVar: VarSetOfTextMonths
ensure field IncomingCash kind: decimal numberOfDigitsAfterPeriod: 2
ensure field OutgoingCash kind: decimal numberOfDigitsAfterPeriod: 2
c. Grid layout
ensure form OutputDashboard
ensure composite MonthlyCashflow
ensure layoutGrid PieChartLayout kind: xyChartPieChart
xAxis: MonthlyCashflow.TransactionType
ensure xAxis Axis1
valueOptionId: "keyCredit"
color.value: green
color.shade: s300
ensure xAxis Axis2
valueOptionId: "keyDebit"
color.value: red
color.shade: s300
ensure yAxis Axis3 field: MonthlyCashflow.Amount
// layoutGrid, xyChartBarGraph, BarChartLayout
ensure layoutGrid BarChartLayout kind: xyChartBarGraph
xAxis: MonthlyCashflow.TransactionType
ensure xAxis Axis1
valueOptionId: "keyCredit"
color.value: green
color.shade: s300
ensure xAxis Axis2
valueOptionId: "keyDebit"
color.value: red
color.shade: s300
ensure yAxis AxisY
field: MonthlyCashflow.Amount
ensure composite DebitBreakdown
ensure layoutGrid PieChartLayout kind: xyChartPieChart
xAxis: DebitBreakdown.Target
ensure xAxis AxisX1
valueOptionId: "keyFixed"
color.value: blue
color.shade: s300
ensure xAxis AxisX2
valueOptionId: "keyEmergency"
color.value: red
color.shade: s300
ensure xAxis AxisX3
valueOptionId: "keyEssential"
color.value: green
color.shade: s300
ensure xAxis AxisX4
valueOptionId: "keyEntertainment"
color.value: yellow
color.shade: s300
ensure xAxis AxisX5
valueOptionId: "keyGrocery"
color.value: purple
color.shade: s300
ensure xAxis AxisX6
valueOptionId: "keySubscription"
color.value: cyan
color.shade: s300
ensure xAxis AxisX7
valueOptionId: "keyTransportation"
color.value: grey
color.shade: s300
ensure xAxis AxisX8
valueOptionId: "keyInvestment"
color.value: orange
color.shade: s300
ensure yAxis AxisY field: DebitBreakdown.Amount
// layoutGrid, xyChartBarGraph, BarChartLayout
ensure layoutGrid BarChartLayout kind: xyChartBarGraph
xAxis: DebitBreakdown.Target
ensure xAxis AxisX1
valueOptionId: "keyFixed"
color.value: blue
color.shade: s300
ensure xAxis AxisX2
valueOptionId: "keyEmergency"
color.value: red
color.shade: s300
ensure xAxis AxisX3
valueOptionId: "keyEssential"
color.value: green
color.shade: s300
ensure xAxis AxisX4
valueOptionId: "keyEntertainment"
color.value: yellow
color.shade: s300
ensure xAxis AxisX5
valueOptionId: "keyGrocery"
color.value: purple
color.shade: s300
ensure xAxis AxisX6
valueOptionId: "keySubscription"
color.value: cyan
color.shade: s300
ensure xAxis AxisX7
valueOptionId: "keyTransportation"
color.value: grey
color.shade: s300
ensure xAxis AxisX8
valueOptionId: "keyInvestment"
color.value: orange
color.shade: s300
ensure yAxis AxisY field: DebitBreakdown.Amount
ensure composite CreditBreakdown
ensure layoutGrid PieChartLayout kind: xyChartPieChart
xAxis: CreditBreakdown.Source
ensure xAxis AxisX1
valueOptionId: "keyPayCheck"
color.value: blue
color.shade: s300
ensure xAxis AxisX2
valueOptionId: "keyInterest"
color.value: green
color.shade: s300
ensure xAxis AxisX3
valueOptionId: "keyRental"
color.value: cyan
color.shade: s300
ensure xAxis AxisX4
valueOptionId: "keyDividend"
color.value: orange
color.shade: s300
ensure xAxis AxisX5
valueOptionId: "keySale"
color.value: deepPurple
color.shade: s300
ensure xAxis AxisX6
valueOptionId: "keyOther"
color.value: grey
color.shade: s300
ensure yAxis AxisY
field: CreditBreakdown.Amount
// layoutGrid, xyChartBarGraph, BarChartLayout
ensure layoutGrid BarChartLayout kind: xyChartBarGraph
xAxis: CreditBreakdown.Source
ensure xAxis AxisX1
valueOptionId: "keyPayCheck"
color.value: blue
color.shade: s300
ensure xAxis AxisX2
valueOptionId: "keyInterest"
color.value: green
color.shade: s300
ensure xAxis AxisX3
valueOptionId: "keyRental"
color.value: cyan
color.shade: s300
ensure xAxis AxisX4
valueOptionId: "keyDividend"
color.value: orange
color.shade: s300
ensure xAxis AxisX5
valueOptionId: "keySale"
color.value: deepPurple
color.shade: s300
ensure xAxis AxisX6
valueOptionId: "keyOther"
color.value: grey
color.shade: s300
ensure yAxis AxisY
field: CreditBreakdown.Amount
ensure var VarConditionIsCreditTransaction kind: condition
modules: ["QuickBooks"]
deploy: fixedOnDeploy
value: "<root>
<stmt>${f:TransactionHistory.Transaction} == ${d:Transaction.keyCredit}</stmt>
</root>"
sourceForm: OutputDashboard
ensure var VarConditionIsDebitTransaction kind: condition
modules: ["QuickBooks"]
deploy: fixedOnDeploy
value: "<root>
<stmt>${f:TransactionHistory.Transaction} == ${d:Transaction.keyDebit}</stmt>
</root>"
sourceForm: OutputDashboard
ensure form OutputDashboard
ensure composite TransactionHistory
ensure layoutGrid TableLayout kind: table
columnSizeSet: ["AutoSize", "AutoSize", "Flex"]
showComps: [
TransactionHistory.Verified,
TransactionHistory.Date,
TransactionHistory.Item,
TransactionHistory.Category,
TransactionHistory.Amount
]
ensure style Style1
value: black
fields: [
TransactionHistory.Item,
TransactionHistory.Transaction,
TransactionHistory.Amount,
TransactionHistory.Category,
TransactionHistory.Verified,
TransactionHistory.Date
]
fieldLayoutOn: header
textStyleSet: ["bold"]
textColor.value: white
ensure style Style2
conditionVar: VarConditionIsCreditTransaction
fields: [TransactionHistory.Amount]
fieldLayoutOn: column
textStyleSet: ["bold"]
textColor.value: green
textColor.shade: s500
ensure style Style3
conditionVar: VarConditionIsDebitTransaction
fields: [TransactionHistory.Amount]
fieldLayoutOn: column
textStyleSet: ["bold"]
textColor.value: red
textColor.shade: s500
ensure composite YearlyCashflow
ensure layoutGrid TableLayout kind: table
columnSizeSet: ["Flex"]
showComps: [YearlyCashflow.Month, YearlyCashflow.IncomingCash, YearlyCashflow.OutgoingCash]
ensure style Style1
value: black
fields: [YearlyCashflow.Month, YearlyCashflow.IncomingCash, YearlyCashflow.OutgoingCash]
fieldLayoutOn: header
textStyleSet: ["bold"]
textColor.value: white
// layoutGrid, xyChartLineChart, LineChart
ensure layoutGrid LineChart kind: xyChartLineChart
xAxis: YearlyCashflow.Month
ensure xAxis AxisX1 valueOptionId: "1"
ensure xAxis AxisX2 valueOptionId: "2"
ensure xAxis AxisX3 valueOptionId: "3"
ensure xAxis AxisX4 valueOptionId: "4"
ensure xAxis AxisX5 valueOptionId: "5"
ensure xAxis AxisX6 valueOptionId: "6"
ensure xAxis AxisX7 valueOptionId: "7"
ensure xAxis AxisX8 valueOptionId: "8"
ensure xAxis AxisX9 valueOptionId: "9"
ensure xAxis AxisX10 valueOptionId: "10"
ensure xAxis AxisX11 valueOptionId: "11"
ensure xAxis AxisX12 valueOptionId: "12"
ensure yAxis AxisY1
color.value: green
color.shade: s300
field: YearlyCashflow.IncomingCash
ensure yAxis AxisY2
color.value: red
color.shade: s300
field: YearlyCashflow.OutgoingCash
// layoutGrid, xyChartBarGraph, BarChart
ensure layoutGrid BarChart kind: xyChartBarGraph
xAxis: YearlyCashflow.Month
ensure xAxis AxisX1 valueOptionId: "1"
ensure xAxis AxisX2 valueOptionId: "2"
ensure xAxis AxisX3 valueOptionId: "3"
ensure xAxis AxisX4 valueOptionId: "4"
ensure xAxis AxisX5 valueOptionId: "5"
ensure xAxis AxisX6 valueOptionId: "6"
ensure xAxis AxisX7 valueOptionId: "7"
ensure xAxis AxisX8 valueOptionId: "8"
ensure xAxis AxisX9 valueOptionId: "9"
ensure xAxis AxisX10 valueOptionId: "10"
ensure xAxis AxisX11 valueOptionId: "11"
ensure xAxis AxisX12 valueOptionId: "12"
ensure yAxis AxisY1
color.value: green
color.shade: s300
field: YearlyCashflow.IncomingCash
ensure yAxis AxisY2
color.value: red
color.shade: s300
field: YearlyCashflow.OutgoingCash
ensure layoutGrid TableLayout
allowToSwitchLayouts: [LineChart, BarChart]
d. Form layout
ensure form OutputDashboard
// layout, content, LayoutDateRange
ensure layout LayoutDateRange kind: content
direction: horizontal
contentPadding: thick
flexCenter.fields: [DateRange.DateRangeLabel]
// layout, content, LayoutNetAssets
ensure layout LayoutNetAssets kind: content
direction: horizontal
contentPadding: thick
flexCenter.fields: [NetAssets.CurrentNetAssetsLabel]
// layout, content, LayoutMonthlyCashflow
ensure layout LayoutMonthlyCashflow kind: content
displayLabel: "Monthly Cashflow"
direction: vertical
showBorderSet: ["top", "bottom", "right", "left"]
contentPadding: thin
showPaddingSet: ["top", "bottom", "right", "left"]
start.padding: thin
start.gridLayouts: [MonthlyCashflow.PieChartLayout]
// layout, content, LayoutDebitBreakdown
ensure layout LayoutDebitBreakdown kind: content
displayLabel: "Debit Breakdown"
direction: vertical
showBorderSet: ["top", "bottom", "left", "right"]
contentPadding: thin
showPaddingSet: ["top", "bottom", "left", "right"]
start.padding: thin
start.gridLayouts: [DebitBreakdown.PieChartLayout]
// layout, content, LayoutCreditBreakdown
ensure layout LayoutCreditBreakdown kind: content
displayLabel: "Credit Breakdown"
direction: vertical
showBorderSet: ["top", "bottom", "left", "right"]
contentPadding: thin
showPaddingSet: ["top", "bottom", "left", "right"]
start.padding: thin
start.gridLayouts: [CreditBreakdown.PieChartLayout]
// layout, content, FlexMainTop
ensure layout FlexMainTop kind:
content direction: horizontal
contentPadding: thick
flexCenter.formLayouts: [
LayoutMonthlyCashflow,
LayoutDebitBreakdown,
LayoutCreditBreakdown
]
// layout, content, FlexMainCenter
ensure layout FlexMainCenter kind: content
displayLabel: "Yearly Cashflow"
direction: vertical
showBorderSet: ["top", "left", "bottom", "right"]
contentPadding: thin
showPaddingSet: ["top", "bottom", "left", "right"]
flexCenter.gridLayouts: [YearlyCashflow.LineChart]
// layout, content, FlexMainFooter
ensure layout FlexMainFooter kind: content
displayLabel: "Transaction History"
direction: vertical
showBorderSet: ["top", "left", "bottom", "right"]
contentPadding: thin
showPaddingSet: ["top", "bottom", "left", "right"]
flexCenter.gridLayouts: [TransactionHistory.TableLayout]
// layout, content, FlexMain
ensure layout FlexMain kind: content
direction: vertical
contentPadding: thick
start.formLayouts: [LayoutDateRange, LayoutNetAssets]
flexCenter.formLayouts: [FlexMainTop, FlexMainCenter, FlexMainFooter]
// layout, template, TemplateLayout
ensure layout TemplateLayout kind: template paperSize: a4Size contentPadding: thick
e. ReportMonthlyCashflow
ensure report ReportMonthlyCashflow kind: query
modules: ["QuickBooks"]
inputForm: FilterDashboard
outputForm: OutputDashboard
fromSpreadsheets: [TransactionSheet]
neoQL: "SELECT
${ss:TransactionSheet.Details.Type} as ${out:MonthlyCashflow.TransactionType},
SUM(${ss:TransactionSheet.Details.TotalAmount}) as ${out:MonthlyCashflow.Amount}
FROM ${ss}
WHERE ${ctx:row.type} = ${ss:TransactionSheet}
AND ${in:Details.From} <= ${ss:TransactionSheet.Details.Date}
AND ${in:Details.To} > ${ss:TransactionSheet.Details.Date}
GROUP BY ${ss:TransactionSheet.Details.Type}"
f. ReportDebitBreakdown
ensure report ReportDebitBreakdown kind: query
modules: ["QuickBooks"]
inputForm: FilterDashboard
outputForm: OutputDashboard
fromSpreadsheets: [TransactionSheet]
neoQL: "SELECT
${ss:TransactionSheet.Details.Target} as ${out:DebitBreakdown.Target},
SUM(${ss:TransactionSheet.Details.TotalAmount}) as ${out:DebitBreakdown.Amount}
FROM ${ss}
WHERE ${ctx:row.type} = ${ss:TransactionSheet}
AND ${in:Details.From} <= ${ss:TransactionSheet.Details.Date}
AND ${in:Details.To} > ${ss:TransactionSheet.Details.Date}
GROUP BY ${ss:TransactionSheet.Details.Target}"
g. ReportCreditBreakdown
ensure report ReportCreditBreakdown kind: query
modules: ["QuickBooks"]
inputForm: FilterDashboard
outputForm: OutputDashboard
fromSpreadsheets: [TransactionSheet]
neoQL: "SELECT
${ss:TransactionSheet.Details.Source} as ${out:CreditBreakdown.Source},
SUM(${ss:TransactionSheet.Details.TotalAmount}) as ${out:CreditBreakdown.Amount}
FROM ${ss}
WHERE ${ctx:row.type} = ${ss:TransactionSheet}
AND ${in:Details.From} <= ${ss:TransactionSheet.Details.Date}
AND ${in:Details.To} > ${ss:TransactionSheet.Details.Date}
GROUP BY ${ss:TransactionSheet.Details.Source}"
h. ReportTransactionHistory
ensure report ReportTransactionHistory kind: query
modules: ["QuickBooks"]
inputForm: FilterDashboard
outputForm: OutputDashboard
fromSpreadsheets: [TransactionSheet]
neoQL: "SELECT
IFMISSINGORNULL(${ss:TransactionSheet.Details.DebitName}, ${ss:TransactionSheet.Details.CreditName}) as ${out:TransactionHistory.Item},
${ss:TransactionSheet.Details.TotalAmount} as ${out:TransactionHistory.Amount},
${ss:TransactionSheet.Details.Date} as ${out:TransactionHistory.Date},
${ss:TransactionSheet.Details.Type} as ${out:TransactionHistory.Transaction},
IFMISSINGORNULL(${ss:TransactionSheet.Details.DebitParentCategory}, ${ss:TransactionSheet.Details.CreditParentCategory}) as ${out:TransactionHistory.Category},
CASE WHEN ${ss:TransactionSheet.Summary.Verified.value} = true THEN '☑️' ELSE '' END as ${out:TransactionHistory.Verified}
FROM ${ss}
WHERE ${ctx:row.type} = ${ss:TransactionSheet}
AND ${in:Details.From} <= ${ss:TransactionSheet.Details.Date}
AND ${in:Details.To} > ${ss:TransactionSheet.Details.Date}"
i. ReportYearlyCashflow
ensure report ReportYearlyCashflow kind: query
modules: ["QuickBooks"]
inputForm: FilterDashboard
outputForm: OutputDashboard
fromSpreadsheets: [TransactionSheet]
neoQL: "WITH q1 as (
SELECT
TOSTRING(DATE_PART_STR(MILLIS_TO_TZ(${ss:TransactionSheet.Details.Date}, 'Asia/Kolkata'), 'month')) as `month`,
SUM(${ss:TransactionSheet.Details.TotalAmount}) as `expenseValue`
FROM ${ss}
WHERE ${ctx:row.type} = ${ss:TransactionSheet}
AND DATE_TRUNC_MILLIS(${in:From}, 'year') <= ${ss:TransactionSheet.Details.Date}
AND (DATE_TRUNC_MILLIS(${in:From}, 'year') + 31536000000 - 1) > ${ss:TransactionSheet.Details.Date}
AND ${ss:TransactionSheet.Details.Type.optionId} = 'keyDebit'
GROUP BY DATE_PART_STR(MILLIS_TO_TZ(${ss:TransactionSheet.Details.Date}, 'Asia/Kolkata'), 'month')
),
q2 as (
SELECT
TOSTRING(DATE_PART_STR(MILLIS_TO_TZ(${ss:TransactionSheet.Details.Date}, 'Asia/Kolkata'), 'month')) as `month`,
SUM(${ss:TransactionSheet.Details.TotalAmount}) as `revenueValue`
FROM ${ss}
WHERE ${ctx:row.type} = ${ss:TransactionSheet}
AND DATE_TRUNC_MILLIS(${in:From}, 'year') <= ${ss:TransactionSheet.Details.Date}
AND (DATE_TRUNC_MILLIS(${in:From}, 'year') + 31536000000 - 1) > ${ss:TransactionSheet.Details.Date}
AND ${ss:TransactionSheet.Details.Type.optionId} = 'keyCredit'
GROUP BY DATE_PART_STR(MILLIS_TO_TZ(${ss:TransactionSheet.Details.Date}, 'Asia/Kolkata'), 'month')
)
SELECT
q1.month as ${out:Month},
q1.expenseValue as ${out:YearlyCashflow.OutgoingCash},
q2.revenueValue as ${out:YearlyCashflow.IncomingCash}
FROM q1
JOIN q2 ON q1.month = q2.month"
//** report, ReportDateRangeQuery
ensure report ReportDateRangeQuery kind: query
modules: ["QuickBooks"]
inputForm: FilterDashboard
outputForm: OutputDashboard
fromSpreadsheets: [TransactionSheet]
neoQL: "SELECT
${in:From} as ${out:From},
${in:To} as ${out:To}
"
//** report, ReportGetNetAssets
ensure report ReportGetNetAssets kind: query
modules: ["QuickBooks"]
inputForm: FilterDashboard
outputForm: OutputDashboard
fromSpreadsheets: [NetAssetSheet]
neoQL: "SELECT
${ss:NetAssetSheet.Details.NetAsset} as ${out:CurrentNetAssets}
FROM ${ss}
WHERE ${ctx:row.type} = ${ss:NetAssetSheet}
LIMIT 1 "
j. Dashboard
ensure report Dashboard kind: composite
modules: ["QuickBooks"]
inputForm: FilterDashboard
outputForm: OutputDashboard
mergeReports: [
ReportMonthlyCashflow,
ReportDebitBreakdown,
ReportTransactionHistory,
ReportCreditBreakdown,
ReportYearlyCashflow,
ReportDateRangeQuery,
ReportGetNetAssets
]
Frontend
1. TransactionCompositeReport
ensure action Dashboard kind: report
modules: ["QuickBooks"]
icon: "DashboardRounded"
report: Dashboard
outputFormContentLayout: FlexMain
outputFormTemplateLayout: Template
2. Group actions
Group these actions under a section named LastPass for easy access.
ensure group QuickBooks
pinnedActions: [AddTransaction, Dashboard]
pinnedActionSetMobile: [AddTransaction, Dashboard]
actionPermission: {
'Dashboard': {
'menuGroup': '5',
'roles': [
'Member'
]
}
}