General Report Manager

General Report structure

Typically, a general report consists of:

A SQL request to retrieve data fro the database (e.g.)
```
select * from assets;
```
An (optional) Lua script, which allows to modify the record data, create summaries, ...
A html template which formats and displays the output.

SQL request

A SQL script which retrieves the data from DB. It may include all SQLITE defined operations.

Lua script

The Lua script may implement these APIs:

Record specific function: handle_record

This function is called for every record retrieved by SQL and allows to modify a record an extract values for a summary:

function handle_record(record)
  -- Your logic to modify a record and apply this function against every record from SQL.
  record:set("extra_value", record::get("VALUE") * 2);
end

Request specific function: complete

This function is called at the end, after all records have been processed.

function complete(result)
  -- Put some accumulated value and apply this function after SQL completes.
  result:set("TOTAL", 1000);
end

HTML template

The HTML template formats the output. It may contain Javascript and CSS for advanced formatting and processing.

A default template can be automatically generated and later modified. See below.

Opening General Report Manager

Call General Report Manager (GRM) tool or use main menu Tools → General Report Manager….

Adding a new report

Either a report is created from scratch or an existing report is imported and adapted.

Creating the report from scratch

Using the Navigator, mouse right-click on the Reports label.
Choose the menu item: New Empty Report.
Enter the name for the new report group and the new name.
Rename reports, if required, by right-clicking on the menu item: Rename Report.
Five tabs will be displayed: Output, SQL, Lua, Template and Description.
Choose the tab: SQL, insert the SQL script into the editor window, check that the SQL script finishes with a semicolon, then press the Save button.
Press the Test button to execute the SQL script.
If the test is passed the Create Template button will become available. Press it to generate an HTML template for the report.
Press the Run button to get the HTML report in the Output window.

Creation of a new report from scratch animation

Importing report

There are a lot of reports provided by users or developers on GitHub.

Report Parameters

MMEX provides the possibility to use report parameters in the SQL script, which are set with widgets and evaluated during the report generation.
These parameters are available:

Parameter	Description	Widget
"&single_date"	A calender widget
"&begin_date" and "&end_date"	A date range widget
"&only_years"	A year selection drop down
"&single_time"	A time widget
"&stock_selection"	A list of stock names
"&filter"	A generic text field for filter or similar conditions, together with a reset button (see description below)
"&selection"	A generic selection list widget (see example below), which shows a list of predefined values

Notes:

All parameters must be enclosed in quotes.
"&begin_date" and "&end_date", "&single_date" and "&only_years" are mutually exclusive
Report parameters can not be out-commented, if they are in the SQL (regular or as comment), they will be displayed.

Example

For example, this SQL script returns all accounts balances for the specified date using a &single_date:

SQL

