Custom SQL Reports

Home





Introduction

Custom SQL Reports allow the user to create a unique report to suit the user. The Custom SQL Reports Manager is used to write an SQL script that can communicate directly with the database. This script is added to the navigation tree of MMEX using a Script Heading. A Heading can also be added without an associated script, to allow scripts to be set up in sections.

The Custom SQL Reports Manager Interface has the following features:

Report Title
Text field

This is used to give the script a Title.
The title is used to access the script from the navigation tree.

Heading
Check Box

A Heading can be added without an associated script.
A Menu item has an associated script.

When Checked: Allows a title to be used as a Heading in the navigation tree.

Sub-Menu
Check Box

When Checked: The navigation tree will set the title as a sub-menu under the previous menu item. The menu item is not necessarily a heading.

SQL Source Script Text Field

This is where the script details can be written.

Open Button

Load an existing script from a file.

Save Button

Allows the user to save the SQL script to the existing file or a new file.
The Report Title will be used as the file name. Spaces in the title will be changed to underscore characters in the file name.

Run Button

Allows the script to be executed without closing the dialog.

Warning: Script changes will be lost when script errors occur.
Recommendation: Save the script before using Run

Clear Button

Clears the SQL Source Script area only.

Close Button

Closes the dialog without saving.
A way of canceling out of the dialog.

Once the script is created, and saved in MMEX, the report can be easily accessed via the Navigation Tree.

The creation of a new Custom SQL Report is via:

This action will open the Custom SQL Reports Manager dialog, and allows the user to directly write a script and save it as a file.

Useful Custom SQL Scripts are also available from the MMEX Forum on the Internet, which can be accessed as follows:

Recommendations:

Note:





Back to Top

Home





Running Report via the Navigation Tree

Reports created by the Custom Reports Manager, will be permanently recorded in the Navigation Tree. The report is given a name when the report is saved. By using the report title, the user can:

A report title can be saved without an SQL script file attached. This is used as a title for grouping scripts together. The navigation tree then behaves similar to the standard reports. Reports can only be nested one level deep.



Back to Top

Home





MMEX Database Structure.

In order to create script files, we need to understand the underlying structure of the database. This structure is displayed as follows:


Basic Structure of MMEX





Table Information

The diagram above, shows the table names with their associated fields. Table links shows the associations that connect the tables via the table's primary index field.

Table Names

Table Description

InfoTable_V1

This contains settings specific to the database.

CurrencyFormats_V1

Contains the Currency values for the database

AccountList_V1

Contains all the account names, and data specific to an account.

Payee_V1

Contains all payees, and the last category this payee used.

Category_V1

This is a list of all the categories in the system

SubCategory_V1

This is a list of all the sub-categories in the system

CheckingAccount_V1

This is the main table where all the transactions are maintained.

SplitTransactions_V1

Contains the data associated with Split Transactions

Stock_V1

Used for Stocks, and is linked to table: AccountList_V1 using the Held At field

Assets_V1

Contains all asset details

BudgetYear_V1

The specific year for a budget.
For monthly budgets, contains the Year-Month

BudgetTable_V1

Budget details

BudgetSplitTransactions_V1

Split Transactions for budgets

BillDeposit_V1

This contains all the details for Repeating Transactions. Similar to table: CheckingAccountV1

MMEX Initialization Table


mmexini.db3

This table contains the settings for MMEX. These settings are used to initialize MMEX at system start-up.

Table View

Special table definition known as a View

AllData

This view joins (almost) all data from CheckingAccount_V1, AccountList_V1, Payee_V1, (sub)category_V1 and CurrencyFormats_v1 tables.

If this view is removed, MMEX will automatically recreate it at System Start.


Table View: AllData

MMEX creates a special view called AllData. This view can easily be used by SQL Scripts to create specialized reports that users may require without having to create complicated scripts.
For more information use the SQL Command: select * from AllData;

For closer inspection, a script file can be created to view the data in each table using the following SQL Command:
select * from <table_Name>
where <table_name> is one of the table names above.


Table Views

SQL has the ability of creating virtual table definitions from existing tables. These virtual tables, called Views, use the data from the original base tables, and can be used in reports like tables. The SQL command for creating a view is:

create view <VIEW_NAME> as <SQL STATEMENT>;

Where:
<VIEW_NAME> is the name of the view.
<SQL_STATEMENT> is the SQL statement that defines the view.

Example:
create view budget as
select * from BudgetYear_V1 BY, BudgetTable_V1 BT, Category_V1 C, SubCategory_V1 SC
where BY.BudgetYearID = BT.BudgetYearID and BT.CategID = C.CategID and BT.SubCategID = SC.SubCategID;

To inspect the view, use the SQL command: select * from Budget;
To remove the view use the SQL command: drop view Budget;





Back to Top

Home





SQL Scripting (Introduction)

Attempting to teach SQL programming is beyond the scope of this help file, but the following is a quick guide into understanding scripts, and possibly creating your own scripts if required.

In creating scripts, the main command we would tend to use is the SELECT statement.

The basic format is:

SELECT <name>

<name> represents the field names of the table, or * for all names.

FROM <table>

<table> represents the table or tables the command is aimed at.

WHERE <condition is met>

[optional] here we test for some condition.

ORDER BY <parameter>

[optional] sorts the output by this parameter


Examples:

SELECT * FROM AccountList_V1;
* This would display all fields for the table: AccountList_V1

SELECT AccountName, Status FROM AccountList_V1 where AccountType = “Term”;
* This would display the Term Account names and the account status from table: AccountList_V1


Here we describe a more complex script:


Although this script is useless, it demonstrates some basic principles.

select pT.PayeeName payee,
    cT.CategName,
    scT.SubCategName
from payee_v1 pT,
    category_v1 cT,
    subcategory_v1 scT
where
    pT.categID = cT.categID and pT.subCategID = scT.subCategID
order by
    payee


In this example we see the table name is: payee_V1 and the field name is PayeeName

In the FROM clause we see the table name: payee_V1 pT
* Here the pT at the end, acts as an alias for the table name.

Now in the SELECT we see: pT.PayeeName payee
* The pT. is used to state the table that owns the field PayeeName
* The payee at the end, is an alias for the table.fieldname combination

We can separate table names and field names with commas, as we see more tables and fields in this example.

In the WHERE clause, we see: pT.categID = cT.categID
* Here we test to make sure the two fields are the same in both tables.

Now in the ORDER BY clause, we can now use the simplified name of payee we defined in the SELECT clause.

I hope this short introduction takes the mystery out of the script files as seen in the MMEX Forum in the Custom SQL Report Scripts section.








Back to Top

Home