Crystal Reports Help Filtering Multiple Times

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Help Filtering Multiple Times

Author

Message

roxaneamanda
Newbie
Newbie

Joined: 10 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 23

Topic: Help Filtering Multiple Times
Posted: 10 Aug 2009 at 8:40am

I need to create a report with 2 different selection criteria, to paint a picture, I report on IT Service Desk incidents logged which are assigned to a number of resolver teams.

I need to report on 3 things in the same report;

Number of open incidents remaining, by team at the end of the day and

Number of incidents completed by each team during that day and

Number of incidents assigned to each team during that same day.

As soon as I add on one selection crieria, eg filter by all open, everything else is filtered based on that first filter????

HELP

IP Logged

DBlank
Moderator

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

Posted: 10 Aug 2009 at 9:09am

Use an OR statement between the 3 items

Something like

ISNULL(completion date) OR

completed date = Date of choice OR

assigned date = Date of choice

Edited by DBlank - 10 Aug 2009 at 9:10am

IP Logged

roxaneamanda
Newbie
Newbie

Joined: 10 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 23

Posted: 11 Aug 2009 at 2:46am

I know by checking manually that the results should be stating this

	Completed Yesterday	Assigned Yesterday	Still Open
Total	176	169	525

However, when I use the following statement, I only get 192 record returned?

{completion date} in currentdate-1 or
{creation date} in currentdate-1 or
not ({status} in ["Completed", "ClosedMail", "Closed"])

Please help?

IP Logged

DBlank
Moderator

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

Posted: 11 Aug 2009 at 5:56am

I would test each part seperately to validate you get the correct numbers per statement and that the issue is really trying to get all 3 at once but my guess is that your last statement requires the completion date to be NULL and the I have found that if I do not handle NULL statements in crystal in a particular fashion it can get a little off. So try your condition that needs NULL first and you should see a big change in numbers.

not ({status} in ["Completed", "ClosedMail", "Closed"]) or

{completion date} in currentdate-1 or
{creation date} in currentdate-1

IP Logged

roxaneamanda
Newbie
Newbie

Joined: 10 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 23

Posted: 11 Aug 2009 at 6:44am

That Worked, Thanks.

I have now created 3 different formula's fields;

Field 1 - if {completed date} = currentdate-1 then 'Completed Yesterday'

Field 2 - if {creation date} = currentdate-1 then 'Created Yesterday'

Field 3 - if {status} = 'Assigned' then 'Still Open'
else if {status} = 'Accepted' then 'Still Open'
else if {status} = 'Rejected' then 'Still Open'
else if {status} = 'In Progress' then 'Still Open'
else if {status} = 'Waiting for...' then 'Still Open'
else if {status} = 'Change Pending' then 'Still Open'

Don't suppose you know how I can now group them all together to look like this

	Still Open	Created Yesterday	Completed Yesterday
TEAM A	2	58	4
TEAM B	54	58	12
TEAM C	9	87	12

When I use a summary, for some reason it is splitting it out like this

	0
	473
Still Open	473
Created Yesterday	41
Still Open	41
	23
	11
Still Open	11
Created Yesterday	12
	12
Completed Yesterday	175
	59
	59
Created Yesterday	116
	116

IP Logged

DBlank
Moderator

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

Posted: 11 Aug 2009 at 7:42am

Well you have to be careful on your grouping here becasue these are not mutually exclusive groups of data and once you group an item it will ony appear in that group (not both).

Create a GRoup on TEAM FIELD

Create 3 Running Totals (or 6 if you need group and report totals)

RT#1 name= "TeamStillOpen" (or whatever you want)

Field to summarize=preferably a primary key field

Type of Summary=DistinctCount

Evaluate set to "use a formula" and insert the formula as

not ({table.status} in ["Completed", "ClosedMail", "Closed"])

Reset =On change of Group (select group1...Team field level)

Place on GroupFooter 1.

RT#2 is the same but name it "Team_Created_Yesterday"

and use the evaluate formula of

{creation date} in currentdate-1

Place on GroupFooter 1.

RT#3 is the same but name it "Team_Completed_Yesterday"

and use the evaluate formula of

{creation date} in currentdate-1

Place on GroupFooter 1.

If you need these for Overall Report totals

make 3 more the exact same as the other 3 (reuse the 3 evaluate formulas) but make the reset as NEVER for each and place on the Report Footer.

Does this work for you?

Edited by DBlank - 11 Aug 2009 at 7:44am

IP Logged

roxaneamanda
Newbie
Newbie

Joined: 10 Aug 2009
Location: United Kingdom
Online Status: Offline
Posts: 23

Posted: 11 Aug 2009 at 8:03am

You're brilliant, I wish I knew as much as you!

Thanks a million

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