Tag: Reporting

Overstated costs on the Service Management Contract Analysis Report

The Maxwell Systems Management Suite (MSMS) Service Management Contract Analysis Report can be useful, but recently, in trying to reconcile service revenue and costs, I ran into a problem where the Contract Analysis Report was reporting actual costs twice, once as a current contract cost, and once as a historical contract cost.

The problem happens when you have current costs that are processed for a Work Order that is regarded as belonging to a historical contract. Here’s the particulars–first there’s a contract that expired December 31, 2008, and was renewed. Then Contract Closeout established the “current contract record” expiring December 31, 2009. And finally, a Work Order was created December 15, 2008, but the work was not completed and billed until January 2009. It is this Work Order that causes the confusion for the Contract Analysis Report.

In January 2009, the work on the December 15, 2008 Work Order was performed, and was billed. The Ticket Invoice Journal correctly updated the costs to the Historical Contract File, and also updated the Customer Contract Monthly Cost File for January 2009. But, the Contract Analysis Report adds those January 2009 costs into the Current Contact (the one dated January 1 to December 31, 2009) and it also reports those costs as part of the historical contract data. Thus the costs are overstated.

There is no real solution to this problem except to give each Contract a new number and that’s impractical in most cases.

Reporting number of employees for Maryland Unemployment with Maxwell Systems Management Suite

As part of the quarterly payroll reporting, the state of Maryland requires employers to file the Maryland Quarterly Unemployment Insurance Contribution/Employment Report (DLLR / OUI 15) with a count of employees working on the 12th day of each month in the quarter. Specifically, line 20 of that report requires employers to

Enter the number of full-time and part-time workers (subject to the Maryland Unemployment Insurance Law) who worked during or received pay for any part of any payroll period which includes the 12th of the month.

But, the Maxwell Systems Management Suite (MSMS) State Unemployment Report does not provide the count of employees, so here’s how to gather that information.

The trick to getting the employees that worked on the 12th day of any month is to print the YTD Detail Payroll Register for the check date range that includes the 12th of each month. For example, for January 2009, figure out what check date include the pay for January 12th, then run the YTD Detail Payroll Register and use that check date as the beginning and ending check date.

Now for the hard part — print the report to the screen, then count how many employees are printed on the report. That’s the number to use when completing line 20 of the Maryland Quarterly Unemployment Insurance Contribution/Employment Report.

Another possible solution is to create a Payroll Formatted Report using Prefix PR7, define a Formatter Field called Counter with the definition simply as 1 (just the number 1), then print that report, for the date range that include the 12th day of pay for each month, and look at the total of the counter field at the end of the report.

Either way, to determine the information needed by Maryland requires printing a report for each month that includes that 12th day.

Reporting service call volume with Maxwell Systems Management Suite

The Maxwell Systems Management Suite (MSMS) Service Management Systems records all the service calls made by customers. Recently, someone wanted to track a customer activity to determine if a service technician had not visit a site in the last 60 days. Unfortunately, there is no reporting mechanism that would allow that kind of analysis.

But, with the use of the Formatted Report ability in MSMS, you can at least see all the calls that were made to customer site in the last 60 days. I know, not the same as seeing sites NOT visited in the last 60 days, but still useful.

The trick is using an Advanced Filter that only reports those calls marked as completed in the last 60 days. For this example, use the Formatted Report Prefix SPB and create a filter called Date Completed, and under the Advanced tab, add this filter:

NOT(NUL(DATE_COMPLETED$)) AND 
JUL(NUM(MID(X$,37,2)),NUM(MID(X$,31,2)),NUM(MID(X$,34,2))) - 
JUL(NUM(MID(DATE_COMPLETED$,3,2)),NUM(MID(DATE_COMPLETED$,5,2)),
NUM(MID(DATE_COMPLETED$,7,2))) < 60

