locked
CRM 4.0 Pipeline Stages and Sales Pipeline Report RRS feed

  • Question

  • I have installed CRM 4.0 and am trying to create some example data that can show business users how to leverage the Sales Pipeline report.  The business has a multi-stage pipeline they would like to see the report grouped by.

     

    I have migrated some opportunity data from another system including the pipeline stage.  I have tried populating both the 'salesstagecode' and 'stepname' fields with the appropriate values but the Sales Pipeline report always shows all opportunities as 'Not Specified' and 'None' as the Pipeline Phase on the Opportunity form.

     

    What is the right way to record pipeline stages so that the report includes them appropriately and it value is displayed on the opportunity form?

     

    Thanks,

    Seth Bates

    sbates@datalan.com

     

    Monday, March 3, 2008 6:28 PM

Answers

  •  

    HI all,

     

    I came across the same problem as listed in this thread. We are trialing MS CRM 4.0 for our company and Sales Pipeline is one the major components we are looking at. After hacking my way through to make the salesstagename filter through to the stepname (Pipeline Phase), I still wasn't able to get the fields to update and also the report to work properly. I noticed 2 things;

    1. The workflow is not instantaneous, so give it 10 seconds or so to update after saving the opportunity.

    2. The sales piple.rdl (report) looks for salesstagename to display, I managed with a little bit of persitence to work out the views that where called by this report and noticed the sales stage names that where in the report that I wanted to display actually had the alias given to them as salesstagecodename. I modifed the report by doing a simple search and replace on all and saved the report.

     

    As the CRM appears to be using SSRS all reports are precompiled you will need to upload the file through the EDIT report options in the reports view. This will upload and recompile the report.

     

    Once I done this I now have the different pipeline stages that I was attempting to get...

     

    Hope this helps. (it worked for me so I hope it works for every one else)..

     

    Regards,

     

     

    (Additional note: this will break the sub report Sales Pipeline detail.rdl so its not a total fix to the problem, but I've had enough for one night)..

     

    Wednesday, June 4, 2008 1:54 PM

All replies

  • Dear Seth,

     

    You have to develop your report for such purpose and then link accordingly.. for that use business intelligence , VS 2005

     

    http://blogs.msdn.com/crm/archive/2006/11/21/sales-pipeline-report.aspx

     

    this will help you.

     

    Regards,

    Imran

     

    http://microsoftcrm3.blogspot.com

     

    Tuesday, March 4, 2008 4:07 AM
    Moderator
  • This link appears to be for CRM 3.0 rather than 4.0.  I also am not looking to develop a customized report, instead leverage the existing report out of the box.  This report obviously pulls Sales Stage from somewhere, the question is...where?  Which field on the OpportunityBase table is driving Sales Stage on this report and which field is driving the Pipeline Phase value displayed on the lower right corner of the Opportunity form?

     

    Tuesday, March 4, 2008 2:57 PM
  • There is a field called Pipeline Phase not included on the Opportunity by default.  Add this field to the form and populate it with name of the Sales Stage.  I think this will give you what you need.

     

    Hope this Helps! 

     

    Tuesday, March 4, 2008 8:25 PM
  • I tried to populate the "Pipeline Phase" but the report is still not working correct.

     

    (A nice little trick: the way i populated the "Pipeline Phase" field was by using a workflow, that updated the field each time the "salesstage" field was changed)

    Thursday, March 6, 2008 9:14 AM
  • Has anyone gotten the out-of-the-box Sales Pipeline report to display correctly when grouped by Sales Stage?  Like the previous poster, I have been able to populate the Pipeline Phase of an opportunity using the salesstage field but the report continues to show all opportunities in a 'Not Specified' group.  Replies from MS and others have said to create a Sales Process but my understanding is that a Sales Process definition is a CRM 3.0 construct and not part of 4.0.

     

    Thanks for your help.

    Tuesday, March 11, 2008 10:52 PM
  • Same problem here. I went through exactly the same process (ie. created a workflow to update the Pipeline Phase field from the salestage field), and even deleted all CRM 3.0 reports that came as duplicates during the upgrade to 4.0. This way I'm sure the right report is running.

     

    Now the Pipeline Phase is showing OK in the lower right corner of the Opportunity form, but alas when I run a report they all show in a 'Not Specified' group.

     

    C'mon Microsoft, this is basic functionality we can expect of any CRM product. It shouldn't be that hard to create such a standard report ...

     

    Wednesday, March 12, 2008 9:04 PM
  • Hey all;
    Any news from Microsoft on this.  It seems that I am not the only one having this problem.  Is this because MS has changed how the Sales Process and Sales Stages work in CRM and simply forgot to update this report?

    THeir silence is ominous and what is more annoying is the bolgs, forums and groups are full of people who have got this problem but there is no answer!!!
    MM
    Thursday, May 22, 2008 9:28 PM
  •  

    HI all,

     

    I came across the same problem as listed in this thread. We are trialing MS CRM 4.0 for our company and Sales Pipeline is one the major components we are looking at. After hacking my way through to make the salesstagename filter through to the stepname (Pipeline Phase), I still wasn't able to get the fields to update and also the report to work properly. I noticed 2 things;

    1. The workflow is not instantaneous, so give it 10 seconds or so to update after saving the opportunity.

    2. The sales piple.rdl (report) looks for salesstagename to display, I managed with a little bit of persitence to work out the views that where called by this report and noticed the sales stage names that where in the report that I wanted to display actually had the alias given to them as salesstagecodename. I modifed the report by doing a simple search and replace on all and saved the report.

     

    As the CRM appears to be using SSRS all reports are precompiled you will need to upload the file through the EDIT report options in the reports view. This will upload and recompile the report.

     

    Once I done this I now have the different pipeline stages that I was attempting to get...

     

    Hope this helps. (it worked for me so I hope it works for every one else)..

     

    Regards,

     

     

    (Additional note: this will break the sub report Sales Pipeline detail.rdl so its not a total fix to the problem, but I've had enough for one night)..

     

    Wednesday, June 4, 2008 1:54 PM
  • I've found that by adding a "Pipeline Phase" field to the form (it can be read only) it will ensure the Pipeline Phase status is updated correctly.

     

    Tuesday, November 25, 2008 4:21 PM
  • This doesn't work on CRM Online, correct? My understanding is that you're unable to pull something out into reporting services and modify it. additionally, I can't see how you'd be able to update the pipeline phase from a native workflow.

    Any insight or clarity would be helpful!
    Friday, March 27, 2009 1:13 PM
  • Hi, Gerben Graske-Borst, I'm resurrecting an old thread... I followed your instructions for the main report and it worked! Thanks! Were you able to fix the sub report?

    Thank you,

    Mark
    Monday, April 27, 2009 9:20 PM
  • Has anyone tried using a staged workflow for this?

    If you build stages into your workflow and then run the pipeline report, you will see a "Group By Sales Process" list box/parameter.  Select your staged workflow and the report will group the data according to where the Opportunities fall within that staged workflow.

    Tuesday, April 28, 2009 8:13 PM
  • I tried CRMMike's solution, and it works.  You have to assign names to the Sales Stages, and those names will appear in the pipeline report.  Additionally, if you update the Pipeline Phase field in the workflow, that will update the phase that is displayed in the right side of the footer for the Opportunity form. 
    Tuesday, June 2, 2009 4:37 PM
  • That's very intersesting CRMMike. Any chance you could post a screenshot of your workflow?

    Thanks!
    Tuesday, June 2, 2009 5:13 PM
  • CRMMike is correct.
    The sales pipeline report picks salesstagename from the workflow stage and not the salesstagecodename.

    So if this is what you are looking for, here's how you can get it to work:

    1. Create a workflow on opportunity entity, say on create opportunity.
    2. Add a stage to the workflow: call it my stage
    3. Publish the workflow.
    4. Create an opportunity, and see that the workflow triggers.
    5. Now run the sales pipeline report. Make sure that the group by sales is not equal to the workflow you created.

    You should be able to see opportunity data grouped by stage taht you defined in the workflow.


    Tuesday, June 2, 2009 6:56 PM
  • I've been working with this report some more today and noticed some interesting behavior while experimenting with ways to allow users to take an opportunity backwards in the pipeline.  The simple approach I tried was to cancel a workflow that was running against an opportunity.  Then restart the workflow. The workflow was designed to restart the state and the sales stage when it first starts.  That part works.  It does set the sales stage correctly, but the report behavior is tricky.  If you have an opportunity that has had multiple instances of the workflow run against it, the estimated value for that opportunity is counted in the report for each instance of running the workflow. If your workflow has sales stages A, B and C, and you cancel the workflow at stage B, then start a new instance of the workflow so the wf stage is stage A, the value of that opportunity will be counted in both stage A and stage B.  It's double counted!! Cancel the new instance and start another new instance, and it's triple counted.  One thing you can do to clean this up is to delete the old instances of the workflow. However, due to the asynchronous nature of the delete function in CRM, the sales pipeline report will still double or triple count the opportunity until CRM gets around to cleaning up the deleted instances of the workflow.  

    I tried canceling the opportunity. That did indeed remove it from the report. But if you reopen the opportunity and run the report, the old instances of the workflow are still in the back end table, so the numbers are still counted multiple times. I could ask the users to close the opportunity and open a new one when the sales stage needs to regress, but my sales people say that they will frequently have to go back during the later stages of the sales pipeline because they were over confident in stating the probability of the sale.  This especially happens towards the end of a sales period.  So the approach of closing the opportunity and creating a new one would be cumbersome and would result in loss of history, and is not an acceptable solution.

    Has anyone found a better work around for this?  Does MS know about this?

    I took a look at the report definition, the view and the table used for this report, and there's no field in the table that tracks the status of the workflow, and it's not joining that instance table to the table that has the actual status of the WF, so there's no way for the report to filter out the instances of the workflow that have been superceded by new instances of the workflow. 


    Wednesday, June 3, 2009 3:49 AM
  • In step 5 in Nimishas post, I believe it should say "Make sure that the group by sales is now equal to the workflow you created."  If that correction were made, I would vote that as the answer.
    Wednesday, June 3, 2009 3:52 AM
  • Thanks for catching the typo Christine. It indeed should be equal to the workflow.
    (It was a little to late night in India I guess :))
    Wednesday, June 3, 2009 4:49 AM
  • I have EXACTY the same problem - there are going to be many sales pipeline reports run that will be very inaccurate!  This seems like a basic school boy error..........

    Wednesday, July 1, 2009 1:17 PM
  • CRMMike is correct.
    The sales pipeline report picks salesstagename from the workflow stage and not the salesstagecodename.

    So if this is what you are looking for, here's how you can get it to work:

    1. Create a workflow on opportunity entity, say on create opportunity.
    2. Add a stage to the workflow: call it my stage
    3. Publish the workflow.
    4. Create an opportunity, and see that the workflow triggers.
    5. Now run the sales pipeline report. Make sure that the group by sales is not equal to the workflow you created.

    You should be able to see opportunity data grouped by stage that you defined in the workflow.



    I'm still not sure how this workflow works? I'm honestly pretty good with workflows but what specifically did you configure on your workflow? Its on create of an opportunity... but what is within the workflow stage you created? Could you post a screenshot or the specifics? Thanks so much.
    Wednesday, July 1, 2009 3:47 PM
  • The workflow must contain stages. The stage names will appear as the Pipeline Phases in the report.  Within each stage, put the logic you want to have to drive the opportunity through the workflow.  For example, your workflow could include a wait statement that waits for the Probability field to be greater than a specific number in order to advance to the next sales stage.  You could have a first stage named "Stage 1".  The stage would wait for probability to be greater than 10%.  When that condition is met, you should update the Pipeline Phase (stepname) field to drive the display on the Opportunity form (lower right hand corner in footer).  The workflow then falls through to the next stage, named "Stage 2".  Stage 2 contains a statement to wait until Probability is greater than 50%.  It then updates the pipeline phase and falls through to the next Stage in the workflow. 

    I just did this for a system that has defined certain fields that need to be set to "yes" in order to advance to the next sales stage. To do it right takes a complicated workflow.  For example, the workflow we created checks for criteria of current or future stages to be true so you can skip ahead. It also checks if the opportunity was closed, and takes different actions if it was won or lost. 

    My suggestion: forget the out of the box report.  Use the Pipeline Phase field to maintain the sales stage.  Create your own report that is driven by Pipeline Phase field. 

    There is a new Accelerator available that has just such a report along with extensive dashboarding features. You should check it out on codeplex.  Some pieces of the accelerator require SQL Server 2008 because it uses charts that are not available in prior versions. 
    Thursday, July 2, 2009 1:57 AM
  • Can you share a step by step of your workflow? That would be really helpful.
    Friday, November 27, 2009 12:19 PM
  • Oshabka,

    My workflow is based on many custom fields, and probably wouldn't be useful for you.  But here's an outline of steps you could use if you wanted to drive the stage based on the probability field.  I'm doing this from memory, so don't hold me to the exact step names. The workflow should be configured to trigger when an opportunity is created.

    Stage 1: Qualify
    Set property PipelinePhase = 'Qualify'
    Wait until probability >=20%

    Stage 2: Needs Analysis
    Set property PipelinePhase = 'Needs Analysis'
    Wait until probability >=40%

    Stage 3: Solution Definition
    Set property PipelinePhase = "Solution Definition"
    Wait until probability >=70%

    Stage 4: Proposal
    Set property PipelinePhase = "Proposal"
    Wait until probability >=90%

    Stage 5: Close
    Set property PipelinePhase = "Close"
    Wait until probability >=100%

    <end of workflow>

    This workflow uses the "Stage" step in the workflow to advance the sales stage of the opportunity.  This workflow assumes that you will advance the Pipeline Phase based on the value that was manually set for Probability.  A better solution would add custom attributes that represent milestones that should be completed in order to consider a phase to be complete. The Wait statements above would be replaced with statements where you wait for the attributes that represent the milestones to be completed (such as getting the name of sponsor).  In the example above, the pipeline phase field is updated by the workflow in order to keep the attribute synchronized with the staged workflow. However, that step is technically not required in order to drive the out of the box Sales Pipeline reports in CRM 4.0. 

    Hope this helps.  

    Also, there's a new sales analysis Accelerator available on CodePlex.  That Accelerator has pipeline reports that are driven based on the value of the Pipeline Phase field, and do not require a workflow.  You might check those out.  
    Monday, November 30, 2009 6:41 AM