#

# Custom SQL Reports

Home

- Introduction

- Running Reports via the Navigation Tree

- MMEX Database Structure

- SQL Scripting (Introduction)

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

- The menu item: Tools –> Custom SQL Reports

- The icon on the toolbar.

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:

- Use the menu option: Help –> Report Issues or Feedback

- Search the forum heading: Custom SQL Report Scripts

Recommendations:

- For new scripts, use a useful title for your report.

- Save the script before running in the Custom SQL Reports Manager.
  Note: Script errors may result in loss of your script if not saved first.

- Use the pop-up menu on the navigation tree to edit the file if necessary.

Note:

- All script titles are saved in an index file called: CustomSQLReportIndex.txt

- Custom SQL Reports can be remove by deleting the index file.

- Reports can be reorganized by directly modifying the index file if necessary.

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:

- Run the report

- Edit the report

- Delete the report.

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