Sub Report And Returning Values 1

[robco]

Hello,

I am a beginner with crystal, and I want to be able to report on the following.

2 Tables, Incident and Task table, which are a one to many relationship.

I would like to report on all Incidents that are open "pending", "assigned", where all tasks associated to the incident are either "closed" or "cancelled".

The problem I am having with the select expert is that my report will only return values that are specified above. However, I do not want to show any open Incidents that have any open tasks. I just want to show all open incidents where all tasks are closed.

Thanks!!!!

 

[IanWaterman]

I guess you must have a status field on both incident and task table.
so select statement would look something like

incident.status in [ st1, st2...] and
task.status in[st3, st4,....]

Ian

 

[robco]

Hi Ian,

Yes, there is 2 Status fields. I did what you had recommended and I had the same results as using the select expert.

E.g. It is showing Incidents that are open, with closed tasks, however some of those incidents have open tasks as well.

I only want to see incidents where ALL tasks are closed, not just some.

Thanks Again!



Rob

 

[IanWaterman]

Crystal can not do that properly you have two choices.

Stick with select as above and then in the report display conditionally suppress the details that you want to exclude.

I section suppression formula

Tas.status in [St3, st4]

This will hide those task, however, be careful as data is still there so any summaries will include those records. You will have to use Running Totals which evaluate under the same conditions so the hidden records are excluded from summaries.

Option 2

Cretae a SQL command which brings back the data you want and use that rather than reading directly off database tables. However, this will require some intermediate SQL skills.

Ian

 

[lbass]

First, you must allow all task statuses into the report (you have to be able to check whether a task is open). Then you can accomplish this by inserting a group on incident and then creating a formula:

//{@notclosed}:
if {table.taskstatus} <> "closed" then 1

Then go to report->selection formula->GROUP and enter:

sum({@notclosed},{table.incident}) = 0

This would display only those incidents where the task status for all tasks = closed. If the task status can be null when open, change the first formula to:

if isnull({table.taskstatus}) or
{table.taskstatus} <> "closed" then 1

-LB

 

[robco]

Thank you LB.....it worked great....very helpful indeed.