Grouping

[slimshades26]

CR8.5
I have the entire report grouped by Group#1 then I have two different fields that need to fall equally under that. So currently I have Group#2 (F4211.SDDOCO) and Group#3 (F4801.WADOCO). I have tried switching them around, carrying them to the same group but I either get too many or the first of a group or just not enough records. The following is an example 1 of Group#2 as WADOCO then example 2 is Group#2 as SDDOCO. I need it to read as the example 3. I can see all this in my detail section (Da & Db) but cannot sort it or group like I want. Please see below...

EX 1)

GP#1 U11 282
GP#2 99142 WO UNIMOTOR WO Jul 22, 02
GP#3 124512 SK FOURSEASONSDIVISION Jul 22, 02
GP#3 142537 SK FOURSEASONSDIVISION Aug 12, 02
GP#3 177615 SK FOURSEASONSDIVISION Sep 02, 02
GP#2 100689 WO UNIMOTOR WO Jul 22, 02
GP#3 126546 SG SMPBLUESTREAKCAN Jul 22, 02
GP#3 105007 WO UNIMOTOR WO Aug 12, 02
GP#3 124512 SK FOURSEASONSDIVISION Jul 22, 02
GP#3 142537 SK FOURSEASONSDIVISION Aug 12, 02
GP#3 177615 SK FOURSEASONSDIVISION Sep 02, 02
GP#2 114729 WO UNIMOTOR WO Sep 02, 02
GP#3 124512 SK FOURSEASONSDIVISION Jul 22, 02
GP#3 142537 SK FOURSEASONSDIVISION Aug 12, 02
GP#3 177615 SK FOURSEASONSDIVISION Sep 02, 02

EX 2)

GP#1 U11 282
GP#2 124512 SK FOURSEASONSDIVISION Jul 22, 02
GP#3 99142 WO UNIMOTOR WO Jul 22, 02
GP#3 105007 WO UNIMOTOR WO Aug 12, 02
GP#3 114729 WO UNIMOTOR WO Sep 02, 02
GP#2 126546 SG SMPBLUESTREAKCAN Jul 22, 02
GP#3 100689 WO UNIMOTOR WO Jul 22, 02
GP#2 142537 SK FOURSEASONSDIVISION Aug 12, 02
GP#3 99142 WO UNIMOTOR WO Jul 22, 02
GP#3 105007 WO UNIMOTOR WO Aug 12, 02
GP#3 114729 WO UNIMOTOR WO Sep 02, 02
GP#2 177615 SK FOURSEASONSDIVISION Sep 02, 02
GP#3 99142 WO UNIMOTOR WO Jul 22, 02
GP#3 105007 WO UNIMOTOR WO Aug 12, 02
GP#3 114729 WO UNIMOTOR WO Sep 02, 02

What I want is:

EX 3)

GP#1 U11 282
GP#? 99142 WO UNIMOTOR WO Jul 22, 02
GP#? 124512 SK FOURSEASONSDIVISION Jul 22, 02
GP#? 100689 WO UNIMOTOR WO Jul 22, 02
GP#? 126546 SG SMPBLUESTREAKCAN Jul 22, 02
GP#? 105007 WO UNIMOTOR WO Aug 12, 02
GP#? 142537 SK FOURSEASONSDIVISION Aug 12, 02
GP#? 114729 WO UNIMOTOR WO Sep 02, 02
GP#? 177615 SK FOURSEASONSDIVISION Sep 02, 02

Note: Data "UNIMOTOR WO" is a text field and "FOURSEASONS..." is a database field.

I realize this is long but I wanted to try to be clear. I hardly understand it myself. I will be happy to try to make more explanation. You guys are the wizards, work that magic... ss26

 

[Ngolem]

Sorry Slim but we need a bit more information...I am having trouble seeing a pattern.

99142 WO UNIMOTOR WO Jul 22, 02

name the fields associated with this row of data.

