IF-ELSE用于BIRT的多个SQL标准的替代方案 [英] IF-ELSE Alternative for Multiple SQL criteria for use in BIRT
问题描述
我想使用BIRT创建报告。我有5个SQL标准作为报告的参数。通常,当我有3个标准时,我使用嵌套的if-else作为带有javascript的WHERE语句。
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.
例如表雇员的标准,具有以下5个标准:年龄,城市,部门,职称和教育。所有标准都是动态的,您可以将其留空以显示所有内容。
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?
推荐答案
有一种神奇的方法可以在没有任何脚本的情况下处理这个问题,这使报告更容易维护!我们可以使用这种SQL查询:
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=? )
因此每个条件都有两个数据集参数,OR子句允许忽略条件时参数根据您的喜好获取特定值,空值或空值。所有这些OR子句都使用常量值进行评估,因此查询的性能不会受到影响。
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.
在此示例中,我们应该有4个报告参数,8个数据集参数(每个报告参数绑定到2个数据集参数)和0个脚本。使用此方法查看报告的实时示例此处。
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.
整数参数处理
如果我们需要处理全部值对于整数列这样的年龄:我们可以将报告参数age声明为String类型,将数据集参数age声明为整数。然后,在数据集的参数选项卡中使用值表达式而不是链接到报表参数。例如,如果我们喜欢处理allnull和空值的强健输入,则输入的表达式为:
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
样本报告可以下载这里(v 4.3.1)
The sample report can be downloaded here (v 4.3.1)
这篇关于IF-ELSE用于BIRT的多个SQL标准的替代方案的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!