Group and select one field but group & print another field 1

[dellguy]

Crystal Reports 2008 Oracle 10g

Hi all. Need some help with this one. I have a table with records:

FLD-1 FLD-2 FLD-3
A A 01-JAN-2011
A B 10-JAN-2011
B C 02-JAN-2011
C C 03-JAN-2011
D C 12-JAN-2011

I need to group/break by FLD-2 but only include the newest record for FLD-1 based on FLD-3

The above example shows 2 records for FLD-1 A. I only want to see the FLD-1 A record for FLD-2 B because it has a newer date in FLD-3.

Somehow need to group on FLD-1 but keep FLD-2 as the major grouping. At the moment, I am thinking command file to do the FLD-1 grouping but prefer not to. Subreport the only other option?

Ideas?

 

[dellguy]

I wished tabs worked properly here

FLD-1 FLD-2 FLD-3
A A 01-JAN-2011
A B 10-JAN-2011
B C 02-JAN-2011
C C 03-JAN-2011
D C 12-JAN-2011

 

[fisheromacse]

Maybe...

Group on FLD-1, sort descending on FLD-3 and place your fields in the group header, suppress the details section.
It should show the newest date and fields associated with it.

 

[dellguy]

fisheromacse, thanks for your reply.

The report output must show grouping based on FLD-2 as the major group. So if the major group is FLD-2, minor group is FLD-1, the 2 records with FLD-1 A will both show under the 2 separate groups for FLD-2.

I need only the record with FLD-1 A (FLD-3 10-JAN-2011) to show under group FLD-2 B.

GH1 FLD-2=A will not show at all
GF1 FLD-2=A will not show at all

GH1 FLD-2=B
D will show FLD-1 A FLD-3 10-JAN-2011
GF1 FLD-2=B count 1

GH1 FLD-2=C
D will show FLD-1 B FLD-3 02-JAN-2011
D will show FLD-1 C FLD-3 03-JAN-2011
D will show FLD-1 D FLD-3 12-JAN-2011
GF1 FLD-2=C count 3

So of the 5 records processed, only 4 will appear on detail lines

 

[fisheromacse]

ok....i am a little confused, and not in front of crystal to test anything.
Bear with me, while i sort through this....If you create the {@Next1} formula below and put it in the details section, then the {@resetNext1} in the Group Header. Is that any closer?

//{@next1}
datevar f3;
not(onlastrecord);
IF {table.FLD-1} = next({table.FLD-1})
THEN
(IF {table.FLD-3} < next({table.FLD-3})
THEN f3 := next({table.FLD-3})
ELSE f3 := {table.FLD-3})
ELSE f3 := {table.FLD-3});
f3

//{@resetnext1}
datevar f3 := "";

 

[dellguy]

I realize explaining this situation is a little confusing, sorry about that.

The core problem here is to summarize the records by FLD-1, the report group 1 must be set to FLD-1. I think your logic assumes that. However, the report output needs to be grouped by FLD-2 so your logic wouldn't find duplicates because the input query would be sorted by FLD-2.

)

 

[fisheromacse]

the formulas i just posted should work even if you group on FLD-2. The formula will simply refer to the previous record without regard to group. I may be overthinking this and all you really would need is a suppression formula, but.........without i would need to get in front of crystal myself and test it out.

 

[dellguy]

I changed the value for FLD-1 on a couple records to make it clearer.

If I grouped on FLD-2, the duplicate FLD-1 A records would not be in sequence and the IF {table.FLD-1} = next({table.FLD-1}) would never be true.

FLD-1 FLD-2 FLD-3
A A 01-JAN-2011
D B 10-JAN-2011
B C 02-JAN-2011
C C 03-JAN-2011
A C 12-JAN-2011

 

[lbass]

Create a SQL expression {%maxdt} like this:

(
select max(A.`FLD-3`)
from table A
where A.`FLD-1` = table.`FLD-1`
)

In report->selection formula->record, enter:

{table.FLD-3} = {%maxdt}

Now you can simply group on {table.FLD-2} and proceed.

-LB

 

[dellguy]

Hi LB. I fiddled around with your suggestion a bit today and think I can make it work but got tied up with something else today. I really appreciate your help whenever I post.

Thanks to fisheromacse too !!

Cheers!! )

 

[dellguy]

OK LB, this worked great !!

However, I need to limit the select in the {%maxdt}
SQL expression to FROM DATE and TO DATE parameters used for the overall report. How do I do that?

Thanks!!

 

[lbass]

Unfortunately, you cannot use parameters in a SQL expression. You could do something like this in a command where you create the parameters in the command. It is best if you set up the command to act as the datasource for the entire report instead of linking it to tables. Is this a route you would consider taking?

-LB

 

[dellguy]

That would be a huge design change to the report. I looked at using a subreport as well but that won't work either. I think I'm going to see if the end user will accept a different grouping (one level less). That will have to do. I've spent too much time on this already.

Thx LB

 

[lbass]

You could try setting up the equivalent of the SQL expression in a command and then link it to the main report--it will be slower, because the link will have to be processed locally, but it still may meet your needs. Go to database->add command and create the parameters in the screen at the right. Then create the command like this:

select max("table"."fld-3") "maxdt", "table"."fld-1"
from "table"
where "table"."fld-3" >= {?Start} and
"table"."fld-3" < {?End}+1
group by "table"."fld-1"

Link the command to the main table using an equal join and linking {table.date} to {command.maxdt} and
{table.fld-1} to {command.fld-1}.

-LB