Crystal Reports Exculsionary report

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

Topic: Exculsionary report
	<< Prev Page of 2

Author	Message
DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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
	IP Logged

rkh7437 Newbie Joined: 06 Jul 2009 Online Status: Offline Posts: 13	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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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?
	IP Logged

rkh7437 Newbie Joined: 06 Jul 2009 Online Status: Offline Posts: 13	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.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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.
	IP Logged

rkh7437 Newbie Joined: 06 Jul 2009 Online Status: Offline Posts: 13	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.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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).
	IP Logged

<< Prev Page of 2

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.