10-16-2013 01:13 AM
I have created an advanced query to filter the contact opportunities. The type of query is a contact query.
I am searching for records that are older than 200days but added within the last 400 days and the product name is "PAT SURVEY".
I am using the report "Opportunities by Contact" to show the results.
But, if the query finds 1 record matching the criteria, it will show every single record for that contact, even ones that don't match the criteria.
Am i doing something wrong?
10-16-2013 01:17 AM
This is how i setup my query:
Search for - Contact
Type - Opportunity Field Name - Open Date Operator - Older than Value - 200
Type - Opportunity Field Name - Open Date Operator - Within Last Value - 400
Type - Opportunity Field Name - Product Operator - equal to Value - PAT survey
10-16-2013 08:04 AM
The query is working fine. The problem is the structure of the Report. The report first looks a the Contact, then lists all opportunities. You have limited ability to filter what opps you can view. Here are some options.
1. After the running the Lookup, export the the results to Excel. In Excel you can slice and dice the opportunities any way you want.
2. Purchase an ACT! Add-On report writer like Stonefield Query. These programs offer much more control over the output of your report.
3. Install the ACT Reader. This will give you the ability to view the underlying opportunity table. I like viewing the data both in Excel and Access. Here's a KB to get you started 17538.
Tom Koller, "The ACT Guy"
10-16-2013 08:21 AM
The problem isn't the query, but you can't carry the query results into the oppertunity subreport of a contact based report. However, there are filters that you can use on the oppertunity subreport. The report filters for date range in oppertunities uses the estimated close date for any open oppertunity and the actual close date for all other oppertunity status settings. It's likely that if you use the proper report filter settings you won't even need to do the query.
10-16-2013 08:31 AM
the query would not help. You'll get all the oppertunities for the contact with in the date range. Your best bet for that would be a third party report editor like the Durkin Action reports.
10-16-2013 12:26 PM
The product Roy is referring to is named Impact Report Designer v5. It comes with over 60 pre-designed reports. You can create your own or duplicate an existing report to modify.
Here is the link: http://www.durkincomputing.com/
-- Jim Durkin