We use SQL to get our data out of the product catalog because of the pains you mentioned.
Off the top of my head - doesn't meet your model/version or date requirement... might want to add the tier info... select product_name from pct_product_catalog minus select distinct product_name from hpd_help_desk hth, Tony From: "Muhlethaler, Laurie" <lmuhletha...@firstrepublic.com> To: arslist@ARSLIST.ORG Date: 07/22/2009 12:13 PM Subject: Stuck - need help generating zero-call report Sent by: "Action Request System discussion list(ARSList)" <arslist@ARSLIST.ORG> ** Hi Listers ~ management has asked me to provide a report listing all products / versions that have not been referenced in Incidents within a specified date range. I?ve tried creating several outer-joins as follows: Product and Model/Version outer join (FRB:ProductVersionLookup): Primary - PCT:Product Catalog Secondary - PCT:Product Model/Version Criteria ? $Product ID$ = ?Product ID? * Not all products have corresponding version data Product, Model/Version and Incident outer join: Primary - FRB:ProductVersionLookup (1st join) Secondary ? HPD:Help Desk Criteria ? $Product Name$ = ?Product Name? One of the problems I?m running into is that for many Incidents, the Version information is not provided but since my first join form joins based on product ID, there is no entry for NULL versions so these obviously would not be output in the Crystal Report. Aside from requiring version information, I?m assuming I have to bypass the version information in the report? I?ve also thought that maybe a Crystal Report on the PCT:Product Catalog form with a sub-report of the Incidents (suppressing those with a count >= 1) would work but I wanted to see if I could squeeze the version information in there as well? Any ideas? Thank you in advance. Windows Server 2003 SQL Server 2005 7.1.00 Patch 002 AR Server, MT, Admin, Client 7.0.03 Patch 006 Service Desk Asset Management Laurie Muhlethaler First Republic Bank Remedy Developer / Administrator 415.364.4436 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This message cannot be guaranteed to be secure or error-free. First Republic Bank and its related entities do not take responsibility for, or accept time-sensitive instructions sent by email including orders, funds transfer instructions or stop payments on checks. All instructions of this nature must be handled by direct communication, not email. We reserve the right to monitor and review the content of all email communications sent or received. Emails sent to or from this address may be stored in accordance with regulatory requirements. First Republic Bank is a Division of Merrill Lynch Bank & Trust Co., FSB _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_ ********************************************************************** CONFIDENTIALITY NOTICE: This is a transmission from Kohl's Department Stores, Inc. and may contain information which is confidential and proprietary. If you are not the addressee, any disclosure, copying or distribution or use of the contents of this message is expressly prohibited. If you have received this transmission in error, please destroy it and notify us immediately at 262-703-7000. CAUTION: Internet and e-mail communications are Kohl's property and Kohl's reserves the right to retrieve and read any message created, sent and received. Kohl's reserves the right to monitor messages by authorized Kohl's Associates at any time without any further consent.
<<image/jpeg>>
