sorting group summaries

[charlieintegral]

Hi, I'm very stuck here. I'm using CR9
I have two columns in my report, grouped by the first column(defaulted in ascending order), and a percentage summary (worked out from GP 1 sum of a formula returning 1 or 0 in the details divided by a count of site)
I want to be able to present the report in highest percentage first - a bit like a league/results table.

EG.
Site Percentage
A 50%
B 70%
C 40%
D 100%

and I want it to look like this...
Site Percentage
D 100%
B 70%
A 50%
C 40%

Does anyone know how to do this?

Thanks in advance.

Charlie

 

[lbass]

Please also identify your datasource and show the content of the conditional formula your are summing.

-LB

 

[charlieintegral]

My fields are = site, work order number, planned date, actual date.
My groups are,
GP1 - planned date (by month)
GP2 - Site (defaulted to ascending)

Formula in details is,
if actual date <= planned date then 1 else 0 - this is to give a sum of whether that work order was completed in time.

My summaries for GP2 are, distinct count of work order and sum of in time.

I have a separate formula inserted into GP2, for the % in time.
i.e. (@in time)/distinctcount(work order number) * 100

Is this enough info?

 

[lbass]

Actually, without knowing the formulas, I would suggest creating a SQL expression like this:

(select count(`Site`)
from Table A
where A.`Site` = Table.`Site` and
A.`Field` = 'ABC')/(select count(`Site`)
from Table A
where A.`Site` = Table.`Site`) * 100

Replace "Table" with your table name. Change the punctuation as appropriate to your datasource. Replace A.`Field` = 'ABC' with the field you are counting as 1 in your conditional formula, as in:

A.`YourField` = 'YourValue'

Place the SQL expression in the detail section and insert a maximum on it at the group level--you need to do this so that the group sort operation becomes available. Then go to report->group sort->and choose maximum of {%yourSQLexpression}, descending, as your sort formula.

In the group header, select the summary and click on % in the toolbar to format it correctly.\

-LB

 

[lbass]

Didn't see your post. Use:

where A.`Actual Date` <= A.`Planned Date`

I still don't know your table name, but just substitute that. The bigger issue is how you use "distinct" in the SQL expression, and again, that varies by datasource, and I'm afraid I don't know all the options by datasource for that.
In Oracle, it would be:

(select count(distinct `Work Order Number`) [etc.]

-LB

 

[charlieintegral]

it's oracle database.

the table is an IAL - XXX_AWOT2

site is "SECONDARY"
work order is "WO_NO"

can you help, I'm not good at SQL, and seem to be inputting it wrong!

 

[lbass]

What do you mean by:

the table is an IAL - XXX_AWOT2

What exactly is the table name? Do all fields come from the same table?

-LB

 

[charlieintegral]

all fields are from the IAL.

the view is XXX_AWOT2 with the fields below in it.

SECONDARY
WO_NO

 

[lbass]

What are the names of the date fields (actual and plan)? Are they in the XXX_AWOT2 table?

-LB

 

[charlieintegral]

yes, they are
REAL_F - for Actual Completion
for planned completion, I have a formula which requests the larger of the two date fields below. (I know, this makes it more complicated!!)

REQUIRED_END_DATE - is a deferral date but not always larger
PLAN_F - is the planned completion,

so my formula for "planned completion" is:

IF ISNULL("XXX_AWOT2"."REQUIRED_END_DATE")
THEN "XXX_AWOT2"."PLAN_F"
ELSE IF "XXX_AWOT2"."REQUIRED_END_DATE" > "XXX_AWOT2"."PLAN_F"
THEN "XXX_AWOT2"."REQUIRED_END_DATE"
ELSE "XXX_AWOT2"."PLAN_F

 

[lbass]

So IAL is the owner? This is kind of a long shot, but try the following SQL expression:

(
select count(distinct "WO_NO")
from "IAL"."XXX_AWOT2" A
where A."SECONDARY" = "XXX_AWOT2"."SECONDARY" and
A."REAL_F" <=
(
case
when A."REQUIRED_END_DATE" is null then A."PLAN_F"
when A."REQUIRED_END_DATE" > A."PLAN_F" then A."REQUIRED_END_DATE"
else A."PLAN_F"
end
)
) /
(
select count(distinct "WO_NO")
from "IAL"."XXX_AWOT2" A
where A."SECONDARY" = "XXX_AWOT2"."SECONDARY"
) * 100

-LB