• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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
Asked:
SaxonWica
1 Solution
 
SpykairCommented:
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
 
GJParkerCommented:
Insert a group sort  Report menu -> Group sort expert ->

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

Hope this helps

Gary
0
 
SaxonWicaAuthor 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GJParkerCommented:
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
 
SaxonWicaAuthor 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
 
GJParkerCommented:
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
 
bdreed35Commented:
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
 
SaxonWicaAuthor 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
 
GJParkerCommented:
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
 
SaxonWicaAuthor 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
 
GJParkerCommented:
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
 
SaxonWicaAuthor 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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now