Group by Summary Field

[Cozmo2]

I would like to group by a summary field, however it is not one of the fields I can select when grouping data. Is there any way to do this?

 

[lbass]

You haven't provided much info. If you create the summary in a SQL expression, you can group by it. If your intent is really just to order groups based on a summary field, you could alternatively use group sort.

-LB

 

[Cozmo2]

I am creating the summary using the insert summary function in Crystal. I am summing the number if students that signed up for a course.

 

[lbass]

So you are all set?

-LB

 

[Cozmo2]

No, I'm sorry I will try to be clearer. The sort will not work. How would I create a SQL expression?

 

[lbass]

What tables and fields are you working with? It might help if you go to database->show SQL query and copy and paste it into this thread.

-LB

 

[Cozmo2]

The formula is adding the number of students in each course section. Current grouping is: course, school yr, section. We are looking to group by several ranges for the enrollment count (1 - 4, 5 - 9, 10 - 14 up to 30+.

Here is the query:

SELECT "course"."course", "crssect"."sch_yr", "crssect"."semester", "crssect"."timedesc", "course"."level_", "nmcrs"."course", "nmcrs"."section", "course"."transfer", "nmcrs"."crs_txt", "course"."dept_cod", "nmcrs"."soc_sec"
FROM ("Eurekadb"."dbo"."nmcrs" "nmcrs" INNER JOIN "Eurekadb"."dbo"."course" "course" ON "nmcrs"."course"="course"."course") INNER JOIN "Eurekadb"."dbo"."crssect" "crssect" ON "nmcrs"."sid"="crssect"."sid"
WHERE "course"."course" NOT LIKE 'ATH%' AND ("crssect"."sch_yr"='200708' OR "crssect"."sch_yr"='200809' OR "crssect"."sch_yr"='200910') AND ("crssect"."semester"='1' OR "crssect"."semester"='2') AND NOT ("crssect"."timedesc" LIKE 'ARR' OR "crssect"."timedesc" LIKE 'TBD') AND "course"."transfer"=0
ORDER BY "course"."course", "crssect"."sch_yr", "nmcrs"."section", "nmcrs"."course

 

[chelseatech]

Use the group Sort feature. Select the group and the summary you want to sort the groups by.

It might be listed under TopN Sort Group Expert.

It can group by any summary. it can't group by formulas based on a sumamry although we've found a way to do this, and covered this in the latest Crystal Clear newsletter.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Cozmo2]

Thanks for the help and the link to the Crystal Clear newsletter! I couldn't get it to work (I'm using a count for the summary) but learned some new things about Crystal.

 

[lbass]

Are the section IDs (sid?) unique? In other words, is a section ID used in only one course in one year? And are you looking just for the enrollment count within sections?

-LB

 

[Cozmo2]

Yes to both questions. We also want to report/group by range, 1-4, 5 -9 etc up to 30+.
Thanks

 

[lbass]

Well, I'm not sure which sid field to use, but try something like this for a SQL expression {%cnt}:

(
select count("soc_sec")
from "Eurekadb"."dbo"."nmcrs" A
where A."sid" = "nmcrs"."sid"
)

...where I'm assuming "soc_sec" will act as a unique student ID and where "sid" = the section ID. If that isn't correct, substitute the correct field names. Then I would place this on the report and check for accuracy before using it in a formula in the formula editor like this:

select {%cnt}
case 1 to 4 : "01 - 04"
case 5 to 9 : "05 - 09"
etc//
default : 0

Then you can insert a group on this formula.

-LB

 

[Cozmo2]

I am not familiar with sql expressions in crystal reports. Thanks for you help.

 

[lbass]

I don't understand what your intention is in saying this.

-LB

 

[Cozmo2]

I guess I'm trying to say I think I'm in way over my head (at this point)! But I really appreciate the help you have given me.