irfan.khazi123 Posted March 9, 2016 Share Posted March 9, 2016 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. Link to comment Share on other sites More sharing options...
kkriplani Posted March 9, 2016 Share Posted March 9, 2016 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-selectHope this helps,KKriplani Link to comment Share on other sites More sharing options...
javier.ggi90 Posted March 9, 2016 Share Posted March 9, 2016 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. Link to comment Share on other sites More sharing options...
kkriplani Posted March 10, 2016 Share Posted March 10, 2016 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. Link to comment Share on other sites More sharing options...
irfan.khazi123 Posted March 12, 2016 Author Share Posted March 12, 2016 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. Link to comment Share on other sites More sharing options...
irfan.khazi123 Posted March 12, 2016 Author Share Posted March 12, 2016 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. Link to comment Share on other sites More sharing options...
kkriplani Posted March 15, 2016 Share Posted March 15, 2016 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 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