JasperReports with Window Sql-Clauses

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.

This is a page about an enhancement that I did in May 2012. This enhancement is not (yet) inserted to ADempiere source trunk. If you find it useful and your version of adempiere misses it please inform the trunk administrator of your choice. :-)


The Problem

Sometimes you used a search criteria in your window and sorted the list view by clicking on a column header. What if you want to print a report that contains exactly that records in that order?!?

I created a tracker entry about this as [IDEMPIERE-270]. Then I wote a patch that solves the issue. You can find it in [my bitbucket repository] as some commits beginning with "IDEMPIERE-270". Please try it out and do a peer review for me. (BTW: Every Branch/Fork of ADempiere (or Compiere) is invited to insert my code - please send me a note for my pride).


internal report generator

If you use the standard report generator you have nothing to do. The old behaviour was that you get a list with only one (the active) record. You had the possibility to use the search button to create a very new search filter. With my patch the default is that you take the search filter from the window. If someone needs the old behaviour (it may be a good way to create various special forms for the actual record) you can press the report button while holding the SHIFT key. I did not transfer the sort order because normally the sort order is defined in the PrintFormat. I was not sure which source of order is better and I left it untouched.


JasperReports

The main part of my patch is to have the possibility to create a JasperReport that contains exactly the data from the window.

Before reading further you should be familiar with JasperReports and it's integration into Adempiere. Read Using Jasper Report Form in place of Standard Forms and/or JasperReports Tips und Tricks (german) and/or other sources in the wiki.

With my patch there are four new JasperReports Parameters that you can use in your JasperReports:

CONTEXT

This parameter keeps the ADempiere Context. This has nothing to do with the issue talked about at this page but you could use that if you want.

REPORT_WHERE

This parameter contains the whole WHERE clause that gives you the records that are actual shown in your tab. It does not contain the word "WHERE" so you can use it in a wider term and combine it with AND to filter it even more if you want. If there is not defined any search this parameter contains "TRUE" so you can safely insert it in your WHERE clause at any point.

REPORT_ORDERBY

This parameter contains the ORDER clause that is defined in the Application Directory's Tab definition. You can use it in your Query and combine inside a wider term too. If there is defined no order you get the term "0-1". In PostgreSQL (and I believe in Oracle too) you can use this inside a ORDER BY clause and it does nothing. So it does not harm if you use this variable to construct your query.

REPORT_SORT

If you changed the sorting inside the Tab by clicking on a column header you get a sort description here. It is similat to the REPORT_ORDERBY value. If you did not sort your table it contains also the value "0+1" so you can safely insert it into your query string.


how to use this in JasperReport

If you use iReport to create your reports you should first define new parameters with the given names. If you define also the default values I used it would be easy to test your report from inside iReport (else it works only from inside ADempiere when the parameters are set).

Now you go into your query (In the context menu on the root element of your "Report Inspector" choose the "Edit query" menu item). In the top right you see the following comment: "Drag a parameter into the query to add a parameter. Hold CTL to add the parameter as query chunk". This is what you do: You drag & drop the parameter you want into your query string while pressing the CTRL key. In the query string you get a parameter entry with "!". It uses the syntax: $P!{...}

I will give you a simple example (I did not use exacly this - it is a demonstration): 

SELECT *
FROM c_order
WHERE c_order.orderDate > now() AND $P!{REPORT_WHERE}
ORDER BY $P!{REPORT_SORT}, $P!{REPORT_ORDERBY}, orderDate ASC

Have fun and please give me feedback about this. :-) --Tbayen 20:57, 26 May 2012 (UTC)

Links

Retrieved from "http://www.adempiere.com/index.php5?title=JasperReports_with_Window_Sql-Clauses&oldid=15795"