Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Grouping and Averaging Group Post Reply Post New Topic
Author Message
joesmithf1
Newbie
Newbie


Joined: 03 May 2010
Location: United States
Online Status: Offline
Posts: 4
Quote joesmithf1 Replybullet Topic: Grouping and Averaging Group
     Posted: 03 May 2010 at 10:45am
Hi All,
 
I am a newbie and pulling my hairs out trying to figure the following problem out.  I know how to do groupings, but can't figure out how to put the count and average side-by-side based on conditions(see below). Please help!!!
 
Sample Source Data:
Lawyer---C1---Joe---FT---$8.00
Lawyer Secretary---C1---Max---PT---$10.00
Lawyer---C1---Max---FT---$10.00
Attorney---C2---Sandy---FT---$22.00
Judge---C2---Adam---PT---$12.00
Judge---C2---Brian---PT---$40.00
 
Report Result I Want:
As you can see, i like to group by C1 and C2. Then i would like to get a count between the number of FT and PT, and get a average pays for each .
 
[Group:]                       [Count FT]    [Avg-FT]  [Count PT]  [Avg-PT]
----------------------------------------------------------------------------------------------
C1
  Lawyer                             2               $9.00
  Lawyer Secretary                                                  1               $10.00
---------------------------------------------------------------------------------------------
C2
   Attorney                          1              $22.00
   Judge                                                                   2                $26
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 03 May 2010 at 11:04am
Keep your C1/C2 group and add a group on Job Title (Lawyer, Attorney, etc.)
 
You'll have to use some formulas - 3 each for full time and part time.  They'll look something like this:
 
If {table.pay_status} = 'FT' then 1 else 0
 
If {table.pay_status} = 'FT' then {table.pay_rate} else 0
 
Sum({@FTPay}, {table.job_title}) / sum({@isFT}, {table.job_title}
 
You'll do the same for Part Time employees.
 
To get the number of FT and PT employees, create a summary to sum {@IsFT} and another to sum {@IsPT}.
 
In the report, put the data in a Job Title footer section, suppress the Job Title header and the details.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
joesmithf1
Newbie
Newbie


Joined: 03 May 2010
Location: United States
Online Status: Offline
Posts: 4
Quote joesmithf1 Replybullet Posted: 03 May 2010 at 4:22pm
hey, thank you! it works! Now i just need to figure out how to get rid of duplicates.  :-)
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 04 May 2010 at 3:43am
An easy way to get rid of duplicates is to go to the Database menu and turn on "Select Distinct Records".  However, this only works if ALL of the data in any record in the result set is exactly the same - if even one field that is used in the report in ay way has different values, you'll get duplicate records.  There are ways around that, but try this first and let me know if it works.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.