Maintaining your custom reports

If you've been working with (or evaluating) xTuple ERP for any length of time at all, you already know about our embedded report writer OpenRPT--and the ways it can be used to customize standard reports. Changes might range from adding the company logo to modifying query logic to suit your business needs. In either case, you perform your edits and then save the file back to the database with a grade higher than "0". Any report having a grade higher than 0 is considered a custom report--and the system will use the report having the highest grade first.

You also probably know that, in some cases, custom reports may need to be updated from release-to-release. What you may not realize is that detailed information exists to help simplify the report maintenance process....

This detailed information comes in the form of a text file, such as the one attached below. (This same file is also available for download on SourceForge.) The file is essentially a context diff, showing from release-to-release all reports which have been added, removed, or changed. The name of the file indicates the versions it refers to. In this case, the file "reports_331to340.txt" compares the reports in xTuple ERP version 3.3.1 against version 3.4.0--and describes the differences between the two. A similar file is created for every final release.

Here's a snippet from the top of our sample file:

====================================================================
REMOVED REPORTS:
SequencedBOM
====================================================================
CHANGED REPORTS:
CreditMemo
ExpenseCategoriesMasterList
GLTransactions
IncidentsByCRMAccount
Invoice
ListTransferOrders
POHistory
PackingList-Shipment
PackingList
PendingBOMChanges
Quote
RunningAvailability
SingleLevelBOM
SummarizedSalesByCustomerType
WOScheduleByParameterList

As you can see in the example, the first thing we learn is the scope of the changes--that is, one report was removed and 15 were changed. If after reading the list you don't see your custom reports listed there, then your work is done. If, however, they do appear, then continue reading the file. It contains more information you'll need to perform your updates.

Let's look at an example from the detail section for the CreditMemo report definition:

 REPORT: CreditMemo
 QUERY: GroupExtended
-SELECT formatExtPrice(COALESCE(cmhead_freight,0.0) + COALESCE(cmhead_tax,0.0) + COALESCE(cmhead_misc,0.0) +
-         ( SELECT COALESCE(SUM((cmitem_qtycredit * cmitem_qty_invuomratio) * cmitem_unitprice / cmitem_price_invuomratio), 0.0)
-             FROM cmitem, itemsite, item
-            WHERE ((cmitem_cmhead_id=%1)
-              AND (cmitem_itemsite_id=itemsite_id)
-              AND (itemsite_item_id=item_id)
-             )
-           )
+SELECT formatExtPrice( COALESCE(cmhead_freight,0.0) +
+                       ( SELECT COALESCE(SUM(tax * -1.0), 0)
+                         FROM ( SELECT ROUND(SUM(taxdetail_tax), 2) AS tax
+                                FROM tax JOIN calculateTaxDetailSummary('CM', cmhead_id, 'T')
+                                           ON (taxdetail_tax_id=tax_id)
+                                GROUP BY tax_id ) AS data ) +
+                       COALESCE(cmhead_misc,0.0) +
+                       ( SELECT COALESCE(SUM((cmitem_qtycredit * cmitem_qty_invuomratio) *
+                                              cmitem_unitprice / cmitem_price_invuomratio), 0.0)


  -- snip --

The detail here is telling us that in the "GroupExtended" query for the CreditMemo report, the lines with the "-" symbol next to them were removed for xTuple ERP 3.4.0 and the lines with the "+" symbol were added. If this were one of the reports you customized, you'd have to edit your custom version to make sure the same lines were added/removed. And so, using this technique, you can ensure your custom reports are kept up-to-date.