Sample SQL from a report I once wrote to help with 1099 processing
One of the more amusing points that comes up when talking about the features of Infor M3 is the number of reports that come with the system and are available “out of the box”.
In my experience of many years and many M3 implementations I can say that very few customers use more than a handful of those “canned” reports, and without exception every M3 customer ends up building their own suite of reports, for both daily operational use and management oversight.
Those basic M3 reports typically go through StreamServe and allow users to either receive a PDF via email or a paper printout, both formats which aren’t easy to work with when analyzing large amounts of data. If you have hundreds or even thousands of records to work with, spreadsheets remain a vital tool in most environments.
Once the decision has been made to create a reporting suite against Infor M3, there are some important considerations to keep in mind, however! Be careful to follow these Commandments and save yourself a lot of heartache…
1. Thou Shalt Not Run External Reports Against The Production Database
This is a big big big big “NO”, and should mark the fundamental technical requirement of your external M3 reporting solution. The M3 Grid manages the interaction between the Business Engine and the database, and if you throw database queries from external report users into the mix, you run the risk of seriously reducing system performance for your users.
NOTE: This recommendation does not apply to reporting options within the M3 solution, such as a well-configured List Panel or the use of Ad Hoc Reports, which are managed by the M3 Grid and cooperate nicely with other processes.
I recall one client who regularly experienced slow system performance each morning, as various Crystal Reports ran to completion while users went for an extra cup of coffee or found other things to do rather than try to work within M3. It was one of those problems that started out as a minor annoyance but became worse over time.
The solution? We used BPW (Business Performance Warehouse) to define a datamart that copied selected tables over from M3 each night, and pointed the reports to run off the datamart instead. There are a number of other options you can use to create a parallel reporting database as well, particularly if you are running on SQL Server.
Not only did this allow users to be productive right from the start each morning in M3, but I imagine they saved a bit of coffee expense, too. 🙂
2. Honor Thy Users’ Requests For Spreadsheets
One of the big frustrations many M3 users have is the lack of an easy way to get reports in spreadsheet form from the system. Yes, you can dump the contents of a panel into Excel, and there is a cumbersome process that can turn StreamServe output into a spreadsheet, but these are far from ideal.
The reality is that for many situations, users need to be able to work with hundreds or even thousands of records at a time, and spreadsheets facilitate that kind of analysis far better than a PDF or other reporting format.
A tool like Crystal Reports or Microsoft’s SQL Server Reporting Services (SSRS) provide the flexibility for users to get their information in the format that fits them best, whether a PDF or spreadsheet.
3. Thou Shalt Write Proper SQL For Crystal Reports
One issue for you Crystal Reports users is to make sure that report writers understand the M3 database structure and write SQL statements to retrieve only the appropriate information for a given report, and no more!
If you use Crystal’s Select Wizard to pick which tables you need, and then apply filters to determine which records will show on a report, Crystal Reports will try to load those tables in their entirety and then filter down to show only the required lines. This is tremendously inefficient, both in terms of client performance and the amount of network traffic needed to run a particular report.
For example, if you have a daily Open Orders report for your purchasing department to monitor, using the basic Select Wizard in Crystal will cause entire purchase order tables (as well as any other tables you are linking to) to be brought over to the machine running the report, and then that machine will crank through the data and figure out which PO’s are open and which are not.
Instead, report writers should craft a proper SQL statement that retrieves only the data that is needed. This allows the database server to do the work that database servers are designed to do – grab the specific records and fields you need, and then only transmit that information across your network to the Crystal Reports application, which can then do the work of formatting things nicely and presenting the report.
4. Create SQL Views To Define Common Business Terms
Quite often you may need to develop a set of reports that offer different slices and dices over the same data, such as Open Order reports that may be broken out by product line, vendor, facility, etc. General Ledger data is another area where the same records and fields may need to be presented in a variety of ways.
In those cases, defining a View in your SQL reporting database can provide a shortcut for the development of most of those reports. That view can define standard record selection criteria and the fields needed across a family of reports, and be re-used in multiple queries. This reduces the time spent creating custom queries for each report, and avoids the risk of error when reinventing the wheel with every new report.
5. Custom M3 Reporting Is A Necessary Investment
An ERP system like Infor M3 doesn’t just serve as the mechanism that enforces your business rules, it also needs to grow and adapt as your business changes. Your reporting requirements will follow that change as well, so prepare yourself by making sure you have the necessary expertise to present whatever information your key business users need.
If you need assistance with reporting against Infor M3, head over to the Contact page and send a note! I can not only help with designing and implementing a custom reporting solution, but can coach up your technical staff so they can handle your reporting needs going forward.
