IF-ELSE Alternative for Multiple SQL criteria for use in BIRT
birt, if-statement, sql
Solution
There is a magical way to handle this without any script, which makes reports much easier to maintain! We can use this kind of SQL query:
SELECT *
FROM mytable
WHERE (?='' OR city=? )
AND (?=-1 OR age>? )
AND (?='' OR department=? )
AND (?='' OR title=? )
So each criteria has two dataset parameters, with a "OR" clause allowing to ignore a criteria when the parameter gets a specific value, an empty value or a null value as you like. All those "OR" clauses are evaluated with a constant value, therefore performances of queries can't be affected.
In this example we should have 4 report parameters, 8 dataset parameters (each report parameter is bound to 2 dataset parameters) and 0 script. See a live example of a report using this approach here.
If there are many more criteria i would recommend to use a stored procedure, hence we can do the same with just one dataset parameter per criteria.
Integer parameter handling
If we need to handle a "all" value for an integer column such age: we can declare report parameter "age" as a String type and dataset parameters "age" as an integer. Then, in parameters tab of the dataset use a value expression instead of a "linked to report parameters". For example if we like a robust input which handles both "all" "null" and empty values here is the expression to enter:
(params["age"].value=="all" || params["age"].value=="" || params["age"].value==null)?-1:params["age"].value
The sample report can be downloaded here (v 4.3.1)
Problem
I want to create a report by using BIRT. I have 5 SQL criterias as the parameter for the report. Usually when I have 3 criterias, I am using nested if-else for the WHERE statement with javascript. Since right now I have more criteria it becomes more difficult to write the code and also check the possibilities, especially for debug purposes. For example the criteria for table employee, having these 5 criterias : age, city, department, title and education. All criteria will be dynamic, you can leave it blank to show all contents. Do anyone know the alternative of this method?