?
Solved

Crystal Reports repeat page n times per value

Posted on 2013-01-22
9
Medium Priority
?
2,624 Views
Last Modified: 2013-02-12
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.
0
Comment
Question by:NCIT
9 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 38808014
Create another table with the numbers from 1 to the max number of copies. Then join the orders table with this new table something similar to:

SELECT * FROM Orders o INNER JOIN NumbersTable n ON o.Qty>= n.ID
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 38808017
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
0
 
LVL 1

Author Comment

by:NCIT
ID: 38808203
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 101

Expert Comment

by:mlmcc
ID: 38808274
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
0
 
LVL 1

Accepted Solution

by:
NCIT earned 0 total points
ID: 38861949
So it appears my original question is not possible without doing SQL tricks, which we finally did to achieve our goal.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38862293
What "tricks" did you use?

mlmcc
0
 
LVL 1

Author Closing Comment

by:NCIT
ID: 38875500
There was no resolution.
0
 
LVL 1

Author Comment

by:NCIT
ID: 38881773
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38883120
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question