Creating Dynamic query for jasper server in ireport designer

[irfan.khazi123]

Hi All,

Iam working on HR and payroll reports. i have 3 input controls in jasper server.

1) Company name which is mandatory.

2) Employee Code non mandatory.

3) joining date non mandatry.

if i select company name all employee should get display in report which is working but  remaining two non mandatory inputs i may select or may not i ll select.

please help me for designing this dynamic query in ireport designer so that i can generate report based on input criteria.

 

thanks, Irfan khazi.

[kkriplani]

Hi,
It very less likely anyone will be able to give the exact query required and I would suggest that you iniate and ask doubts on this forum as and when you find any issues or need clarifications with the logic.

Some steps you need to know/keep in mind for the requirement are as follows;
1) The use of conditional parameters ($P!{abc}).   (Which I have defined to the fullest I know here)
2) The use of default expression 
3) The use of $X{IN, .... , ...} in case any of your input controls are multi-select

Hope this helps,
KKriplani

[javier.ggi90]

What Kkriplani said is true, you should go and check out the information he recommends. But to answer your question I always use something like this:

WHERE company = $P{COMPANY_NAME}
AND ($P{EMPLOYEE_CODE} is null OR $P{EMPLOYEE_CODE} = code)
AND ($P{JOIN_DATE} is null OR $P{JOIN_DATE} = joindate)

What this does is that it checks whether the PARAMETER is null and if it is not null, then it compares to the field you want to filter with.

[kkriplani]

Hi javier.ggi90,
Just need a clarrification, "code" and "joindate" are the columns in the table right?
what i get from your logic is that you compare if the parameter is empty or has a value and if it has a value, you compare it with the respective column thus giving a filter. Correct me if i am wrong.

regards,
KKriplani.

[irfan.khazi123]

hi all, i have fixed the issue with following. till company name my querry is is fixed and static. remaning is optional and conditional i may give or may not.

here is my solution. for example for employee code.

1) i have created two param with one name is $p{empCode} and another $p{empCodeNot}.

2) $p{empCode} i initialized in default expression $p{empCode}.equals(null)?"":$p{empCode}

3) $p{empCodeNot} should be prompt false and contaion the actual query in default exp. like $p{empCode}!=null?"and empCode="'+$p{empCode}'"":""

4) pass this $p!{empCodeNot} in query in ireport after where condition and it works.

 

Thank you @kkriplani you are right.

 

 

[irfan.khazi123]

hi guys for same report i have second scenario which iam struggling to solve i have two dates param fromDate and toData.

1) if enter fromDate date param byDefault my toDate should be todays date automaticaly.

2) if i enter both fromdate and toDate how is my query would be please help me for this.

 

help me for writting this query.

 

[kkriplani]

Glad you have found a solution. I would suggest you to mark the answer you find is right so it helps others with the same question to get to the right answer. Also start a new thread for the question you asked above.

Regards,
KKriplani