ERP Reporting - ability to create/modify reports

All ERP systems come with plenty of standard reports, some useful for your circumstances, some not. But no matter how many reports there are, there always seems to be a few reports missing or reports not quite as required.

The reporting shortfall can be broken down into two main categories:
a) basic layout changes required, or the addition/removal of a small number of fields from existing reports

b) completely new reports required, some from a single data table, some requiring quite complex data structuring across two or more data tables.

To overcome these problems, ERP systems normally provide some form of report development tool. SAP for instances has its User Report Generator and ABAP programming language.

The development tool may be useable by end users with little or no IT experience, or it may require a person of considerable skill and experience. If the latter, then the running and installation costs of the software will be increased and in some circumstances even doubled (or worse).
Most systems claim to be useable by end users. This claim should be tested at some point – hopefully before committing money to the software. As not all claims are justified - I had better leave it at that.

One approach to filling the reporting gap is to use generic reporting tools like Crystal Reports or even the good old spreadsheet. These too come at a cost, they may not be of high monitory value, but the cost could well be hidden in things like data accuracy, completeness, timeliness and overall reliance on an external system to perform tasks that should be performed by the ERP system itself. Remember, that is probably one of the reasons you bought the system in the first place.

What should be avoided is using expensive IT developers or the system developer/reseller to remedy basic layout changes. Nowadays, this task should be within the scope and capability of system users and the ERP system should be capable of supporting this requirement. For example, under SAP the Report Generator is for users to create new reports. To change an existing standard report, will normally require knowledge of ABAP. Many ERP systems are sold that do not allow for standard reports to be changed by anyone! They are ‘locked’ by the system and require specialist knowledge or software to effect changes.

So when investigating an ERP system for use in your organisation, check its reporting abilities and the ability to change standard reports for category a) type changes.
Check the claims that it is user friendly.
Check on what experience and knowledge an IT person requires to create category b) type reports. Then determine the cost of that knowledge in the job market.

The answers to these questions will help you select the correct system for your SME.

Visual FoxPro - changing a Class in data grids and forms

Visual FoxPro programmers can use the VFP delivered base classes to construct forms and data grids for user data entry, reporting and viewing. The functions and tasks performed by these base classes are rudimentary. VFP programmers are encouraged to use these base classes as the starting point from which to construct their own classes in a hierarchical manner. Their own classes can greatly extend the tasks and routines performed providing an enriched programming environment that can easily be applied in a consistent manner to deliver highly functional applications to end users.

None of this is new to VFP programmers. However, there is one limitation in the Visual programming environment in respect of data grids and the addition of columns to a grid.

Under Visual FoxPro when a grid is added to a form, the programmer defines the number of columns the grid is to have. Having set the number of columns, the background VFP code actively adds the columns to the grid using the base classes of ‘Column’, ‘Header’ and ‘Textbox’ for the respective Column, Header and Text attributes. To change these base classes to the programmer defined classes; in order to use the additional functionality; under the Visual Programming environment is no small task. Especially when an application may have hundreds of data grids.

What the programmer would like to be able to do is to have the ability to define the classes for the Column, Header and Text that the grid uses when adding these elements to its structure. Unfortunately, this is not possible.

Up until recently, I have reluctantly accepted this limitation. But no more!
I have come across a method/process to overcome this limitation. What is more I could kick myself for not thinking it through and coming to the answer before now.

Most experienced VFP programmers know that the file that defines a form is in fact a normal VFP table file in disguise. Table files have the extensions
.dbf - for data and structure
.fpt - for the content of memo fields defined in the .dbf
.cdx - for defined indexes

Whereas, form files have the extensions
.scx – for the form definitions
.sct – for the content of memo fields defined in the .scx

Using the command window a form can be opened as a table with the commands:
Use “form.scx” exclusive
Browse

This shows the form.scx file as a table. Looking at the table column headings and data, it does not take long to discover that the column/field “Class” holds the class name of each object held in the form. Whilst, the column/field “ClassLoc” holds the relative address of the class library; being empty when a base class is set under “Class”.
What is more, any changes made to the tables data and saved will be reflected next time the form is opened under the Visual FoxPro programming environment.

Putting this information together into a small programming routine has allowed me to change the “Class” of every grid column text attribute in an application of over a hundred forms (most with grids) from the base class of “textbox” to my own defined class of “_textbox” which has additional programming to consistently handle, double click and right click events. Thereby making redundant my prior manual efforts of defining these events in every grid and textbox attribute in all the forms. What a productivity saver this would have been, if I had come across the solution earlier.

So what’s the small routine to save all this manual effort:

LOCAL ls1, ls2, ln1, i, lspathtemp, lnfiles
* gcnetfldr is a global holding the default drive and path
* – C:\Program Files\….
lspathtemp = gcnetfldr + "\FORMS"
* set the folder holding the forms to be changed
SET DEFAULT TO (lspathtemp)

*Step 1a
*– get the names of all SCT files in the current folder
* and change their extensions to FPT – as for a table
lnfiles = ADIR(larrayFile1, "*.sct")
FOR i = 1 TO lnfiles
ls1 = STRTRAN(larrayFile1[i,1], ".sct", ".fpt", 1, 1, 1)
RENAME (larrayFile1[i,1]) to (ls1)
ENDFOR

