Problem in Record/Group Selection

[ssingh20]

Hello,

I have CR 10 Professional and Oracle 8i database.

I have to prepare a report given the following data:

Case Number Entry Time Queue Name
101 1/5/2005 Finance
101 2/5/2005 HR
101 3/5/2005 Software
102 1/5/2005 Software
102 3/5/2005 Finance
102 4/5/2005 HR
103 1/5/2005 Finance
103 2/5/2005 Software

The Primary Key is an Internal Record Number.

Now, I have to find the last Queue in which each case is and of course display the results grouping the data by queue.

Thus, Expected Output is:

(Group) Queue Num of Cases
Finance 0
Software 2
HR 1

If I group by the Case Number to get a single row for each of the Case Numbers, then I can’t group by Queue Name.

I would appreciate any help available.
Thank you,
ssingh20

 

[lbass]

Try using "Add Command" as your datasource and build in an expression, as in:

Select
Table."CaseNo",Table."QueueName",Table."EntryTime",
(Select max(A."EntryTime") from Table A where
A."CaseNo" = Table."CaseNo")
From
"Table" Table
Where
Table."EntryTime" = (Select max(A."EntryTime") from Table A where A."CaseNo" = Table."CaseNo")

This will return just the most recent Queue Name per Case Number. You can then group on Queue Name and insert summaries. However, note that in your example, for finance, you would not get a 0, since there would be no records to group. If you need to show all Queue Names, you could use running totals, one for each queue name, where you use an evaluation formula like: {table.QueueName} = "Software".

If you don't want to try the Add Command method, you could insert a group on case number and then go to report->selection formula->GROUP and enter:

{table.EntryTime} = maximum({table.entryTime},{table.CaseNo})

Then use running totals where you use an evaluation formula like I mentioned earlier.

-LB

 

[BoniR]

Hello...
Try grouping by Case Number and then use the "Max" function from the formula expert to create a formula that will pull the latest date in the group...

Maximum({table.date})

pull the fields you need into the group footer and suppress the details...
add the queue name field as well to this group footer...

Hope this helps!
Boni



Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269

 

[BoniR]

Sorry,
try lbass first...he is a more experienced crystal developer...
B

Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269