Grouping Data based on Formul

[lauriebizz]

Good Morning. I'm running Crystal 9 on SQL.

I've created a basic report detailing item and last ship date. I've got a formula to pull the maximum ship date and need to create a formula to group based on the following criteria.
>180 days
121-180
91-120
61-90
0-60

When I try to group on the maxium ship date, I am not able to select the formula.

My data currently looks like this:

A132001 02/13/2006
A132003 02/12/2005

I'd like the results to look like this:

0-60
A132001 02/13/2006

>180
A132003 02/12/2005

Thanks-
Laurie

 

[synapsevampire]

If you refer to a formula that isn't doing what you want, post the formula.

If you're trying to GROUP on a maximum ship date, then you don't understand what a group is.

A group contains ALL items in the recordset, a maximum date is a subset of those records.

I don't think that a maximum should be involved here at all, group by the item, and use your formula to create the breakdown of the dates, and in the Report->Selection Formulas->Group place:

{table.date} = maximum({table.date},{table.item})

This assumes that you only want the maximum for each item, but it's hard to say because I suspect that you're showing some maximum dates in your examples instead of an example of all of the dates.

Example data shoudl show what's in the data being returned, not after you've further processed it.

-k

 

[lauriebizz]

Thanks - Your assumptions were correct. I appreciate the response and your suggestion worked perfectly.

Laurie

 

[lbass]

You can't create the grouping you want using group selection, but you could accomplish this by using a command to return the maximum per group. Let me know if you want more help with this.

-LB

 

[lauriebizz]

lbass,
Just for my edification, if you have the time, can you explain further?
Thanks-
Laurie

 

[lbass]

If you go to database->database expert->your datasource->add command, you would enter something like the following where {table.ID} is the field you want the maximum ship date for:

Select max(table.`ship date`) as maxdate, table.`ID`
From `table`table
Group by table.`ID`

Link the command to your main table on both the ID field and by linking maxdate to your main table ship date.

Select the links ->link options and make sure that "Enforce both" is checked.

This should return ONLY one record to the main report so that you can now create a formula like this:

if datediff("d",table.shipdate, currentdate) > 180 then ">180" else
if datediff ("d",table.shipdate, currentdate) > 90 then ">90 to 180" else //etc.

You could then insert a group on this and make it your highest order group.

-LB

 

[lauriebizz]

So far it looks great. Is it supposed to be so slow?
Here's the SQL Query:

Select max(coitem.ship_date) as maxdate, item.item
From coitem,item
Group by item.item

Thanks-
Laurie

 

[Turkbear]

Hi,
You have created a cartesian join ( a bad thing), since you fail to include any join information..

Instead do:
Select max(coitem.ship_date) as maxdate, item.item
From coitem,item
where coitem.field1 = item.field1 //Or Whatever.
Group by item.item




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lauriebizz]

Thanks so much - that worked great!

Nice to learn something new.

Thanks-
Laurie