Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

arrays and group name issue 1

Status
Not open for further replies.

trapset4

Programmer
Oct 21, 2008
11
I am using 2 arrays. 1 array captures the groupname(finish name). The other captures occurrences of the groupname. I then find the max of occurrences and use my counter to find the corresponding finish name. The counter works and I can capture the occurrences, but I cannot capture the finish name - it seems like it is always blank. Is this a proper way to do it?
This formula is on the finish group header. "finish" at the bottom is just an output test(which failed).
Any ideas?

WhilePrintingRecords;
Shared StringVar Array finish;
Shared NumberVar i; //other counter
If GroupName ({@Finish}) <> "" Then
(
i:= i + 1;
Redim Preserve finish;
finish = GroupName ({@Finish})
);
finish
 
Can you explain why you are doing this? How will the results be used? Maybe it would help to see your other array formula.

I'm wondering though whether you could achieve the same results by inserting a crosstab that uses {@finish} as the row field, and a count as the summary field.

-LB
 
Here is the array of occurrences (it is in details section)
WhilePrintingRecords;
Shared Numbervar i; // counter
Shared Numbervar array fc; //series species finish count
If GroupName ({@Finish})<>"" Then
(
Local NumberVar r:= {VM_ORDER_LINES_AC_HS.QTY_ORDERED}; //quantity ordered
Redim Preserve fc;
fc:= fc + r
)

We make and finish furniture. I need to see the top finish used for each species of each style for marketing. My grouping is style, species, finish. I put (unsuccessfully) each unique finish in the first array and then collect the occurrences in this one at the same element number. I then perform a maximum on this array. I run through a While...Do loop to count the elements to the max element and use the counter to get my finish name from the first array. But I can't tell if any of this works, because I can't populate the first array. The second array works just fine. I know nothing about Crosstabs but am willing to learn if needed here.
Thanks for your time.
Let me know if you need more info.
 
It might be simpler to use the group sort/topN feature. You could either order the finish groups based on the qty (descending) or display only the top 1 per species.

To activate the group sort feature, you must insert a summary, so if you mean to use a sum per finish group, then right click on the quantity field in the detail section and insert a sum at the species level. Then go to report->group sort->choose "all" or topN = 1, depending upon the desired display.

-LB
 
OK, I got the summary working - many thanks!
However there is a 2nd part that I stayed away from until I saw what I could do with the grouping.
My finish color is actually a 4 character code consisting of 2 colors (2 characters each). I have successfully parsed them into the 2 codes and used a subreport with shared variables to translate the codes into the 2 color names. But, how do I capture the group name(4 character code) that is the Top 1 of the group? I only seem to be able to capture the quantity as the sum.

Thanks again, I think I am much closer to the target.
 
I figured out how to capture the finish group name - it wasn't hard once I knew what I was looking at. But, I am having trouble sending the code to the subreport to find the name ( I have done this as parameters, but not as shared variables before). Is there a trick with variables between reports I should know about?
 
Thought I would include more info. I break the code into its 2 parts successfully and store as Shared variables MaxFinish1 and MaxFinish2. In the subreport( placed in a second Species Group line) I have this code in the details section where C1, C2, and Colors are defined as "" in the subreport header:

WhilePrintingRecords;
Shared StringVar Colors;
Shared StringVar MaxFinish1;
Shared StringVar MaxFinish2;
Shared StringVar C1; //Color name 1
Shared StringVar C2; //Color Name 2
If Trim({CONFIG_OPTIONS.PART_FRAG}) = MaxFinish1 Then
C1:= Trim({CONFIG_OPTIONS.DESC_FRAG});
If Trim({CONFIG_OPTIONS.PART_FRAG}) = MaxFinish2 Then
C2:= Trim({CONFIG_OPTIONS.DESC_FRAG});
If C1 = "" Then Colors:= C2
Else If C2 = "" Then Colors:= C1
Else Colors:= C1 & ", " & C2;
""

I then pass Colors back to the main report to list with the species.
 
Nevermind. I revamped a some stuff and now it works. Thank you for your time on Summary issue.
 
Still having trouble!! The summary works great. BUT, my hierarchy is series->species->finish. I need:
top 3 finishes for each species. This works, thanks lbass.
total for each species within series.
total for each series. This works because it is just a total.

Report should look like:
Qty SeriesA QtySpeciesA QtySpeciesB QtySpeciesC etc.
finish1 finish1 finish1
finish2 finish2 finish2

Qty SeriesB QtySpeciesA QtySpeciesB QtySpeciesC
finish1 finish1 finish1

The problem is that the top 3 summary for finish seems to be driving my species totals as well. I need all records summed for species regardless if finish is in top 3.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top