Need to print 'one' line on report, from multiple records 1

[yenesi]

I am using Crystal 8.5
I have a DB2 'view' (from 3 tables) that includes the following information:

Name Definition
Grp :Group #
ACB: Acct #
ACD :Tran date
GrpNme :Group Name
Tsk : Task #
TskDt :Task Completion Date

I want to build a report like:

Grp ACB ACD GrpNme Task44 Task51 Task59.....
12345 1 4/1/2002 xyz 4/10/2002 5/1/2002
25212 1 4/2/2002 abc 5/3/2002 5/5/2002


there are six (6) tasks for each group that I need to select and print dates (whether or not the date is there).
Currently....I get six (6) print lines for each group #.

Any suggestions as to how (or if) this can be accomplished??

Below is a copy of my current code:

SELECT
GRB, ACB, GRM_OWN, ACD, TSK, TskDt
(CASE TSK WHEN 44 THEN TskDt ELSE NULL END) AS Task44,
(CASE TSK WHEN 51 THEN TskDt ELSE NULL END) AS Task51,
(CASE TSK WHEN 59 THEN TskDt ELSE NULL END) AS Task59,
(CASE TSK WHEN 60 THEN TskDt ELSE NULL END) AS Task60,
(CASE TSK WHEN 61 THEN TskDt ELSE NULL END) AS Task61
FROM
PROD.table
WHERE
(GRB NOT IN (297020, 265250, 285871, 294403) AND ACC_TRSN_STAT = '1' (active status)
AND Tsk = 44 OR
Tsk = 51 OR
Tsk = 59 OR
Tsk = 60 OR
Tsk = 61) AND
GRB IN
(SELECT GRB
FROM PROD.table
WHERE
(Tsk = 62 AND TskDt IS NULL))
ORDER BY
"GRB" ASC,
"ACB" ASC,
"ACD_TRSN" ASC



any suggestions greatly appreciated....I'm going crazy!!!

 

[IdoMillet]

Nothing wrong with going crazy; I've enjoyed life much more ever since it happened to me... )

Before attempting anything complex, please try to create a CrossTab with TASK # as columns, everything else as Rows,
and the Maximum or Minimum of TskDt as the summary.

hth,
- Ido
CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[yenesi]

Thanks Ido, but that didn't do the trick for me, Dang! ......I had tried crosstab once B/4 .....using 'Count' on the Group # for my summary, and that didn't do it either.

Any other suggestions??


Perhaps I should take your suggestion about going crazy and actually enjoy this!

 

[kenhamady]

I would use cross-tab with a Maximum on the date field.
Row is group#
Column is Task#

However, you can't easily have multiple columns at the beginning of each row (unless you concatenate these fields into one big 'row' field).

I would use a manual cross-tab as described in faq149-243 . Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[yenesi]

Ido......way too many columns. Using either min or max, creates a column for 'every'date under each task. Each group can have different date/task.....ie; if group 123456
has a TskDt 1/1/2002 for Task44, and group 789123 has a TskDt 5/1/2002 for Task44 that creates two separate columns under Task44 on the report...?? Makeing the columns under each Task...pretty much, unlimited.
Am I explaining myself ok?
I need just one column under each task, showing the completion date for that task within that group.
Unless I am missing something, that would make my report much to 'wide'!! Thanks anyway...

HTIEKF.....I tried that, it might work for me. However, the 'extra lines (rows)' are still there whether I suppress them or not. If I can find a way to delete those detail lines and keep the footer, that might do it.

I am trying to find out if I can get my DB people to create me another 'view' combining all six (6) records (all the tasks) into one record.....

Again....if you have more suggestions, I am willing to try them....Thanks for all your help!!! It is much appreciated!!! Whatever happens....I'm learning either way!

 

[kenhamady]

The detail lines shouldn't be visible if they are suppressed. Right click on the word 'details' in the left margin and select 'suppress'. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[IdoMillet]

If group 123456 has a TskDt 1/1/2002 for Task44,
and group 789123 has a TskDt 5/1/2002 for Task44 that will NOT creates two column. It will result in a SINGLE column for Task44 with the dates under it.

Note that the MAXIMUM or MINIMUM summary operation aims at simply returning thos dates. We are just "fooling" the CrossTab to believing it's conducting a summary operation even though each GROUP x TASK intersection has (at most) one date.

hth,
- Ido

CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[yenesi]

Ido, I must have done something wrong then, as I ended up with multiple columns.....

However, I did get the results I wanted (using HTIEKF and Ken's suggestions) moving 'all' the information (group, acct, groupname, Task# (as functions)) to the Group Footer line and suppressing the Details.
Incidentally....Ken, I stumbled upon that just prior to getting your response!! I'm learning!

Thanks to all of you for your help!!! I certainly appreciate it!!! This is one great place to find help!!

 

[IdoMillet]

You probably included the date column in the CrossTab columns instead of just using it for the Summary operation.

Regardless, glad to hear you got it working using the
Manual CrossTab approach.

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm