crystal使用表中不同行的记录报告组名称公式(crystal reports group name formula using a record from a different line in table)

 我试图使用表中不同行的记录创建一个组名公式，而不是链接到该组中包含的记录的行。 
 
 首先，我的数据中包含单词group ，所以为了避免混淆，我将它用斜体区分以区别于Crystal Reports中的组。 
 
 我从这个组名中提取记录的表有： 
 
 
 
 {GroupSection.Group}库存数据库中的项目组 
 
 
 {GroupSection.Section}节（这些类似于嵌套在每个库存组中的子组 ）。 
 
 
 {GroupSection.Description}问题从这里开始，因为{GroupSection.Group}和{GroupSection.Section}的描述都存储在这里。 
 
 
 这是我的表的一个示例： 
 
    {GroupSection.Group}    {GroupSection.Section}     {GroupSection.Description}
    3.00                      0.00                      PRECAST CONCRETE PRODUCT
    3.00                     50.00                      MISC PRECAST CONCRETE PRODUCT
    3.00                     99.00                      *Z* MISC PRECAST CONCRETE PRODUC
    4.00                      0.00                      CEMENT SUPPLIES
    4.00                     50.00                      MISC CEMENT SUPPLIES
    4.00                     99.00                      *Z* MISC CEMENT SUPPLIES
 
 此表中的第一行和第四行是{GroupSection.Group}的描述（它们在{GroupSection.Section}行中有0.00），其余是{GroupSection.Section}的描述。 此报告中包含的实际数据位于不同的表中，并且与此表中的前两个字段具有相同的两个字段，但不是第三个字段，因此需要链接并使用此表来对其进行描述。组名。 另一个表没有链接到0.00的行的记录。 
 
 我希望我的Group Tree看起来像这样： 
 
    3.  PRECAST CONCRETE PRODUCT
          50  MISC PRECAST CONCRETE
          99  *Z* MISC PRECAST CONCRETE
    4.  CEMENT SUPPLIES
          50  MISC CEMENT SUPPLIES
          99  *Z* MISC CEMENT SUPPLIES
 
 这是我现在在顶级组的Group Name公式中使用的有缺陷的公式： 
 
ToText (left(Cstr({GroupSection.Group}),2))+ " " + ToText (If {GroupSection.Section} <> 0 then {GroupSection.Description} Else " ")
 
 这是内嵌套组的组名公式（工作正常）： 
 
ToText (left(Cstr({GroupSection.Section}),2))+ " " + ToText ({GroupSection.Description})
 
 这就是我的Group Tree现在的样子： 
 
    3.  MISC PRECAST CONCRETE
          50  MISC PRECAST CONCRETE
          99  *Z* MISC PRECAST CONCRETE
    4.  MISC CEMENT SUPPLIES
          50  MISC CEMENT SUPPLIES
          99  *Z* MISC CEMENT SUPPLIES
 
 正如您所看到的，我需要从表中的第一行获取外部组的名称，即使它将组中的记录链接到表中的第二行和第三行。 合理？ 
 
 我已尝试在公式中使用previous（）但它会出现以下错误：无法使用此函数，因为必须稍后进行评估。 
 
 编辑：我也尝试再次添加相同的表并将其链接到组 ，一次链接到该部分 ，它适用于组名称，但现在我有190万条记录，真的重复了一些。 除非我能弄清楚如何修复多个记录，否则这将无法工作。 
 
 “。” 在组名描述中是一个无关的问题。 这是我能做到最好的3.00显示为3和50.00显示为50。 
 
 谢谢你的帮助！ 
 
 
 编辑2013年12月30日的用户@Promethean： 
 
 对不起，我是SQL Command Tables的新手。 我从另一个报告中获取了一个命令表并对其进行了一些更改。 这是我得到了多远： 
 
SELECT 
"GroupSection"."Group",
"GroupSection"."Section",
"GroupSection"."Description"

 FROM   ("SpruceDotNet"."dbo"."InventoryCommon" "InventoryCommon" with (nolock)

INNER JOIN 
"SpruceDotNet"."dbo"."GroupSection" "GroupSection" with (nolock)
ON "InventoryCommon"."Group"="GroupSection"."Group")

WHERE
"GroupSection"."Section"=0

ORDER BY 
"InventoryCommon"."Group"
 
 你有没有机会使用这个信息修改你的命令表，所以像我这样的假人可以遵循它？ 看起来你要两次添加其中一个字段，但我并不遵循一切。 
 
 然后你将如何将命令表加入包含更详细的更大的表？ 我必须使用正确的联接，所以我最终不会重复记录？ 
 
 任何帮助是极大的赞赏。 

