Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
SolvedPrivate

Certain Functions Dont Work Between Crystal Reports 2011 and Crystalreports.com

Posted on 2013-11-05
10
Medium Priority
?
71 Views
Last Modified: 2016-02-17
Hi,
 
I work with Crystal Reports 2011 and post to our crystalreport.com account to connect to salesforce and refresh the data. I have run into a problem when i use the IN and INSTR function in my report filter.
 
The fields I am filtering are multi-picklist and when it's just one selected, it works fine, but when multiple options are selected it doesn't work, it drops the records. This only happens when i run it in Crystalreports.com, it works fine in the program on my desktop. Am i missing something?
 
InStr ({Field1}, "Work In Progress")>0 and
InStr ({Field3}, "On Hold")>0
 
"Work In Progress" IN {Field1} and
"On Hold" IN {Field3}
 
Also tried to add TOTEXT() and CStr()
0
Comment
Question by:Angelmar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
10 Comments
 
LVL 35

Expert Comment

by:James0628
ID: 39626964
It seems like those formulas should work, although I haven't used Crystalreports.com, so that's a complete unknown to me (I've heard of it, but that's about it).

 Are you using a parameter?  The examples that you posted just look for two specific strings, "Work In Progress" and "On Hold".

 If you're using a parameter, does it allow multiple values?  Is that what you meant by "multi-picklist" and "multiple options"?  Actually, I'd expect you to get an error if you tried to use something like InStr with a multi-value parameter, so I guess "multi-picklist" probably meant something else.

 James
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39627874
As to why these specific functions don't work, I don't know.  That being said, crystalreports.com is an old product (it's basically a webified version of Enterprise XI R2).  The official FAQ recommends using Crystal Reports XI R2 with it.  If you're using Crystal Reports 2011 against it, I can see that causing problems, although those issues should be limited to features that don't exist in XI R2.

If you have an active salesforce.com account then you should have support (at least through salesforce.com, if not through SAP).  I would recommend you contact your appropriate support channel.

Also, I have the same question as James.  You specifically stated "fields I am filtering are multi-picklist", which implies you're filtering data with a parameter, but your sample code doesn't reference parameters.  It's possible you're simply not filtering correctly.
0
 

Author Comment

by:Angelmar
ID: 39627996
Hi,

Thanks for your comments. This report does not have any parameter fields. It is pulling from the database directly from sf.com and the multipick "fields" shows up as a string[4099] field. I am using those string fields to filter.

I initially thought it was from selecting more than one value, but now im not sure why it's not working. The field shows the data as text when i add the fields to the report. Any other function I could try?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 35

Expert Comment

by:James0628
ID: 39629760
Is the data being filtered based on some user input (like selecting a value from a list), or is the filter always the same (ie. you are always looking for the records where one field contains "Work In Progress" and another field contains "On Hold") ?

 James
0
 

Author Comment

by:Angelmar
ID: 39630406
Yes, I am always looking for a field that contains  "Work In Progress" within the string and another field contains "On Hold" within the string.  The field will not just contain that value, but multiple values ex: "Plan A; Plan B; Work In Progress; Plan C"
0
 
LVL 35

Expert Comment

by:James0628
ID: 39630520
OK.  Then the formulas you posted should work.  They'll only be true if both conditions are met (the first field contains "Work In Progress" and the second field contains "On Hold"), and the case may have to match (eg. "On Hold" may not be the same as "on hold").

 If you're only looking for the records where the two fields both contain those strings, and the case isn't an issue, then I don't know why you wouldn't get some records.

 James
0
 

Author Comment

by:Angelmar
ID: 39630689
I agree, it should work esp. since it works in the actual program. Not sure why it's dropping some records. I had to upload to BIONDEMAND and it worked fine there too.

 Not sure if there is another function i could try using instead of IN, CStr, or InStr
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 39633013
I don't think CStr is going to do you any good.  It just converts values to a string, and if the field is already a string, I don't think it does anything.

 For what you seem to be trying to do, I don't think anything else will work any better/different than IN or InStr.

 The only other thing that I can think of at the moment is to try each test separately -- Run the report with just the "Work In Progress" test, and then with just the "On Hold" test -- and see if you get the expected records with each test.  If you do, then maybe the problem has something to do with combining the two tests.  If you don't get the expected records, are the results off for both tests, or just one of them?

 James
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 1000 total points
ID: 39633762
I had to upload to BIONDEMAND and it worked fine there too.

That makes perfect sense, since BI ONDEMAND is basically the new version of CrystalReports.com and is designed to be used with the latest version of Crystal Reports (that you're currently using...). Ultimately, I think you have a compatibility issue coupled with a bug (since InStr definitely was available in XI R2).
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question