问题描述
我熟悉SQL,但不熟悉水晶报表。我要处理的导入数据集包含5列:
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
...
我试图做一个子查询,但不知道什么CR等效的WHERE在SQL中是。我想按照以下行做一些事情:
从tab1中选择count(id)where dead = TRUE
有任何建议吗?
正如Conrad和dotjoe所观察到的,sql 其中子句的Crystal等同于Select Expert - 您应该可以在报告菜单上找到它。 / p>
如果您需要在详细信息部分中同时包含true和false Dead 记录,记录 Dead 为真,最简单的方法是设置公式项。为此:
- 右键单击字段资源管理器中的公式字段选项,然后选择新建...。
- 输入合适的公式字段名称,例如 DeadCount 。
-
公式如下(假设 Dead 是一个字符串):
如果{tab1。 Dead} ='TRUE'then 1
-
使用 x-2 按钮(或Alt-C)以检查公式是否没有任何错误,然后按保存并关闭按钮退出公式编辑器。
- 拖放
- 右键单击刚刚添加到报表中的公式字段,然后从报表中选择插入>摘要...
- 在插入摘要对话框中,将摘要操作指定为Sum,将摘要位置指定为总计(报表页脚),然后单击确定。标记为 Sum of @DeadCount 的汇总字段应显示在报表页脚中。 (您现在应该从报表设计区域中删除未汇总的公式字段。)
类似于在sql查询中包括求和的case值 - 类似于: select sum(当Dead ='TRUE'然后1 end时的情况)作为DeadCount从tab1
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 ...
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
Any suggestions?
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.
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... .
- Enter a suitable formula field name, like DeadCount.
In the Formula editor, enter a formula like the following (assuming Dead is a string):
If {tab1.Dead} = 'TRUE' then 1
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.
- 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.)
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
这篇关于水晶报表相当于'WHERE'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!