Also I do not see a pattern in your example 3. Please tell us with a couple of examples of data....especially on those grouped fields Jim Broadbent

 

[dgillz]

When you say you are getting too many, what does that mean? Is it returning multiple records when you only want one?

If so we can work around this by using running total fields and hiding the details section. Or sometomes subreports are a way around this. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[slimshades26]

EX 1)

GP#1 U11 282
GP#2 {F4801.WADOCO}99142 WO UNIMOTOR WO Jul 22, 02
GP#3 {F4211.SDDOCO}124512 SK FOURSEASONSDIVISION Jul 22, 02
GP#3 {F4211.SDDOCO}142537 SK FOURSEASONSDIVISION Aug 12, 02
GP#3 {F4211.SDDOCO}177615 SK FOURSEASONSDIVISION Sep 02, 02
GP#2 {F4801.WADOCO}100689 WO UNIMOTOR WO Jul 22, 02
GP#3 {F4211.SDDOCO}126546 SG SMPBLUESTREAKCAN Jul 22, 02
GP#3 {F4801.WADOCO}105007 WO UNIMOTOR WO Aug 12, 02
GP#3 {F4211.SDDOCO}124512 SK FOURSEASONSDIVISION Jul 22, 02
GP#3 {F4211.SDDOCO}142537 SK FOURSEASONSDIVISION Aug 12, 02
GP#3 {F4211.SDDOCO}177615 SK FOURSEASONSDIVISION Sep 02, 02
GP#2 {F4801.WADOCO}114729 WO UNIMOTOR WO Sep 02, 02
GP#3 {F4211.SDDOCO}124512 SK FOURSEASONSDIVISION Jul 22, 02
GP#3 {F4211.SDDOCO}142537 SK FOURSEASONSDIVISION Aug 12, 02
GP#3 {F4211.SDDOCO}177615 SK FOURSEASONSDIVISION Sep 02, 02


My detail for this grouping looks like...

U11 282
Da 99142 WO UNIMOTOR WO Jul 22, 02
Db {F4211.SDDOCO}124512 SK FOURSEASONSDIVISION Jul 22, 02
Da 99142 WO UNIMOTOR WO Jul 22, 02
Db {F4211.SDDOCO}142537 SK FOURSEASONSDIVISION Aug 12, 02
Da 99142 WO UNIMOTOR WO Jul 22, 02
Db {F4211.SDDOCO}177615 SK FOURSEASONSDIVISION Sep 02, 02
Da 100689 WO UNIMOTOR WO Jul 22, 02
Db {F4211.SDDOCO}126546 SG SMPBLUESTREAKCAN Jul 22, 02
Da 105007 WO UNIMOTOR WO Aug 12, 02
Db {F4211.SDDOCO}124512 SK FOURSEASONSDIVISION Jul 22, 02
Da 105007 WO UNIMOTOR WO Aug 12, 02
Db {F4211.SDDOCO}142537 SK FOURSEASONSDIVISION Aug 12, 02
Da 105007 WO UNIMOTOR WO Aug 12, 02
Db {F4211.SDDOCO}177615 SK FOURSEASONSDIVISION Sep 02, 02
Da 114729 WO UNIMOTOR WO Sep 02, 02
Db {F4211.SDDOCO}124512 SK FOURSEASONSDIVISION Jul 22, 02
Da 114729 WO UNIMOTOR WO Sep 02, 02
Db {F4211.SDDOCO}142537 SK FOURSEASONSDIVISION Aug 12, 02
Da 114729 WO UNIMOTOR WO Sep 02, 02
Db {F4211.SDDOCO}177615 SK FOURSEASONSDIVISION Sep 02, 02

The SK's are duplicated two times each from {F4211.SDDOCO} when in GP#3. When I switch these fields and move them to GP#2 (which puts {F4801.WADOCO} in GP#3) it duplicates those doco number.