with
b as  (
    select
        ACCOUNTID,
        STATUS,
        (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT,
        TRANSDATE
      from
        CHECKINGACCOUNT_V1
      where
        STATUS <> 'V'
        and TRANSDATE <= '&single_date'

    union all

    select
        TOACCOUNTID,
        STATUS,
        TOTRANSAMOUNT,
        TRANSDATE
      from
        CHECKINGACCOUNT_V1,
        var
      where
        TRANSCODE = 'Transfer'
        and STATUS <> 'V'
        and TRANSDATE <= '&single_date'
    )

select
    a.ACCOUNTNAME,
    a.ACCOUNTTYPE,
    total(TRANSAMOUNT) + a.INITIALBAL as Balance
  from
    ACCOUNTLIST_V1 as a
    left join b on a.ACCOUNTID=b.ACCOUNTID
  where
    a.STATUS = 'Open'
    and a.ACCOUNTTYPE !='Investment'
  group by
    a.ACCOUNTNAME
  order by
    a.ACCOUNTNAME asc;

Explanation:

In lines 12 and 27 the value of the single date widget will be inserted, before processing the SQL

Resulting report

GRM report output using single date parameter
example

Filter Widget

With the filter widget an arbitrary value can be provided for the SQL processing.

Example with filter Widget

The following show the previous example extended with a generic filter to return specific accounts on a specified date using a &single_date and a &filter widget:

SQL


with
  var as (
    select
      -- Filter defined as as variable
      '&filter' AS ACC_FILTER                    -- <= NEW

      -- Single Date is defined here as variable:
      '&single_date' AS MAX_DATE,
  ),

  b as  (
    select
        ACCOUNTID,
        STATUS,
        (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT,
        TRANSDATE
      from
        CHECKINGACCOUNT_V1,
        var
      where
        STATUS <> 'V'
        and TRANSDATE <= var.MAX_DATE

    union all

    select
        TOACCOUNTID,
        STATUS,
        TOTRANSAMOUNT,
        TRANSDATE
      from
        CHECKINGACCOUNT_V1,
        var
      where
        TRANSCODE = 'Transfer'
        and STATUS <> 'V'
        and TRANSDATE <= var.MAX_DATE
  )

select
    a.ACCOUNTNAME,
    a.ACCOUNTTYPE,
    total(TRANSAMOUNT) + a.INITIALBAL as Balance
  from
    ACCOUNTLIST_V1 as a
    left join b on a.ACCOUNTID=b.ACCOUNTID,
    var
  where
    a.STATUS = 'Open'
    and a.ACCOUNTTYPE !='Investment'
    and a.ACCOUNTNAME like var.ACC_FILTER                      -- <= Filter variable is applied here
  group by
    a.ACCOUNTNAME
  order by
    a.ACCOUNTNAME asc;

Explanation:

The important line is 5, which defines the filter widget
In line 51 the filter variable is applied

Generated report example:

filter example output

Notes:

The value entered in the filter field is directly processed in the SQL
Used place holders must be the SQLITE placeholder '%', e.g. a filter value of "M%"" will select all values, which start with "M".

Adapting the &filter with options

With the help of filter options the name of the filter and a default value can be set. The options are provided as a SQLITE comment in the SQL script with following syntax:

-- &filter?name="any value"&default="any value"

SQL Example:

Extending the previous example to name the filter "Account:"

SQL

--
-- Optional Filter name definition:
-- '&filter?name="Account:"

with
  var as (
    select
       '&single_date' AS MAX_DATE,
       '&filter' AS ACC_FILTER
       :

will create the filter widget:

Advanced filter handling

If the filter should be used without place holders, it is possible to modify the behavior in SQL:

SQL Script:

SQL

--
-- Optional Filter name definition:
-- '&filter?name="Account:

with
  var as (
    select
       '&single_date' AS MAX_DATE,
        Case
          WHEN '&filter' = '' THEN '%'
          WHEN substr('&filter', -1) = '%' THEN '&filter'
          ELSE
            '&filter' || '%'
          END
          as A_FILTER
  ),

  b as  (
    select
        ACCOUNTID,
        STATUS,
        (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT,
        TRANSDATE
      from
        CHECKINGACCOUNT_V1,
        var
      where
        STATUS <> 'V'
        and TRANSDATE <= var.MAX_DATE

    union all

    select
        TOACCOUNTID,
        STATUS,
        TOTRANSAMOUNT,
        TRANSDATE
      from
        CHECKINGACCOUNT_V1,
        var
      where
        TRANSCODE = 'Transfer'
        and STATUS <> 'V'
        and TRANSDATE <= var.MAX_DATE
  )

select
    a.ACCOUNTNAME,
    a.ACCOUNTTYPE,
    total(TRANSAMOUNT) + a.INITIALBAL as Balance
  from
    ACCOUNTLIST_V1 as a
    left join b on a.ACCOUNTID=b.ACCOUNTID,
    var
  where
    a.STATUS = 'Open'
    and a.ACCOUNTTYPE !='Investment'
    and a.ACCOUNTNAME like var.ACC_FILTER
  group by
    a.ACCOUNTNAME
  order by
    a.ACCOUNTNAME asc;

Here the placeholder will be automatically added during SQL processing, therefore just by entering the first letters the matching accounts will be selected.

will create the filter output:

Selection Widget

With the &selection widget a predefined set of values is provided for SQL processing.

The selections values are defined as SQLITE comment, together with an optional name and default, using following syntax:

-- &selection?values=[value],[value2],[value3],...&name=[name]&default=[default value]

The values list is mandatory, otherwise the selection widget will be displayed as an empty list.

The name and default parameters are optional.

Selection widget example:

The following show the previous (filter) example extended with an additional &selection widget, which allows to select the accounts depending on their state.

SQL

--
-- Selection value definition (required):
-- '&selection?name="Status:"&values="Open","Closed","All"&default="All"
--
-- Optional Filter name definition:
-- '&filter?name="Account:"
--
with
  var as (
    select
       '&single_date' AS MAX_DATE,

        -- Filter Widget for account name:
        Case
          WHEN '&filter' = '' THEN '%'
          WHEN substr('&filter', -1) = '%' THEN '&filter'
          ELSE
            '&filter' || '%'
          END
          as A_FILTER,

        -- Selection Widget for State:
        Case
          WHEN '&selection' = 'All' THEN '%'
          ELSE
            '&selection'
          END
          as A_SELECTION
  ),

  b as  (
    select
        ACCOUNTID,
        STATUS,
        (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT,
        TRANSDATE
      from
        CHECKINGACCOUNT_V1,
        var
      where
        STATUS <> 'V'
        and TRANSDATE <= var.MAX_DATE

    union all

    select
        TOACCOUNTID,
        STATUS,
        TOTRANSAMOUNT,
        TRANSDATE
      from
        CHECKINGACCOUNT_V1,
        var
      where
        TRANSCODE = 'Transfer'
        and STATUS <> 'V'
        and TRANSDATE <= var.MAX_DATE
  )

select
    a.ACCOUNTNAME,
    a.ACCOUNTTYPE,
    total(TRANSAMOUNT) + a.INITIALBAL as Balance,
    a.STATUS
  from
    ACCOUNTLIST_V1 as a
    left join b on a.ACCOUNTID=b.ACCOUNTID,
    var
  where
    a.STATUS like var.A_SELECTION                              -- <= The selection statement!!
    and a.ACCOUNTTYPE !='Investment'
    and a.ACCOUNTNAME like var.A_FILTER
  group by
    a.ACCOUNTNAME
  order by
    a.ACCOUNTNAME asc;

Explanation:

The important line is 3, which defines the selection widget
Lines 22 - 28 maps the selection value: If All is selected, the SQLITE placeholder symbol % is used, otherwise the selected value
In line 70 the selection condition is applied

Generated report example:

selection example output

Exporting report definition

If you have created any helpful report it may be exported into ZIP file.