Can anyone help with a sort problem in CR. 9

[Brian555]

I have a report that looks at the following table (simplified).

Date Customer Visits Number of Sales
Jan 05 Smith 4 2
Jan 05 Jones 8 3
Jan 05 Brown 6 4
Feb 05 Smith 6 2
Feb 05 Jones 7 5
Feb05 Brown 2 2
Etc.

The report is grouped by customer

I have a formula, @SalesRatio, that displays the ratio of sales per visit per customer.

@SalesRatio
Sum ({Visits}, {Customer}) / iif(Sum ({Number of Sales}, {Customer})= 0, 1, Sum ({Number of Sales}, {Customer}))

However when I try to sort the report by @SalesRatio it does not appear on the list of fields that can be sorted.

I would really appreciate any help on this!!

 

[lbass]

The only solution I've found for this uses SQL expressions. I don't have CR 9.0, so am not sure whether these SQL expressions will work in the SQL expression editor as they do in 8.0, or whether you will have to use "Add Command" as your datasource, where you would add these expressions into the "Select" part of the query (where they will work, if they don't in the SQL expression editor). Also note that syntax/punctuation can vary depending upon datasource.
Is there only one table? Or more than one?

You can try this:

[%Visits:]
(select sum(AKA.`visits`) from Table AKA where
AKA.`customer` = Table.`customer`)

[%Sales:]
(select sum(AKA.`sales`) from Table AKA where
AKA.`customer` = Table.`customer`)

You need to substitute the exact field names for "sales" and "customer" and the table name for "Table". Leave "AKA" as is, since it represents an alias table name. If you have selection criteria, these might need to be built into each expression.

Then create a formula {@salesratio}:

if {%Sales} = 0 then
{%Visits} else
{%Visits}/{%Sales}

Place this formula in the detail section and insert a maximum on it (the value will be the same as in the detail section, but you need this step in order to use topN). Then go to topN/group sort and choose "maximum of {@salesratio}".

-LB

 

[Brian555]

lbass

Sincere thanks for your help in sorting (pun intended) my problem.