Simple Report Giving me a headache

[zootweller]

As you will see, the Subject of this message wasn't easy to put into a few words....

This is in relation to a Helpdesk database:

I have 2 tables, joined by a field called 'Problem_ID'.
Tablename'Problem' is the main table for each problem raised on the system, and has a unique 'Problem_ID' - Table'Session' contains any 'attachments' that have been added to the 'Problem_ID' and has it's own unique 'Session_ID', this means that many 'Session_ID's can be part of a 'Problem_ID' - imagine a piece of note-paper with a few additional post-it notes stuck onto it, each with an individual reference, and you will get the idea (stick with me, I'm getting there!).
Each 'Session.Session_ID' has a 'Call_Code' (categorised call description), and I am trying to report on each 'Problem.Problem_ID' that does not have a Session_ID attached with the 'Call_Code' category of 'ISSUE'.

Example:
My report will return 4 instances for the 'Problem_ID' of '12345', excluding the attachment with the 'Call_Code' of 'ISSUE' (5 attachments in total). I do not want to see this Problem_ID at all!!
Is there a solution? I have tried all I can think of so far.
Any help appreciated.

 

[kenhamady]

This can be done, but it is tricky.
The key is that you have to think of your problem as a group of records (one for each issue) and you have to evaluate the whole group to determine if any of the group is an "Issue" code.

Here is the process:

1) Select all records to be evaluated (ISSUES and Non-Issues) and make sure that you do an outer join so that you don't eliminate problems with no ISSUE records at all.

2) Create a formula that says:
if {codefield} = "ISSUE"
then 1
else 0

3) Place this on the detail band. Some records will be 1 and others will be 0.

4) Create a subtotal of this column for each Problem. If there are any ISSUE codes for this problem, the subtotal will be > 0.

5) Go into the select expert and add a rule that says this subtotal (which will be a Sigma in the field list) has to be = 0. Viola, all groups with ISSUE codes will vanish.

6) If you need any grand totals, use running totals. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/