Creating Reports In The User Interface

This page is for reporting administrators who configure reports from the PinkApple screens. It replaces the SQL-seed view with the actual UI workflow users follow.

Use this page when you want to:

• add a new report group
• create a runnable report definition
• build a financial statement report from templates, lines, columns, and mappings
• understand how drilldowns behave in runtime
• use presets properly when running reports

Where Each Setup Task Lives

What you want to create	Screen
Report group	Administration -> Reporting -> Report Catalog -> Report Groups
Report definition	Administration -> Reporting -> Report Catalog -> Report Definitions
Financial statement template	Administration -> Reporting -> Financial Statements -> Templates
Statement lines	Administration -> Reporting -> Financial Statements -> Line Items
Account mappings	Administration -> Reporting -> Financial Statements -> Account Mappings
Column definitions	Administration -> Reporting -> Financial Statements -> Column Definitions
Column sets	Administration -> Reporting -> Financial Statements -> Column Sets
Notes	Administration -> Reporting -> Financial Statements -> Notes
Run and validate	Reporting -> Reports

Before You Create A Report

Have these decisions ready first:

• the dataset procedure or statement engine you want to use
• the report type and output mode the frontend should expect
• the business units and roles that should see the report
• the default runtime filters users should start with
• whether the report needs drilldowns
• whether the report should ship with shared presets

For financial statements, also confirm:

• the template already exists or will be created now
• the line hierarchy is clear
• the account mappings are agreed
• the column layout is agreed

Choosing Report Type And Output Mode

Report Type tells PinkApple what kind of business result the report represents.

Dataset Output Mode tells PinkApple what shape the procedure returns.

The important rule is this:

• Report Type is a business classification
• Dataset Output Mode is a payload contract
• they are related, but they are not the same thing

Use this matrix when creating report definitions in the UI.

Report Type	Use It For	Normal Output Mode	Notes
LIST	operational listings, ledgers, account statements	ROWSET	Best for row-based procedures and paged listings.
FINANCIAL_STATEMENT	trial balance, balance sheet, income statement, cash flow	REPORT_JSON	Financial statement procedures are forced into this combination.
MANAGEMENT_DASHBOARD	mixed KPI + table + trend reports for managers	REPORT_JSON	Use when the procedure returns KPIs, series, and possibly multiple tables.
KPI	compact indicator packs	REPORT_JSON	Use when the emphasis is on cards and summary values, not a primary data grid.
SUMMARY	summarized operational results	REPORT_JSON	Good for grouped totals, exposure summaries, aging buckets, and executive summaries.
CHART	chart-led analytical reports	REPORT_JSON	Use when the procedure returns chart series as the main output.
PIVOT	analytical cross-tab style reports	REPORT_JSON	Use only when the procedure contract is clearly pivot-like and not a normal row list.
CUSTOM	specialized viewer behavior	REPORT_JSON	Use sparingly for procedures with a domain-specific structured payload.

In practice:

• if the procedure contract is ROWSET, PAGED_RESULT, or ACTIVITY_LOG, use Dataset Output Mode = ROWSET
• if the procedure contract is SCHEMA1 or another structured JSON payload, use Dataset Output Mode = REPORT_JSON
• if the selected procedure is a known financial statement procedure, the form now fixes the correct combination automatically

Common pairing examples:

• LIST + ROWSET: journal listing, account statement, client register
• FINANCIAL_STATEMENT + REPORT_JSON: trial balance, balance sheet, income statement
• MANAGEMENT_DASHBOARD + REPORT_JSON: portfolio overview, management dashboard
• KPI + REPORT_JSON: KPI packs and indicator summaries
• SUMMARY + REPORT_JSON: PAR summaries, deposit summaries, aging summaries

Dataset Procedure Reference

The Dataset Procedure field is not just a free text name. It points to a registered procedure in system_procedure_registry, and that registry contract is what should guide the report definition.

Use this rule of thumb:

Registry contract	What it means in practice	Report definition expectation
PAGED_RESULT	The procedure returns tabular rows with paging semantics	Use a row/grid-style report and Dataset Output Mode = ROWSET
ROWSET	The procedure returns tabular rows without paged wrapper metadata	Use a row/grid-style report and Dataset Output Mode = ROWSET
ACTIVITY_LOG	The procedure returns audit-style or timeline-style rows	Use a row/grid-style report and Dataset Output Mode = ROWSET
CUSTOM	The procedure returns a procedure-specific payload shape	Verify the payload contract before finalizing Report Type and output mode

Important current behavior:

• known financial-statement procedures are forced to Report Type = FINANCIAL_STATEMENT
• known financial-statement procedures are forced to Dataset Output Mode = REPORT_JSON
• rowset-style contracts must not be configured as FINANCIAL_STATEMENT
• if the selected procedure is not approved for reporting, report-definition save is rejected

Current Approved Reporting Dataset Procedures

These are the reporting dataset procedures currently seeded in system_procedure_registry with allowed_for_reporting = 1.