Essentially that says, if the Date Completed field is not empty AND the Terminal Date minus the Date Completed is less than 60 days, include the Work Order on the report. Note the JUL function is used to convert the two dates to a Julian date so the dates can be subtracted from each other.

Not the cleanest method of analyzing call history, but certainly this method gives a service manager a meaningful tool to determine if a customer’s needs are being addressed.

Limits to where PDF files can be printed and stored

Some people try to be true to the whole “paperless” society concept by printing all reports to PDF. But, a problem can arise if the path and filename for that PDF exceeds too many digits.

A customer was trying to print an Open Accounts Payable Report to PDF and used the following Path/Filename: “F:\Mxw\PDF\Jason Builders and Service, Inc\Accounts Payable\2009\20090430\Open Accounts Payable Report.pdf” but kept getting a message that the file did not have an extension. It’s clear that .pdf is the extension.

After a bit of playing around, we changed the name of the folder “Jason Builders and Service, Inc” to just Jason, and sure enough the PDF successfully was created and saved.

So the moral of the story is, “keep the Path/Filename combination of PDFs to fewer than 100 characters”.

How to set the Aged AR Trial Balance default Aging Date

It’s always a pain to have to change the Default Aging Date field when printing the AR Aged Trial Balance by Job report. It’s an easy program change, but the Maxwell Management Suite requires adding a line of code to the standard CARRE0 program that prints that report. Here’s that line of code:

10041 IF X$(41,2)+X$(37,2)+X$(31,2)=FISCAL_YR.VAL$+FISCAL_PD.VAL$ 
THEN LET AGING_DATE.VAL$=X$(41,2)+X$(37,2)+X$(31,2)+X$(34,2)

This change will set the Default Aging Date to the current terminal date, only if the current terminal date is in the Period and Fiscal Year ending date selected for the report.

Reporting Actual Service Hours via SPD and SPI Prefixes

When trying to reconcile Actual Payroll Hours posted to Service Work Orders, Maxwell Management Suite software offers two Formatted Report Prefixes, SPD (History/Billed Tickets), and SPI (Current/Unbilled Tickets), that give you access to the Actual Payroll Hours spent on Work Orders.

The problem is field (F035) in the SPD/SPI Prefixes is called Actual Quantity/Hours, meaning Actual Hours shares the field with Actual Quantity. Actual Quantity is the quantity of material items used on that Work Order, for example, from an Inventory Material Requisition. So the question is, “How do you separate Actual Hours from Actual Quantity when trying to analyze Payroll hours?” Here are several solutions.

Using the Unit of Measure Field to determine if it is a Payroll transaction

The first method I used to isolate the Hours from the Quantity involved using the Unit of Measure (F023), which assmes all Payroll transactions have a Unit of Measure of “HR” (hours). I created a Formatter Field (F500) and tried to use this definition:

F035;IF F023="HR" THEN X7$="0"

Unfortunately, the program kept reporting a syntax error with that equation. A little detective work found the ‘code parser’ was converting the F023 into an invalid variable “J(-6)” and testing that numeric variable against a character string. The parser didn’t seem to like the “F023”. Here’s what Formatter Field Maintenance reported as the incorrect parsed code:

LET A=J(6);IF J(-6)="HR" THEN X7$="0"

So, remembering back to old days, found that Maxwell still kept a string array U$, where each element contained that particular field value (e.g. U$[23] is the Unit of Measure). So using that array allowed for a successful field definition. Essentially, the definition says, “assign the Actual Quantity/Hours (F035) to our variable, but if Unit of Measure (U$[23]) is NOT “HR” then zero out our variable (Actual Hours).” Note that X7$ is the current field. Here’s the completed definition that worked:

F035;IF U$[23]<>"HR" THEN X7$="0"

Using the Source Code Field to determine if it is a Payroll transaction

