Using parameter value in a Domain Query

[neilelliott]

I'm using iReport against a Domain Datasource, hence I'm using domain query language.

My query is quite simple (I think!) - I'm using a paramere to calculate a date which is then to be used in the query so that rows are only returned whose 'create date' field is before the given date.

In the Query Editor I can seem to do this via 'Filter expression..' builder. But what seems to be happening is that this filtering is done after the query has been executed against the database (I've turned on logging to see the SQL generated).

So my query is how can I pass in a parameter value to the main part of the query string (below is an example of what I'm trying to do, yet this causes a parsing exception).

<query>  <queryFields>    <queryField id="PM.CompanyDescription"/>    <queryField id="PM.CompanyCode"/>    <queryField id="PM.SupplierDetails.SupplierCode"/>    <queryField id="PM.SupplierDetails.SuppliedName"/>    <queryField id="PM.SupplierDetails.CodeandName"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.OrderReference"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.OrderValue"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.CreateDate"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.Buyer"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.BuyerName"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.LocationDescription"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.Location"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.OrderCreateUser"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.Element"/>  </queryFields>  <queryFilterString>           PurchaseOrderProcessing.PurchaseOrders.DocumentIndicator == 'O' and            PurchaseOrderProcessing.PurchaseOrders.OrderStatus == '0' and             PurchaseOrderProcessing.PurchaseOrders.CreateDate < $P{cutOfDate}  </queryFilterString></query>

 

 

Post Edited by neilelliott at 03/04/2011 16:14

Post Edited by neilelliott at 03/04/2011 16:15

[ernestoo]

neilelliott:

 

I'm wondering what your setting in Manage -> Ad Hoc Settings is for "Optimize Queries for JDBC-based Reports" - I believe the product ships with this turned off.

 

It's supposed to use your db instead of memory when you check that setting. If it was unchecked, please see the SQL that is generated in your log to see if something changes.

[neilelliott]

I've resolved the problem. The answer was quite straightforward, in that you just provide the parameter name in the query string, without the need for specifying $P{parameter1}

Note also that the query value in the 'queryFilterString' is in DomEL, so you can use groovy or your own custom functions for specifying the query - see JasperServer User Guide, section 7.2 The DomEL Syntax, and Using Groovy in a filter Expression

Example domain query using parameters and DomEL

<query>  <queryFields>    <queryField id="PM.CompanyDescription"/>    <queryField id="PM.CompanyCode"/>    <queryField id="PM.SupplierDetails.SupplierCode"/>    <queryField id="PM.SupplierDetails.SuppliedName"/>...    <queryField id="PurchaseOrderProcessing.PurchaseOrders.Location"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.OrderCreateUser"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.Element"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.DocumentIndicator"/>    <queryField id="PurchaseOrderProcessing.PurchaseOrders.OrderStatus"/>  </queryFields>  <queryFilterString>    PurchaseOrderProcessing.PurchaseOrders.DocumentIndicator == 'O' and contains (PurchaseOrderProcessing.PurchaseOrders.OrderStatus,'0') and PurchaseOrderProcessing.PurchaseOrders.CreateDate <= Date( parameter1 )  </queryFilterString></query>[/code]

Post Edited by neilelliott at 03/14/2011 09:17

[vahidkh]

Unfortunately when I use any other parameter except which has defined on Domanin's PreFilter paremeters, I get NullPointerException. For example when I create parameter1 through iReport and use it as above example at queryFilterString, I get NPE on even opening the topic through JasperServer UI.

Please help me!

JasperServer: 6.01