Report Design: Count question

Count question

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15440
Printed Date: 12 May 2024 at 5:37pm

Topic: Count question

Posted By: Zuzanna
Subject: Count question
Date 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.

Replies:

Posted By: JohnNash
Date 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

Posted By: DBlank
Date 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} % mailto:%7b@group_total%7d%3c=33 - {@group_total}<=33 then '*'

Posted By: Zuzanna
Date 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 ?

Posted By: DBlank
Date 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

Posted By: DBlank
Date 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