I am trying to create a group name formula using a record from a different line in the table than the line that the records contained in this group are linked to.
 
First off, my data has the word group in it, so to avoid confusion I have italicized it to differentiate it from groups in Crystal Reports.
 
My table that I am pulling the records for this group name from has:
 
 
 
{GroupSection.Group} Groups of items in our inventory database
 
 
{GroupSection.Section} Sections (these are like subgroups nested within each inventory group).
 
 
{GroupSection.Description} The problems start here because the descriptions for {GroupSection.Group} and {GroupSection.Section} are both stored here. 
 
 
This is a sample of my table:
 
    {GroupSection.Group}    {GroupSection.Section}     {GroupSection.Description}
    3.00                      0.00                      PRECAST CONCRETE PRODUCT
    3.00                     50.00                      MISC PRECAST CONCRETE PRODUCT
    3.00                     99.00                      *Z* MISC PRECAST CONCRETE PRODUC
    4.00                      0.00                      CEMENT SUPPLIES
    4.00                     50.00                      MISC CEMENT SUPPLIES
    4.00                     99.00                      *Z* MISC CEMENT SUPPLIES
 
The first and fourth line in this table are descriptions for {GroupSection.Group} (they have a 0.00 in the {GroupSection.Section} line) and the rest are descriptions for {GroupSection.Section}. The actual data that is contained in this report is in a different table and has the same two fields as the first two in this table, but not the third field, hence the need to link to and use this table to make the descriptions of the group names. The other table has no records that link to the lines with 0.00. 
 
I want my Group Tree to look like this:
 
    3.  PRECAST CONCRETE PRODUCT
          50  MISC PRECAST CONCRETE
          99  *Z* MISC PRECAST CONCRETE
    4.  CEMENT SUPPLIES
          50  MISC CEMENT SUPPLIES
          99  *Z* MISC CEMENT SUPPLIES
 
This is the flawed formula I am using now in the Group Name formula for the top group:
 
ToText (left(Cstr({GroupSection.Group}),2))+ " " + ToText (If {GroupSection.Section} <> 0 then {GroupSection.Description} Else " ")
 
This is the Group Name formula for the inner nested group (working fine):
 
ToText (left(Cstr({GroupSection.Section}),2))+ " " + ToText ({GroupSection.Description})
 
This is what my Group Tree looks like now:
 
    3.  MISC PRECAST CONCRETE
          50  MISC PRECAST CONCRETE
          99  *Z* MISC PRECAST CONCRETE
    4.  MISC CEMENT SUPPLIES
          50  MISC CEMENT SUPPLIES
          99  *Z* MISC CEMENT SUPPLIES
 
As you can see, I need to get the name for the outer group from the first row in the table even though it's linking the records that are in the group to the second and third row in the table. Make sense?
 
I have tried using previous() in the formula but it gives the following error: This function cannot be used because it must be evaluated later.
 
Edit: I also just tried adding the same table again and linking it once to the group and once to the section and it worked for the group names, but now I have 1.9 million records, something really duplicated. So that won't work unless I can figure out how to fix the multiple records.
 
The "." in the group name description is an unrelated problem. It's the best I can do to get 3.00 to display as 3 and 50.00 to display as 50.
 
Thanks for any help!
 
 
Edit Dec 30, 2013 for user @Promethean:
 
Sorry I am new at SQL Command Tables. I took a command table from another report and did some changes to it. This is how far I got: 
 
SELECT 
"GroupSection"."Group",
"GroupSection"."Section",
"GroupSection"."Description"

 FROM   ("SpruceDotNet"."dbo"."InventoryCommon" "InventoryCommon" with (nolock)

INNER JOIN 
"SpruceDotNet"."dbo"."GroupSection" "GroupSection" with (nolock)
ON "InventoryCommon"."Group"="GroupSection"."Group")

WHERE
"GroupSection"."Section"=0

ORDER BY 
"InventoryCommon"."Group"
 
Is there any chance you could use this info to modify your command table so a dummy like me can follow it? It looks like you are adding one of the fields twice, but I don't follow everything.
 
And then how would you join the command table to the bigger table that contains the more detailed? I would have to use the right kind of join so I don't end up duplicating the records?
 
Any help is greatly appreciated.

原文：https://stackoverflow.com/questions/20507474