Thanks Susan, When I modified the report, I no longer got the error, but I am still not getting any rows returned. Is anyone else able to use IsNull or other SQL functions in their record selection forumula on ARS 7.1, with WUT 7.5?
Here is the current test query (from the Select Expert in Crystal Developer): {HPD_Help_Desk.Assigned_Group} = "PQ-Infrastructure Engineering" and IsNull({HPD_Help_Desk.Assignee}) Once again, note that other Crystal reports are fine. Kelly Logan, Sr. Systems Administrator (Remedy), GMS ProQuest | 789 E. Eisenhower Parkway, P.O. Box 1346 | Ann Arbor MI 48106-1346 USA | 734.997.4777 kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com> www.proquest.com ProQuest...Start here. 2010 InformationWeek 500 Top Innovator P Please consider the environment before printing this email. This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender, and delete the message from your computer. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Susan Palmer Sent: Tuesday, November 08, 2011 11:17 AM To: arslist@ARSLIST.ORG Subject: Re: Search for null value in Crystal Report ** Kelly, We see this error when the ODBC connection gets hosed. Really has nothing to do with the IsNull statement. We reconfigure the ODBC connection, sometimes have to refresh or recreate the Common File for Crystal on the PC. Then stand, turn 3 times to the left, and pray. At worst it has taken an uninstall of Remedy and reinstall. It's unexplainable, no pattern of who or when it happens to, can be working fine and then not. Generally once the error is received you cannot run any reports initiated via an Active Link. But they do run in the actual Crystal development application which most do not have. Hope that gives another direction to look in. Susan On Fri, Nov 4, 2011 at 5:28 PM, Luttmann, Michael W Mr CTR DISA CD553 <michael.luttmann....@csd.disa.mil<mailto:michael.luttmann....@csd.disa.mil>> wrote: Whenever I use the IsNull() function in Crystal, I put a return value (=TRUE or =FALSE). EX: IF IsNull({table.field})=TRUE THEN [do something] Mike Luttmann DISA Remedy Engineer 303-224-1645<tel:303-224-1645> -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Jason Miller Sent: Friday, November 04, 2011 13:49 To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Search for null value in Crystal Report ** Have you tried on different computers? I am wondering if it is an ODBC issue/setting? On Fri, Nov 4, 2011 at 12:32 PM, Logan, Kelly <kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com>> wrote: ** No. :^) Kelly Logan, Sr. Systems Administrator (Remedy), GMS ProQuest | 789 E. Eisenhower Parkway, P.O. Box 1346 | Ann Arbor MI 48106-1346 USA | 734.997.4777<tel:734.997.4777> kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com> www.proquest.com<http://www.proquest.com/> ProQuest...Start here. 2010 InformationWeek 500 Top Innovator P Please consider the environment before printing this email. This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender, and delete the message from your computer. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Jason Miller Sent: Friday, November 04, 2011 3:31 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Search for null value in Crystal Report ** Hmmm. I was thinking it may have already been set to Yes. Does the error go away when setting it to Yes? On Fri, Nov 4, 2011 at 12:15 PM, Logan, Kelly <kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com>> wrote: ** The 'Override Query in Report' field is set to "No", but setting it to "Yes" overrides the query in the report so that none of the parameters in the report are used. So I have the original parameters (other than the Assignee check) in the Crystal report and the 'Assignee' check in the report 'Base Qualification'. Kelly Logan, Sr. Systems Administrator (Remedy), GMS ProQuest | 789 E. Eisenhower Parkway, P.O. Box 1346 | Ann Arbor MI 48106-1346 USA | 734.997.4777<tel:734.997.4777> kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com> www.proquest.com<http://www.proquest.com/> ProQuest...Start here. 2010 InformationWeek 500 Top Innovator P Please consider the environment before printing this email. This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender, and delete the message from your computer. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Jason Miller Sent: Friday, November 04, 2011 3:10 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Search for null value in Crystal Report ** No problem at all. In the Report record is "Override Query in Report" set to No? Do you need to be able to specify the query from Remedy or will the query be controlled completely by Crystal? We had some broken (web) reports in the last few months with the query embedded in the reports. We had to change it from Yes to No before the reports would start working again. I think when these reports were build way back in the 6.x days the Override Query in Reports option behaved a little differently and nobody noticed/reported they broke somewhere along the line from 6.x -> 7.0 -> 7.1 -> 7.5. Jason On Fri, Nov 4, 2011 at 11:43 AM, Logan, Kelly <kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com>> wrote: ** Hello Jason, and thanks for working with me on this. :^) The report runs in CR Developer (version 11.0.0.1282), and is attached to the 'Report' form. I have also confirmed that the same report without the IsNull() statement (replaced with 'Status' = "Assigned" for testing) works fine, so it's not the 'Report' entry or something else in the report file itself. In the meantime, I'm using a workaround by setting the 'Base Qualification' for the report entry to: 'Assignee+' = $\NULL$ Kelly Logan, Sr. Systems Administrator (Remedy), GMS ProQuest | 789 E. Eisenhower Parkway, P.O. Box 1346 | Ann Arbor MI 48106-1346 USA | 734.997.4777<tel:734.997.4777> kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com> www.proquest.com<http://www.proquest.com/> ProQuest...Start here. 2010 InformationWeek 500 Top Innovator P Please consider the environment before printing this email. This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender, and delete the message from your computer. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Jason Miller Sent: Friday, November 04, 2011 2:17 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Search for null value in Crystal Report ** Hello again, Does the report run fine in CR Developer? How is the report configured in Remedy? I am assuming it is an attachment in the Report form. Jason On Fri, Nov 4, 2011 at 10:44 AM, Logan, Kelly <kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com>> wrote: ** Curiouser and curiouser - I tried creating a simpler report as you did with the query of: IsNull({HPD_Help_Desk.Assignee}) and {HPD_Help_Desk.Assigned_Group} = "PQ-Infrastructure Engineering" And got "Unknown Database Connector Error" when I tried to run it from Remedy. It is perhaps also worth noting that we are using Remedy User 7.5 patch 004. Kelly Logan, Sr. Systems Administrator (Remedy), GMS ProQuest | 789 E. Eisenhower Parkway, P.O. Box 1346 | Ann Arbor MI 48106-1346 USA | 734.997.4777<tel:734.997.4777> kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com> www.proquest.com<http://www.proquest.com/> ProQuest...Start here. 2010 InformationWeek 500 Top Innovator P Please consider the environment before printing this email. This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender, and delete the message from your computer. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Jason Miller Sent: Friday, November 04, 2011 1:14 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Search for null value in Crystal Report ** Hi Kelly, ISNULL should to it. I just built a quick report (against Help Desk 6) to validate using the following query: {HPD_HelpDesk.Arrival_Time} > CurrentDate -1 and ISNULL ({HPD_HelpDesk.Assigned_To_Individual_}) Jason On Fri, Nov 4, 2011 at 8:37 AM, Logan, Kelly <kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com>> wrote: ** Hopefully an easy question. I would like to create a Crystal report to show currently unassigned tickets, but I am having trouble finding the right format to identify tickets where 'Assignee' is null. Here is what I have tried so far: IsNull() - This works in Crystal, but Remedy returns no entries. = "" - This causes an error in Crystal [Database Vendor Code: 300], which I can't find any details on. Note that = "" works fine in an IIf statement I have in another report. = " " - Finds no entries. I have tried these with the formula editor null treatment set to 'exception' and 'default', with no change in results. I also don't have the standard Remedy reports (most were removed) to see if there are any functioning examples of reports that use a check for a null value. Several searches on BMC support also yielded no information on any way to search for a null value in a Crystal report. Is there a functional way to search for a null value in a Crystal Report? Kelly Logan, Sr. Systems Administrator (Remedy), GMS ProQuest | 789 E. Eisenhower Parkway, P.O. Box 1346 | Ann Arbor MI 48106-1346 USA | 734.997.4777<tel:734.997.4777> kelly.lo...@proquest.com<mailto:kelly.lo...@proquest.com> www.proquest.com<http://www.proquest.com/> ProQuest...Start here. 2010 InformationWeek 500 Top Innovator P Please consider the environment before printing this email. This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender, and delete the message from your computer. _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org<http://www.arslist.org/> attend wwrug12 www.wwrug12.com<http://www.wwrug12.com/> ARSList: "Where the Answers Are" _attend WWRUG12 www.wwrug.com<http://www.wwrug.com> ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"