Reports are one of the most important tools that everyone in a well-oiled organization needs to accomplish their goals. From the top of the organizational chain down to front line managers, reports are the life blood. When it’s difficult to summon the exact information you require, heads explode and the life blood starts hemorrhaging. A little over dramatic but point is, it shouldn’t have to be that way.
Canned reports that are provided by that new ERP application can be insufficient and ineffective. You require custom reports that offer the precise information in the correct sort order and so forth. You have reached Information Nirvana.
The problem with creating custom reports in many ERP systems are that they give you the tools such as SAP Crystal Reports but no real good solution to easily enter all those important parameters. If you rely on Crystal Reports internal parameter screen, it’s kludgy at the very least and ugly at best. If you’re providing your client with the panacea of reports, you are letting them in on a huge let down when they have to enter the necessary parameters.
In a recent Sage 300 project, I came across an incredibly elegant solution to my parameter screen problem. A fellow consultant provided me with some valuable information using Visual Basic Macro (.AVB) files to provide a customized parameter screen for Sage 300 projects. Although I’m not an expert in Sage 300, this quite literally has the potential to open a lot of custom report doors and I will share what I learned with you.
If you have some knowledge of Visual Basic for Applications (VBA) then you will have no problem utilizing Visual Basic Macro’s for your Sage 300 report projects. I will refrain from providing a history lesson on the programming language and its intricacies. Let’s get started!
The Three Elements
Sage 300 requires that you provide three elements to creating a fully functional parameter screen for you reports:
- An .AVB file. This is essentially a Visual Basic program. It has the graphical screen and source code embedded in the file. If you have ever built a program using Visual Basic 6 or VBA in Microsoft Access; same thing.
- An entry in the XXrpt.ini file. This file is located in the program modules folder. In our case, we will be using the Manufacturing Module that was developed by AutoSimply. The module folder starts with an “MF” so you will find this in the MF62A folder in our case. The entry will be added to the end of the ini file. The entry consists of report information such as the crystal report name, paper size, number of output parameters, and what those parameter names are. The image below is an example of our setting values.
- And finally, the crystal report. Our report uses an ODBC driver for the data source. It points to our Sage 300 data located in Microsoft SQL Server. I won’t go into the details of setting up the database configuration or report development, since that is a topic deep in mystique and beyond the subject matter in this article. What you need to have in this report are parameters precisely named as the ones in the ini file example above. More on this later.
Where to Start?
What comes first, the report or the parameter screen? The obvious answer in my mind is the report. This, of course, is the most difficult task you might think, and you would be right. After you have developed a parameter screen, this becomes a reusable component in your future report projects in Sage 300 and your main focus and work will be towards making that awesome report for your boss or client.
Writing the Report
As mentioned earlier, I won’t go into the minutia of writing a crystal report but there are a couple of important elements that you will need to include to make this successful.
- A SortBy Group
- The SortBy group is a grouping that allow the report to be sorted by any field in the report that you deem necessary. The sortby group will be passed the value to sort by in a formula that will retrieve the parameter from our parameter screen. It is important to offer your client this option since hard coding sort fields are not dynamic and limit the effectiveness of the report. In our example, we will sort by three different fields, all mutually exclusive.
- This is the most important element to our report. This is the whole reason we’re building a parameter screen in the first place, right? Below is an example of where those parameters are located in the crystal reports designer.
The parameters highlighted will be used in the report select expert. This will force Crystal Reports to only give us the information we requested with our parameters. Under the Crystal covers, it’s essentially creating a select statement to SQL Server for the data related to those parameter fields. Below is an example of one such query:
SELECT “MFORDH”.”MONUM”, “MFORDH”.”MOTYPE”, “MFORDH”.”ITEMNO”, “MFORDH”.”MODESC”, “MFORDH”.”MOREF”, “MFORDH”.”ORDQTY”, “MFORDH”.”PRODQTY”, “MFORDH”.”ITEMDESC”, “MFORDH”.”DUEDT”, “MFORDH”.”ORDERDT”, “MFORDH”.”RELEASEDT”, “ICITEM”.”STOCKUNIT”, “MFORDH”.”COMMENTS”, “MFOPT”.”MOSERIES”, “MFORDH”.”MOSERIES”, “MFORDH”.”MOSTATUS”, “OEORDH”.”PONUMBER”, “MFORDSO”.”SONUM”, “OEORDH”.”CUSTOMER”
FROM (((“ASDAT”.”dbo”.”MFORDH” “MFORDH” INNER JOIN “ASDAT”.”dbo”.”ICITEM” “ICITEM” ON “MFORDH”.”ITEMNO”=”ICITEM”.”FMTITEMNO”) LEFT OUTER JOIN “ASDAT”.”dbo”.”MFORDSO” “MFORDSO” ON “MFORDH”.”MOUNIQ”=”MFORDSO”.”MOUNIQ”) LEFT OUTER JOIN “ASDAT”.”dbo”.”OEORDH” “OEORDH” ON “MFORDSO”.”SONUM”=”OEORDH”.”ORDNUMBER”) INNER JOIN “ASDAT”.”dbo”.”MFOPT” “MFOPT” ON “ICITEM”.”AUDTORG”=”MFOPT”.”AUDTORG”
WHERE (“MFORDH”.”MONUM”>=” AND “MFORDH”.”MONUM”<=’ZZZZZZZZ’) AND (“MFORDH”.”ITEMNO”>=” AND “MFORDH”.”ITEMNO”<=’ZZZZZZZZ’)
Pretty long and confusing if you’re not fluent in the T-SQL language.
With your report completed, we will continue on to the .AVB file. As stated earlier, if you have experience writing Visual Basic code in any of the Microsoft Office suite of products, then you will know how to write code in Visual Basic Macros. Since this is a high level article, I will not give a line by line rundown of the code but a list of programming rudiments.
- Your Main Module will include API definitions for Sage 300 and some MS Windows functions.
- References to the Accpac Manager and MS Windows dll’s.
- A main sub routine to start the program with your initializations.
- A main form that will display the necessary parameter fields. (Figure 3)
- Event code that will fire when any of those parameter fields or buttons are acted upon.
- Error captures. This is needed to handle error exceptions in an elegant way with some form of exception description for debugging.
With the ability to create these custom parameter screens, you have a set of powerful development tools at your fingertips. There is no need for SDK’s that are costly and have heavy learning curves. For those with some programming ability, this capability with bring immediate dividends to your organization. If you were given the choice between figure 4 and figure 5, which one would you choose?
I think figure 5 is the obvious choice.
written by John Trainer, Consultant
WAC Solution Partners- New England