Grouping groups?

[wesleycrusher]

I am putting together a report of equipment information. I have it grouped by company first, then model name, etc. The details section contains the model numbers. However, some model names share the same model numbers, i.e. have all of the same model numbers in common. I want to list all of the model names together (separated by commas) that share this same exact list of model numbers. Any ideas on how to do that?

Right now, I have no formula instituted that will find which model numbers are associated with which model names because the grouping has taken care of that up until now. I guess this would be the best way to do it?

 

[bdreed35]

Can you post an example of what you expect your results to look like?

~Brian

 

[wesleycrusher]

I wish it to look something like this (GM is the company; model name is Chevy, GMC, Oldsmobile; and the rest would be model numbers):

GM
Chevy, GMC, Oldsmobile
SUVF**A1
SUVF**B2
SUV**A3
SUV**B4

Instead of:

GM
Chevy
SUVF**A1
SUVF**B2
SUV**A3
SUV**B4
GMC
SUVF**A1
SUVF**B2
SUV**A3
SUV**B4
Oldsmobile
SUVF**A1
SUVF**B2
SUV**A3
SUV**B4

 

[dgillz]

Create a formula:

If {Model} in ["Chevy","GMC","Oldsmobile"] then "Chevy, GMC, Oldsmobile" else
If {Model} in ["Form","Mercury","Lincoln"] then "Form, Mercury, Lincoln"

Group on this formula instead of the actual {model} field.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[wesleycrusher]

I don't think I can really do that because our DB can be changed by members via the web. They may add new model names for the same model numbers or vice versa. Also, the model numbers are not always grouped as clear cut as this example. I need to introduce some sort of formula for dynamic selection. Maybe a method to select the model names associated with each model number? These two fields are in different tables.

 

[Ngolem]

I suppose that I must ask the question....in your example do Chevy, GMC, Oldsmobile have to have the same model numbers....seems odd that they would to me. If not...How would you know what belonged to what??

Jim Broadbent

 

[wesleycrusher]

Say GM produces a thousand widgets each with a unique model number. However, they sell some under Chevy, some under GMC, and some under Oldsmobile. There is some overlap, i.e. one widget can be sold under 1 of the three, 2 of the three, or all three. I want to somehow select those widgets that are listed under common model names (Chevy, GM, Oldsmobile) and combine them into one list rather than three. It's sort of a search that will group the widgets shared under common model names under one list. I can then list widgets under a single model name in a second list.

 

[FVTrainer]

What is your backend database? Does it support stored procedures?

 

[wesleycrusher]

I have no idea, I didn't create it.... just phoned someone... its a Postgrsql version 7 3. Hope I got that right.

 

[Gramm]

If your system does support stored procedures I would suggest exploring that avenue. The query could associate the correct widget with the product at the back end and those results would be passed to crystal reports. This is a much more efficient processing method as well.

 

[Ngolem]

**************
Say GM produces a thousand widgets each with a unique model number. However, they sell some under Chevy, some under GMC, and some under Oldsmobile. There is some overlap, i.e. one widget can be sold under 1 of the three, 2 of the three, or all three. I want to somehow select those widgets that are listed under common model names (Chevy, GM, Oldsmobile) and combine them into one list rather than three. It's sort of a search that will group the widgets shared under common model names under one list. I can then list widgets under a single model name in a second list.
***************

Well at least you have defined your problem better.

Your report counld look like this (a much better example to work from)

GM
Chevy, GMC, Oldsmobile
SUVF**A1
SUVF**B2
Chevy, Oldsmobile
SUV**A3
GMC
SUV**B4

Am I not correct??? Obviously perhaps not with these model numbers but something like that. If this is the case the report requires a bit of thought first



Jim Broadbent

 

[lbass]

Why not use a crosstab? It wouldn't give you exactly the display you're looking for, but it would give you the info in a simple, dynamic format. If you used model number as the row, and model name as the column and distinctcount of the model name as the summary, you could display:

Chevy Ford GM
g123 1 1 0
b456 0 1 1
c336 1 1 1

-LB

 

[kenhamady]

Why not put a single row cross-tab in the group header. That will give you a list of the models for the group. Just give it model as a row and summarized field, and maximum as the operation. Then you can suppress the lines and headers and you get pretty much what you want.

If you wanted to put the names in the FOOTER you could use a running total of text as described on the formulas page of my web site. Formula #16.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[wesleycrusher]

Thanks. I will try to get the cross-tab to work for me today. The problem is I am taking over the development of our product directory. It was outsourced and now it has become an in-house project. I am bound in what I am able to do with the design. Thus, I am unable to change the look. It has to match previous editions, so that is why I need the info grouped by model names separated by commas. I have never worked with crystal before and you all have been a great help. I will let you know how it goes today.

P.S. I don't work for GM, thta's just a basic example