Crystal Reports Grouping Specified Data

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions

Topic: Grouping Specified Data

Author

Message

a4chan
Newbie
Newbie

Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21

Topic: Grouping Specified Data
Posted: 21 Dec 2010 at 8:51am

Hello,

I have the data like this :

Employee Id	Deparment ID
A101	Null
A102	9000
A103	9500
A104	Null
A105	Null
A106	10000
A107	Null
A108	10500
A109	11000
A110	12000

I need it to be grouped by Department ID and the data should look like this

	Count
No Department Assigned	4
Department Assigned	6

Can anyone help me to resolve this problem.

Thanks,

Avi.

Edited by a4chan - 21 Dec 2010 at 8:54am

a4chan

IP Logged

lockwelle
Moderator

Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372

Posted: 21 Dec 2010 at 9:06am

create a formula like;

shared numbervar assigned;

shared numbervar unassigned;

if isnull({table.departmentID}) then

unassigned := unassigned + 1;

else

assigned := assigned + 1;

suppress the detail section, and place the formula in the detail section

in the report footer add 2 labels, on for assigned and one for unassigned (as in example) then create 2 formula and place them next to the desired labels.

one formula will look like:

shared numbervar assigned

and the other:

shared numbervar unassigned

HTH

in the non-suppressed section put a

IP Logged

a4chan
Newbie
Newbie

Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21

Posted: 21 Dec 2010 at 9:17am

Thanks for your reply but i need the data to be dynamic rather than creating labels for assigned and unassigned department.For example

if isnull(table.departmentid) then No department assigned

else department assigned.

When i use this formula and group it by the respective formula i got the data like this

	Count
No Department Assigned	4
Department Assigned	1
Department Assigned	1
Department Assigned	1
Department Assigned	1
Department Assigned	1
Department Assigned	1

btw is your post complete? I couldn't see the entire post.

Edited by a4chan - 21 Dec 2010 at 11:53am

a4chan

IP Logged

a4chan
Newbie
Newbie

Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21

Posted: 21 Dec 2010 at 11:55am

Can anyone help me to resolve this problem

a4chan

IP Logged

DBlank
Moderator

Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053

Posted: 21 Dec 2010 at 12:05pm

i do not understand your request exactly.

in one place you say you want to group by the department, but your charts are all just assigned and unassigned.

If you just want a total of the 2 options create a formula

if isnull(table.departmentid) then "No department assigned"

else "department assigned"

then insert a crosstab in you report header or footer (whever you want it)

Row set to use the fomrula field above

sumamrized field as a distinctCount of employee ID field

if you want 'not assigned' and a count per other department, change your formula to:

if isnull(table.departmentid) then "No department assigned"

else table.departmentid

Edited by DBlank - 21 Dec 2010 at 12:06pm

IP Logged

a4chan
Newbie
Newbie

Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21

Posted: 21 Dec 2010 at 12:21pm

I better explain you clearly.

In group Header All the nulls should come under No Department assigned,all the departments and their respective count

In Details Section it should show all the employee id's

when i use this formula "department"

if isnull(table.departmentid) then "No department assigned"

else "department assigned"

and grouped it by department I got data something like this:

GH	Department Assigned	1
Details	A102
GH	Department Assigned	1
Details	A103
gH	Department Assigned	1
Details	A106
gH	Department Assigned	1
Details	A108
gH	Department Assigned	1
Details	A109
gH	Department Assigned	1
Details	A110
gH	No Department Assigned	4
Details	A101
	A104
	A105
	A107

but I need the data to look like this

GH	Department Assigned	6
Details	A102
	A103
	A106
	A108
	A109
	A110
GH	No Department Assigned	4
Details	A101
	A104
	A105
	A107

Please let me know if I am not clear.

Thanks,

a4chan

Edited by a4chan - 21 Dec 2010 at 12:22pm

a4chan

IP Logged

DBlank
Moderator

Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053

Posted: 21 Dec 2010 at 1:39pm

Do not group on the database field but rather group on the formula field

IP Logged

a4chan
Newbie
Newbie

Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21

Posted: 21 Dec 2010 at 4:23pm

Well I group by formula field but I didn't get the required data whenever the department name is changed "Department Assigned" will be repeated like below

GH	Department Assigned	1
Details	A102
GH	Department Assigned	1
Details	A103
gH	Department Assigned	1
Details	A106
gH	Department Assigned	1
Details	A108
gH	Department Assigned	1
Details	A109
gH	Department Assigned	1
Details	A110
gH	No Department Assigned	4
Details	A101
	A104
	A105
	A107

a4chan

IP Logged

DBlank
Moderator

Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053

Posted: 22 Dec 2010 at 3:39am

it looks like you only placed the group formula field in the group header but it is still not the field that is being used for the grouping.

Delete your group

adda new group

Fromt he avialbale field select the formual field which I beleive you have named @Department

Now preview the report

Edited by DBlank - 22 Dec 2010 at 3:39am

IP Logged

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.031 seconds.