Grouping Question 1

[giggles7840]

xp/xir2

I have a set of records in the detail section and I have to group them asc so that the most recent record can be shown in the group header. The next step is grouping the records by Description.

The problem with that is my GH2 is by Road and that needs to remain. If I add a GH4 on Description then it takes the hidden Details into account and I do not want to see them.

I either need a way to prevent those extra detail records out of the report or a way to group just the records in GH3 without including the hidden details.

The records shown below are in GH3 and need to be grouped by Desc.

Division Road Desc Date Days
Pecan Crk EAST AVENUE Spray 07/06/2009 4
Pecan Crk EAST LANE Hang 07/06/2009 4
Pecan Crk EAST ROAD Optional 07/08/2009 2
Pecan Crk EAST LANE Cables 07/06/2009 4
Pecan Crk EAST AVENUE Drywall 07/06/2009 4
Pecan Crk EAST ROAD Deliver 07/06/2009 4
Pecan Crk EAST LANE Stem 07/06/2009 4
Pecan Crk EAST AVENUE Taping 07/06/2009 4
Pecan Crk EAST ROAD Paint 07/06/2009 4
Pecan Crk EAST LANE Trenchs 07/06/2009 4
Pecan Crk EAST AVENUE Pull Top 07/06/2009 4
Pecan Crk EAST LANE Trenchs 07/06/2009 4
Pecan Crk EAST AVENUE Hang 07/06/2009 4
Pecan Crk EAST ROAD Stem 07/06/2009 4
Pecan Crk NORTH DRIVE Cables 07/06/2009 4
Pecan Crk NORTH DRIVE Cables 07/06/2009 4
Pecan Crk NORTH DRIVE Plumbing 07/06/2009 4
Pecan Crk NORTH DRIVE Taping 07/06/2009 4
Pecan Crk NORTH DRIVE Soil Dig 07/06/2009 4
Pecan Crk NORTH DRIVE Stucco 07/07/2009 3

 

[lbass]

This is unclear. What is your group #1 field? What is your Group #3 field? Based on your data above, what results would you want to see?

-LB

 

[giggles7840]

I need the report to group as follows:
schedule
division
address
description

I probably shouldnt have take off the street number. i just realized i did that.

The data starts out looking like this:
GH1-Schedule 1
GH2-Pecan Crk
GH3-Division Road Desc Date Days
GH3-Pecan Crk 111 EAST LANE Cables 07/06/2009 4
D-Pecan Crk 111 EAST LANE Cables 07/06/2009 4
D-Pecan Crk 111 EAST LANE Trim 07/03/2009 3
D-Pecan Crk 111 EAST LANE Prep 07/02/2009 2

From here i supress the detail records since i no long care about them. Then when i go to group on description, that is where the problem arises because even though the details are hidden, the new group still takes them into consideration and redisplays them in the new group.

The end result should look something like this:
Division Road Desc Date Days
GH3-Pecan Crk 111 EAST LANE Cables 07/06/2009 4
GH3-Pecan Crk 165 NORTH DRIVE Cables 07/06/2009 6
GH3-Pecan Crk 325 NORTH DRIVE Cables 07/06/2009 3
GH3-Pecan Crk 222 EAST AVENUE Drywall 07/06/2009 4
GH3-Pecan Crk 124 EAST LANE Hang 07/06/2009 1
GH3-Pecan Crk 123 EAST AVENUE Spray 07/06/2009 4

Does this make more sense?

 

[lbass]

If you are saying that you want the Group #3 instances clustered by description, then do not group on description. Instead, you should eliminate all but the most recent record per group #3 by creating a SQL expression like this {%mostrec}:

(
select max(`Date`)
from Table A
where A.`Schedule` = Table.`Schedule` and
A.`Division` = Table.`Division` and
A.`Address` = Table.`Address`
)

Then in your record selection formula, use:

{table.Date} = {%mostrec}

Then also place {table.desription} in the detail section and insert a maximum on it at the Grp#3 level. Then go to report->group sort and choose "all"->maximum of {table.description}. This will then sort your group #3's by the description field.

This assumes you are working with only one table. You also might need to build in selection criteria into the SQL expression.

-LB

 

[giggles7840]

If i run this query in crystal, i get 0 records. If i run it in mgmt studio i get 1 record.
(
select max("rpt_CurrentEventStage"."TargetDate")
from "rpt_CurrentEventStage"
where "rpt_CurrentEventStage"."Schedule" = "rpt_CurrentEventStage"."Schedule" and
"rpt_CurrentEventStage"."Subdivision" = "rpt_CurrentEventStage"."Subdivision" and
"rpt_CurrentEventStage"."Address" = "rpt_CurrentEventStage"."Address"
)

If I run this query in mgmt studio i get 81 records back and if i run it in crystal I get an error that states, Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.

(
select max("rpt_CurrentEventStage"."TargetDate")
from "rpt_CurrentEventStage"
where "rpt_CurrentEventStage"."Schedule" = "rpt_CurrentEventStage"."Schedule" and
"rpt_CurrentEventStage"."Subdivision" = "rpt_CurrentEventStage"."Subdivision" and
"rpt_CurrentEventStage"."Address" = "rpt_CurrentEventStage"."Address"
group by schedule, Subdivision, address
)

Maybe i wrote the query wrong?

 

[lbass]

Please set it up the way I suggested (exactly):

(
select max("TargetDate")
from "rpt_CurrentEventStage" A
where A."Schedule" = "rpt_CurrentEventStage"."Schedule" and
A."Subdivision" = "rpt_CurrentEventStage"."Subdivision" and
A."Address" = "rpt_CurrentEventStage"."Address"
)

-LB

 

[giggles7840]

its sort of working. im going to play with it some more and see if i can get it to work. Ill post if i have more questions.

 

[lbass]

Sort of? In what way isn't it working? You can test the results by placing the expression in the innermost group section (Address) and observe whether it is returning the correct value for that group.

Note that a SQL expression goes directly to the database, so if you have other selection criteria in the report, they might need to be added into the where clause of the expression.

-LB

 

[giggles7840]

it worked. i just had to play around with the where statement like you first said.

i hadnt play with the sql expressions so this was pretty cool. the only time i had seen it work was when one of my users was using it as a formula rather than this type of thing.

as always you rock thanks for helping!