|
          |
 |
|
 |
 |
|
|||
 |
|
Saturday, January 19, 2008
Tip: Printing Large Quantities of W2 FormsWe have quite a few clients that print hundreds of W2’s for their employees. Using Adobe to print the W2’s makes the process work better and avoids the hassle of collating hundreds of forms. 2) Print the W2’s to the screen and tie-out the totals page with your 941’s. 3) When ready, print the W2’s to a PDF file. You can use the Master Builder PDF printer, but I like the full Adobe Acrobat product better. Check the final page and make sure it matches the values you used in step 2. 4) Print the W2’s from Adobe Acrobat or the Adobe Acrobat Reader (a free download from Adobe). When you print, print six copies (the number of parts in each set of W2’s) without collating. The result you want is page 1 (the first two employees) to be printed six times, then page 2 printed six times (employees three and four). Be careful here as some printers have their own collating capabilities. Check the printer properties and turn this feature off if it exists before you print. This process has the advantage of retaining a complete exact electronic copy of the forms you sent out. Also, it is much easier to re-print a single or group of employees from this document should you need to do so by selecting specific pages to print. (law) Labels: master builder tip W2 printing payroll
posted by Web Master
at
10:50 AM
0 Comments
Thursday, January 17, 2008
Bug: Accounts Receivable Invoice Listing FailsBug: Last tested with Master Builder version 13.0.2.3Here is one to file under bizarre and cool. We ran into this one yesterday at a client that just upgraded to Master Builder version 13. This bug seems to have been introduced with version 13 of Master Builder and, believe me, it was not easy to find! In some circumstances, the accounts receivable reports 3-1-2-21, 22, etc. will not print a complete list of invoices for a job or a group of jobs. If you print the report for just a single job, you can get the error message “The page number you entered to print does not exist for this report.” When we encountered this situation where an AR invoice listing mysteriously stopped without completing, we did all of our standard testing. We reindexed the files (7-4); we replaced all of the .cdx files and reindexed again; we rebuilt the data tables; we ran our own program that can test for many other issues we know can exist in a Master Builder data set. We checked for the know issues with special characters (Sage KB1725) and damaged report files. Nothing worked. Finally, we discovered that the problem was related to the long name of the job as defined in the job screen (3-5). Out testing revealed that the AR list would stop on the page just prior to the page that a specific job would have printed on. If we printed just the identified problem job, we got the “The page number you entered to print does not exist…” error. Once we identified the problem was a specific job, we could narrow our search for the problem. It turned out that the problem was in the name. By changing the long name of the job in the job set up screen (3-5), we found we could get the report to print. This effect was repeatable. Use the original name – the report fails. Use a different name – the report works. This is the kind of problem that drives users (and consultants) batty. Once we discovered the source of the problem, we fixed it for the client and went on with our business. Still – we had to know! What about the job name caused the problem? So, we experimented (later) and we found the pattern. If the characters “IF (“ appear (without the quotes) in the long name of the job, we can crash the report. Why would that be you might ask. Well, we cannot say for sure, but we can speculate. The Master Builder report writer has the ability to interpret formulas. One of the allowed formulas is an “IF” statement which appears like this “IF (a=b) THEN (c).” The characters “IF (“ must be literally entered into the formula. It is possible that the SMB report writer is trying to treat a portion of the job name as a formula. The contents of the formula will not be valid as a job name, so the report writer tries to read an invalid formula and fails. Thus, the report fails. Try it yourself. It’s fun! Just put the characters “IF (“ in the long name of a job and try to print a list of invoices (3-1-2-21 ) associated with the job you modified. If you are using version 13, you are likely to get a failed report. Remove the characters “IF (“ and the report will work. (law) Labels: bug master builder accounts receivable ar
posted by Web Master
at
9:40 PM
0 Comments
Tuesday, January 15, 2008
A Method to Generate Complex ReportsDo 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 Labels: master builder report writer queries excel
posted by Web Master
at
9:04 AM
0 Comments
|
||||
|
|
|||
 |
|||
|
|||
|
|