Structured Financial Statement Procedures

Dataset Procedure	Display Name	Domain	Registry Contract	Setup Note
get_trial_balance_report	Trial Balance	GL	CUSTOM	Structured statement-style output. Validate against the report viewer contract before publishing.
get_balance_sheet_report	Balance Sheet	GL	CUSTOM	Financial statement procedure. Use FINANCIAL_STATEMENT with REPORT_JSON.
get_income_statement_report	Income Statement	GL	CUSTOM	Financial statement procedure. Use FINANCIAL_STATEMENT with REPORT_JSON.
get_cash_flow_report	Cash Flow	GL	CUSTOM	Financial statement procedure. Use FINANCIAL_STATEMENT with REPORT_JSON.
generate_comparative_fs_proc	Comparative Financial Statement	FS	CUSTOM	Financial statement procedure. Use FINANCIAL_STATEMENT with REPORT_JSON.
generate_consolidated_fs_proc	Consolidated Financial Statement	FS	CUSTOM	Financial statement procedure. Use FINANCIAL_STATEMENT with REPORT_JSON.
generate_financial_ratios_proc	Financial Ratios	FS	CUSTOM	Procedure-specific structured payload. Validate viewer compatibility before publishing.

Rowset And Paged Listing Procedures

Dataset Procedure	Display Name	Domain	Registry Contract	Setup Note
get_journal_listing_report	Journal Listing	GL	PAGED_RESULT	Use a list-style report with ROWSET.
get_budget_variance	Budget Variance	BUDGET	PAGED_RESULT	Use a list-style report with ROWSET.
list_loan_account	Loan Accounts	LOANS	PAGED_RESULT	Use a list-style report with ROWSET.
get_loan_transactions	Loan Transactions	LOANS	PAGED_RESULT	Use a list-style report with ROWSET.
list_deposit_accounts	Deposit Accounts	DEPOSITS	PAGED_RESULT	Use a list-style report with ROWSET.
list_deposit_transactions	Deposit Transactions	DEPOSITS	PAGED_RESULT	Use a list-style report with ROWSET.
get_reconciliations	Reconciliations	RECONCILIATION	PAGED_RESULT	Use a list-style report with ROWSET.
client_register_dataset_proc	Client Register	CLIENTS	PAGED_RESULT	Use a list-style report with ROWSET.
get_asset_register_report	Asset Register	ASSETS	PAGED_RESULT	Use a list-style report with ROWSET.

Custom Summary, Dashboard, And Analytical Procedures

Dataset Procedure	Display Name	Domain	Registry Contract	Setup Note
get_gl_account_statement	GL Account Statement	GL	CUSTOM	Validate whether the procedure returns row data or structured JSON before finalizing the viewer setup.
kpi_dashboard_proc	KPI Dashboard	DASHBOARD	CUSTOM	Usually paired with a summary or KPI-style viewer contract.
management_dashboard_proc	Management Dashboard	DASHBOARD	CUSTOM	Usually paired with a management dashboard style report definition.
portfolio_overview_dataset_proc	Portfolio Overview	PORTFOLIO	CUSTOM	Analytical structured payload. Validate the viewer contract before publishing.
portfolio_accounts_dataset_proc	Portfolio Accounts	PORTFOLIO	CUSTOM	Analytical structured payload. Validate the viewer contract before publishing.
portfolio_historical_dataset_proc	Portfolio Historical	PORTFOLIO	CUSTOM	Analytical structured payload. Validate the viewer contract before publishing.
cashflow_summary_dataset_proc	Cashflow Summary	CASHFLOW	CUSTOM	Summary-style structured payload. Validate the viewer contract before publishing.
risk_par_summary_dataset_proc	PAR Summary	RISK	CUSTOM	Summary-style structured payload. Validate the viewer contract before publishing.
risk_aging_dataset_proc	Risk Aging	RISK	CUSTOM	Analytical structured payload. Validate the viewer contract before publishing.
user_listing_report_proc	User Listing	ADMIN	CUSTOM	Validate whether the procedure is better rendered as a rowset or a structured JSON payload.
deposit_summary_dataset_proc	Deposit Summary	DEPOSITS	CUSTOM	Summary-style structured payload. Validate the viewer contract before publishing.

Worked Example 1: Create A Trial Balance Report

Use this flow for reports like GL_TRIAL_BALANCE.

1. Create Or Confirm The Report Group

Open Administration -> Reporting -> Report Catalog -> Report Groups.

If the group does not exist yet:

• click Create
• enter a stable group code
• enter a user-facing group name such as General Ledger Reports
• set sort order so it appears in the right place
• keep it active only when it is ready for use

This controls where users find the report later in Reporting -> Reports.

2. Create The Report Definition

Open Administration -> Reporting -> Report Catalog -> Report Definitions and create a new definition.

For a trial balance, the important fields are:

• Report Code: GL_TRIAL_BALANCE
• Report Name: Trial Balance
• Report Group: General Ledger Reports
• Dataset Procedure: get_trial_balance_report
• Report Type: FINANCIAL_STATEMENT
• Dataset Output Mode: REPORT_JSON
• Is Active: enable only after validation

What these choices mean:

• the report code is the stable system identity
• the group controls where it appears
• the dataset procedure is what the backend runs
• the output mode must match what the procedure returns

For list-style reports such as account statements or journal listings, the output mode is usually ROWSET. For structured statements like trial balance, it is usually REPORT_JSON.

3. Set The Default Parameters

Still in the report definition, set a sensible default payload.

For trial balance, common defaults are:

• date_from
• date_to
• any optional page size or scope defaults your procedure supports

Use defaults for the values most users should start with every time they open the report. Keep them practical and low-risk.

Good example defaults:

• current month
• last month
• year to date

4. Add Access Rules

Before publishing the report, assign the right audience.

In the report definition access sections:

• add allowed business units if visibility should be restricted
• add allowed roles if only finance, audit, or operations users should run it

Do not leave access wide open unless the report is intentionally global.

5. Add Drilldowns If The Report Needs Deeper Detail

For trial balance, a common drilldown is from an account balance row into an account statement.

In the drilldown configuration area:

• add a drilldown row
• choose the Target Drilldown Procedure
• set a clear Action Label such as View Account Statement
• keep Allowed Row Types as DETAIL unless the action should also appear on other row types
• add Required Row Fields when the clicked row must contain a specific identifier such as coa_id

What PinkApple carries automatically at runtime:

• the current report filters such as date range, business unit scope, and currency where applicable
• the clicked row context such as account identifiers, line identifiers, or business unit identifiers when present

What the user does not need to do:

• manually map date fields one by one
• manually build JSON
• manually enter procedure payload syntax

The user experience is simple:

• user runs trial balance
• user clicks a detail row
• PinkApple opens the configured follow-up report with the carried row context

Drilldowns will only work well if:

• the source row matches the configured row type
• the source row contains the required row fields
• the target procedure can use the carried report filters and clicked-row identifiers
• the current user has permission to run the target

6. Run The Report And Validate It

Open Reporting -> Reports.

Then:

1. select Trial Balance
2. confirm the parameter form opens with the defaults you configured
3. run the report
4. validate totals, grouping, exports, and any drilldown links

Do not treat the report definition as complete until runtime validation passes.

7. Save Useful Presets

After the report works, save presets for real operator use.

Good presets for trial balance:

• This Month
• Last Month
• Year To Date

Use presets for repeatable user choices. Use the default payload only for the initial starting state.

Worked Example 2: Create An Income Statement In The UI

Use this flow for reports like GL_INCOME_STATEMENT.

1. Create The Financial Statement Template

Open Administration -> Reporting -> Financial Statements -> Templates.

Create a template such as:

• Template Code: IS_MAIN
• Template Name: Statement of Comprehensive Income
• Template Type: INCOME_STATEMENT

This is the reusable statement shell. The report definition comes later.

2. Create The Statement Lines

Open Line Items and add the statement structure in reading order.

Typical income statement lines include:

• revenue header
• interest income detail
• fee income detail
• other income detail
• total revenue subtotal
• expense header
• interest expense detail
• personnel expense detail
• administrative expense detail
• total expenses subtotal
• net income total

Use the line hierarchy intentionally:

• HEADER for sections
• DETAIL for mapped balances
• SUBTOTAL for section rollups
• TOTAL for final output rows

3. Map Accounts To The Right Lines

Open Account Mappings.

For each meaningful detail line:

• choose the statement line
• choose the chart of account to include
• confirm the mapping direction and business meaning are correct

This is the step that turns the template into a real statement.

4. Create The Column Definitions

Open Column Definitions.

Typical income statement columns are:

• Current Period
• Prior Year
• Budget
• Variance

When creating column definitions:

• choose the template
• enter the column label
• choose the amount type
• set display format and decimal places

Important behavior note:

• the generated column code is system-managed in the current UI, so users mainly control the label and presentation settings

Important UI notes:

• Currency Used should be selected from the currency lookup
• Budget Version should be selected from the budget lookup when the column is budget-based
• the active state should reflect whether the column is ready for runtime use

5. Create The Column Sets

Open Column Sets.

This is where you bundle column definitions into runnable layouts such as:

• Current Period
• Comparative
• Budget vs Actual

For example:

• IS_CURRENT_PERIOD
• IS_COMPARATIVE
• IS_BUDGET_VS_ACTUAL

The template defines the rows. The column set defines how users will view them.

6. Add Notes If The Statement Needs Narrative Disclosure

Open Notes.

Use notes for disclosures, commentary, and supporting narrative that belong to the statement package.

When creating a note:

• link it to the right template or report context
• choose the fiscal year from the fiscal year lookup
• set the display order and title clearly

7. Create The Report Definition

