Solved

Crystal Reports repeat page n times per value

Posted on 2013-01-22
9
2,234 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
Comment Utility
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 22

Expert Comment

by:Ido Millet
Comment Utility
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
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Accepted Solution

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

Expert Comment

by:mlmcc
Comment Utility
What "tricks" did you use?

mlmcc
0
 
LVL 1

Author Closing Comment

by:NCIT
Comment Utility
There was no resolution.
0
 
LVL 1

Author Comment

by:NCIT
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now