need help with sorting on calculated field

[dalma]

Data in the report looks like this:
Shift ID time time in secs
A 2111 00:01:58 118
A 2174 00:01:59 119
B 2113 00:01:27 87
B 2176 00:01:33 93
B 2174 00:01:36 96

Grouping is set on shift and ID field. All the fields sit in Id group footer section.
Need to sort on time in secs.
Tme in secs is average of the following formula:

If datediff("s", {apparatus.dispatchdate},{apparatus.scenedate})<0 then 0 else

datediff("s", {apparatus.dispatchdate},{apparatus.scenedate})

Report needs to look like this:

Shift ID time time in secs
B 2113 00:01:27 87
B 2176 00:01:33 93
B 2174 00:01:36 96
A 2111 00:01:58 118
A 2174 00:01:59 119


Is it possible to do this in CR? I can always export and do the sort in excel, but would like to have this accomplished in CR.

Any and all help is appreciated!

[SvB_NY]

You can sort your records using the Record Sort Expert (->Report->Record Sort Expert).

[dalma]

SvB_NY

You can sort your records using the Record Sort Expert (->Report->Record Sort Expert).

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Not possible since the report is sorting on the 2 groups first.

I do not want to group on the calculated field, just trying to force the sort on this field first, regardless of the 2 groups.

Thanks.

[SvB_NY]

I see I missunderstood you :).

I was sure you wanted to sort your records inside of the groups.
Sorry, but I don't know how to sort records 'on a field first, regardless of the 2 groups'.

Let me ask you what for do you need those 2 groups if you want to ignore them?
If you need to show records sorted on time_in_secs, delete those groups
but if you need to have some totals for those groups, use a subreport to keep your records sorted on time_in_secs in your main report.

[dalma]

Thanks, I do have to keep the groups as I do totals on some of the fields.

Can you please explain the solution with subreports? I haven't been working with those at all.

Where would I put the subreport in the main report?
Do I only have the details in the subreports and no groupings and totals?
Also, report has parameters, how do I deal with those in the subreport?

Thank you!

[JaganEllis]

You might be able to use the group sort expert. Read the help on it, and have a play with it.
e.g. create two formulas, one as the average secs per ID, another as the minimum secs per shift. Use the corresponding formula in the group sort expert for each group with an 'All' sort.
Trouble is, I think you want the outer group to be sorted on the average of the ID group not the minimum of the Shift group, and I'm not sure that'll work...
You might be able to overcome that with an SQL expression to return the ID group average on each record so that you're not trying to aggregrate an aggregrate.
Confused?

[dalma]

Thanks JaganEllis,

I did try group sort, but as you said, it is not possible to sort outer group on the average of ID group.

[SvB_NY]

Here is an explanation of my solution, in case if you decide trying it.
Before starting, I would suggest you to read CR help file on how to create/insert subreports and link them with the main one.
--------
Create a copy of your report, so you would have 2 of the same reports.
In one of them, delete your groups and sort your records in the way you need and save it.
In the second one, suppress details but keep showing your groups with your totals and save it as well.

As I understood these 2 reports is the result which you want to show in one report.
Actually, it doesn't matter which report will be used as a main, let's use the first one.

Open it and in the Design mode go to the Insert menu ->Subreport, check 'Choose an existing report' and browse your second report.
Then go to the Link tab and link your subreport with the main one (from your example, I think it has to be linked by {table.shift_ID}). Select that field from the 'Available Fields' section and add it to the 'Field(s) to link to', click OK.

You can place your subreport in the RH if you want the groups with the totals to be shown in the beginning of the report.
Or you can place it in the RF, if you want it in the end of the report.
If you need to modify your subreport, f.e. to suppress RF or to add some fields if you need, just double click on it.

-------------------------------------------
-------------------------------------------

Thanks, I do have to keep the groups as I do totals on some of the fields.

Can you please explain the solution with subreports? I haven't been working with those at all.

Where would I put the subreport in the main report?
Do I only have the details in the subreports and no groupings and totals?
Also, report has parameters, how do I deal with those in the subreport?

Thank you!