问题描述
我熟悉 SQL,但不熟悉 Crystal Reports.我正在尝试处理包含 5 列的导入数据集:
I'm familiar with SQL but not Crystal Reports. I'm trying to deal with an imported data set with 5 columns:
id deathDate giftDate giftAmount Dead
123 2008-01-06 2011-09-08 25.00 TRUE
456 2009-06-08 2011-10-13 10.00 TRUE
789 0 2011-12-04 50.00 FALSE
...
我正在尝试执行子查询,但无法弄清楚 SQL 中 WHERE 的 CR 等效项是什么.我想做一些事情:
I'm trying to do a subquery but can't figure out what the CR equivalent of WHERE in SQL would be. I'd like to do something along the line of:
SELECT count(id) from tab1 where dead=TRUE
有什么建议吗?
推荐答案
正如 Conrad 和 dotjoe 所观察到的,与 Crystal 等效的 sql where
子句是 Select Expert - 您应该能够找到这个在报告菜单上.
As Conrad and dotjoe have observed, the Crystal equivalent of the sql where
clause is the Select Expert - you should be able to find this on the Report menu.
如果您需要在详细信息部分同时包含真假 Dead
记录,但只希望那些 Dead
为真的记录合计,最简单的方法是这样做将设置一个公式项.这样做:
If you need to include both true and false Dead
records in the detail section, but want a total for only those records where Dead
is true, the simplest way to do this would be to set up a formula item. To do so:
右键单击字段资源管理器中的公式字段选项,然后选择新建....
Right-click on the Formula Fields option in the Field Explorer and select New... .
输入合适的公式字段名称,例如 DeadCount
.
Enter a suitable formula field name, like DeadCount
.
在公式编辑器中,输入如下公式(假设 Dead
是一个字符串):
In the Formula editor, enter a formula like the following (assuming Dead
is a string):
如果 {tab1.Dead} = 'TRUE' 那么 1
使用 x-2
按钮(或 Alt-C)检查公式是否有任何错误,然后按保存并关闭"按钮退出公式编辑器.
Use the x-2
button (or Alt-C) to check that the formula does not have any errors, then press the Save and Close button to exit the formula editor.
将新公式字段从字段资源管理器拖放到报表中的任意位置.
Drag and drop the new formula field from the Field Explorer onto anywhere in the report.
右键单击刚刚添加到报表中的公式字段,然后选择插入 >摘要...来自菜单.
Right-click on the formula field that you have just added to the report and select Insert > Summary... from the menu.
在插入汇总"对话框中,将汇总操作指定为 Sum,将汇总位置指定为总计(报表页脚),然后单击确定.一个汇总字段,标记为 Sum of @DeadCount
,应该出现在报告页脚中.(您现在应该将未汇总的公式字段从您在报表设计区域中放置的位置删除.)
In the Insert Summary dialog, specify the Summary operation as Sum and the Summary location as Grand Total (Report Footer), then click OK. A summarised field, labelled something like Sum of @DeadCount
, should appear in the Report Footer. (You should now remove the un-summarised formula field from where you placed it in the report design area.)
这种技术本质上类似于在 sql 查询中包含求和的 case 值 - 类似于:select sum(case when Dead = 'TRUE' then 1 end) as DeadCount from tab1
This technique is essentially similar to including a summed case value in a sql query - something like: select sum(case when Dead = 'TRUE' then 1 end) as DeadCount from tab1
这篇关于Crystal Reports 相当于“WHERE"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!