function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Srividya SharmaSrividya Sharma 

SOQL for the Company Performance Dashboard

Hi

 

I recently created a developer account at force.com. I added the sample dashboard provided - Company performance dashboard.

 

I want to know the SOQL used in these reports. Can anyone post the SOQL for these or point me where to find them?

 

For instance, the SOQL for the Closed Sales To Date report or the Opportunity by Stage and Type report.

 

I am not sure if this is the right board to post this message. In case it is wrong, forgive me / direct it to the correct board.

 

 

Thanks a lot.

Srividya

Best Answer chosen by Admin (Salesforce Developers) 
jhurstjhurst

Srividya,

 

The reports are all standard queries that are grouped in the report builder.  The SOQL behind the data is relatively simple, you will just have to sort and group the results depending on how you want.  So for instance, the "Closed Sales to Date" would be:

 

 

SELECT Account.Name, CloseDate, Amount FROM Opportunity WHERE CloseDate = LAST_N_FISCAL_QUARTERS:2 AND StageName = 'Closed - Won'

 

 

For Pipeline By Stage and Type would be:

 

 

SELECT StageName, Count(ID), Sum(Amount) FROM Opportunity WHERE (CloseDate = THIS_FISCAL_QUARTER OR CloseDate = NEXT_FISCAL_QUARTER) AND StageName = 'Closed - Won' Group By StageName

 

The rest of the reports will have similar queries.  You just have to take the date ranges and groupings as defined.

 

You can find out more in the docs:

 

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_dateformats.htm

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_date_functions.htm

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_groupby.htm

 

Jay

 

All Answers

jhurstjhurst

Srividya,

 

The reports are all standard queries that are grouped in the report builder.  The SOQL behind the data is relatively simple, you will just have to sort and group the results depending on how you want.  So for instance, the "Closed Sales to Date" would be:

 

 

SELECT Account.Name, CloseDate, Amount FROM Opportunity WHERE CloseDate = LAST_N_FISCAL_QUARTERS:2 AND StageName = 'Closed - Won'

 

 

For Pipeline By Stage and Type would be:

 

 

SELECT StageName, Count(ID), Sum(Amount) FROM Opportunity WHERE (CloseDate = THIS_FISCAL_QUARTER OR CloseDate = NEXT_FISCAL_QUARTER) AND StageName = 'Closed - Won' Group By StageName

 

The rest of the reports will have similar queries.  You just have to take the date ranges and groupings as defined.

 

You can find out more in the docs:

 

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_dateformats.htm

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_date_functions.htm

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_groupby.htm

 

Jay

 

This was selected as the best answer
Srividya SharmaSrividya Sharma

Thank you Jay, that really helps.