locked
30/60/90 day Opportunity Report RRS feed

  • Question

  • Hi there,

    I am trying to create a report that shows opportunities with an estimated close date of 30/60/90 days from today. I have used the Advanced view to show what is closing this month and what is closing next month, but I can't figure out how to show what is going to close between 60-90 days from today. Any help is greatly appreciated.

    Martin

     

    Sunday, March 18, 2012 10:16 PM

Answers

  • Hi Martin,

    Perhaps a custom report can assist you. Here is a sample SQL script that you can use/modify for your custom report.

    The calculated "Days_Group" colume is the days range that you're trying to find. Negative numbers means the Estimated Close Date have passed. 0 means 0-30 days, 1 means 31-60 days, 3 means 61-90 days and so on.

    Hope this give you some ideas.

    select * from
    	(select	accountidname, name, estimatedclosedate,
    			DATEDIFF(day, getdate(), estimatedclosedate) as DaysToEstClose,
    			(DATEDIFF(day, getdate(), estimatedclosedate)/30) as Days_Group
    	from	filteredopportunity
    	where	statuscodename = 'In Progress')t
    Group by Days_Group, accountidname, name, estimatedclosedate, DaysToEstClose


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    • Marked as answer by Martin Evers Thursday, March 22, 2012 7:50 PM
    Monday, March 19, 2012 1:07 AM

All replies

  • Hi Martin,

    Perhaps a custom report can assist you. Here is a sample SQL script that you can use/modify for your custom report.

    The calculated "Days_Group" colume is the days range that you're trying to find. Negative numbers means the Estimated Close Date have passed. 0 means 0-30 days, 1 means 31-60 days, 3 means 61-90 days and so on.

    Hope this give you some ideas.

    select * from
    	(select	accountidname, name, estimatedclosedate,
    			DATEDIFF(day, getdate(), estimatedclosedate) as DaysToEstClose,
    			(DATEDIFF(day, getdate(), estimatedclosedate)/30) as Days_Group
    	from	filteredopportunity
    	where	statuscodename = 'In Progress')t
    Group by Days_Group, accountidname, name, estimatedclosedate, DaysToEstClose


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    • Marked as answer by Martin Evers Thursday, March 22, 2012 7:50 PM
    Monday, March 19, 2012 1:07 AM
  • Hi Eric,

    thanks for the fast reply. Do you know if I can do this using FetchXML, instead of SQL? Sorry, I am still new to this...

    Martin

    Monday, March 19, 2012 5:54 PM
  • Hi Martin,

    I don't think you can archive this with fetchXML (maybe other forum contributors have further ideas).

    Another alternative is to export the Open Opportunities to a Dynamic Worksheet and do your formating in Excel.


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Tuesday, March 20, 2012 5:16 AM