Multiple records per customer - indormation on header

[yoniman]

I have the following records:-

Cust Rec Type Value
1 1 House
1 2 Gas
1 3 Heating
1 4 Boiler

What I want is one line per report that takes info from each record per customer e.g
Cust 1 House Gas Heating Boiler
Cust 2 House Elec Cooking Oven

Any help would be gratefully received

 

[lbass]

You could use the three-formula method, if you are willing to use the group footer instead:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar type := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar type := type + {table.typevalue} + " ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar type;

Then suppress the detail section and drag the groupname into the group footer.

Or, if you must display this in the group header, you could insert a crosstab, using {table.typevalue} as the column and for the summary field, use "NthMostFrequent of {table.typevalue}". You would need to format it to suppress row totals (customize style->suppress row totals), and also you would want to suppress the column headings and the total label, and eliminate the grid (customize style->format grid lines->uncheck show grid). You could then resize the label cells to minimize them.

The final option is to create a subreport linked on customerID that uses the three formula method above, and place this in the group header.

-LB

 

[yoniman]

Thanks. Couple of questions. I'm fairly simple in my use of Crystal so not sure where I put the //{@reset} command etc. Also if i use the string command the values are not 'columnised' I tried a formula @Property If Rec=1 then Property=Type Vale, @Method If Rec=2 then Method=Type etc and bringing these down to the footer but it sems that the value is being reset after each record as the footer with the formula values contains spaces but if I show it on the appropriate line it contains the correct value

 

[lbass]

I didn't realize you wanted the results aligned in columns. Are there always four records? Is there only one record per "Rec"? Is "Rec" a database field or is it the special field "recordnumber"?

If you grouped on {table.customer}, you could use your formula method:

//{@Property} to be placed in the detail section:

if {table.rec} = 1 then {table.typevalue} //repeat for other
//values of {table.rec}

Then you would insert a maximum on each formula and suppress the details.

-LB

 

[synapsevampire]

You might be better served to generate an array of the elements, and then have multiple formulas which display each array element to handle the columnar display desired.

//{@reset} to be placed in the group header:
whileprintingrecords;
numbervar counter := 0;
stringvar array type := ["","","","","",""];// uses 6

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar counter:=counter+1;
stringvar type[counter]:= {table.field};

//{@display} to be placed in the group footer:
// First element
whileprintingrecords;
stringvar array type[1]

// Second element
whileprintingrecords;
stringvar array type[2]

etc.

You may even want to sort the array.

-k

 

[yoniman]

Ive grouped by Customer Id. The Rec is a record ID so Cust is prime key, Rec Id is secondary key, so each customer has multiple records with different data on each record which i want to show on one print line. As i mentioned the formulas are appearing blank in the footer although if I show them on the detail line they show the correct data. What did you mean by maximum on each formula, and am I being thick, but what is \\{} around the formula name. Last thing is I'm on 8.5

Thanks for taking time out

 

[lbass]

After you create each formula and place it in the detail section, you need to right click on it and choose "Insert summary" and choose "maximum" in order to get the result in the group footer. You can then drag this maximum into the group header next to your group name and suppress the details. The formula by itself will not necessarily appear correctly in the footer (which would display only the last record before the footer) or the header (which would display only the first record after the header). The maximum will give you a record with the correct value.

In a formula, "//" is used to comment out whatever follows so that it is not read as part of the formula. I usually comment out a line preceding the formula which provides the formula name so that the reader doesn't mistake that for part of the (Crystal syntax) formula.

-LB

 

[yoniman]

Brilliant lbass Thanks so much. I owe you a beer