CR 2008 and Salesforce.com

[Dross]

I am trying to develop a report for a customer that links to Salesforce.com. It has a few tables that are not high volume tables (less than 500,000 rows) and a couple advanced formulas but not too hard.

When I run the report, my Crystal seems to hang there and never return. I don't get an error or anything, just seems to stop responding. I let it run all night with nothing ever coming back. Using Salesforce is new to me and from what I can tell I am very restricted on what I can do. Even the view SQL does't really show me what is going on.

Does anyone have experience with Salesforce and Crystal that can lend some ideas to what may be happening?

 

[lbass]

First step would be to compare the selection formula (report->selection formula->record) with the SQL query generated (database->show SQL query) to see if all of your criteria are passing. The "Not responding" message occurs during the accessing database phase--while it is working, but it shouldn't take hours.

-LB

 

[Dross]

I normally to that with any other data connection so I can run it in something like SQL Mgt Studio but SalesForce doesn't work that way. If I view the SQL query it just shows multiple select statements but not a full query and there is no way to run it independent of Crystal. that is why I was hoping someone who has Crystal experience in SalesForce had a couple ideas.

 

[lbass]

If it is showing multiple queries, then that means tables aren't linked to each other. Maybe someone here has used SalesForce, but maybe not. In working with SalesForce (I'm assuming as a datasource), are you working directly with the tables to do the linking, etc.?

-LB

 

[Dross]

I link the tables in Crystal just like I was using SQL Server. It has it's own custom Salesforce driver in the datasources so it is hard to tell what is happening "behind the scenes".

The tables are linked together, and INNER joined to hopefully speed up the selection.

 

[lbass]

Can you post the SQL query generated?

-LB

 

[Dross]

Sure, but it won't be until later this afternoon.

 

[Dross]

Here is what is in the show SQL. I let this thing run all day at home and finally got a Failed to retreive data error, exceeds limit. It looks like Salesforce brings everything client side then shows what you want?

http://www.salesforce.com

SELECT Cost__c, DP_Profit_Margin__c, ThirdParty_Tracked_Placement__c
FROM SAC_Stat__c
EXTERNAL JOIN SAC_Stat__c.ThirdParty_Tracked_Placement__c={?

http://www.salesforce.com:

ThirdParty_Tracked_Placement__c.Id}

http://www.salesforce.com

SELECT Social_Ads_Campaign__c, CM_Delivery_Partner__c, Id
FROM ThirdParty_Tracked_Placement__c
EXTERNAL JOIN ThirdParty_Tracked_Placement__c.Id={?

http://www.salesforce.com:

SAC_Stat__c.ThirdParty_Tracked_Placement__c} AND ThirdParty_Tracked_Placement__c.Social_Ads_Campaign__c={?

http://www.salesforce.com:

Social_Ads_Campaign__c.Id} AND ThirdParty_Tracked_Placement__c.CM_Delivery_Partner__c={?

http://www.salesforce.com:

Contact.Id} AND ThirdParty_Tracked_Placement__c.CM_Delivery_Partner__c={?

http://www.salesforce.com:

Contact.Id} AND ThirdParty_Tracked_Placement__c.CM_Delivery_Partner__c={?

http://www.salesforce.com:

Contact_LinkedName.Id}

http://www.salesforce.com

SELECT Name, CM_Network__c, Action_Quantity__c, Action_Price__c, Delivered_Quantity__c, Type__c, Media_Cost__c, End_Date__c, Start_Date__c, DMA__c, Age_Range_Low__c, Age_Range_High__c, Interests__c, Approximate_Ethnicity_Targeting__c, Delivery_Pacing__c, Pacing_Description__c, States__c, Countries__c, Gender__c, Network_Type__c, Network_Platform_Rate__c, Budget__c, Sum_Cost__c, Id, CM_Agency__c, CM_Brand__c, CM_Sales_Person__c
FROM Social_Ads_Campaign__c
EXTERNAL JOIN Social_Ads_Campaign__c.Id={?

http://www.salesforce.com:

ThirdParty_Tracked_Placement__c.Social_Ads_Campaign__c} AND Social_Ads_Campaign__c.CM_Agency__c={?

http://www.salesforce.com:

Contact.Id} AND Social_Ads_Campaign__c.CM_Brand__c={?

http://www.salesforce.com:

Contact.Id} AND Social_Ads_Campaign__c.CM_Sales_Person__c={?

http://www.salesforce.com:

Contact.Id} AND Social_Ads_Campaign__c.CM_Agency__c={?

http://www.salesforce.com:

Contact.Id} AND Social_Ads_Campaign__c.CM_Brand__c={?

http://www.salesforce.com:

Contact.Id} AND Social_Ads_Campaign__c.CM_Sales_Person__c={?

http://www.salesforce.com:

Contact.Id}

http://www.salesforce.com

SELECT DP_Payment_Type__c, Optimization_Fee__c, Spend_Platform__c, Id
FROM Contact
EXTERNAL JOIN Contact.Id={?

http://www.salesforce.com:

ThirdParty_Tracked_Placement__c.CM_Delivery_Partner__c} AND Contact.Id={?

http://www.salesforce.com:

ThirdParty_Tracked_Placement__c.CM_Delivery_Partner__c} AND Contact.Id={?

http://www.salesforce.com:

Social_Ads_Campaign__c.CM_Agency__c} AND Contact.Id={?

http://www.salesforce.com:

Social_Ads_Campaign__c.CM_Brand__c} AND Contact.Id={?

http://www.salesforce.com:

Social_Ads_Campaign__c.CM_Sales_Person__c} AND Contact.Id={?

http://www.salesforce.com:

Social_Ads_Campaign__c.CM_Agency__c} AND Contact.Id={?

http://www.salesforce.com:

Social_Ads_Campaign__c.CM_Brand__c} AND Contact.Id={?

http://www.salesforce.com:

Social_Ads_Campaign__c.CM_Sales_Person__c}

http://www.salesforce.com

SELECT Name, Id
FROM Contact
EXTERNAL JOIN Contact_LinkedName.Id={?

http://www.salesforce.com:

ThirdParty_Tracked_Placement__c.CM_Delivery_Partner__c}

 

[lbass]

Can you clarify how you linked these tables in the database expert? Did you have multiple starting points? Or were the tables all linked together?

-LB

 

[Turkbear]

Hi,
Apparently the Saleforce tables are seen as being different datasources not just different tables in the same datasource.
This item about EXTERNAL JOINS seems to indicate that

In the book "The Complete Reference - Crystal Reports 2008" by George Peck, it says:

If you have added tables from more than one database type to your report, the SQL Query dialog box will show queries for all database types, including "External Link" notations. The reference to an External Link indicates that Crystal Reports will perform the link locally after all queries have processed on their respective database servers.

By only performing the link AFTER returning the data from each datasource, performance is greatly degraded.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dross]

Thanks for the info. Turns out my 1 report maxed out the allowable limit of the Salesforce API calls with 27,000 calls. I am looking into having them do a daily extract to a normal database for reporting. They want to try to keep it open source and on the web, any suggestions?