Solved

Group headers in crystal reports

Posted on 2013-06-21
8
510 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 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
thanks
any idea how would I do it in a subreport ?
Mike
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
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
 
LVL 34

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

763 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

7 Experts available now in Live!

Get 1:1 Help Now