locked
Opportunities Report - how to track OPEN opportunities month over month? RRS feed

  • Question

  • Hi all,

    I wanted to create a report to do 4 things and was looking for some feedback. I want the report to be Month over Month and report on:

    1) New Opportunities per each month
    2) Won Opportunities per each month
    3) Lost Opportunities per each month
    4) Open Opportunities - this is what I am struggling with.. because on a given month, I can have open opportunities from prior month and I am trying to understand how would this work in CRM. Would I need to use goals? Example

    January: 2 open and stay open
    February: 2(from Jan) + 3(new for Feb) = 5 open in February
    March: 1(from Jan) +2(from Feb) + 5(new for March) = 8 open in March

    I would probably create this in Visual Studio and want to have an option to run report anytime. So for example if I run report on November 6th, it will have data for previous months and for November up to Nov 6th.

    Any advice would be much appreciated! I also added rough image what I am hoping to achieve here.

    Dan

    Thursday, November 6, 2014 3:55 PM

All replies

  • Hi,

    If you want to take the open opportunities for each month one logic I thought of is Say if you are starting from the month Jan then the open opportunities of Jan will have the created on date as Jan and the new open opportunities of Feb will have the created on date as Feb and march new will have created on date as march and so on... So based on the Created on date and status open opportunities if you query you will be able to get the report i guess.

    To have a dynamic reports you can go for SSRS reports.

    Rekha.J

    Friday, November 7, 2014 6:27 AM
  • HI Dony,

    You have to create a SSRS report for this requirement.

    Write a sql query like this:

    select 
    case Month1
    when 1 then (select count(opportunityid)as count1
    			from filteredOpportunity
    			where statecodename = 'Open'and datepart(month,createdon) = 1)
    			
    when 2 then (select count(opportunityid)as count1
    			from filteredOpportunity
    			where statecodename = 'Open'and 
    					(datepart(month,createdon) = 1 OR datepart(month,createdon) = 2))
    when 3 then (select count(opportunityid)as count1
    			from filteredOpportunity
    			where statecodename = 'Open'and 
    					(datepart(month,createdon) = 1 OR datepart(month,createdon) = 2
    					OR datepart(month,createdon) = 3))
    when 4 then (select count(opportunityid)as count1
    			from filteredOpportunity
    			where statecodename = 'Open'and 
    					(datepart(month,createdon) = 1 OR datepart(month,createdon) = 2
    					OR datepart(month,createdon) = 3 OR datepart(month,createdon) = 4 ))
    end as OpenOpportunities,
    Month1
    from 
    (
    select datepart(month,createdon) as Month1
    from filteredOpportunity
    where statecodename = 'Open'
    group by datepart(month,createdon)
    ) AS derivedTable 
    

    Regards, Saad


    • Edited by Mohd Saad Friday, November 7, 2014 8:56 AM
    Friday, November 7, 2014 8:51 AM
  • One more solution. create a sql query like this

    select count(opportunityid) as OpenOpportunities ,datepart(month,createdon) as Month1
    from filteredOpportunity
    where statecodename = 'Open'
    group by datepart(month,createdon)

    In Series Set this Expression

    =RunningValue(Fields!OpenOpportunities.Value, Sum, "Your DataSet Name")


    Regards, Saad

    Friday, November 7, 2014 9:10 AM