Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Group headers in crystal reports

Posted on 2013-06-21
8
Medium Priority
?
533 Views
Last Modified: 2013-06-24
I am writing a crystal report in version 10

The following is some example data

Product Code      Substr(product code ,6)
AAAAAA123      AAAAAA                  
AAAAAA123      AAAAAA
AAAAAA124      AAAAAA
AAAAAA124      AAAAAA
AAAAAA125      AAAAAA
BBBBBB123      BBBBBB
BBBBBB123      BBBBBB
BBBBBB123      BBBBBB
BBBBBB124      BBBBBB

If have taken the first 6 characters of the product code in column 2 above

Column 2 is my group header so my report looks like

      GH1      -      AAAAAA      
      Detail record
      Detail record

      GH1      -      BBBBBB
      Detail record
      Detail record


What I would like is this in my report is a new field which will contain all the items that are part of the product code substr.  This field will display on the GH1 record

      GH1      -      AAAAAA      (AAAAAA123, AAAAAA124, AAAAAA125)
      Detail record
      Detail record

      GH1      -      BBBBBB      (BBBBBB123, BBBBBB124)
      Detail record
      Detail record
0
Comment
Question by:MECR123
  • 4
  • 3
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 39266010
You can't get that in the group header except by using a subreport.
Crystal doesn't have the equivalent of the SUM for numbers when applied to strings.

mlmcc
0
 

Author Comment

by:MECR123
ID: 39266099
thanks
any idea how would I do it in a subreport ?
Mike
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 39266780
Build a report that just brings in the product code.

Create your formula for the 1st 6 characters

Create another formula in the subreport header
WhilePrintingRecords;
Shared StringVar Array ProductCodes;
GLobal NumberVar ArraySize := 0;
""

In the detail section
WhilePrintingRecords;
Shared StringVar Array ProductCodes;
GLobal NumberVar ArraySize;
ArraySize := ArraySize + 1;
ReDim Preserve ProductCodes[ArraySize];
ProductCodes[ArraySize] := {ProductCodeField};
""

In the main report header add a formula
WhilePrintingRecords;
Shared StringVar Array ProductCodes;
""

IN the group header
WhilePrintingRecords;
Shared StringVar Array ProductCodes;
"(" & Join(ProductCodes,", ") & ")"

The subreport has to be in a section by itself so add a new GH1 section and move it to the top

mlmcc
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 35

Expert Comment

by:James0628
ID: 39267848
You would also need to link your formula that produces the first 6 characters to the subreport, and then use that in the record selection formula in the subreport, so that it only includes those products.

 mlmcc's detail formula assumes that the subreport will only read each product code (AAAAAA123, AAAAAA124, etc.) once.  If the data could include the same product more than once, you'd need to add a check to the detail formula, so that the same code isn't added to the list more than once.

 Also, FWIW, since you're just trying to produce a simple list, you could do this with a simple string variable, instead of an array.  The detail formula would just append each new code to the end of the string, instead of adding it to the array.  That would be a bit "simpler", but, practically speaking, I don't think it makes much difference.  More of a personal preference than anything else.  I just wanted to mention the possibility.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39268257
He has it placed in the group header.  A formula like that in the main report would require it to be displayed in the group footer.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 39268651
If you're referring to the part about using a simple string variable instead of an array, that would still be in a subreport.  I was just saying that he could use formulas in a subreport, like the ones that you described, but append the values to a string, instead of save them in an array.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39268699
Sure.  I just find the array easier to cleanup so you don't have the leading or trailing comma to deal with

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 39269617
There's that, but it depends on how you build the string.  You can use something like
if var = "" then {field} else var + ", " + {field}

 And without the array you don't need ArraySize (one less variable), and I've always been a bit concerned about the "cost" of doing ReDim Preserve with every record.  It may be insignificant.  It just seems like something that could be relatively "expensive" when it comes to memory or other "resource" usage (although, now that I think about it, in this context, it may not be much different than making a string variable longer by appending new values to it).

 James
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

877 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