Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Exculsionary report Post Reply Post New Topic
Page  of 2 Next >>
Author Message
rkh7437
Newbie
Newbie


Joined: 06 Jul 2009
Online Status: Offline
Posts: 13
Quote rkh7437 Replybullet Topic: Exculsionary report
    Posted: 20 Nov 2009 at 7:47am
Hello. I am trying to set up a report based on the following:
Employee has "x" amount of customers he needs to contact in a month. I need a report that shows 1) The people contacted and then 2) the people not contacted in a given month.  I cannot get it to list the people not contacted. I tried using an isnull statement, but to no avail.
 
Can you please assist me?
Thanks!
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Nov 2009 at 9:03am
depends on your tables, joins and select statement...
Likely you have a table of Employees, a table of assigned 'People' and a table of contacts (with contact dates per 'assigned people').
Is this correct? Can you give more detailed information?


Edited by DBlank - 20 Nov 2009 at 9:04am
IP IP Logged
rkh7437
Newbie
Newbie


Joined: 06 Jul 2009
Online Status: Offline
Posts: 13
Quote rkh7437 Replybullet Posted: 20 Nov 2009 at 9:46am
I have one table that has the contact information which would include the employee's name and one table that has the contact's history which also includes the employee name.  Basically I want to see if the employee had contact with a customer, that it would list them in one area, then the remainder of the contacts that had no contact, it would list them in another area.
IP IP Logged
rkh7437
Newbie
Newbie


Joined: 06 Jul 2009
Online Status: Offline
Posts: 13
Quote rkh7437 Replybullet Posted: 20 Nov 2009 at 9:51am
Basically, the report would be based off of this information:
 
Employee RJ Reynolds has 5 customers to contact monthly. They are John Smith, Joe Bob, Ray Jones, Michael Scott, and Jim Halpert
 
 
RJ Reynolds Contacted
John Smith
Joe Bob
Ray Jones
 
RJ Reynolds did not contact
Michael Scott 
Jim Halpert 
 
I can't get the report to show Michael Scott and Jim Halper as not having been contacted.
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Nov 2009 at 9:57am
How do you know these are the 5 people? They are listed in the Contact INfomration table with the employee name and is there any other definition like a boolean that is active or inactive?
When you link to the contact history is this a double join on employee name and contact person name?
Can you write a Query outside crystal to limit the data from contact history table?
IP IP Logged
rkh7437
Newbie
Newbie


Joined: 06 Jul 2009
Online Status: Offline
Posts: 13
Quote rkh7437 Replybullet Posted: 20 Nov 2009 at 10:09am
I have the 5 people tagged with RJ Reynolds as the Record Manager and additionally marked as a Prospect. I have the contact and contact history tables linked by using the CONTACTID field. I had it as an inner join, but changed it to an outer-join.
 
This is the formula I used to get the the customers that were contacted:
{VRP_CONTACT.CONTACT ID/Status} like "*Prospect*" and
{VRP_CONTACT_HISTORY.HISTORY Record Manager} = "RJ Reynolds" and
{VRP_CONTACT_HISTORY.HISTORY Created On} in DateTime (2009, 11, 01, 0, 0, 0) to DateTime (2009, 11, 30, 0, 0, 0) and
{VRP_CONTACT_HISTORY.HISTORYTYPE ACTIVITYTYPEID} = 1019
 
I thought I could do a reverse on the HISTORYTYPE ACTIVITYTYPEID saying not equal to 1019, but it gave me nothing. I tried adding in a isnull statement, but it still wouldn't make the other customers without history show.
 
P.S. These tables are from an ACT database.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Nov 2009 at 10:20am
Select statements can change your outer join to an inner join and that was what I was trying to figure out and what I think is happening.
using the fields from the VRP_CONTACT_HISTORY.HISTORY table in the select statment will make the outer join moot (hence omitting the NULL records).
get rid of the last 2 parts and alter the second part (in red below) to point to the same field in the VRP_CONTACT table not the history table.
{VRP_CONTACT.CONTACT ID/Status} like "*Prospect*" and
{VRP_CONTACT_HISTORY.HISTORY Record Manager} = "RJ Reynolds"
 
 
Now see if you get your list of 5 people correctly. There might be a million rows as long as it is only the 5 correct 'people'.
If so make a formula field to flag your contacted people as 'ContactType';
if isnull({VRP_CONTACT_HISTORY.HISTORY Created On}) then "No Contact" else if {VRP_CONTACT_HISTORY.HISTORY Created On} in DateTime (2009, 11, 01, 0, 0, 0) to DateTime (2009, 11, 30, 0, 0, 0) and
{VRP_CONTACT_HISTORY.HISTORYTYPE ACTIVITYTYPEID} = 1019 then "Contact" else "No Contact"
Group on Rj Reynolds fields then the @ContatcType field, suppress the details and you should see your report close to what you want. You can tweak it to make it visually as you want.
 


Edited by DBlank - 20 Nov 2009 at 12:18pm
IP IP Logged
rkh7437
Newbie
Newbie


Joined: 06 Jul 2009
Online Status: Offline
Posts: 13
Quote rkh7437 Replybullet Posted: 20 Nov 2009 at 12:17pm
Thank you for your help. I will try it out on Monday. I really appreciate it.
IP IP Logged
rkh7437
Newbie
Newbie


Joined: 06 Jul 2009
Online Status: Offline
Posts: 13
Quote rkh7437 Replybullet Posted: 23 Nov 2009 at 6:20am

You are awesome! Thank you. It almost works. How do I get rid of million rows and get it down to 1 row per company? If I suppress the details, everything goes away. I tried to suppress the duplicates and I get one line each where the company is listed, but I can't get rid of the rest below it.

I really appreciate all of your help!
 
Rhonda
IP IP Logged
rkh7437
Newbie
Newbie


Joined: 06 Jul 2009
Online Status: Offline
Posts: 13
Quote rkh7437 Replybullet Posted: 23 Nov 2009 at 6:26am
I spoke too soon. I just saw that all of the contacts are listed under the No Contact section, even though there is one that is also listed under the Contact section.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version 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.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.