Go back to Report Definitions and create the runnable report:

• Report Code: GL_INCOME_STATEMENT
• Report Name: Income Statement
• Dataset Procedure: generate_comparative_fs_proc
• Report Type: FINANCIAL_STATEMENT
• Dataset Output Mode: REPORT_JSON

Set default parameters such as:

• template_code = IS_MAIN
• column_set_code = IS_CURRENT_PERIOD
• date_from
• date_to

8. Run It And Validate The Full Flow

Open Reporting -> Reports, select the income statement, run it, and check:

• line order is correct
• mapped balances appear where expected
• comparative columns are aligned
• budget columns only appear when appropriate
• notes appear where expected
• export output is readable

Worked Example 3: Design Financial Statement Lines For A Balance Sheet

Use this section when you are in Administration -> Reporting -> Financial Statements -> Line Items and want to build the balance sheet structure correctly before mappings.

This is the most important design rule:

• Templates define which statement you are building
• Lines define what users read and where values roll up
• Account Mappings decide which balances feed each detail line

If the line structure is weak, the statement may still run, but it will be hard to read, hard to reconcile, and hard to maintain.

1. Understand What A Line Really Is

An FS line is a statement row with business meaning.

Some lines are only structural:

• section headers
• subtotals
• totals

Some lines are actual balance carriers:

• cash
• receivables
• savings deposits
• retained earnings

In practice, design the statement so:

• DETAIL lines are the rows that receive mappings
• HEADER, SUBTOTAL, and TOTAL lines organize and roll up those detail rows
• the line order matches the order finance expects to read the statement

2. What Each Line Field Means In The UI

When you create or edit a line, these are the fields that matter most.

Field	What it means	Practical impact
Template	The financial statement template this line belongs to	A line only rolls up and renders inside its own template
Generated Line Code	System-generated row identity	You do not design this manually in the current UI; PinkApple generates it and uses it as the stable internal reference
Label	The text users read on the statement	This is the business-facing row name, so make it clear and finance-readable
Line Type	Whether the line is a HEADER, DETAIL, SUBTOTAL, or TOTAL	This controls whether the row is structural or a real detail row that should carry mapped balances
Balance Source	Where the amount should come from	For balance sheets, this is usually GL_BALANCE; MANUAL is only for controlled manual entries; CASHFLOW_SPECIAL is for cash flow work, not balance sheets
Sign Behavior	How debit or credit natural balances should display	This determines whether a liability or equity line shows as a positive presentation value or flips sign
Cashflow Section	Optional section for cash-flow templates	Leave this blank for balance sheet lines
Parent Line	The row this line belongs under	This controls hierarchy and rollup behavior, so the wrong parent means the wrong subtotal or total
Display Order	The sequence in which the row appears	This controls reading order and final statement structure
Active	Whether the line is currently in use	Inactive lines should not be treated as part of the live statement design

3. How Each Field Affects Runtime

These are the design consequences behind those fields.

Label

This is what appears in the runtime statement. It should describe the financial meaning, not technical setup.

Good examples:

• Cash and Cash Equivalents
• Loans and Advances
• Member Savings Deposits
• Retained Earnings

Weak labels create confusion later even when the numbers are correct.

Line Type

Use line type intentionally:

• HEADER: section organizer such as Assets or Current Liabilities
• DETAIL: a row that should represent a real mapped balance
• SUBTOTAL: a section rollup such as Total Current Assets
• TOTAL: a final or major rollup such as Total Assets

For maintainable statements, treat DETAIL as the main mapping layer. In other words:

• map accounts to the detail rows
• use headers, subtotals, and totals to organize and summarize

Balance Source

For balance sheets:

• use GL_BALANCE for most lines
• use MANUAL only when the value is intentionally maintained outside ordinary GL aggregation
• do not use CASHFLOW_SPECIAL on a balance sheet

If balance source is wrong, the line can appear empty or use the wrong type of amount.

Sign Behavior

For balance-sheet presentation:

• asset lines are usually fine with NORMAL
• liability lines usually need INVERT_IF_CREDIT
• equity lines usually need INVERT_IF_CREDIT

Why this matters:

• many liability and equity accounts are naturally credit-balance accounts
• if sign behavior is not aligned with the presentation intent, the statement may show negative-looking values where users expect positive reporting values

Even when NORMAL happens to work for a line, being explicit for liabilities and equity makes the design easier to understand later.

Parent Line

Parent line creates the tree.

That tree drives:

• where rows appear
• which section a detail belongs to
• which subtotal or total accumulates its descendant detail lines

If the parent is wrong, the numbers may still exist, but the statement logic becomes misleading.

Display Order

This is not cosmetic. It is how the statement is read.

Build the order exactly as the finance team expects:

1. section
2. subsection
3. detail rows
4. subtotal
5. next subsection
6. final total

4. Recommended Balance-Sheet Line Pattern

A standard balance sheet usually follows this reading flow:

