Multiple filters in iReport for Domain reporting

[klennon]

Very new to Jasper and ultimately iReport.

I've created standard ad hoc (template reports) but am now dipping my feet into iReport and have struggled with something that I feel should be very simple but for the life of me can't seem to figure it out.... 

As a SAAS company we've leverage the domain logic and it works great!

My challenge is that everything I've read (from manuals to forum posts and the like all have to do with SQL, yet  I only have access to SL query language using QueryFields and QueryFilterString. 

Issue: I have 4 input controls as parameters for a report that may or may not contain data. I need to be able to have end users either enter data (or not) for an input control that looks at a database (through domain) that either will contain data in one or all of those fields or it will not.  Data is only returning to the report if there is data in the database (skipping where a record has a null value).

Example (the following user would not show on the report because Bureau is null):

Fist Name = John

Last Name = Smith

Emp Type = Manager

Bureau = null

I have set the default value expression for the parameters to "%", to be a wildcard to grab everything if nothing is submitted by the user running the report.

My filter string:

  <queryFilterString> startsWith (newSet1.dim_lms_user1.first_name,first_name) and  startsWith (newSet1.dim_lms_user1.last_name,last_name) and  startsWith (newSet1.dim_lms_user1.cua122,emp_type) and startsWith (newSet1.dim_lms_user1.cua42, bureau)
  </queryFilterString>

Each of the 4 fields have the following expression (similar to the following):

(($F{newSet1.dim_lms_user1.cua122}!=null && $F{newSet1.dim_lms_user1.cua122}.length()>0)?$F{newSet1.dim_lms_user1.cua122}:"")

My dilemma is that the report will only display data where there is a value (non null) in all of the 4 fields.

Any help would be greatly appreciated as this will be the foundation for 90% of the reports that will be created using iReport.

[slow]

I don't know what language you use... but with IReport and java your issue seems very simple to solve...

I've created a similar example to yours to test a solution, but in the query editor, editing the filter expression, and adding a condition similar to this (related to yours!):

new Boolean(
$F{FIRST_NAME}.startsWith(($P{firstName}!=null && $P{firstName}.length()>0)?$P{firstName}:"")
&&
$F{LAST_NAME}.startsWith(($P{lastName}!=null && $P{lastName}.length()>0)?$P{lastName}:"")
&&
$F{EMP_TYPE}.startsWith(($P{empType}!=null && $P{empType}.length()>0)?$P{empType}:"")
&&
$F{BUREAU}.startsWith(($P{bureau2}!=null && $P{bureau }.length()>0)?$P{bureau}:"")
)

it works very well...

note that the
FIRST_NAME, LAST_NAME, EMP_TYPE, BUREAU are fields coming from DB or data source...
firstName,lastName,empType,bureau2 are parameters coming from users...
(i don't set default value for them...)

so, when I haven't a string coming from user, then I compare the filed with the startinf string "" (empty!), so it is true...

give me feedback...

 

_________________________________________

if it works... give me KARMA points please!    : ) 
_________________________________________

listening: Datura - West Line

 

 

 

Post Edited by slow at 06/11/2010 07:49

[klennon]

Thanks for the response. With the filter expression that you supplied it only supports the data at time of input (parameter side). With a startsWith comparator - if the database has data in any of the 4 fields the expression will validate for false and skip the record. What I am looking for is similar code that supports skipping if the field is null (including it) or validating against the value supplied by the parameter. If bureau from the DB is null, automatically include the field - otherwise validate the field against the parameter.

[slow]

I'm not sure I understand your problem... but I suppose that:

1)
if there is a row in the DB that presents a single field null, then you want that this field don't be compared to the relative user parameter... and the row, if pass the filter about the non-null fields, will be present in the final report.

2)
i suppose also that if a field has a null value and the relative user parameter has a non blank value (lenght>0) then the row does'nt pass the filter and it isn't in the report.

for this try to use:

new Boolean(
($F{FIRST_NAME}!=null?($F{FIRST_NAME}.startsWith(($P{firstName}!=null && $P{firstName}.length()>0)?$P{firstName}:"")):($P{firstName}!=null && $P{firstName}.length()>0)?false:true)
&&
($F{LAST_NAME}!=null?($F{LAST_NAME}.startsWith(($P{lastName}!=null && $P{lastName}.length()>0)?$P{lastName}:"")):($P{lastName}!=null && $P{lastName}.length()>0)?false:true)
&&
($F{EMP_TYPE}!=null?($F{EMP_TYPE}.startsWith(($P{empType}!=null && $P{empType}.length()>0)?$P{empType}:"")):($P{empType}!=null && $P{empType}.length()>0)?false:true)
&&
($F{BUREAU}!=null?($F{BUREAU}.startsWith(($P{bureau2}!=null && $P{bureau2}.length()>0)?$P{bureau2}:"")):($P{bureau2}!=null && $P{bureau2}.length()>0)?false:true)
)

note that the
FIRST_NAME, LAST_NAME, EMP_TYPE, BUREAU are fields coming from DB or data source...
firstName,lastName,empType,bureau2 are parameters coming from users...

 

if you don't want the second condition (so, if it is a null field, the relative parameter would be ignored) use this:

new Boolean(
($F{FIRST_NAME}!=null?($F{FIRST_NAME}.startsWith(($P{firstName}!=null && $P{firstName}.length()>0)?$P{firstName}:"")):true)
&&
($F{LAST_NAME}!=null?($F{LAST_NAME}.startsWith(($P{lastName}!=null && $P{lastName}.length()>0)?$P{lastName}:"")):true)
&&
($F{EMP_TYPE}!=null?($F{EMP_TYPE}.startsWith(($P{empType}!=null && $P{empType}.length()>0)?$P{empType}:"")):true)
&&
($F{BUREAU}!=null?($F{BUREAU}.startsWith(($P{bureau2}!=null && $P{bureau2}.length()>0)?$P{bureau2}:"")):true)
)

if it doesn't solve your issue... try to be more more clear with an example and excuse me for not understanding...

 

 

_________________________________________

if it works... give me KARMA points please!    : ) 
_________________________________________

listening:  Nine Inch Nails - Complication

 

 

[klennon]

Thanks - that was perfect (I was including a second expression and it wasn't working out).