SaxonWica
asked on
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)
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)
Insert a group sort Report menu -> Group sort expert ->
for this group sort = all
baed on = {@Total Achievement}
Hope this helps
Gary
for this group sort = all
baed on = {@Total Achievement}
Hope this helps
Gary
ASKER
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.)
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.)
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
Gary
ASKER
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 =(
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
Gary
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.
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.
ASKER
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
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
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
Gary
ASKER
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 =)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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