1. Assets
2. Current Assets
3. current-asset detail rows
4. Total Current Assets
5. Non-Current Assets
6. non-current-asset detail rows
7. Total Non-Current Assets
8. Total Assets
9. Liabilities
10. liability detail rows
11. Total Liabilities
12. Equity
13. equity detail rows
14. Total Equity
15. Total Liabilities and Equity

That is the pattern you want to recreate in Line Items.

5. Concrete Balance-Sheet Example Flow

Here is a good example of how to build the line structure.

Order	Line Label	Line Type	Parent Line	Balance Source	Sign Behavior	Why it exists
100	Assets	HEADER	none	GL_BALANCE	NORMAL	Top-level asset section
110	Current Assets	HEADER	Assets	GL_BALANCE	NORMAL	Groups short-term asset rows
120	Cash and Cash Equivalents	DETAIL	Current Assets	GL_BALANCE	NORMAL	Holds mapped cash and bank balances
130	Loans and Advances	DETAIL	Current Assets	GL_BALANCE	NORMAL	Holds loan principal or receivable balances
140	Interest Receivable	DETAIL	Current Assets	GL_BALANCE	NORMAL	Holds accrued interest receivable
150	Other Receivables	DETAIL	Current Assets	GL_BALANCE	NORMAL	Holds other short-term receivables
160	Investments	DETAIL	Current Assets or Non-Current Assets depending on policy	GL_BALANCE	NORMAL	Holds investment balances
190	Total Current Assets	SUBTOTAL	Current Assets	GL_BALANCE	NORMAL	Summarizes the section
200	Non-Current Assets	HEADER	Assets	GL_BALANCE	NORMAL	Groups longer-term assets
210	Property and Equipment	DETAIL	Non-Current Assets	GL_BALANCE	NORMAL	Holds fixed asset balances
220	Other Non-Current Assets	DETAIL	Non-Current Assets	GL_BALANCE	NORMAL	Holds longer-term asset balances outside PPE
290	Total Non-Current Assets	SUBTOTAL	Non-Current Assets	GL_BALANCE	NORMAL	Summarizes the section
300	Total Assets	TOTAL	Assets	GL_BALANCE	NORMAL	Main asset total
400	Liabilities	HEADER	none	GL_BALANCE	INVERT_IF_CREDIT	Top-level liabilities section
410	Current Liabilities	HEADER	Liabilities	GL_BALANCE	INVERT_IF_CREDIT	Groups short-term liabilities
420	Member Savings Deposits	DETAIL	Current Liabilities	GL_BALANCE	INVERT_IF_CREDIT	Holds deposit liabilities
430	Borrowings	DETAIL	Current Liabilities	GL_BALANCE	INVERT_IF_CREDIT	Holds borrowed funds
440	Interest Payable	DETAIL	Current Liabilities	GL_BALANCE	INVERT_IF_CREDIT	Holds accrued interest payable
450	Accounts Payable	DETAIL	Current Liabilities	GL_BALANCE	INVERT_IF_CREDIT	Holds payables
490	Total Current Liabilities	SUBTOTAL	Current Liabilities	GL_BALANCE	INVERT_IF_CREDIT	Summarizes the section
500	Total Liabilities	TOTAL	Liabilities	GL_BALANCE	INVERT_IF_CREDIT	Main liabilities total
600	Equity	HEADER	none	GL_BALANCE	INVERT_IF_CREDIT	Top-level equity section
610	Share Capital	DETAIL	Equity	GL_BALANCE	INVERT_IF_CREDIT	Holds share capital
620	Statutory Reserves	DETAIL	Equity	GL_BALANCE	INVERT_IF_CREDIT	Holds reserve balances
630	Retained Earnings	DETAIL	Equity	GL_BALANCE	INVERT_IF_CREDIT	Holds accumulated earnings
690	Total Equity	TOTAL	Equity	GL_BALANCE	INVERT_IF_CREDIT	Main equity total
700	Total Liabilities and Equity	TOTAL	none	GL_BALANCE	NORMAL or explicit presentation choice	Final balance-sheet control total

6. How To Build That Example In The UI

In Line Items, create the rows in this order:

1. create the root section headers first
2. create the subsection headers under each root
3. create all DETAIL rows under the right subsection
4. create the SUBTOTAL rows after the detail rows
5. create the larger TOTAL rows last

As you build:

• keep Cashflow Section blank
• use GL_BALANCE unless you have a specific approved reason not to
• use explicit INVERT_IF_CREDIT for liability and equity detail lines
• keep the display order spaced cleanly so later insertions are easy

7. What To Map And What Not To Map

After the line structure is ready, move to Account Mappings.

As a practical rule:

• map DETAIL rows
• do not rely on headers to carry balances
• use subtotals and totals as structural rollup rows

That gives you a cleaner reconciliation model because each mapped balance has a clear business row and each rollup row has a clear presentation role.

8. Quick Validation Checklist For Balance-Sheet Lines

Before you leave Line Items, confirm:

