Crystal Reports - Sale Order Header Formula

I'm not sure if you can do this as a header, since it would mean you have to evaluate each line. Those evaluations happen top to bottom, so you might end up with it being displayed in the footer.

Make sure to sort by Sales Orders, so all the 123s are together and all the 124s are together, etc.

Create 3 formulas:

// formula: Reset
WhilePrintingRecords;
global stringVar delimiter := "";
global stringVar output := "";
"" 

// formula: evaluate
WhilePrintingRecords;
global stringVar delimiter;
global stringVar output;
output := if {SO_Header.Sales_Order} <> previous({SO_Header.Sales_Order}) //this should prevent you from seeing 123, 123, 123 etc.
      then output + delimiter + {SO_Header.Sales_Order}
      else output;

delimiter := " | ";

"" 


// formula: print
WhilePrintingRecords;
global stringVar output;


If you put the "Reset" formula on the group header, (or in report header if you don't have any groups).

Put the "Evaluate" formula on the detail line,

Put "Print" on the group footer (or report footer).

To get the list in the header you would have to use a subreport that just compiles the list.

You could also do it through SQL stored procedure

mlmcc

It may be possible to get a list in the report header without using a subreport.  I've just been playing with an idea and it works for me, but I don't recall ever seeing this suggested here on EE before, so there may be a problem that I'm not seeing.  All I can say for sure is that it works in my test report in CR 10.

 The basic idea is to take advantage of the fact that CR processes reports in multiple passes.  Have it save the order numbers in a variable during the reading pass, and then output the numbers in the report header during the printing pass.

 If you want to try this, start by creating a new report header section and move it above the report header section where you want to see the list.  The new section is where the variable will first be declared.  It may not have to be before the other report header section (since the formulas are being evaluated in different passes).  That just seemed like a good idea, so that's what I did.

 Create a formula like the following (call it whatever you like) and put it in the new report header section (above the old report header).  This declares the variable.

WhileReadingRecords;
Global StringVar chklist;
""


 Create a formula like the following and put it in the detail section.  This builds the list.

WhileReadingRecords;
Global StringVar chklist;

if chklist = "" then
  chklist := "~" + CStr ({SO_Header.Sales_Order}, "#") + "~"
else
  if not ("~" + CStr ({SO_Header.Sales_Order}, "#") + "~" in chklist) then
    chklist := chklist + CStr ({SO_Header.Sales_Order}, "#") + "~";
""


 Create a formula like the following and put it in the old report header section.  This outputs the list.

WhilePrintingRecords;
Global StringVar chklist;
Local StringVar tmp;

if Length (chklist) > 2 then
  tmp := Mid (chklist, 2, Length (chklist) - 2)
else
  tmp := chklist;

"Sales Orders:  " + Join (Split (tmp, "~"), ", ")



 Like I said, it works for me, but I'm a bit surprised that I haven't seen something like that suggested here before, so maybe there's a catch that I'm not seeing.  Whether or not it works might also depend on your report structure, or version of CR.

 Basically, the formula that builds the list checks to see if the list is empty.
 If so, just put in the number (as a string), surrounded by "~"s.  "~" is used as a delimiter to separate the numbers.
 If not, add the new number (as a string), with a "~" after it (there will already be a "~" in front of it, at the end of the last number that was put in the list).

 When checking to see if a number is already in the list, I put "~"s around it.  That avoids false matches, like looking for 3100 and getting a match because 31000 is already in the list.  Instead, it's looking for ~3100~, so it won't match something like 31000.  This may not be a problem if the records are read in ascending order number order, but since I don't know what order they'll be in, I played it safe.


 Then the formula that outputs the list removes the first and last "~" from the list, splits the list at the "~"s that are in between the numbers, and rejoins it with ", " in between the numbers.

 The list will be in whatever order the records were read.  If it's not sorted and you would like it to be, the formula that outputs the list could sort it first.


 If anyone is wondering, I tried using an array instead of a string variable and that didn't work.  For some reason the values in the array were lost with each new record.  Each new value was put in the next slot, as it was supposed to be, but all of the previous slots were empty.  I wasn't using Redim at all in that formula.  It was very odd.  The only thing I can think of is that it had something to do with using an array during the "read" pass.  On the last record, I only had the value from that record, and that was the only value I got in the report header.

 Using normal variables seems to work fine.  I just had a problem with the array for some reason.

 James

I will try these next week and get back to you.  Thanks for your help!

-----------------The second formula:----------------------
 Create a formula like the following and put it in the detail section.  This builds the list.

WhileReadingRecords;
Global StringVar chklist;

if chklist = "" then
  chklist := "~" + CStr ({SO_Header.Sales_Order}, "#") + "~"
else
  if not ("~" + CStr ({SO_Header.Sales_Order}, "#") + "~" in chklist) then
    chklist := chklist + CStr ({SO_Header.Sales_Order}, "#") + "~";
""


----------Says is two many argruements on the line below at the #-------------

  chklist := "~" + CStr ({SO_Header.Sales_Order}, "#") + "~"

Maybe try without the "#" anywhere you are using CSTR

chklist := "~" + CStr ({SO_Header.Sales_Order}) + "~"

Sales_Order is probably already a string

chklist := "~" +  ({SO_Header.Sales_Order}) + "~"

mlmcc

Sorry for the delay.  I got pulled away for a few days but this seems to work perfectly.  Thank you!

You're welcome.  Glad I could help.

 James