Sorting by Calculated Field from Totals

[Sacheveral]

I have a very simple report that groups by employee, and the detail section has two fields, a formula for the item that is 1 if it is an enquiry and 0 if not, and a similar formula for if it is a deal. The detail section is hidden. In the employee group footer I have 2 totals: sum of enquiries and sum of deals, then I have a formula that is a ratio of the 2 totals.

What I want is to sort the report by ratio, so that the employee with the highest ratio comes first etc.

When I try to use the Top N/Sort Group Expert it will not allow me to choose the ratio as the sort field. This seems a pretty useful thing to do so I am sure there must be a way. Any ideas?

I am using Crystal Reports 8.5, but will shortly be upgrading to 10 if that will help.

Thanks,

Joe

 

[lbass]

Do enquiry and deal come from the same field? Assuming they do, if you have the option of using a SQL expression, create the following two SQL expressions:

//{%Deal}:
(select count(AKA.`type`) from Table AKA where
AKA.`EmployeeID` = Table.`EmployeeID` and
AKA.`type` = 'Deal')

//{%Enquiry}:
(select count(AKA.`type`) from Table AKA where
AKA.`EmployeeID` = Table.`EmployeeID` and
AKA.`type` = 'Enquiry')

You would replace "Table" with your table name, and substitute your exact field names for "type" and "EmployeeID" and the exact results for "Enquiry" and "Deal". Leave "AKA" as is, since it is an alias field name.

Then create a formula {@ratio}:
if {%Enquiry} <> 0 then
{%Deal} / {%Enquiry}

Place {@ratio} in the detail section and then insert a maximum on it (the detail result will be the same as the maximum, but you need an inserted summary in order to use topN). Then go to topN and use 'Maximum of %ratio" as your topN field.

Please note that you might need to limit the SQL expressions further based on your record selection criteria.

-LB

 

[Sacheveral]

Thanks very much, that certainly seems to be the solution. However the SQL I need to use for the deal count is:

(select count(AKA."Reporting_Product_Needs_Id") from Reporting_Product_Needs AKA
where AKA."Account_Manager_Id" = Reporting_Product_Needs."Account_Manager_Id" and
AKA."Pipeline_Status" = 'won' and AKA."Number_of_Months" > 0 and
AKA."Create_Time" in dateserial(year(CurrentDate),month(CurrentDate)-1,1) to
dateserial(year(CurrentDate),month(CurrentDate),0))

It works fine without the two references to dateserial. Can you see any way round this?

Thanks,

Joe

 

[lbass]

Looks like you're trying to limit the results to last month. Try using the following clause:

(AKA."Create_Time" >= {fn NOW()}-{fn DAYOFMONTH({fn NOW()})}-
{fn DAYOFMONTH({fn NOW()}-{fn DAYOFMONTH({fn NOW()})})}+1 and
AKA."Create_Time" <= {fn NOW()}-{fn DAYOFMONTH({fn NOW()})})

Kind of an awkward work around, but it seems to work.

-LB

 

[Sacheveral]

Your solution worked well, but I now need to change the SQL expression so that it selects data from two tables. The second table is called 'Centre' and they link on Centre_ID. How do I format the FROM clause using your AKA nomenclature?

Also I want the clause above (where AKA."Account_Manager_Id" = Reporting_Product_Needs."Account_Manager_Id") to be changed to link on {Centre.Area} on the new table instead, but the rest of the 'where' clauses to be on the original table as above.

Thanks,

Joe

 

[lbass]

Try the following:

(select count(AKA."Reporting_Product_Needs_Id") from Reporting_Product_Needs AKA, Centre C
where C."Area" = Centre."Area" and
AKA."Pipeline_Status" = 'won' and
AKA."Number_of_Months" > 0 and
(AKA."Create_Time" >= {fn NOW()}-{fn DAYOFMONTH({fn NOW()})}-
{fn DAYOFMONTH({fn NOW()}-{fn DAYOFMONTH({fn NOW()})})}+1 and
AKA."Create_Time" <= {fn NOW()}-{fn DAYOFMONTH({fn NOW()})})

This should give you the equivalent of results at a Area group level, if that's what you're trying to do.

-LB