*Step 1b
* – get the names of all SCX files in the current folder
* and change their extensions to DBF – as for a table
lnfiles = ADIR(larrayFile2, "*.scx")
FOR i = 1 TO lnfiles
ls1 = STRTRAN(larrayFile2[i,1], ".scx", ".dbf", 1, 1, 1)
RENAME (larrayFile2[i,1]) to (ls1)
ENDFOR
RELEASE larrayFile1, larrayFile2

* Step 2
*get all the .dbf files in the current folder into an array
lnfiles = ADIR(larrayFile3, "*.dbf")

*set the complete path to the form table files
* as they are not associated with an open database
ls1 = "C:\Program Files\Microsoft Visual FoxPro 9\Acceptum\FORMS\"
* loop through the files
FOR i = 1 TO lnfiles
ls2 = ls1 + ALLTRIM(larrayFile3[i,1])
ln1 = 0
USE (ls2) IN 0 exclusive ALIAS xxx
SELECT "xxx"
* changing the Class textbox to _textbox
* and the Class Location to a relative folder path
SCAN FOR LOWER(class) = "textbox"
replace class WITH "_textbox", classloc WITH "..\..\ffc\_base.vcx"
ln1 = ln1 + 1
ENDSCAN
closetable("xxx")
Messagebox("Done " + ALLTRIM(larrayFile3[i,1]) + ;
STR(ln1),64,”Form Update”)
ENDFOR
RELEASE larrayFile3

*Step 3
* is to change the form tables back to form files
SET DEFAULT TO (lspathtemp)
lnfiles = ADIR(larrayFile4, "*.fpt")
FOR i = 1 TO lnfiles
ls1 = STRTRAN(larrayFile4[i,1], ".fpt", ".sct", 1, 1, 1)
RENAME (larrayFile4[i,1]) to (ls1)
ENDFOR

lnfiles = ADIR(larrayFile5, "*.dbf")
FOR i = 1 TO lnfiles
ls1 = STRTRAN(larrayFile5[i,1], ".dbf", ".scx", 1, 1, 1)
RENAME (larrayFile5[i,1]) to (ls1)
ENDFOR
RELEASE larrayFile4, larrayFile5

SET DEFAULT TO (gcnetfldr)
Messagebox("Complete ", 64,”Form Updated”)


And that’s it. All textbox attributes now use the programmer defined class of _textbox to provide a better and consistent approach to the application. As an aside it also reduced the size of the executable.

I now hold this code in a routine which I can run regularly after adding a new form/grid combination to my application.

Backup that data!

In the last week I have rebuilt an entire database from a few key tables over 4 months old!

Why was this necessary?

Because the user; a SME; never made a backup of its key ERP system.

So when somehow the ERP database got deleted from the server. It had no backup to go back to.

Its only recourse was to use a copy of a few files e-mailed to the system developer (yes me); to try and rebuild the entire database based upon those key tables. Yes it was done - but at a not insignificant cost.

So SME's - make sure you take regular backups and copy them to a PC or media that is not the prime server/PC.
This daily/weekly routine could save you embarrassment and money.

Be warned; PCs/Servers do
crash;
get stolen;
get infected by viruses;
deliberately get sabotaged by discontent employees;
lose data through foolish acts by novice users.

Installing an ERP system

Many companies defer installing an ERP computer system because of the disruption and risk involved in the installation process. These risks and disruptions can be minimised with good planning and preparation.

The extent of the work involved will depend on the size of the organisation involved and on its volume of clients, customers, suppliers, products, open transactions, etc. at the point of cut-over. But whatever the extent, the principles to be applied remain the same, whether the data is Master or Transactional.

These principles are to:
a) identify each entity; customer, supplier, product, account code, open sales/purchase/production orders, account balance, etc.; required to be set up in the new system.

b) identify the means of transporting data for each entity from existing systems into the new system. This normally means exporting data to an Excel spreadsheet or a data extract file useable by the new system.

c) identify the data manipulations required to change old format into new system format.

d) identify any missing data that the new system requires and determine how to acquire this data.

e) identify data used in existing systems that is 'dropped' by the new system; and decide how important this data is to the organisation and how any functional gap caused by this loss, is to be covered. This should have occurred when the new system was selected. But it should be re-examined now that more information on the new system will have been obtained in the interim period.
This is the point of greatest risk, for if some 'must have' business function appears not to be covered by the new system, it can become a 'show stopper'. Or at best, delay the implementation timeframe, whilst alternatives are investigated and assessed.

f) identify all actions that need to be performed on existing systems before the data is transferred across to the new system. Examples of this would include:- data clean-up, removal of old records, payment of all open items to suppliers, fulfilment of as many customer/production orders as possible.

g) whenever possible, coincide the switch to the new system with a financial year end, as it is always much easier. When this is possible, always import closing balance into the old year on the new system and do a 'year end roll' into the new year so that monthly/period movement reports are not compromised by cutover data.

h) identify new hardware and software requirements of the new system and the acquisition path and any delivery/installation delays.

i) collate the work required from the above points and assign to individuals along with developing a timeframe for each task. Critical points and data constrictions need to be identified so that timeframes are not delayed and additional resources can be focused in the right place when tasks are actually performed.

For a small organisation these tasks may only take a day or two to complete and not require significant testing prior to doing it for real. In larger organisations, test runs will be required to gain insights into exact data requirements and loading sequences. Whatever the size of an organisation, planning the installation process and preparing data in advance, goes a long way to making an easy and smooth systems installation.