Crystal reports - generating report multiple times on database parameter

Question

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.

Answer 1

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!