Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Count question Post Reply Post New Topic
Author Message
Zuzanna
Groupie
Groupie


Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
Quote Zuzanna Replybullet Topic: Count question
    Posted: 24 Jan 2012 at 2:27pm
I am creating a new report that has to be sorted by department, seniority date and name. The incoming data will look similar as follows:
 
Dept            Seniority Date             Name
100             1990/02/01               Test4
100             1984/10/01               Test2
100             1983/02/01               Test1
100             1985/01/02               Test3
200             1981/10/01               Test2
200             1979/02/01               Test1
200             1985/01/02               Test3
100             1985/03/02               Test11
100             1985/04/02               Test12
 
I will sort this report in Dept, Seniority Date and Name so it will look as follows:
Dept            Seniority Date             Name
100             1983/02/01               Test1
100             1984/10/01               Test2
100             1985/01/02               Test3
100             1990/02/01               Test4
100             1985/03/02               Test11
100             1985/04/02               Test12
200             1979/02/01               Test1
200             1981/10/01               Test2
200             1985/01/02               Test3
 
I'd like to do a count of the employees by department and highlight the top 1/3 of people out of total employees within each department and put '*' beside them and print a message at dept break saying eg. "3 out of 6 have the highest seniority" in case dept 100 or "1 out of 3 have the highest seniority" etc.
 
The report should look as follows:
   Dept            Seniority Date             Name
* 100             1983/02/01               Test1
* 100             1984/10/01               Test2
* 100             1985/01/02               Test3
   100             1990/02/01               Test4
   100             1985/03/02               Test11
   100             1985/04/02               Test12
Department 100 3 out of 6 have the highest seniority
 
* 200             1979/02/01               Test1
   200             1981/10/01               Test2
   200             1985/01/02               Test3
Department 200 1 out of 3 have the highest seniority etc.
 
Is there a way to do this ?
 
Thanks in advance.
 
IP IP Logged
JohnNash
Newbie
Newbie
Avatar

Joined: 10 Jan 2012
Online Status: Offline
Posts: 11
Quote JohnNash Replybullet Posted: 25 Jan 2012 at 3:50am
Hi,
 
This could be possible only through super scripting, just give a try and please let us know
 
 
John
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Jan 2012 at 4:11am
maybe this?
group on dept. (you can hide the grop headers and footers if you want)
create a formula field called "group_total" as
count(name,dept)
create a running total to count records per group
name=row_count
field to summarize=name
type=count
evaluate= for each record
reset= on change of group (name)
create a 3rd formula for your asterisks to palce on your detail section
if {#row_count} % {@group_total}<=33 then '*'
 
IP IP Logged
Zuzanna
Groupie
Groupie


Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
Quote Zuzanna Replybullet Posted: 25 Jan 2012 at 6:04am
Thanks, that works great. For the asteriks formula, what does the '%' do ? I haven't seen it before. Can I count the asteriks to do my 3 out of 9 etc. total ?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Jan 2012 at 6:14am
% is percentage division instead of just division so
1 % 3 results in 33
instead of
1 / 3 results in .33
 
The formula should always adjust the asterisks to show for the first 33% of values for each group.  e.g.
if you have 9 in the group it will * the first 3
if you have 3 in the group it will * the first 1
if you have 100 in the group it will * the first 33
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Jan 2012 at 7:33am
to get your values for the text you can use the
count(name,dept) for the total
you would likely need another formula for the top percetnage
count(name,dept) / 3
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.047 seconds.