neilelliott Posted March 4, 2011 Share Posted March 4, 2011 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:14Post Edited by neilelliott at 03/04/2011 16:15 Link to comment Share on other sites More sharing options...
ernestoo Posted March 12, 2011 Share Posted March 12, 2011 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. Link to comment Share on other sites More sharing options...
neilelliott Posted March 14, 2011 Author Share Posted March 14, 2011 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 ExpressionExample 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 Link to comment Share on other sites More sharing options...
vahidkh Posted August 18, 2015 Share Posted August 18, 2015 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now