• every section appears in the right reading order
• every DETAIL row sits under the correct parent
• liabilities and equity have sensible sign behavior
• no balance-sheet line has cash-flow-only configuration
• the labels are finance-readable
• the rows are active and ready for approval

Only after this should you move on to mappings and runtime validation.

9. Design The Balance-Sheet Account Mappings

Use this section when you are in Administration -> Reporting -> Financial Statements -> Account Mappings after the line structure is already approved.

This is the practical rule:

• Lines describe the meaning of the row
• Mappings decide which GL balances are allowed to feed that row
• Mapping Level decides how broad or narrow that selection is
• Mapping Type decides whether the selection is added or removed
• Weight decides whether the included balance is taken fully or proportionally

If the mappings are wrong, the line labels may look correct while the numbers underneath them are misleading.

10. What The Mapping Fields Mean In Practice

Field	What it means	Practical use
FS Line	The statement row you are feeding	Choose the DETAIL line that should carry the balance
Mapping Level	How broad the account selection should be	Start as specific as possible and only go broader when the business meaning is still correct
Foundation Account	Broadest account family	Use when the entire family belongs on the same line and exceptions are rare
Major Account	Mid-level account grouping	Use when one major account maps cleanly to one reporting line
Account Header	Narrower group under a major account	Useful when several related COAs belong together on one line
Chart Of Account	Most specific posting account	Best when you want exact control of which account feeds the line
Mapping Type	Whether the selected accounts are included or excluded	INCLUDE adds balances to the line; EXCLUDE removes matched accounts from a broader include
Weight	Multiplier applied to included balances	Keep it 1.0000 for normal reporting; use another value only for a deliberate allocation rule
Active	Whether the mapping is live	Only active mappings should be expected in runtime output

Think about Mapping Level like this:

• FOUNDATION: broadest and highest-risk if unrelated accounts sit under the same family
• MAJOR_ACCOUNT: still broad, but narrower than foundation
• ACCOUNT_HEADER: good when a clean account bucket maps to one reporting row
• COA: safest when precision matters

For most finance teams, the default design discipline is:

1. map at COA level when accuracy matters most
2. map at ACCOUNT_HEADER or MAJOR_ACCOUNT only when the grouping is already clean
3. use FOUNDATION only when you are certain the whole family belongs on that one row

11. What Mapping Type And Weight Actually Do

These two fields are the ones that usually confuse users.

Mapping Type

• INCLUDE means the selected account scope is allowed to feed the line
• EXCLUDE means the selected account scope is removed from a broader include

Use EXCLUDE when you intentionally start broad and then carve out exceptions.

Good example:

• include all deposit liability accounts under one account header into Member Savings Deposits
• exclude one suspense or clearing account that should not appear in that presentation row

Bad example:

• using EXCLUDE on its own without a broader include and expecting it to create a balance

EXCLUDE is a refinement tool, not a primary source of statement values.

Weight

Weight is a multiplier on included balances.

Typical meaning:

• 1.0000 = include 100% of the balance
• 0.5000 = include 50% of the balance
• 2.0000 = double the included value

For ordinary financial statement design, you should almost always leave Weight at 1.0000.

Use a non-default weight only when the reporting policy explicitly requires a proportional allocation. Do not use weight as a shortcut for correcting bad mappings or forcing the statement to balance.

Important practical note:

• on EXCLUDE rows, weight is not the thing doing the business work
• the real effect of EXCLUDE is removal of the matched accounts from the included set
• in normal use, leave exclusion rows at 1.0000 too

12. Balance-Sheet Mapping Example

Here is a concrete example of how a balance-sheet design might look after the lines are created.

FS Line	Mapping Level	Selected Account Scope	Mapping Type	Weight	Why this is correct
Cash and Cash Equivalents	COA	Main Bank Account	INCLUDE	1.0000	This line should include the exact bank balance
Cash and Cash Equivalents	COA	Back Office Cash	INCLUDE	1.0000	Physical cash belongs in the cash presentation line
Cash and Cash Equivalents	COA	Cash Suspense Account	EXCLUDE	1.0000	Suspense balances should not inflate operating cash presentation
Loans and Advances	ACCOUNT_HEADER	Performing Loan Principal Accounts	INCLUDE	1.0000	Several loan COAs may reasonably roll into one lending line
Interest Receivable	COA	Interest Receivable Control	INCLUDE	1.0000	This is usually a tightly controlled single-purpose balance
Member Savings Deposits	ACCOUNT_HEADER	Savings Deposit Liability Accounts	INCLUDE	1.0000	A clean liability bucket can map together into one reporting line
Member Savings Deposits	COA	Dormant Savings Clearing Account	EXCLUDE	1.0000	Remove balances that belong to another presentation or control line
Share Capital	COA	Share Capital Control	INCLUDE	1.0000	Equity control account should map directly and explicitly
Retained Earnings	COA	Retained Earnings Control	INCLUDE	1.0000	Precision matters for equity presentation

