Exculsionary report
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8421
Printed Date: 03 May 2024 at 1:00am
Topic: Exculsionary report
Posted By: rkh7437
Subject: Exculsionary report
Date 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!
|
Replies:
Posted By: DBlank
Date 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?
|
Posted By: rkh7437
Date 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.
|
Posted By: rkh7437
Date 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.
|
Posted By: DBlank
Date 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?
|
Posted By: rkh7437
Date 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.
|
Posted By: DBlank
Date 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.
|
Posted By: rkh7437
Date Posted: 20 Nov 2009 at 12:17pm
|
Thank you for your help. I will try it out on Monday. I really appreciate it.
|
Posted By: rkh7437
Date 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
|
Posted By: rkh7437
Date 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.
|
Posted By: DBlank
Date Posted: 23 Nov 2009 at 7:39am
|
Sorry about that. I usually address this kind of thing in a view or stored procedure.
Are those options available or can you write a Command?
|
Posted By: DBlank
Date Posted: 23 Nov 2009 at 1:22pm
|
Here is a way to do it all in crystal. Almost the same as my other suggestion. It will not be sorted alpha though.
Select statment:
{VRP_CONTACT.CONTACT ID/Status} like "*Prospect*" and
{VRP_CONTACT_HISTORY.HISTORY Record Manager} = "RJ Reynolds"
Group on the People field.
Flag your Contacted records
if isnull({VRP_CONTACT_HISTORY.HISTORY Created On}) then 0 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 1 else 0
Now based on the SUM of this flag at teh 'People' group you can mark each one as seen or not. If the SUM(formula, peoplefield)>0 then "Seen" else "Not seen"
Place this ofmrula on the group header next the name, suppress the detail and the footer.
If you want it to order it by Seen and Not Seen you can change your group sort on TOP N of the SUM(formula, people) to TOP 100
|
Posted By: rkh7437
Date Posted: 25 Nov 2009 at 6:29am
|
Hello again! First off, I truly appreciate all of your help!! The not seen/contacted part is what is not working. It is pulling all history that is not in November, including field changes, opportunity history, etc. I think that is why I have such a hard time doing exclusionary reports because if it is not equal to the criteria you are looking for, it pulls everything else. So if there is any history on the customers not equal to the criteria (which they all have), they are listed under the not contacted.
Thank you again!
Rhonda
|
Posted By: DBlank
Date Posted: 25 Nov 2009 at 8:36am
|
I am going to be out over the holiday here so not sure if we can get this before then but I will try and check in with you.
Basically what you need are 2 source tables that are filtered before you get to crystal. You can also do this in a Command if you get creative with the SQL query.
The first table you need a list of only the Employees and each of the contaqct names that you want to check against.
The second is a list of employees with contact names that had contact in your date range.
Now you just outerjoin them together.
IN SQL it would be easy to make 2 views that did each part then you use these views as the crystal report sources and left outerjoin in crystal.
In a Crystal Command you could try and write a SQL statement to outerjoin one query to another query.
Are these options for you?
|
Posted By: rkh7437
Date Posted: 25 Nov 2009 at 8:51am
|
I'm not in a great hurry, after the holiday will work. Right now, I ran a subreport that listed the employee's customers that they are going to contact. Below that, I ran a subreport that listed the customers that were contacted with the history notes. I did a subreport for each employee and one for each history.
I'm not sure what you mean by a crystal command. I'm sorry for being stupid. I don't think that ACT! tables allow you to work that way.
|
Posted By: DBlank
Date Posted: 25 Nov 2009 at 9:14am
|
In the Database Expert under your connection there may be an Add Command option. This allows you to write a SQL query. Not sure how that would work with your DB though.
|
Posted By: rkh7437
Date Posted: 25 Nov 2009 at 10:03am
|
I do have that option! I'm not good with SQL, but it's good to see that I have that option.
|
Posted By: DBlank
Date Posted: 25 Nov 2009 at 10:08am
|
Break it into parts.
Think of each table as a query. Define each query with conditions to get the data limited to what you need.
Write the SQL statement to outerjoin the querys together.
You calso may want to test a simple outerjoin command works first before getting complex. No need to waste time it that fails. Some data types won't support outer joins (e.g. excel).
|
|