• Status: Solved
• Priority: Medium
• Security: Public
• Views: 444

# Group Sort from a formula

How to use formula as a "based on" sort at group sort expert?

For example, I have a data like this

Employee XXXXX
Date          Item           Sell     Target  Achievement
1/1/2005    item A        \$100   \$110     91%
1/2/2005    item X        \$110   \$200     55%
Total   \$210   \$310     68%

Employee XXXXX
Date          Item           Sell     Target  Achievement
1/1/2005    item A        \$90     \$110     82%
1/2/2005    item X        \$20     \$200     10%
Total   \$110   \$310     36%
and so on

The column Achievement was done using formula. And now, I want to sort the employee group based on total achievement. Is it possible to do this?

More explanation on the example :

Employee XXXXX
Date          Item           Sell     Target  Achievement
1/1/2005    item A        \$90     \$110     82% --> Formula (Sell / Target * 100)
1/2/2005    item X        \$20     \$200     10%
Total   \$110   \$310     36%
^                   ^
Sum of Sell      Formula (sum(sell,employee) / sum(target,employee) * 100)
0
SaxonWica
1 Solution

Commented:
Create 2 groups.

First group: Employee

2nd group: Achievement
In you Group Expert, select "In Descending Order".
Section Expert -> Suppress the Group Header and Group Footer

Hth,
Spykair
0

Commented:
Insert a group sort  Report menu -> Group sort expert ->

for this group sort = all
baed on = {@Total Achievement}

Hope this helps

Gary
0

Author Commented:
Gary :
I use Crystal Report 9 Advanced Edition, and from what I know, "based on" fields cannot be typed manually, and the options that are available are only summaries, not formulas

Spykair :
I can't just group it, because I need to sort the employee group based on total achievement (This kind of "achievement" thing cannot be summarized, as no method available to count it --> Not even using average method.)
0

Commented:
Yes of course you are correct, you can only do a group sort on summaries and not formulas. You will probably need to do some work on the db end to accomplish this as you can't do it directly with CR.

Gary

0

Author Commented:
Well, the problem is that I need to make the report drilldownable......... And I can't imagine a thing that doesn't do any work on the db end =(
0

Commented:
I would create a view on the db which shows the total achievement per employee and link this in to the report, you can then use this to sort in the required order.

Gary
0

Commented:
If you right click the achievement formula (not the formula that calcs the avg), is Insert, Summary available?
I think if you can insert an average summary on that formula you should be able to use that summary in the group sort expert.
0

Author Commented:
GJParker : That would solve the problem if i don't have lots of group like this. My report needs at least 5 groups and each are totalled by achievement sum (Grouped By : Product Line, Area, Manager, Sub Manager, Sales Force, etc.).

bdreed35 : No, the insert summary isn't available, since the formula I use also includes other summaries. Here's some code on the formula I use :

if Sum ({Command.BudgetAmount}, {Command.EmployeeIDAM}) <> 0 then
Sum ({Command.ActualAmount}, {Command.EmployeeIDAM}) / Sum ({Command.BudgetAmount}, {Command.EmployeeIDAM}) * 100
else
if Sum ({Command.ActualAmount}, {Command.EmployeeIDAM}) = 0 then
0
else
100
0

Commented:
If you want to sort the report by each od these achievement valuse then you will need to create a view for each level of grouping and link these to the report.

Gary
0

Author Commented:
Whoa... Is there totally no other choice that this? I'm afraid that it's going to load my sql server up, since we have hundred thousands record in total. Well, if there's no other solutions then I'm going to accept your answer, Gary. Unless there's a better solution. I'll wait for another 3 days if you don't mind =)
0

Commented:
Creation of views should add very little overhead to your SQL Server as all you are really creating is a SQL statement to run over the existing data.

Gary
0

Author Commented:
If there's only one view, that might be true... but to create a view for each level of grouping means that the view itself runs for a couple times if I'm not mistaken... Well, I tried this once before, but it runs very slow, so I decided not to use it. That'a also why I'm trying to find a solution to process the result in Crystal Report, but it seems that problems like this can't really be done from Crystal report itself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.