英文:
Crystal reports - generating report multiple times on database parameter
问题
如果您在查找如何根据数据库条目中的列值多次生成水晶报表时遇到问题:例如,您希望根据订单中的数量打印产品标签,其中订单包含数量列。
英文:
If you are having issues finding out how to generate a crystal report multiple times given a column value in your database entries: e.g. you would like to print labels for products depending on the quantity in an order where the order contains a column for quantity.
答案1
得分: 0
我是用以下MSSQL代码在我的SQL数据库中首先创建一个数字表来完成的:
create table numbers (number int);
insert into numbers(number)
select top 2000 row_number() over(order by t1.number) as number
from master..spt_values t1
cross join master..spt_values t2;
上述代码创建了一个包含1到2000值的数字表。如果需要的话,你可以手动指定每一行的值。
在Crystal Reports(CR)中的关键是使用新的分组并隐藏新分组创建的部分。基本上,你创建一个新的分组,然后在部分专家中可以指定你希望隐藏该组的部分,因此只显示你想要的实际报告,因为其他部分只是用来重新打印报告的虚拟部分。
要做到这一点,你需要将数字表添加到CR连接中,并链接到数据库中保存重新打印报告数量的参数。在本例中,我将使用SQL表中保存订单产品数量的列"ORDQTY"。因此,我会将包含"ORDQTY"的表链接到"numbers"表中的"number"列。然后,你需要指定连接要发生在">="上,以便你获得来自"numbers"表的每个"number"小于或等于"ORDQTY"的条目。最后一步是添加条件"{ORD.ORDQTY}>={numbers.number}"(我使用了"ORDQTY"位于"ORD"表中),然后在组专家中添加分组(我指定在每次组更改后新建一页)。
希望这对于那些遇到和我一样的问题困扰了很多天的人有所帮助!感谢SAP提供了这个非常直观和易于使用的美丽软件!
英文:
The way I did it was by firstly creating a numbers table in my SQL database by using the following code in MSSQL:
create table numbers (number int);
insert into numbersGFM(number)
select top 2000 row_number() over(order by t1.number) as number
from master..spt_values t1
cross join master..spt_values t2;
The above creates a numbers table with values from 1-2000. You can always specify each row by hand if you so wished...
The trick in Crystal Reports (CR) is to use a new grouping and hide the sections created by the new grouping. Basically you create a new grouping and then in the section expert you can specify that you want to supress the sections for this group, so only the actual report you want is shown since the other sections are just dummy sections over which to reprint the report.
To do this you need to add the numbers table to your CR connection and link the parameter in your database which holds the quantity over which to reprint the report. In this example I'll use "ORDQTY" as the column in my SQL table which holds the product quantity for the order. So I would link the table containing the "ORDQTY" to the "number" column in the "numbers" table. Then you need to specify that you want the join to occur for >= so that you obtain an entry from the "numbers" table for every "number" less than or equal to "ORDQTY". The last step is to then add the condition that {ORD.ORDQTY}>={numbers.number} (where I have used that "ORDQTY" lives in the "ORD" table), and then add the grouping in the group expert (and I specified a new page after each change in the group)
Hope this helps anyone stuck on the same problem I was for days!! Thanks SAP for the beautiful, beautiful piece of software which is so very intuitive and easy to use!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论