How to show invoice number and multiple product numbers on same line 2

[hallux]

Created a report showing invoice number and product number. Problem is it creates a new line for each invoice number because there are multiple product numbers associated with each invoice number.

table invoice, field "inv_num"

table prod_sold, field "prod_num"


Report is something like this:

Invoice Number Product Number
001 989
001 608
002 700
003 899
003 900

How can I make it show as:

Invoice Number Product Number
001 989, 608
002 700
003 899, 900

Thanks,
-Brent

 

[IdoMillet]

In the detail section, use a formula to add the product number and a ", " to a string variable.

In the Invoice group footer use another formula to remove the last ", " and display the result.

In the Invoice group header use a formula to reset the string variable.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[hallux]

1. Having some trouble with the formula to add the product number and a "," to a string variable.

StringVar prods;
prods:= prods + {prod_num} + ","

Getting error:
"A string can be at most 254 characters long"

2. What should be the scope of the variable string I am using?

3. What do you use for debugging variables in Crystal? With other languages I would echo out the current value so I can see the progress.
Thanks,
-Brent

 

[IdoMillet]

Only Crystal 9 allows you to avoid the 254 character limit.
It also provides debugging functionaity...

The variable scope should be global.

Make sure yoy add
-----------------------
WhilePrintingRecords;
-----------------------
as the first line in these formulas.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Another option is to insert a linked "multi-column" subreport in the Group Footer...

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

There's a very annoying limit of 254 chars per formula output for CR version 8.5 or less, that's the problem, you probably coded correctly.

How many product numbers might you encounter in this scenario?

You can create more than one variable to allow for larger amounts of data, or you might approach it very differently.

Another option would be to:

Group by the Invoice Number.

Create a subreport (join by Invoice Number) with multiple columns and select print across then down.

This would create a standard width field to contain the Invioce Numbers, so it might have too rigid of a feel for your needs.

Anyway, you can place the Invoice Number and the subreport in the Group Header alongside each other, with proper margins set on the subreport to allow for formatting.

-k

 

[hallux]

From what I see of the data, I will constantly be hitting a wall with the 254 character limit. So, I will try the linked subreport with join by invoice number on Monday. That is a great idea IdoMillet and synapsevampire!

A lot of the reports I am modifying have subreports. One of the weird things I keep running into is inserting a subreport in the main report header and then referencing a shared variable created in the subreport. The subreport needs to be hidden or suppressed so it doesn't appear in the report, but then I find that the shared variable doesn't show in the footer of the main report.

Are there performance advantages to using subreports? Can they run parallel to the main report? Use a separate channel to the server?

Any suggestions or FAQs available for referencing subreport variables and/or linking in subreport fields?

BTW, I am grateful for all of your help. It sounds like I definitely need to upgrade from 8.5 to 9 to help in debugging.

 

[hallux]

Thanks for your help.
The subreport worked perfectly. Two things I had to do:
1. Check the "Can grow" box for object format.
2. Turn off “Line Style”, by setting all sides to none, to prevent a box from surrounding the text.

-Brent
kd7ict