The problem with using the Unit of Measure field is that it is not completely accurate. Meaning, a Work Order could also contain subcontract hours that use the Unit of Measure of “HR”. The only other accessible field that signifies a Payroll cost, is the Ticket Actual Cost Source Field (F049). When the Payroll Journal posts the Actual Cost transactions to Service, it uses a “1” representing the Payroll Journal, plus the Journal Date, plus the Report ID, to form the Ticket Actual Cost Source Field. So the solution was to use the first digit of the field to find out if the transaction originated in Payroll. Here’s the definition that worked, and proved more accurate than using the Unit of Measure field:

F035; IF MID(U$[49],1,1)<>"1" THEN X7$="0"

One other note: With Version 6.5.3 of Maxwell Management Suite, the Formatter Field specification for Actual Quantity/Hours (F035) is only defined for a field length of 11, but is stated to be a length of 15 in the Data Dictionary. To make the above examples work, the “SPD 035” and “SPI 035” records in CSCOD and CSCFF need to be corrected to reflect the correct field length.

What does the Purchase History by Job Report really report?

Until today, I never understood what the Purchase History by Job Report really reported. After a little research I found that the title of that report is a bit misleading. It does not report purchases, but rather it reports Purchase Order lines that have been completely received and invoiced (via AP).

Here’s some examples. For stock item, the amount of a given Purchase Order line will not be reported until the full quantity ordered is both received and invoices. For a Lump Sum amount, the report will only reflect the amount when the whole lump sum amount is fully invoiced by the Vendor.

Any lines of a Purchase Order that are partially invoiced, or partially received, will not be reflected on the report.

I vote that the name of the report be changed to “Completed Purchase History by Job Report”.

Using Advanced Filters

If you need to create a report of subcontract Vendors that have expired insurance dates, you’ll need to learn how to use Filters. There’s two types of filters. The first type of filter I call a Simple filter because you use a series of pull-down boxes to define your filter. The second type of filter is an Advanced filter where the filter definition is manually constructed.

In this example, we’ll need to use an Advanced filter, because the Simple filter can’t handle the complex logic. Remember, the report must show all subcontract Vendors (Vendors with a Normal G/L Account of 507000) where the Workers Comp, General Liability, or Other Insurance dates are expired (expired before the current terminal date).

Here’s the filter:

NORMAL_GL_ACCOUNT$="507000" AND
(WORKERS_COMP_INSUR_EXPIR_DATE$ < X$(41,2)+X$(37,2)+X$(31,2)+X$(34,2)
   OR
GENERAL_LIAB_INSUR_EXPIR_DATE$ < X$(41,2)+X$(37,2)+X$(31,2)+X$(34,2) 
  OR 
OTHER_INSURANCE_EXPIR_DATE$ < X$(41,2)+X$(37,2)+X$(31,2)+X$(34,2))

The X$ stuff represents the current date, so this filter shouldn’t need to be changed each time it’s used for a report.

If you ever need to report data and don’t want to see pages and pages of information, then remember to use a Filter. And if the Filter is complex, you may need to resort to constructing an Advanced filter.

Monarch mines for data

As good as Maxwell’s DataLink Module is at exporting reports and data to text, Excel, and PDF files, there are times when you need something more in your tool-kit. That extra tool could be Monarch Pro from Datawatch.

Monarch Pro is called a data mining or data extraction tool. It takes the output from any report you have, extracts the data from the report, and outputs it into a file, such as an Excel spreadsheet, for easy manipulation.

The beauty of Monarch Pro is it’s simplicity. Just print your report to a text file, then point Monarch Pro to that file. Then a simple to use parsing tool scans, filters, and extracts data from your report into a spreadsheet like format. The data can then be output to almost any type of file including spreadsheets. And once the parsing rules are defined, you save those rules as a template to be over and over each time you print the report.

Monarch Pro can also access data files directly. Almost any file can be read by Monarch Pro. Access and Excel files as well as any data file accessible via ODBC can be read and manipulated.

So if you can’t get just the right data extracted using your standard tools, consider Monarch Pro (priced at $559.00 at this time).