A Method to Generate Complex Reports

Do you – as a Master Builder user – have to spend a lot of money to automate very complex reports such as certified payroll reports? Well, the answer is “maybe not.” Here is an alternative to expensive custom reporting that your organization may be able to do with in-house talent.

Report Writers

There are, basically, three ways to generate reports from data in Sage Master Builder (SMB). The first and easiest option is to use a report writer. SMB has a built-in report writer (linked to the form designer) that is reasonably serviceable and is used to generate most of the pre-created reports in SMB. While not terribly sophisticated in formatting capabilities, the built-in SMB report writer has the very strong advantage of having many of the data relationships pre-defined. For example, if you are printing a list of accounts payable invoices, you don’t have to know which table (actpay.dbf) has the vendor names and which table has the detail lines of the invoice (acpinv.dbf). You can just point and click through the linked tables in the report writer wizard or calculation builder and you are set.

The drawbacks to the SMB report writer are primarily two. First, it is a fussy bit of software prone to crashing inexplicitly and the deeper functions of the report writer are not well documented. I have experienced what I call the dreaded “crash and corrupt” where a report with a minor modification will simply shut down never to run again – often requiring a complete rebuild of the report.

Secondly – and much more limiting for complex reporting – is that the SMB report writer can only report on a single level of detail. A report cannot be created based on a query on a subset of data. For example, the SMB report writer will not allow the user to query the accounts receivable database for all open invoices with a balance greater than 10% of the invoice total and then give a detail report of those invoices. This functionality can be simulated by using filters and queries, but this method is limited to simple reporting problems and it is very slow on large data sets.

A more comprehensive report writer such as Crystal Reports (
http://www.businessobjects.com/
) can be used as well. Crystal is another report writer that you purchase and install on your computer as an independent program. It can then be set up to read SMB files through the ODBC drivers in Windows. (Note – If I lost you in that last sentence, you are not likely to enjoy learning how to use Crystal.) Crystal has terrific formatting capabilities, reporting on sub-queries, and great speed. We use it extensively for reporting ourselves. The downside of Crystal is that it has a much greater learning curve and there are no predefined relationships for Sage Master Builder data. The user must know which of the 400+ tables in a Master Builder database are linked together, how they are linked, and with which key fields to write reports effectively. This is essentially programmer level knowledge that the average user will not and probably doesn’t want to know.

Custom Reports

Custom reports that use more of a programming language such as Microsoft Access, Microsoft Visual Fox Pro (currently our favorite tool for this type of work), Visual Basic, and many others can be used to build complex reports. There is really no limit to what can be done with these tools because they have complete access to the SMB data and can manipulate it in any imaginable method. The only limit is time and money. Custom reports tend to be quite time consuming and expensive. Sometimes a custom report is justified – we actually write many of them. However, it is usually a report that is used frequently and is critical to business success to be economically justified.

Using Queries to Populate Spreadsheets

There is a third way to create reports that is not fully automated, but has several advantages over report writers without the investment of custom reports. This is the method of using the SMB queries to populate pre-built Microsoft Excel spreadsheets. The concept is very easy and can be explained in a few steps.

– Create one or more queries in Master Builder to extract the data you want. For a few tips on how to use the SMB queries, see our recent
blog entry
on this issue.

– Create a pre-formatted spreadsheet with worksheets dedicated to pasting the data from the queries. The data should be in one or more dedicated worksheets and all of the analysis should be in worksheets other than the data. This will make it easy to keep the data that is updated isolated from the rest of the formatted report.

– Create a process to move the data from the query to the spreadsheet. If the results of the queries are typically small (say less than 1000 lines), a simple cut and paste is fine. If the data is very large, then it may be necessary to save the query results to an intermediate file and then import into the Excel spreadsheet.

While this is not a perfect method or 100% automated, with a well-designed spreadsheet and a little practice it can be remarkably efficient. The other advantage is that the formatting of an Excel spreadsheet can be very flexible allowing for small adjustments as needed.

Attached is a simple example of this technique with a few of my favorite Excel tricks included and functions included. The analyzed data can be filtered with the Excel auto-filter as well as sorted by any of the analysis columns. This is a simple and useful report as it is, but if you care to, there are many other opportunities to analyze just these two simple queries extended the work that has already been completed.

As an example of taking this technique to the extreme – I have attached a replica of a certified payroll report required by state of Connecticut created using this method. There are only two queries required from Master Builder to generate the data needed. This would be a very challenging report to create in any report writer. Because of the complexity of this spreadsheet, I have chosen only to show the results. (law)

Example Report from Simple Excel Spreadsheet

Simple Excel Spreadsheet Integrated with Queries

Example Complex Certified Payroll Report