What this example is showing:

• cash is usually best mapped precisely at COA level
• lending and deposit lines may be mapped at ACCOUNT_HEADER level when the grouping is already clean
• EXCLUDE is useful when a broad include is mostly correct but has a known exception
• equity lines are usually better when mapped narrowly and explicitly

13. How To Decide The Right Mapping Level

Use this decision pattern:

1. ask whether the line has one exact control account
2. if yes, map at COA
3. if no, ask whether a single account header cleanly represents the row
4. if yes, map at ACCOUNT_HEADER
5. if not, move broader only when the entire group truly belongs together

Do not choose a broader level just because it is faster to configure. Broader mappings are easier to create, but they are harder to audit later.

Also avoid mapping the same line repeatedly across overlapping scopes unless there is a deliberate design reason. A cleaner pattern is:

• start with one clear include scope
• add precise excludes only where necessary

14. What Not To Map

For balance-sheet reporting, do not treat every row as a mapping target.

As a default design rule:

• map DETAIL rows
• do not use headers as balance carriers
• do not use subtotals as primary mapping rows
• do not use totals as direct mapping rows unless your reporting design explicitly requires it

This keeps the statement readable and makes reconciliations easier.

15. Create The Balance-Sheet Column Definitions

Use this section when you are in Administration -> Reporting -> Financial Statements -> Column Definitions.

For a balance sheet, columns usually represent comparative views of position at different points in time. In practice, most balance-sheet designs start with:

• Current Period
• Prior Year

Only add more columns when they carry clear reporting value.

This is the practical rule:

• Lines define what the row means
• Mappings define which balances feed the row
• Columns define how the row is presented at runtime
• Column Sets decide which columns are bundled together when the report runs

16. What Each Column Definition Field Means

Field	What it means	Practical impact
Template	Which financial statement template this column belongs to	The column is only available for that template
Column Label	The heading users see at runtime	Use finance-readable names such as Current Period or Prior Year
Amount Type	What kind of number the column represents	Common values are ACTUAL, BUDGET, VARIANCE, and VARIANCE %; balance sheets usually start with ACTUAL
Display Format	How the value is presented	AMOUNT is the standard balance-sheet choice; percentage and ratio are special-purpose
Translation Method	How amounts should be translated when currency translation matters	CLOSING is the most typical balance-sheet choice
Currency Used	Optional presentation currency	Select a currency when the statement must present in a specific currency; otherwise leave it blank to use the default reporting currency
Budget Version	Which budget set to use	Only relevant when Amount Type is BUDGET
Sort Order	Left-to-right column order	Lower numbers appear first
Decimal Places	Display precision	2 is the normal finance default unless policy says otherwise
Show Zero Values	Whether zero amounts should still display	Useful when you want structural consistency across branches or periods
Active	Whether the column is available for live runtime use	Inactive columns should not be relied on in report execution

17. Balance-Sheet Column Definition Example

Here is a clean starting example for a comparative balance sheet.

Column Label	Amount Type	Display Format	Translation Method	Currency Used	Budget Version	Sort Order	Decimal Places	Show Zero Values	Why it works
Current Period	ACTUAL	AMOUNT	CLOSING	blank or reporting currency	not used	10	2	On	Main as-of reporting column
Prior Year	ACTUAL	AMOUNT	CLOSING	blank or same reporting currency	not used	20	2	On	Gives a clean comparative column
Budget Position	BUDGET	AMOUNT	CLOSING	reporting currency if required	approved budget version	30	2	Off or On based on policy	Optional only if your organization budgets balance-sheet positions

What this example is telling you:

• a standard balance sheet is usually well served by two ACTUAL columns first
• BUDGET is optional, not automatic
• balance-sheet columns should stay easy to read before they become sophisticated

18. How To Build The Balance-Sheet Columns In The UI

In Column Definitions, create the columns in this order:

1. create Current Period
2. create Prior Year
3. create any optional budget or variance columns only if the business already agreed on them

For the first two columns, the safest setup is:

• same template as the balance-sheet lines
• Amount Type = ACTUAL
• Display Format = AMOUNT
• Translation Method = CLOSING
• Sort Order = 10, then 20
• Decimal Places = 2
• Active = On

If you add a budget-based column:

• change Amount Type to BUDGET
• choose the budget version from the lookup
• confirm finance actually wants a balance-sheet budget presentation before you publish it

19. How Column Definitions And Column Sets Work Together

Column definitions do not become runnable layouts by themselves.

After you create the column definitions:

• open Column Sets
• bundle the columns into a named runtime layout
• give the set a clear purpose such as Current Period or Comparative

For a balance-sheet example:

• BS_CURRENT_PERIOD could contain only Current Period
• BS_COMPARATIVE could contain Current Period and Prior Year
• BS_BUDGET_VIEW should only exist if budgeted balance-sheet reporting is a real business requirement

20. Quick Validation Checklist For Mappings And Columns