{F4801.WADOCO} 99142
{F4801.WADCTO} WO
{TExT} UNIMOTOR WO
{@ReqDateWA} Jul 22, 02

{F4211.SDDOCO} 124512
{F4211.SDDCTO} SK or SG
{F0111.WWDC} FOURSEASONSDIVISION or SMPBLUESTREAKCAN
{@ReqDateSD} Jul 22, 02

Goldmine, How are running totals done on string fields? I've only worked with them when wanting number totals. I'm going to play with that while I wait for your response.

Jim, I tried again, Was that better?

Thanks for both your responses and questions. ss26

 

[Ngolem]

I think I see your problem...there really is no relationship between {F4211.SDDOCO} and {F4801.WADOCO}

What does a data record look like...for example is it

you show 2 tables here...are they linked together or by a third table

{TExT} how is this field created?

I don't think this is a grouping problem but rather an overall design problem. I see you wanting to dovetail to tables together somehow but I don't see the common link Jim Broadbent

 

[slimshades26]

I'm trying to recreate a UNIONed SQL from another application. I thought I could make Crystal do it without the union by grouping. I came very close so that's why I posted. I have not any idea how to begin to union the statements from the previous writers sql. I have messed with the SQL query in Crystal quite a bit but only in the FROM statement to LEFT JOIN or something. What about the visual linking? Won't it get in the way of the union?

Thanks for your responses. I'll keep plugging away at it. ss26

 

[Ngolem]

OK...let's step back a bit.

Some people here advocate adding a union to the query directly in Crystal but I am not one of those...doing this is pretty touchy.

However, a subreport feeding it's results to a main report sort of acts like a union query...also we can handle outter join situations better some times.

give me a printout of the union query that you use. Once I have it I probably will be in a better position to suggest how this can be don with subreports Jim Broadbent

 

[slimshades26]

Here is the SQL from the other application that uses Excel. He used this query then dumped into a blank excel sheet as a gathering place. He then used VB to move it around as desired on yet another sheet. I was able to do almost everything in crystal with one less file (did not use the F4102 as shown in SQL) without VB. So this query would be modified slightly but if I got it to work then I would deal with that later. I tried to LEFTJOIN some of the files but it return NO data. So here I am. Thanks for interest and help. I'll be back Monday.

