NCIT
asked on
Crystal Reports repeat page n times per value
I would like to have crystal reports, for label printing purposes, reprint the same page break by the number of a value in the records. For this example it would be a page for each Qty of each record of an Order.
For example against the following data:
Order ID Line Type Style Qty Color
--------------------------
000123456 1 Door Square 125 Red
000123456 2 Door Square 50 Red
000123456 3 Door Square 10 Red
I want user to type in "000123456" in the Order ID prompt (allows multiples if it matters) and to get 125 pages of Line 1, 50 pages of line 2 and 10 pages of line 3 printed out.
I figured I could do it by altering the dataset to have a record per order-line-qty but wanted to allow the report designers to do it all without needing to do stored procedures if possible. We use the full Crystal Reports Professional 2008 edition not the visual studio crystal designer.
For example against the following data:
Order ID Line Type Style Qty Color
--------------------------
000123456 1 Door Square 125 Red
000123456 2 Door Square 50 Red
000123456 3 Door Square 10 Red
I want user to type in "000123456" in the Order ID prompt (allows multiples if it matters) and to get 125 pages of Line 1, 50 pages of line 2 and 10 pages of line 3 printed out.
I figured I could do it by altering the dataset to have a record per order-line-qty but wanted to allow the report designers to do it all without needing to do stored procedures if possible. We use the full Crystal Reports Professional 2008 edition not the visual studio crystal designer.
One possible solution is to create a "REPEATER" table with a single column (How_Many) that looks like this:
How_Many
1
2
3
4
5
6
etc.
Now, in your report, add the Repeater Table and add a join
condition of:
--------------------------
Order.quantity >= Repeater.How_Many
--------------------------
If the ">=" join option is not available, remove the join to the REPEATER table and create this condition in the Record Selection condition:
--------------------------
{Order.quantity} >= {Repeater.How_Many}
--------------------------
This would cause each order to be duplicated as many times as the value of {Order.quantity}.
If you are restricted to using an equal join, you can also modify the repeater table to include N records for each quantity N:
1
2
2
3
3
3
...
This would cause each order to be duplicated as many times as the value of {Order.quantity}.
If you need to print "N of M" modify the repeater table to look like this:
N M
1 1
2 1
2 2
3 1
3 2
3 3
...
Cheers,
- Ido
How_Many
1
2
3
4
5
6
etc.
Now, in your report, add the Repeater Table and add a join
condition of:
--------------------------
Order.quantity >= Repeater.How_Many
--------------------------
If the ">=" join option is not available, remove the join to the REPEATER table and create this condition in the Record Selection condition:
--------------------------
{Order.quantity} >= {Repeater.How_Many}
--------------------------
This would cause each order to be duplicated as many times as the value of {Order.quantity}.
If you are restricted to using an equal join, you can also modify the repeater table to include N records for each quantity N:
1
2
2
3
3
3
...
This would cause each order to be duplicated as many times as the value of {Order.quantity}.
If you need to print "N of M" modify the repeater table to look like this:
N M
1 1
2 1
2 2
3 1
3 2
3 3
...
Cheers,
- Ido
ASKER
I know I could do it through SQL tricks but was hoping for a way to allow report designers to do it with page break or section grouping within the report since they aren't SQL experts.
We do similar page breaking in other reports but it is by a single record group and inserts page break after each group with repeating headers.
If the solution is very complicated at all for the designer we will revert to SQL, in which case we have a programmer that can acheive through SQL or C# but want to avoid more custom code in the software that runs the reports/labels.
We do similar page breaking in other reports but it is by a single record group and inserts page break after each group with repeating headers.
If the solution is very complicated at all for the designer we will revert to SQL, in which case we have a programmer that can acheive through SQL or C# but want to avoid more custom code in the software that runs the reports/labels.
There is no way for the report to easily generate that many copies.
I have inserted extra sections and put the information in them then suppressed based on the number required. It works for numbers up to about 10-15. After that it becomes unwieldy to make changes to all the sections as required.
The solutions provided above are the easiest ways to generate a "random" number of copies.
mlmcc
I have inserted extra sections and put the information in them then suppressed based on the number required. It works for numbers up to about 10-15. After that it becomes unwieldy to make changes to all the sections as required.
The solutions provided above are the easiest ways to generate a "random" number of copies.
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What "tricks" did you use?
mlmcc
mlmcc
ASKER
There was no resolution.
ASKER
What we did was intercept the dataset and manipulate it in the C# application.
We duplicated the records per qty on each record and then set the datasource of the report to the duplicated dataset. Not technically a "SQL Trick" more of a data manipulation in our report application. IT works best for our purpose because now many reports can utilize this functionality without the designers having to worry about SQL stuff.
We duplicated the records per qty on each record and then set the datasource of the report to the duplicated dataset. Not technically a "SQL Trick" more of a data manipulation in our report application. IT works best for our purpose because now many reports can utilize this functionality without the designers having to worry about SQL stuff.
We gave you the basic methods available in the report. Since you had control over the viewing application that gave you another method. Good solution
mlmcc
mlmcc
SELECT * FROM Orders o INNER JOIN NumbersTable n ON o.Qty>= n.ID