I have been trying to import data from Salesforce using the Salesforce connector. The data is importing fine although I have one field that I need to filter. I am importing sales data for sales opportunities. Each record is an opportunity and these opportunities have a percentage complete, amount, an ID for the company they belong to and a company name.
In order for my graph to work I need to be able to Sum all of the pipeline sales for each company and then display them on a bar stacked above each other. I was able to get this to work fine with data imported from excel with the sales sorted in to percentage columns. I then used flags to sort it so that I could have a pipeline, target sales and sales to date bar on the graph.
I need to be able to import my data from Salesforce and depending on the percentage, put the amount in to a column under the percentage complete. I have tried to do this in he load script although I am having problems as Salesforce uses SOQL which I am not all that familiar with and seems very limited compared with regular SQL.
I cant make changes to how my data is presented at the source as it is a complex salesforce site, It would be useful if I could just import a report instead as I could sort the data on salesforce however as far as I am aware this is not possible.
Here is my code so far:
CUSTOM CONNECT TO: *******
Account:
Load
Name,
Id as AccountId
SQL SELECT Name, Id
FROM Account;
Opportunity:
Load
Probability,
AccountId,
Amount
SQL SELECT Probability, AccountId, Amount
FROM Opportunity
WHERE Probability != 0 and Amount > 0 and Amount != NULL and Probability != NULL;
Notice above that I am trying to remove Null values too as I see to get lots when importing. This validation does not seem to remove them. Apologies if there are errors in the code I had to type it out as it would not copy and paste in.
Right I've figured out how to sort the values in to pipeline sales (less than 100%) and complete sales (100%) by using an if statement on load. I have then sorted all my pipeline sales in to percentages on the graph by using a validation in the chart expression. The only problem I have now is sorting the bars on the bar chart. I am using stacked bars. I want to have different bars for pipeline sales, complete sales and target sales sorted by the dimension Company name. I have all of the following in my chart but they have all stacked on top of each other. In the past I have sorted this by using a chart dimension called Flag and putting a reference to the Flag dimension in the load.
I cannot get this to work as some of the data I need to split apart comes from the same table. I cannot reference Flag twice in the same table as I get an error on load. I have tried splitting the data in to different loads but SOQL doesn't seem to like loading from the same table twice.
Is there any other way to split stacked bars in a bar chart?