/SSMPLEW01
SELECT
a.walitm as part,
substr (b.iblitm,6,3) as sort, <---*He used this as the primary sort (GP#1 in my example)
b.ibprp4 as planfam,
a.wadl01 as desc,
a.wadrqj as reqdate,
a.wadcto as vendor,
a.wadoco as order,
a.wadcto as doctype,
a.wauorg-a.wasoqs as qty,
a.warorn as related,
a.wasrst as custpo

FROM
JDFDTAFS/F4801 AS A INNER JOIN
JDFDTAFS/F4102 AS B
ON
a.waitm = b.ibitm

WHERE
A.WAMCU = ' 22101'
and A.WADRQJ between 101001 and 102335
AND A.WASRST < '95'
AND b.IBGLPT = 'FG'
and b.ibprp4 like 'U%'
AND B.IBMCU = ' 22101'
and substr (b.iblitm,6,3) between '001' and '999'
and b.iblitm not like '528%'

UNION
/SSMPLEW01
SELECT
a.sdlitm as part,
substr (a.sdlitm,6,3) as sort,
c.IBPRP4 as planfam,
a.sddsc1 as desc,
a.sddrqj as reqdate,
substr(b.wwdc,1,12) as vendor,
a.sddoco as order,
a.sddcto as doctype,
a.sduorg as qty,
substr(a.sdrorn,4,5) as related,
a.sdvr01 as custpo

FROM
jdfdtafs/f4211 as a
inner join jdfdtafs/f0111 as b
ON a.sdan8 = b.wwan8
inner join jdfdtafs/f4102 as c
ON a.sditm=c.ibitm

WHERE
a.sdmcu = ' 22101'
and a.sddrqj between 101001 and 102335
and c.ibprp4 like 'U%'
AND c.IBMCU = ' 22101'
and a.sduorg > 0
and substr (c.iblitm,6,3) between '001' and '999'
and a.sdnxtr < '580'
and b.wwidln = 0
and c.iblitm not like '528%'
ORDER BY 3,2,5,1

&quot;Almost only counts in hand grenades and horse shoes.&quot;

ss26

 

[Ngolem]

ok...now I see the problem better.

this field...ibitm &quot;sort&quot;...is noted as a Primary sort field in your query....but the Order By places it as a secondary sort...after ibprp4 &quot;planfam&quot;

Is it really how you want the information sorted

If this is true I think we have a shot at this

I would construct the report this way

1. the report would consist of a main report Plus TWO (2) subreports.
2. the main report would be based on the table jdfdtafs/f4102
which you have currently left out.
3. the two subreports would be based on :
Subreport 1 : JDFDTAFS/F4801
Subreport 2 :jdfdtafs/f4211 and jdfdtafs/f0111
4. the links between the main report and both subreports
will be main report field&quot;ibitm&quot; to their
respective fields in the subreports

5. the set up for the main report is:

Record select formula:

{JDFDTAFS/F4801.ibprp4} like 'U*' and
{JDFDTAFS/F4801.IBMCU} = ' 22101'

//I think &quot;substr ({JDFDTAFS/F4801.iblitm},6,3)&quot; can be
//into an SQL expression which can be passed down to
//the server

<sql expression of substr> in ('001' to '999') and
not({JDFDTAFS/F4801.iblitm) like '528*')

6. create your Groups 1 and 2. In the query as mentioned
above is actually sorted by ibprp4 &quot;planfam&quot;...then
ibitm &quot;sort&quot;...

Depending on what you want create 2 groups based on these
fields.

7. now in the detail section create 3 subsections

Detail A - contains the subreport objects
Detail B - contains data returned by Subreport 1
Detail C - contains data returned by subreport 2

Subreport 1

1. based on table: JDFDTAFS/F4801
2. linked to main report {JDFDTAFS/F4102.iblitm) to
subreport {JDFDTAFS/F4801.waitm}.

Also there can be a link between main report
{JDFDTAFS/F4102.IBMCU) to subreport
{JDFDTAFS/F4801.WAMCU}.

When the links are made the record selection formula
of the subreport will be updated.

3. to this record select add the following

<link addition> and
//the brackets below may be square brackets...
//can't remember
{JDFDTAFS/F4801.WADRQJ} in (101001 to 102335) and
{JDFDTAFS/F4801.WASRST} < '95'

now select the data needed in the main report and assign to shared variables for transport back to the main report. Have the shared variables assigned to NULL so that we can suppress the detail section if nothing is returned

in each formula in the main report we must check this condition and if not true assign the result as null as well
to prevent the data from showing

Create Subreport 2 in a similar manner

I am going to stop at this point...since there is still a fair bit of work to do and I don't know if this is what you want...but I think this will work fine...it may be a tad slow but will depend on the amount of data you will have to process since there are a fair number of subreport calls.

let me know if you like it and if you have any questions

We still have to &quot;hide&quot; the subreports and deal with the shared variables but if you are fine with these then you may need no more help...Have a good weekend!







Jim Broadbent

 

[slimshades26]

Ok, I'm looking pretty good here. What I don't understand is the a) the shared variable thing b) why I have areas that are blank. I was finally able to group it right when I put them all in GRP#1 header. How would I use many fields from each subreport in the main report? It works now, except for blank sections, so I don't have to mess with it too much.

