Infor M3 Financial Reports

 

 

I have developed some great financial report tools for Infor M3.
Financial Reports are richer if you can get data to Excel exactly the way you want. If you have data on Excel you can customize your reports, add charts...
This features work with M3 using SQL Server as database. The key is that calculations are made by the server and not by your excel worksheet. This turns calculations faster.

Here is a sample of a Portuguese account balance (Balancete).

Here is a sample of a very simple custom report you can build with this tool.

If this solution interest you please contact.

How does it work?
Simple: you have new functions in Excel to get you data out of M3.

Here is the full set of functions to get data:

M3Balance

(Company, Division, AccountingYear, Period, Account, TotalforYear)

Returns balance for M3 level 1 key.

Parameter description

Company -

M3 Company as registered in MNS095.

Division -

M3 Division as registered in MNS100.

AccountingYear -

Is the Accounting Year from which the balance is to be obtained.

Period -

Is the period reference for the value to be obtained.

Account-

The level 1 key to obtain the balance. This parameter works as StartsWith.

For instance, if you have a key structure like 111 - Account 1; 112 - Account3; .... ; 119 - Account 9, and the

account parameter is filled with "11" the balance calculated is the balance for all the accounts (111;112;...;119).

TotalforYear -

If filled with "Y" calculates the balance for the year with period less or equal to the selected period.

Any other value will calculate balance for the refered period only.

Example

=M3Balance( 1 ,"200", 2016, 6 ,"11","Y")

Calculates the balance for the balance key 1 starting with "11" from period 0 to 6 of the 2010 Year

=M3Balance( 1 ,"200", 2016, 3 ,"119","P")

Calculates the balance for the balance key 1 starting with "119" from period 3 of the 2010 Year

M3BalanceDeb

(Company, Division, AccountingYear, Period, Account, TotalforYear)

Returns debt balance for M3 level key 1

Parameter description

This function uses the same parameters and description as function M3Balance

M3BalanceCre

(Company, Division, AccountingYear, Period, Account, TotalforYear)

Returns credit balance for M3 level key 1

Parameter description

This function uses the same parameters and description as function M3Balance

M3KSBalance

(Company, Division, AccountingYear, Period, TotalforYear, BalanceKey, Key1Start, Key1End

[, Key2Start, Key2End, Key3Start, Key3End , Key4Start, Key4End, Key5Start, Key5End, Key6Start, Key6End, Key7Start, Key7End ] )

Returns balance for selected M3 balance key using key ranges

Parameter description

Company -

M3 Company as registered in MNS095.

Division -

M3 Division as registered in MNS100.

AccountingYear -

Is the Accounting Year from which the balance is to be obtained.

Period -

Is the period reference for the value to be obtained.

TotalforYear -

If filled with "Y" calculates the balance for the year with period less or equal to the selected period.

Any other value will calculate balance for the refered period only.

BalanceKey -

Is the balance key to be used. Balance Key file can be created in M3 program GLS690.

Key1Start -

Is the starting range for the first key of the selected balance key.

Key1End -

Is the finish range for the first key of the selected balance key.

[Key2Start] -

Optional parameter for 2nd key start range for the selected balance key.

[Key2End] -

Optional parameter for 2nd key end range for the selected balance key.

[Key3Start] -

Same as Key2Start but for 3rd Key.

[Key3End] -

Same as Key2End but for 3rd Key.

[Key4Start] -

Same as Key2Start but for 4th Key.

[Key4End] -

Same as Key2End but for 4th Key.

[Key5Start] -

Same as Key2Start but for 5th Key.

[Key5End] -

Same as Key2End but for 5th Key.

[Key6Start] -

Same as Key2Start but for 6th Key.

[Key6End] -

Same as Key2End but for 6th Key.

[Key7Start] -

Same as Key2Start but for 7th Key.

[Key7End] -

Same as Key2End but for 7th Key.

M3KSBalanceDeb

(Company, Division, AccountingYear, Period, TotalforYear, BalanceKey, Key1Start, Key1End

[, Key2Start, Key2End, Key3Start, Key3End , Key4Start, Key4End, Key5Start, Key5End, Key6Start, Key6End, Key7Start, Key7End ] )

Returns Debt balance for selected M3 balance key using key ranges

Parameter description

This function uses the same parameters and description as function M3KSBalance

M3KSBalanceCre

(Company, Division, AccountingYear, Period, TotalforYear, BalanceKey, Key1Start, Key1End

[, Key2Start, Key2End, Key3Start, Key3End , Key4Start, Key4End, Key5Start, Key5End, Key6Start, Key6End, Key7Start, Key7End ] )

Returns Credit balance for selected M3 balance key using key ranges

Parameter description

This function uses the same parameters and description as function M3KSBalance

Disable cache for file/table in Infor Lawson M3

Problem Environment: Infor Lawson M3 Java 

In Infor Lawson M3 Java uses cache. This is great to improve performance but may cause some issues. There are several reasons for you to want to disable cache on a file/table. For instance a service that updates data from an outside environment. Of course Infor Lawson does not subscribe some methods. This is understandable because external interference can cause great damage. But if you know what to do and need to disable cache here is how to do it:

1. Open a file in you environment folder \env\[environment]\properties\mvx\res\Transaction.properties with notepad.
2. You should find a line with this "transaction.cache.readonly.count=5". Increase this number by adding the number of tables you want to disable cache.
3. Next on the file you should find "transaction.cache.readonly.4=-DDOCUX". Add one entry for each file you want to disable. Don't forget to increase the number as in ".4" for each file.
4. Save the changes.
5. Shutdown environment.
6. Start environment.