## 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

1. Using the Navigator, mouse right-click on the ``Reports`` label.
2. Choose the menu item: ``New Empty Report``.
3. Enter the name for the new report group and the new name.
4. Rename reports, if required, by right-clicking on the menu item: ``Rename Report``.
5. Five tabs will be displayed: Output, SQL, Lua, Template and Description.
6. 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.
7. Press the ``Test`` button to execute the SQL script.
8. If the test is passed the ``Create Template`` button will become available. Press it to generate an HTML template for the report.
9. Press the ``Run`` button to get the HTML report in the Output window.

### 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

The accounts displayed depend on the configured accounts in the database

### 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:

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:

Important: The options must be set with a SQLITE comment, otherwise the filter substitution during code execution will fail!

#### 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:

## Customizing the report

Within the context menu of an report, there are options to rename the report, to add a custom icon (see also Custom account and navigator icons for details) and to assign the report to a new/different group.

## Exporting report definition

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