水晶报表相当于'WHERE' [英] Crystal Reports equivalent of 'WHERE'

问题描述

我熟悉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'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！