Grp#1a U11 139
Grp#1b Date Customer CustPO WO or SO#
Grp#1c (blank)
Grp#1d (blank)
Grp#1a U11 282
Grp#1b Date Customer CustPO WO or SO#
Grp#1c Jul 22, 02 UNIMOTOR WO 99142
Grp#1c Jul 22, 02 UNIMOTOR WO 100689
Grp#1d Jul 22, 02 FOURSEASONSDIVISION 28692 124512
Grp#1d Jul 22, 02 SMPBLUESTREAKCAN 0 126546
Grp#1d Aug 12, 02 FOURSEASONSDIVISION 30254 142537
Grp#1d Sep 02, 02 FOURSEASONSDIVISION 32820 177615
ss26

 

[Ngolem]

so you are telling me that the main report works....it is the data from the subreports that doesn't yet work??

OK...you are displaying data in Group footers or headers??

It looks to me that you are doing everything in the headers for the group(s)

You have not followed my layout so I don't know what you are doing... Jim Broadbent

 

[slimshades26]

Yes, I have data in my main report from my subs. I put the subs back in the detail. I moved them to see if I could get them to eliminate blank sections. Other than that I have followed your suggestions. I can preview my subreports if I put a part number in the parm field. I can only see one record at a time. Do I group the subreport like I would a regular report? I have but wondered if I should put the data in detail and leave it alone. How do I use shared variables? I have read a bunch of post on it but don't understand how that will work. Does that pull all my sub fields to the main report in a different way? I can see everything I want to see it's just that I'm seeing too much and need blank sections deleted as you mentioned earlier SV would do.
Thanks so much for help on this. ss26

 

[Ngolem]

hmmm...I don't understand your designation Grp#1a,b,c,d

What sections are these ? headers, foters or details...it is not clear to me Jim Broadbent

 

[slimshades26]

GrpHdr#1 Was where I put the grouping of PRP4 then GrpHdr#2 was for the column headers, GrpHdr#3 & #4 were subreports. I put the subs back in the detail. Linked subs to main on ITM as suggested. I still have Detail A suppressed. Here is how my data outputs.

Date Customer CustPO WO/SO# TY Part# 401
GrpHdr#1U11 395
Detailb Sep 16, 02 UNIMOTOR WO 117886 WO 40135395 300
Detailc Sep 16, 02 FOURSEASONSDIVISION 34292 200415 SK 40135395 400
Detailc Sep 16, 02 FOURSEASONSDIVISION 34034 197184 SK 40135395 300
Detailb Sep 16, 02 UNIMOTOR WO 117886 WO 40135395 300
Detailc Sep 16, 02 FOURSEASONSDIVISION 34292 200415 SK 40135395 400
Detailc Sep 16, 02 FOURSEASONSDIVISION 34034 197184 SK 40135395 300
Detailb Sep 16, 02 UNIMOTOR WO 117886 WO 40135395 300
Detailc Sep 16, 02 FOURSEASONSDIVISION 34292 200415 SK 40135395 400
Detailc Sep 16, 02 FOURSEASONSDIVISION 34034 197184 SK 40135395 300
Detailb BLANK
Detailc BLANK
Detailb Sep 16, 02 UNIMOTOR WO 118538 WO 49035395
Detailc Sep 16, 02 SMPBLUESTREAKCAN 0 198316 SG 49035395

I believe it should read without the dups of 34292/34034 and 197184/200415 as below....

Date Customer CustPO WO/SO# TY Part# 401
GrpHdr#1U11 395
Detailb Sep 16, 02 UNIMOTOR WO 117886 WO 40135395 300
Detailc Sep 16, 02 FOURSEASONSDIVISION 34292 200415 SK 40135395 400
Detailc Sep 16, 02 FOURSEASONSDIVISION 34034 197184 SK 40135395 300
Detailb Sep 16, 02 UNIMOTOR WO 118538 WO 49035395
Detailc Sep 16, 02 SMPBLUESTREAKCAN 0 198316 SG 49035395


Thanks so much...
ss26

 

[slimshades26]

The report is finally complete. Thanks for your help. You went beyond what I would have expected in responses. Thanks again. ss26