Grouping By Formula With Summary Function

[dleewms]

I am using CR 10 with Oracle 8i. I have created a report of all my products (tubes) created within a certain date range. Each tube has a unique product_# identifier and I am first grouping by this field (product_#). I am looking at tubes with defects. One tube can have multiple defects and each defect has a unique Activity_# number. For each tube (group #1), I need to find the first defect and get all of the information associated with that defect. Ideally, I would like to group by the minimum({activity_#}, {product_#}). Since it is impossible to group on formula containing a summary field, due to the fact that it takes place during the second pass, is my ONLY OPTION to create a subreport to get the minimum activity_# per product and then link it to the main report? Any other alternatives would be greatly appreciated. Subreport is taking a long time to process.

Below is an example of my report as it is with just one group.

PH Product_# Defect Code Activity_# Defect_Date

GH1 222333444
D 3300025 923854777 12/15/2003
3300051 923854232 11/15/2003
3300012 923854989 01/15/2004


111222343 3200045 903354789 04/30/2003
3200036 903354456 03/30/2003
3200027 903354123 02/30/2003

Here is my desired report with two Groups.

PH Product_# Defect Code Activity_# Defect_Date

GH1 222333444
GH2 3300051 923854232 11/15/2003
GH1 111222343
GH2 3200027 903354123 02/30/2003

 

[lyanch]

You can do a couple of things, the first would be to add another group on defect date, sort ascending. Put all your "detail" fields in the GH and then suppress the detail.

The other option is to actually exclude the data with a subselect in the where portion of the SQL.

Either by create a SQL statement, or using add command add a subselect to the where that the activity date = min activity date for the product/defect code.

Lisa

 

[lbass]

Another approach would be:

If you group on Product# and then sort by defect date, ascending, you can drag the detail fields into the group header and suppress the details. This will display the minimum defect date.

Another approach would be to go to report->edit selection formula->GROUP and enter:

{table.defectdate} = minimum({table.defectdate},{table.product#}) //where {table.product#} is your group field

This will return only the detail with the minimum date.

-LB

 

[dleewms]

Thanks for the reponses Lisa & LB. I have tried the suggestions and think that the group selection formula may work best for me.
Thanks again,
DLee

 

[dleewms]

I have been given new instructions for my report. I now need to find the last defect applied to a tube before defect 9235854030 was applied to the tube. For example, here are all of the defects for product_# 222333444 defects along with the defect_Date

Defect Defect_Date Activity_#
9235854014 2/7/2004 249480999
9235854038 2/6/2004 249424943
9235854038 2/8/2004 249457048
9235854030 2/14/2004 250584328
9235854044 2/5/2004 249428971
9235854062 1/30/2004 248102725

I need to only display defect 9235854014 because it was the defect directly preceding (chronologically) defect 9235854030.

Is there a way to write a group selection formula to get just the one that I need. Again, any help would be greatly appreciated.
-DLee

 

[lbass]

It is unclear whether you mean the date when referring to the directly preceding record, or whether you mean the defect#, since by date, the preceding record would be 9235834038. Whichever field you mean, use that as the sort field, ascending. Then create a formula:

if {table.defect} = 9235854030 then
totext(previous({table.defect}),0,"")+" "+totext(previous({table.defectdate}),"MM/dd/yyyy")+" "+totext(previous({table.activity#}),0,"")

You should format the detail section to "suppress blank section."

Or as another alternative, you could go to the section expert->details->suppress->x+2 and enter:

next({table.defect}) <> 9235854030

-LB

 

[dleewms]

LB,

I was referring to the preceding record with respect to the date. You're right, the preceding record would be 9235834038. Sorry for the error and any confusion. I will test your suggestions first thing in the morning.

Thanks,
DLee