按组和子组汇总 [英] Summarizing by group and subgroup
问题描述
我有一个水晶报告，它有一个基于两个字段的分组语句
- 公司名称
- 帐户类型
现在Crystal报表工作，它按companyname分组，但不按帐户类型 。我有两种类型的帐户，正常和高级。一家公司有两种类型的帐户与我们，它拉普通，而不是溢价。你认为是什么问题。
我是水晶报告的新手。我熟悉SQL虽然。我看到的区别是
- 没有用于任何列的聚合函数 - 在SQL中，聚合字段是必需的，以获得有意义的结果
- 报表中有大约10个字段，但group by仅用于两个字段 - 在SQL中，如果有多个字段，则必须按所有字段分组
$ b 在这种情况下，我首先按公司名称分组，然后按帐户状态分组。任何洞察力都会有帮助。
请注意，我从Crystal Report中生成了等效的SQL语句。该查询是确定的，除了没有group by语句。我添加了那些和聚合列自己，我得到了相同的结果，因为我得到了水晶（但SQL报告是更准确的，因为它通过两个字段而不只是一个）。
编辑：示例日期。每个公司可以有多个相同类型的帐户。我想要在公司名称和AccountType上合计，以便数据如下所示。
+ + -------------- + ------------- + ------ +
| ID |公司名称| AccountType |出售|
+ ---- + -------------- + ------------- + ------ +
| 1 | ABC | I | 500 |
| 2 | ABC | I | 600 |
| 3 | ABC | O | 1000 |
| 4 | ABC | O | 2000
| 5 | ABC | O | 3000 |
| 6 | XYZ | O | 2500
| 7 | LMN | O | 3400 |
+ ---- + -------------- + ------------- + ------ +
输出我想从上表是
+ -------------- + ------------- + ------ +
|公司名称| AccountType |出售|
+ -------------- + ------------- + ------ +
| ABC | I | 1100 |
| ABC | O | 6000 |
| XYZ | O | 2500 |
| LMN | O | 3400 |
+ -------------- + ------------- + ------ +
解决方案更新以纳入评论讨论和修订问题：
有两个基本选项可解决此问题：
1）修改SQL以执行所需的聚合。
2）在Crystal中，添加两个组，一个用于公司，然后一个用于帐户类型。
如果不执行分组，则可以添加分组，在数据库中读取的每一行都将显示在详细信息部分（技术上，每行重复整个详细信息部分）。
添加第一个组（公司），Crystal在详细信息部分前添加组头1，在详细信息部分后添加组头1。
如果此时运行报表，对于每个公司，Crystal将在与该公司关联的每组详细记录之前显示组头，然后将显示所有
通常，组头用于显示该公司的详细记录，最后显示该公司的组页脚。需要对每个细节记录重复。在此示例中，我们可以显示公司名称和与公司相关的其他信息。
同样，组页脚通常用于显示所有详细记录的摘要信息显示在该组中。
在这种情况下，我们可以添加汇总销售金额的汇总，这将是该公司的总销售额，而不考虑帐户类型。
添加第二个组时，将对原始组执行子分组。
Crystal将在组标题＃1下方和详细信息上方放置组标题＃2，并将组脚注＃2直接放置在详细信息下方和组脚注＃1之上。
此时，您的报告格式类似于以下内容：
组头1 b $ b组头2（帐户类型）
详细信息（个人销售记录）
组尾＃2
组头1＃
在这种情况下，对于每个公司，我们要根据帐户类型对详细信息记录进行分组。因此，我们可以向组头2添加描述帐户类型的信息（如果需要），我们可以向组页脚＃2添加总计以显示公司内每种帐户类型的总计。
现在，如果所需的是显示每个公司中每个帐户类型的总计，那么我们需要在报表中显示的唯一部分是组页脚＃2。所有字段（公司，帐户类型，销售总额）在此页脚中可用，因此我们不需要任何其他区域。
I have a crystal report, it has a group by statement based on two fields
- Companyname
- Account Type
Now Crystal report work and it does group by "companyname" but it does not group by "account type". I have two types of account, "Regular and Premium". One company has both type of account with us and it pulls on regular and not premium. What do you think is the issue.
I am new to crystal report. I am familiar with SQL Though. The differences that I see is
- There is no aggregate function used for any column - in SQL an aggregate field is required to get meaningful result
- There are about 10 fields in the report but group by is used only on two fields - in SQL you have to group by all fields if there are more than two fields
- The group by fields are in the middle and in end - again in SQL the order of groupby field is important
In this case I am grouping by companyname first and then account status. Any insight will be helpful.
Note that I generated the equivalent SQL statement from Crystal Report. That query was ok except there were not group by statements. I added those and the aggregate column myself and I do get the same result as I am getting in Crystal (but the SQL report is more accurate as it does group by both the fields not just one).
Edit: Example Date. Each company can have multiple account of the same type. I want aggregate on "Company Name" and "AccountType" so that the data is listed as follow.
+----+--------------+-------------+------+
| ID | Company Name | AccountType | Sale |
+----+--------------+-------------+------+
| 1 | ABC | I | 500 |
| 2 | ABC | I | 600 |
| 3 | ABC | O | 1000 |
| 4 | ABC | O | 2000 |
| 5 | ABC | O | 3000 |
| 6 | XYZ | O | 2500 |
| 7 | LMN | O | 3400 |
+----+--------------+-------------+------+
Output I want from the above table is
+--------------+-------------+------+
| Company Name | AccountType | Sale |
+--------------+-------------+------+
| ABC | I | 1100 |
| ABC | O | 6000 |
| XYZ | O | 2500 |
| LMN | O | 3400 |
+--------------+-------------+------+
Update to incorporate comment discussion and revised question:
There are two basic options for resolving this issue:
1) Revise the SQL to perform the desired aggregation.
2) Within Crystal, add two groups, one for the company, then one for the account type.
Before you add groups in crystal, you have several standard sections, including the Report Header, Page Header, Details, Report Footer, and Page Footer.
If you do not perform grouping, each row that is read in the database will be displayed in the details section (technically, the entire details section is repeated for each row).
When you add the first group (Company), Crystal adds a Group Header #1 before the Details section and a Group Header #1 after the Details section.
If you run the report at this point, for each company Crystal will show the Group Header before each group of detail records associated with that company, then will show all of the detail records for that company, and finally, will shown the Group footer for that company.
Typically, the group header is used to display common information for that grouping that does not need to be repeated for each detail record. In this example, we could display the company name and other information related to the company.
Likewise, the group footer is typically used to display summary information for all of detail records displayed within that group.
In this case, we could add an aggregate that would summarize the Sale amount, which would be the total sales for that company, regardless of the account type.
When the second group is added, it will perform sub-grouping on the original group.
When the second group is added, Crystal will place a Group Header #2 below Group Header #1 and above the Details and will place a Group Footer # 2 directly below the details and above the Group Footer #1.
At this point, you have a report format similar to the following:
Group Header #1 (Company)
Group Header #2 (Account Type)
Details (the individual sale records)
Group Footer #2
Group Header #1
In this case, for each company, we want to group the details records by account type. So we can add information that describes the account type, if we want, to the Group Header #2 and we can add aggregates to Group Footer #2 to display totals for each account type within the company.
Now, if all that is desired is to show the totals for each account type within each company, then the only section that we need to show in the report is Group Footer #2. All of the fields (company, account type, sale aggregate) are available in this Footer, so we don't need any of the additional areas.
这篇关于按组和子组汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
