Ordering/Sorting

[ggies07]

Hey All,

I work for a school and have a report that uses a subreport. The subreport is linked using the student's system ID number so that the contact info syncs with the right student. That way in cases where there are lots of Johnsons, Smiths, etc. the contact info is not going to the wrong person.

What I'm having trouble with is the sorting. I have to first group the report by Homeroom Teacher, then I need it sorted alpha by student, but because of the second group (which I used for my subreport to make things clean) I can't sort alpha anymore. It has it sorted by the system ID. I then went to the sorting field and add the student's last name, but nothing happened.

Is there are around this or a fix?

Thanks!

 

[kray4660]

Because of the grouping you have, the group would override any sorting. Here is a thought. Create a formula that is a concatenation of the students name (last name, first name?) and their system id, group by that formula. Still use the same linking to the subreport, but now you will be grouped and sorted by the student name.

I hope this helps.

 

[ggies07]

Thanks! Yeah, I knew that groups override sorting, but I just couldn't figure it out and was trying every combo I could think of. I'm not 100% what you mean, but I will play around with it and check back in.

 

[pmax9999]

What kray4660 is suggesting is to create the following formula (substituting actual table.field names):

{Table.Lastname} + {Table.FirstName} + ToText(Table.StudentID, '#')

This will still create a unique result for all students as it includes the StudentID field (NB: I assume this is a numeric field hence the use of ToText).

Group on this formula instead if the StudentID field and you will get students in alpha order.

Cheers
Pete

 

[pmax9999]

The Group Name generated will of course be the concatenated formula result, but that can be deleted / suppressed and replaced with the Student Name field{s) without impacting the report results.

Pete

 

[ggies07]

Thanks kray4660 and pmax9999, that worked! So what was special about adding the system ID number into the formula with the # symbol? Is the # some type of wildcard? I really appreciate the help, this is a great site.

 

[pmax9999]

The # in ToText(Table.StudentID, '#') formats the text output. "#" returns numbers only with no decimal or thousands separator. "00000#" would pad the result with leading zeros to a maximum of 5. In this case it is not essential as the result is not for display purposes - just force of habit on my part.

I recommend you make use of the Help Files within Crystal. They are actually surprisingly helpful.

Cheers
Pete

 

[ggies07]

Interesting.....I'll def. read the help files in my spare time, but I love learning new things from solving problems and others who know more than me.

 

[Charliy]

I recommend putting a space between the name elements, like:
{table.lastname}+' '+{table.firstname}

otherwise you'll have Allen Stevens appear before Yvonne Steven