"Free Milk" — Report Writing Tips

Everyone loves report writing and no one here at xTuple loves report writing more than Chris. As a matter of fact, he loves report writing so much, he has set it free and now report writing — by and large the customer requested report mods — reside under my 'Umbrella of Responsibility'.

So, I thought I'd share the wealth, and be the giver of "Free Milk" — if you will — by sharing some of my report writing tips.

This tutorial will take you through how to use some of the fancy schmancy little known tricks we've put into openRPT and maybe I'll tell you about some of the undocumented stuff too.

I'll assume that you know how to open up a report in OpenRPT with the standalone app, or with the embedded version in the client. The example I'll be using for this tutorial is the ARAging report, mostly because it's fairly straight forward with only one detail section query. We'll also add an 'age' column to reduce our brain's math load, and we'll also make it print more than one account per page.

The Boy Scout Motto: Be Prepared...

The first thing you should do when planning to move up to a new version of xTuple is to read the reports changlog i.e. reports_331to340.txt. This changelog gives you hints on what reports have changed. The report itself is basically in 'diff' format, but it contains everything you should need to modify a particular report to work. Whereupon after reading through this doc, you should find that modifying your old custom report is easier to get working again.

From the example below, everything with a '-' has been changed to the corresponding line below it with a '+'. These are fundamental changes that have occured between 3.3.1 and 3.4.0. Knowing this stuff before a migration makes life much easier. This is one of the things you should be testing and looking at when sandboxing the new version.  Also follow along with Professor Atherton's Best Practice ERP: Migration Checklists , it will save you headaches.

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)
+                         FROM cmitem
+                         WHERE (cmitem_cmhead_id=%1) )

For PostBooks users, this report changelog can be found on sourceforge at http://sourceforge.net/projects/postbooks/files/02%20PostBooks-GUIclient...
And for Standard and Manufacturing users, look in your downloads section on xtuple.org.

 Get Down With Dynamic Parameters.

One of the most helpful items in our arsenal is the support for dynamic and static parameters. Static Parameters are good for things like TRUE/FALSE, and other known variables. Dynamic Parameters allow us to run the report right from OpenRPT without having to know the record id's of things. We can simply write a query to populate a selection list with something we can recognize at print preview time.
Dynamic and Static Parameters are accessed in OpenRPT by navigating through: Document -> Defined Parameters, click Add - they're on the Defined List Tab when we add a parameter.

But, how do we know which parameters to define? That's kinda easy, even for n00b. Simply look at the query source (Document -> Query Sources, edit 'detail') and look for MetaSQL tags. A MetaSQL tag is something that looks like <? value("some_thing") ?>. From this example, we know that 'some_thing' is something that the report can be passed in to query on. You can even write down all of the MetaSQL tags so you don't have to go back and forth.
The ones I see in the ARAging report are:

<? value("relDate") ?><? value("useDocDate") ?><? value("cust_id") ?><? value("custtype_id") ?>
<? value("custtype_pattern") ?>

In this particular ARAging report, everything below the FROM statement is optional. These optional parameters correspond to the filters available on the Receivables Aging screen where you can run the report by All Customers (default) or by customer, customer type, group or pattern, etc.

Since everything in the 'if exists' portion is optional, the main part of this query that is a minimum requirement to get something printing is on the FROM line:

FROM araging(<? value("relDate") ?>, <? value("useDocDate") ?>)

araging is a function in the database that returns a set of records. The parameters that this functions needs to be successful is a "relDate" and a "useDocDate". From the definition of the araging function, "relDate" is a date, and "useDocDate" is a TRUE or FALSE (a boolean).

So, now that we know that the report requires a "relDate", let's add it as a Dynamic Parameter.

Navigate to the defined parameters and click Add. On the Parameter screen, type in:

Name: relDate
Type: string
Active: Checked
Default: 2010-04-22
Description Tab: Relative Date

and click OK to save.

We will ignore the other parameters for the time being.
Now, if you navigate to the File -> Print Preview you should get an ARAging report for everyone that meets that "relDate" requirement, if you don't get a result, try changing the date to something you know works.

Adding Optional Parameters

But, lets say we only want to see a single customer, we need to apply the same filters as the screen. Navigate back to the Parameter screen, and make these entries.

Name: cust_id
Type: string
Active: Checked
Default: leave blank
Description Tab: Customer List
Now, click on the Defined List Tab and click Dynamic
In the Dynamic List Query box, enter:
SELECT cust_id, cust_name||' - '||cust_city||', '||cust_state FROM cust WHERE cust_active ORDER BY cust_name;

Now, when we go to the print preview we have some choices. You should see a grid entry type screen, click in the value box in the cust_id row, now you should see that the list box has enabled.

So, what about those other metaSQL parameters that define other filters? Let's use them.

Go back and add these entries.
Set the Customer Type parameter:

Name: custtype_id
Type: string
Active: NOT Checked
Default: leave blank
Description Tab: Customer Type
Dynamic List Query:
SELECT custtype_id, custtype_code||' - '||custtype_descrip FROM custtype ORDER BY custtype_code;

