问题描述
我有一个带有这样查询的数据集:
I have a DataSet with a query like this:
select s.name, w.week_ending, w.sales
from store s, weekly_sales_summary w
where s.id=w.store_id and s.id = ?
我想修改查询以允许我指定商店ID的列表，例如:
I would like to modify the query to allow me to specify a list of store IDs, like:
select s.name, w.week_ending, w.sales
from store s, weekly_sales_summary w
where s.id=w.store_id and s.id IN (?)
如何在BIRT中完成此任务?我需要指定哪种参数?
How do I accomplish this in BIRT? What kind of parameter do I need to specify?
推荐答案
最简单的部分是报表参数:将显示类型设置为列表框"，然后选中允许多个值"选项.
The easy part is the report parameter: set the display type to be List Box, then check the Allow Multiple Values option.
现在最困难的部分:不幸的是，您不能将多值报表参数绑定到数据集参数(至少不是在我正在使用的3.2版中). BIRT World博客上有一个帖子: http://birtworld.blogspot.com/2009/03/birt -multi-select-statements.html 介绍了如何使用代码插件将多选报告参数绑定到报告数据集.
Now the hard part: unfortunately, you can't bind a multi-value report parameter to a dataset parameter (at least, not in version 3.2, which is what I'm using). There's a posting on the BIRT World blog here:http://birtworld.blogspot.com/2009/03/birt-multi-select-statements.htmlthat describes how to use a code plug-in to bind multi-select report parameters to a report dataset.
不幸的是，当我尝试时，它没有用.如果您可以使用它，那就是我建议的方法.如果不能，则替代方法是修改数据集的queryText，以将report参数中的所有值插入到查询中的适当位置.假设s.id是数字，这是一个可以粘贴到数据源的beforeOpen事件脚本中的函数:
Unfortunately, when I tried it, it didn't work. If you can get it to work, that's the method I would recommend; if you can't, then the alternative would be to modify the dataset's queryText, to insert all the values from the report parameter into the query at the appropriate point. Assuming s.id is numeric, here's a function that can be pasted into the beforeOpen event script for the datasource:
function fnMultiValParamSql ( pmParameterName, pmSubstituteString, pmQueryText )
{
strParamValsSelected=reportContext.getParameterValue(pmParameterName);
strSelectedValues="";
for (var varCounter=0;varCounter<strParamValsSelected.length;varCounter++)
{
strSelectedValues += strParamValsSelected[varCounter].toString()+",";
}
strSelectedValues = strSelectedValues.substring(0,strSelectedValues.length-1);
return pmQueryText.replace(pmSubstituteString,strSelectedValues);
}
然后可以从数据集的beforeOpen事件脚本中调用
，如下所示:
which can then be called from the beforeOpen event script for the dataset, like this:
this.queryText = fnMultiValParamSql ( "rpID", "0 /*rpID*/", this.queryText );
假设您的报告参数称为rpID.您将需要修改查询，使其看起来像这样:
assuming that your report parameter is called rpID. You will need to amend your query to look like this:
select s.name, w.week_ending, w.sales
from store s, weekly_sales_summary w
where s.id=w.store_id and s.id IN (0 /*rpID*/)
脚本中包含0，以便查询脚本在设计时有效，并且数据集值将正确绑定到报表；在运行时，该硬编码的0将被删除.
The 0 is included in the script so that the query script is valid at design time, and the dataset values will bind correctly to the report; at runtime, this hard-coded 0 will be removed.
但是，这种方法可能非常危险，因为它可能使您容易受到SQL注入攻击的攻击: http://en.wikipedia.org/wiki/SQL_injection ，如此处所示: http://xkcd.com/327/.
However, this approach is potentially very dangerous, as it could make you vulnerable to SQL Injection attacks: http://en.wikipedia.org/wiki/SQL_injection , as demonstrated here: http://xkcd.com/327/ .
如果从预定义的选择列表中选择纯数字值，则不应进行SQL注入攻击.但是，在允许使用该参数的自由格式输入字符串的情况下，该方法也很容易受到攻击.
In the case of purely numeric values selected from a predefined picklist, a SQL injection attack should not be possible; however, the same approach is vulnerable where freeform entry strings for the parameter are allowed.
这篇关于如何在BIRT报告中将参数设置为值列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！