SolvedPrivate

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

Posted on 2013-11-05
10
34 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
  • 4
  • 3
  • 2
10 Comments
 
LVL 34

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
 
LVL 34

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 34

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 34

Assisted Solution

by:James0628
James0628 earned 250 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 250 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query 18 80
mySql Syntax 7 32
2 Access tables, count verbiage used 6 20
Very interesting Access query problem. 13 43
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now