Set the Customer Pattern parameter:

Name: custtype_pattern
Type: string
Active: NOT Checked
Default: leave blank
Description Tab: Customer List
Now, click on the Defined List Tab and click Static
In the Static List, click Add:
Value: NOR
Label: Normal

And now we'll use the "useDocDate"

Name: useDocDate
Type: bool
Active: Checked
Default: TRUE
Description Tab: Use Doc Date True/False
Now, click on the Defined List Tab and click Static
In the Static List, click Add:
Value: FALSE
Label: False

To use these filters that are inactive, you can double-click in the VALUE column from the Print Preview Parameter list and toggle them. You can also click on edit in the case of relDate and enter a different date.  Save your progress to a higher grade.

Make sure you save this change back to the database at a higher grade.

Notice anything when you go and print everything? Right! Only one customer per page. This is not a very eco-friendly way to print reports. So lets fix that and while we're at it lets add an age column so we don't have to do math.
Navigate to our 'detail' query (Document -> Query Sources, click "detail" and edit). Let's add an aging column right under the SELECT to the query based on the relative date minus araging document date with:

((<? value("relDate") ?>) - CAST(araging_docdate AS date)) AS age,

And click OK.

Back on the report designer screen let's add an 'Age' column. Usually, I'll select an existing column label in the Customer Group Header section - i.e. PO Number on this report and CTRL-C, CTRL-V it (copy/paste), immediatly after I paste it, I jockey it into position with my arrow keys on the keyboard. IMHO, this is much better/faster than mousing it around, and my keyboard makes awesome clicking sounds that my co-workers love. Once I get it into position, I'll right click for properties and change the 'PO Number' text to 'Age' and set the width to about .25. Next, in the Detail section, give the same copy/paste treatment to the araging_ponumber field and align it under the 'Age' column description. Under properties for this new field, change the column to 'age'.
Now, let's shrink up some sections here so we can fit more than 1 customer on a page.
See that "Note: All amounts are in base currency as of the document date." in the Customer Group Footer? Let's move it. I like it up in the Report Header section. To select both of them, hold down the CTRL key and left click on each label. You should now have them both selected. Release the CTRL key and click and hold your left mouse button on one of the selected items and drag both items up to the report header.
OK, back in the Customer Group Footer section. See the "Total For Customer" label? Left Click select that and move it up 5 up arrow taps. This should bring it right below the horizontal line for that section. Move the remaining 2 items in that section up 5 taps.
Now, over on the right hand side of the report, move the Page Footer (Last) section divider up to just below that cust_name:detail field.
Now you can do a print preview and if it looks OK, save it at a higher grade. Now, lets fix the problem of only having one customer printing per page. This option is a little bit buried:

Select Document -> Section Editor, 
	Highlight 'Detail' -> Click Edit, 
	Select 'Customer Group' Section -> Click Edit, 
	Unselect Insert Page Break After This Footer

Now, print preview your report. If you happen to have part of the "Note: All amounts are in base currency as of the document date." from the Report Header showing up where it doesn't belong it is becuase it does not move both items to the same section. Select the item that is out of place and move it out of it's section then back in to where you want it - literally drag it out then back. This is a bit of a bug. 

Your detail query for the ARAging report should now look like this:

SELECT 	
	((<? value("relDate") ?>) - CAST(araging_docdate AS date)) AS age,
	araging_docdate,
	araging_duedate,
	araging_ponumber,
	araging_docnumber,
	araging_doctype,
	araging_cust_id,
	araging_cust_number,
	araging_cust_name,
	araging_cust_custtype_id,
	araging_custtype_code,
	araging_terms_descrip,
	formatMoney(araging_aropen_amount) AS araging_aropen_amount,
	araging_cur_val,   formatMoney(araging_cur_val)    AS araging_cur_amt,
	araging_thirty_val,formatMoney(araging_thirty_val) AS araging_thirty_amt,
	araging_sixty_val, formatMoney(araging_sixty_val)  AS araging_sixty_amt,
	araging_ninety_val,formatMoney(araging_ninety_val) AS araging_ninety_amt,
	araging_plus_val,  formatMoney(araging_plus_val)   AS araging_plus_amt,
	araging_total_val, formatMoney(araging_total_val)  AS araging_total_amt
FROM araging(<? value("relDate") ?>, <? value("useDocDate") ?>)
<? if exists("cust_id") ?>
   WHERE (araging_cust_id=<? value("cust_id") ?>)
<? elseif exists("custtype_id") ?>
   WHERE (araging_cust_custtype_id=<? value("custtype_id") ?>)
<? elseif exists("custtype_pattern") ?>
   WHERE (araging_custtype_code ~ <? value("custtype_pattern") ?>)
<? endif ?>; 

Your finished ARAging report should now look like this:

Secrets:

Holding shift and control while moving an object keeps it linked to it's section.

More hints come down in the comments section...