Solved

Group headers in crystal reports

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

Accepted Solution

by:
mlmcc earned 500 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
 
LVL 34

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

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 34

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 100

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Builder on BOXIR2 7 54
Crystal Reports - Page Break by Line count 7 56
Formatting a Crystal Report 2 47
Crystal reports subreports as total of main report 13 52
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…
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…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 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

13 Experts available now in Live!

Get 1:1 Help Now