Corresponding Data Not Appearing with Specified Order Groups

[DMunoz]

On Jan 19, 2001 I wrote and received the following, however I have an additional question at the bottom.

*******************************************************
I have a Database Field (which are Regions) in Group Header #1 with values appearing as:

Administrators;West Coast
Consulting
Midwest
Midwest;West Coast
Northeast;West Coast
Administrators;West Coast
West Coast

I need to be able to separate the Regions, which I can do, (sort of like pulling First & Last names) but then merge (Group) all Administrators together, Midwests together, West Coasts together and so on.

I've tried the Select Expert and only listed the individual Regions (Midwest, West Coast, ect.) however, then the "Administrators;West Coast" or the "Midwest;West Coast" and so on doesn't get pulled.

I've tried declaring the separated results and then adding the values together, but then I'm back to square one.

So the question is, how can I separate the data and Group them?

I'm new at formulas, so if you can, be "gentle"!!!

Thanks in advance!



***********************************************************
dcm (Instructor) Jan 20, 2001
DMunoz: The solution to your problem is to use the Specified Order option when grouping your Regions. This will allow you to create for example 2 Named Groups, one Administrators and one MidWest. The process of creating a Named Group will then ask you to define how a record gets included in the Named Group - you would use for example Region startswith Administrator or Region like *MidWest* etc.

David C. Monks

*******************************************************
kenhamady (Instructor) Jan 20, 2001
Unless you are trying to get one record into two groups at the same time. Specified order won't do that.

Take, for instance, the record with the value:

Midwest;West Coast

is that supposed to show up in two different groups?

Ken Hamady

*******************************************************

dcm (Instructor) Jan 20, 2001
As you say Ken, specified order alone won't create a solution for 1 record occupying 2 groups as in Midwest;West Coast. I would investigate the use of embedded subreports liked by a formula field in each (main and sub) which take respectively the first argument and the second i.e. formula in main is left(Region,instr(Region,";&quot-1) and formula in sub is mid(Region,instr(Region,";&quot+1). This would have the effect of linking West Coast (on its own) to any entry in the sub with West Coast as a second argument.

You could then use shared variables to obtain the info you need out of the subreport or display relevant fields whatever you require.

David C. Monks

******************************************************
************** NEW QUESTION FOR THE ABOVE ************
******************************************************

Now my situation is that there are Company Names that go with the Regions such as:

Administrators;West Coast
ABC Company
Consulting
DEF Company
Midwest
GHI Company
Midwest;West Coast
JKL Company
Northeast;West Coast
MNO Company
Administrators;West Coast
PQR Company
West Coast
STU Company

Since I did the Group with Specified Order, I used the *Wildcard* to "separate" the Regions. I needed the Companies associated with the Regions to list multiple times with their corresponding Regions. Such as

Administrators:
ABC Company
PQR Company

West Coast:
ABC Company
JKL Company
MNO Company
PQR Company
STU Company

Midwest
GHI Company
JKL Company

However, what is happening is that the corresponding Company will only associate itself with the first Region, not again with the second (Administrators;WestCoast). I would like to know how to get the Companies to appear under both Regions if they are indeed shared by two Regions.

Again, thanks in advance for the great help!

 

[kenhamady]

You must have missed my first post.

Specified order can't make a record appear twice in the same report. If it qualifies for more than one group it falls into the first group.

The only way you could do this is to create a subreport for each group that finds all of the records for that group. The main report could be one group, while the others would be launched at the end as subreports.

I would put all of the subreports in the report footer (a subsection for each) and use differnt hard coded text formula fields to be the link to each sbreport. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal