Hi all..
i am using ireport 3.0 for creating reports...while designing a report, in the report query i am using sql and i passed parameters... the query fetches the records corresponding to the parameter i passed.. fine...Now if i pass a null value for that parameter the query should return all the records from the table... how can i modify my query... whether i have to set default parameter or Not.. If so what is the default value i have to set so that i can get entire records from my table......
with regards,
Johnson.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
$X{ } option is giving error on 3.53a & IReport 3.0.0
===========> Evaluator.evaluateLogic: Logic does not comply with format '<expression> [<logic> <expression>]' =>
I think safest way is to set the default value in ireport parameter definition window
for the parameter $P{C_BPartner_ID} as
new java.math.BigDecimal(9999999)
add WHERE in SQL query as
AND ( c_order."c_bpartner_id" = $P{C_BPartner_ID} OR $P{C_BPartner_ID} = 9999999 )
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Martin,
the only problem I see is that you will always get records with NULL, which is not desirable.
The paradox situation of this requirement is that the user wants to see only the object he selects or all if he doesn't select an object. A boolean OR doesn't help. You need two different SQL statements: one with the WHERE clause and another without it.
Best regards,
Mario Calderon
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
BTW Martin's solution should work and is much simpler. It does not always return records where BP_ID is null. I didn't read it carefully before responding to you.
Martin's solution: "Where $P{BP_ID} is null or c_Bpartner_ID = $P{BP_ID}"
e.g. if BP_ID= 1000000
-> WHERE 1000000 is null OR C_BPartner_ID=1000000
So the first part of the OR always evaluates to false if the parameter is not null and only the second part is used.
You may have misread it as:
"WHERE C_BPartner_ID is null OR C_BPartner_ID = $P{BP_ID}"
which would behave as you described.
Just be careful to bracket that clause if you have multiple conditions.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi all..
i am using ireport 3.0 for creating reports...while designing a report, in the report query i am using sql and i passed parameters... the query fetches the records corresponding to the parameter i passed.. fine...Now if i pass a null value for that parameter the query should return all the records from the table... how can i modify my query... whether i have to set default parameter or Not.. If so what is the default value i have to set so that i can get entire records from my table......
with regards,
Johnson.
HI Johnson
Try something like
...
Where $P{BP_ID} is null or c_Bpartner_ID = $P{BP_ID}
Assuming BP_ID is your parm.
Regards
Martin
Ntier software Services
www.Ntier.co.za
$X{ } option is giving error on 3.53a & IReport 3.0.0
===========> Evaluator.evaluateLogic: Logic does not comply with format '<expression> [<logic> <expression>]' =>
I think safest way is to set the default value in ireport parameter definition window
for the parameter $P{C_BPartner_ID} as
new java.math.BigDecimal(9999999)
add WHERE in SQL query as
AND ( c_order."c_bpartner_id" = $P{C_BPartner_ID} OR $P{C_BPartner_ID} = 9999999 )
Hi Martin,
the only problem I see is that you will always get records with NULL, which is not desirable.
The paradox situation of this requirement is that the user wants to see only the object he selects or all if he doesn't select an object. A boolean OR doesn't help. You need two different SQL statements: one with the WHERE clause and another without it.
Best regards,
Mario Calderon
Referring to the jasper manual, you could try the $X{IN, columnname, parameter, ...} syntax.
e.g. in the report query
SELECT * from M_Product
WHERE $X{IN, M_Product_ID, productIDparam}
Apparently the $X expression will evaluate as "0=0" if productIDparam is null or empty, otherwise "M_Product_ID IN (productIDparam)".
Untested.
thanks Paul,
I will test it and document it in the Wiki if it works.
Best regards,
Mario Calderon
BTW Martin's solution should work and is much simpler. It does not always return records where BP_ID is null. I didn't read it carefully before responding to you.
Martin's solution: "Where $P{BP_ID} is null or c_Bpartner_ID = $P{BP_ID}"
e.g. if BP_ID= 1000000
-> WHERE 1000000 is null OR C_BPartner_ID=1000000
So the first part of the OR always evaluates to false if the parameter is not null and only the second part is used.
You may have misread it as:
"WHERE C_BPartner_ID is null OR C_BPartner_ID = $P{BP_ID}"
which would behave as you described.
Just be careful to bracket that clause if you have multiple conditions.
Hi Paul, Martin,
What happens when no parameter s entered, i.e. BP_ID equals null ?
The clause would become
-> WHERE null is null OR C_BPartner_ID=null
As the first condition is true, all records are accepted.
Thanks a lot.
Best regards,
Mario Calderon