如何在 Crystal 中显示多个不相关的 1:N 记录
我有一个正在报告的数据集。但是,有的数据是重复的,有的则不是:
ID Project Org Type Facility AssessorManager Assessor
123 Proj1 Org1 Ty1 Facil1 Smith Jones
123 Proj1 Org2 Ty1 Facil2 Smith Black
123 Proj1 Org2 Ty1 Facil3 Smith Black
234 Proj1 Org3 Ty2 Facil3 Harris Jones
234 Proj1 Org1 Ty2 Facil3 Harris Jones
393 Proj2 Org2 Ty3 Facil1 Smith Black
所以,对于一个ID来说,与Project、Type、AssessorManager是1:1的关系;与组织、设施和评估者的 1:N 关系。
我想要一份不会重复过多的报告,并且报告了 1:1 数据,然后为每个 1:N 数据集存储了过程。我得到如下结果,但速度很慢。
ID Project Org Type Facility AssessorManager Assessor
123 Proj1 Org1 Ty1 Facil1 Smith Jones
Org2 Facil2 Black
Facil3
234 Proj1 Org3 Ty2 Facil3 Harris Jones
Org1
393 Proj2 Org2 Ty3 Facil1 Smith Black
我可以为 1:N 字段创建一个函数来对数据进行排序并显示不同的数据吗?我可以对字段使用“如果重复则抑制”选项,但我需要对 ID 中的每个字段单独进行排序。我不能使用group by和set groups,因为有4个1:N字段,而且它们都是相互独立的。
(过去进行分组会产生如下所示的报告:
ID Project Org Type Facility AssessorManager Assessor
123 Proj1 Org1 Ty1 Facil1 Smith Jones
Black
Facil2 Jones
Black
Facil3 Jones
Black
Org2 Facil1 Jones
Black
Facil2 Jones
Black
Facil3 Jones
Black
...等)
编辑... 回答... 我接受了 PowerUser 的回答,主要使用了他的第二个建议。我在查询中添加了字段。它仍然存在重复,因为某些 1:N 字段是查询和排序字段。可能有比这里显示的更多的重复,但这就是想法。另外,我没有使用逗号来分隔备注字段中的值,而是使用了返回字符。
ID Project Org Orgs Type Facility Facilities AssessorManager Assessors
123 Proj1 Org1 Org1,Org2 Ty1 Facil1 Facil1,Facil2,Facil3 Smith Jones,Black
123 Proj1 Org2 Org1,Org2 Ty1 Facil2 Facil1,Facil2,Facil3 Smith Jones,Black
123 Proj1 Org2 Org1,Org2 Ty1 Facil3 Facil1,Facil2,Facil3 Smith Jones,Black
I have a data set that I am reporting on. However, some of the data is duplicated and some is not:
ID Project Org Type Facility AssessorManager Assessor
123 Proj1 Org1 Ty1 Facil1 Smith Jones
123 Proj1 Org2 Ty1 Facil2 Smith Black
123 Proj1 Org2 Ty1 Facil3 Smith Black
234 Proj1 Org3 Ty2 Facil3 Harris Jones
234 Proj1 Org1 Ty2 Facil3 Harris Jones
393 Proj2 Org2 Ty3 Facil1 Smith Black
So, for an ID, there is a 1:1 relationship with the Project, Type, and AssessorManager; and a 1:N relationship with the Org, Facility, and Assessor.
I want a report that doesn't repeat more than necessary, and have reported the 1:1 data, and then stored procedures for each of the 1:N data sets. I get a result like below, but it is dog SLOW.
ID Project Org Type Facility AssessorManager Assessor
123 Proj1 Org1 Ty1 Facil1 Smith Jones
Org2 Facil2 Black
Facil3
234 Proj1 Org3 Ty2 Facil3 Harris Jones
Org1
393 Proj2 Org2 Ty3 Facil1 Smith Black
Can I make a function for the 1:N fields that orders the data and shows distinct data? I can use the suppress if duplicated option for the field, but I need to sort each field within the ID on its own. I can't use group by and set groups, because there are 4 1:N fields, and they are all independent of each other.
(Doing the group by in the past has resulted in a report that looks like the following:
ID Project Org Type Facility AssessorManager Assessor
123 Proj1 Org1 Ty1 Facil1 Smith Jones
Black
Facil2 Jones
Black
Facil3 Jones
Black
Org2 Facil1 Jones
Black
Facil2 Jones
Black
Facil3 Jones
Black
...etc.)
EDIT...
answer...
I accepted PowerUser's answer, mostly using his second suggestion. I added fields to my query. It still had duplication, because some of the 1:N fields were query and sort fields. There is probably more duplication than shown here, but this is the idea. Also, rather than using commas to separate the values in the memo fields, I used a return character.
ID Project Org Orgs Type Facility Facilities AssessorManager Assessors
123 Proj1 Org1 Org1,Org2 Ty1 Facil1 Facil1,Facil2,Facil3 Smith Jones,Black
123 Proj1 Org2 Org1,Org2 Ty1 Facil2 Facil1,Facil2,Facil3 Smith Jones,Black
123 Proj1 Org2 Org1,Org2 Ty1 Facil3 Facil1,Facil2,Facil3 Smith Jones,Black
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
+1为你的例子!让我说清楚。它可以工作,但您希望它工作得更快吗?
Proj 1, Org 1, Black
行。内部连接可以解决这个问题,但要确保它不会在此过程中产生任何其他问题。GroupBy=[ID]&[Proj]&[Org]
,而不是按 ID、Proj、Org 等进行分组。这会加快速度,因为您只有 1 个分组级别。(如果这些建议之一有帮助,请告诉我哪一个。)
+1 for your examples! So let me get this straight. It works, but you want it to work faster?
Proj 1, Org 1, Black
when there is no record in your original data set. Inner joining would fix that, but make sure it doesn't produce any other problems in the process.GroupBy=[ID]&[Proj]&[Org]
. That will speed things up a bit since you only have 1 grouping level.(If one of these suggestions is helpful, please tell me which one.)