Sorting on Percentage

[Belpheghor]

I'am quiete new to CR. I have CR 8.5 and my problem is the following:

I have two groups in my report:

ClientID
DateServiced

Each client can be serviced more than one time over a given period.

I have a total ClientDistributionCost that I managed to compute and a total RouteCost (not only this particular client had been serviced in the route).
Division between the two gives u a percentage that I want to sort on descending. Is it possible to filter out clients for a particular percentage or to compute the top 10 most expensive clients?

I managed to cumpute the percentage as a formula field in the ClientID group footer but how can I sort on that value?

Thanks a lot for your advice

 

[dgillz]

have a look at the Top N expert.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Please provide the content of the percentage formula you want to sort on. If there are any nested formulas in it, show the content of those, too.

-LB

 

[Belpheghor]

I want to sort on the division of two summary fields that are put in the ClientID Group Footer:

It yields : (Sum of ClientDistributionCost)/(Sum of RouteCost)*100

For a reason that I don't know creating the here-above formula and putting it into ClientID Group Footer don't compute the correct value. Using Running total fields work.

So there aren't any nested formula in it.

 

[dgillz]

Please post your actual formula verbatim from the formula editor.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[Belpheghor]

The formula that computes finally the correct percentage and that I want to sort on (@Percentage) is :

Sum ({View_Reporting_ClientStatistics_Unique.DistributedDifferentialCost}, {View_Reporting_ClientStatistics_Unique.ClientId})/Sum ({View_Reporting_ClientStatistics_Unique.Cost}, {View_Reporting_ClientStatistics_Unique.ClientId})*100

Thanks

 

[IdoMillet]

Your prior formula was computing the grand total sums instead of per client id.

A formula that uses sums (or any other aggregate) can't be used for a Group Sort. This is because such a formula is computed in the same report pass as the sorting of the groups. That is just a fancy way of saying this is a limitation of Crystal.

Since you are already using a View, the easiest solution is to add a view or modify the existing view so that it provides the summary values in the raw data set.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Or, insert a SQL expression {%percent} like this:

(
select sum(`DistributedDifferentialCost`)
from View_Reporting_ClientStatistics_Unique A
where A.`ClientId` = View_Reporting_ClientStatistics_Unique.`ClientID`
)/
(
select sum(`Cost`)
from View_Reporting_ClientStatistics_Unique A
where A.`ClientId` = View_Reporting_ClientStatistics_Unique.`ClientID`
) * 100

The punctuation will be specific to your datasource. Place {%percent} in the detail section and insert a maximum on it (you need to do this to activate the topN sort). Then go to topN and sort on the maximum on {%percent}.

-LB