Before you leave the balance-sheet setup, confirm:

• every mapped row is a true DETAIL row
• broad mappings have been reviewed for accidental overreach
• every EXCLUDE row has a clear business reason
• Weight is still 1.0000 everywhere except documented allocation cases
• the balance-sheet columns are in the expected order
• budget columns exist only where finance really uses them
• the column set matches the runtime layout users are supposed to run

How Drilldowns Work In Practice

Drilldowns are not a second report list. They are guided follow-up actions from a report row.

Think of them like this:

• source report shows a summary or balance
• user clicks a supported row
• PinkApple opens a deeper report with row context already filled in

A good drilldown setup has four parts:

1. a source report with the fields you want to pass
2. a target report that can accept those fields
3. a clear label the user understands
4. permission for the same user to run the target

What Users Configure In The UI

In Administration -> Reporting -> Report Catalog -> Report Definitions, each drilldown entry uses a simple builder:

Field	What It Means	Typical Example
Action Label	the text shown in the runtime context menu	View Account Statement
Target Drilldown Procedure	the approved drilldown procedure to run	get_gl_account_statement
Allowed Row Types	which row types should show the action	DETAIL
Required Row Fields	row keys that must exist before the action appears	coa_id

This is intentionally simpler than raw JSON.

Users are not expected to:

• write drilldown_config by hand
• build token payloads manually
• maintain procedure-level payload syntax in the form

Where Drilldowns Are Stored

The drilldown setup spans a small set of reporting tables:

Table	Purpose
report_definition	stores the source report, including drilldown_config
system_procedure_registry	stores approved drilldown procedures and their registry metadata
report_group	groups the source report in the catalog
report_saved_preset	stores reusable runtime presets for the source report

Operationally this means:

• the source report stores its follow-up actions in report_definition.drilldown_config
• the selectable drilldown targets come from system_procedure_registry
• only procedures registered with proc_type = DRILLDOWN should be used as drilldown targets
• runtime execution happens through reporting/drilldown/run

No extra custom drilldown-authoring endpoint is required when the Procedure Registry is maintained correctly.

Typical examples:

• Trial Balance -> Account Statement
• Trial Balance -> Journal Listing
• Summary Dashboard -> Detail Listing

How Runtime Decides Whether To Show The Action

PinkApple only shows a configured drilldown on a row when all of these are true:

• the row is not a header, subtotal, total, separator, or blank row
• the row type matches the configured Allowed Row Types, when that list is set
• the row contains every configured Required Row Field
• the current user is allowed to run the drilldown target

This is why the same report can show a drilldown on one row and hide it on another.

Example: Trial Balance To Account Statement

A practical trial balance drilldown usually looks like this:

• Action Label: View Account Statement
• Target Drilldown Procedure: get_gl_account_statement
• Allowed Row Types: DETAIL
• Required Row Fields: coa_id

At runtime:

• the user opens the trial balance
• the action only appears on detail rows that carry coa_id
• when the user clicks the action, PinkApple carries the active report filters and the selected row context automatically
• the target procedure returns the supporting account statement rows

Example: Trial Balance To Journal Listing

Another practical pattern is:

• Action Label: View Journals
• Target Drilldown Procedure: get_journal_listing_report
• Allowed Row Types: DETAIL
• Required Row Fields: coa_id

If a drilldown is configured but does not show or does not run, usually one of these is wrong:

• the source rows do not include the required row fields
• the row type is not one of the allowed row types
• the target procedure is not registered as an approved active drilldown procedure
• the target report is not active
• the user can run the source but not the target

How To Use Presets Properly

Presets are for repeat usage. They are not the same thing as the report definition default payload.

Use the default payload for:

• the first values a user sees when opening the report
• safe starting filters
• template and column set defaults for statement reports

Use presets for:

• named working views people run often
• period shortcuts
• team-specific operating views
• comparative and budget layouts

Examples of good preset names:

• This Month
• Last Month
• Year To Date
• Comparative View
• Budget Vs Actual
• Head Office Only

Runtime behavior to remember:

• presets load after the user selects a report
• a default preset can auto-apply when the report opens
• users can still change parameters after applying a preset
• a preset should describe a real business use case, not just a random parameter combination

Recommended Build Order For New Reports

When creating a new report through the UI, use this order:

1. confirm the backend procedure or statement engine path
2. create the report group if needed
3. create the report definition
4. for financial statements, create templates, lines, mappings, columns, column sets, and notes
5. add access rules
6. add drilldowns
7. validate in Reporting -> Reports
8. create presets only after validation

Common Mistakes To Avoid

• creating the report definition before you understand the output mode
• publishing a report before runtime validation
• using presets to compensate for a bad default payload
• adding drilldowns before the target report is ready
• forgetting access assignments on sensitive finance reports
• building statement templates without checking account mappings

Related Pages

• Administration & Setup
• Report Catalog
• Financial Statements
• Running Reports
• Snapshots, Presets & Schedules