Don't work sales pipline report

All replies

• 

  

  0

  Sign in to vote

  Hi,
  
  I have experienced similar issue; i.e. you get double/tripple/etc. entries is due to an error in the report.
  
  The report is using a filetered view called FilteredSalesProcessInstance.
  
  As you will see from this view, you get one entry for every step in the process. The report assumes only one entry, and with 4 steps in a process, you will see 4 representations of the opportunity. I don't know if this is correct, and the report is wrong, or vice versa.
  
  You would need to go into the report and sort this out, i.e. enter distinct in the right sql statements.
  I can't see any other way to solve this. It's really surprising. I'm running on a VPC.
  
  Anyway; that is why you get multiple records in your pipeline report, and that it does not appear correct.
  
  Morten

  Tuesday, April 21, 2009 10:47 AM
• 

  

  0

  Sign in to vote

  Many thanks. You have perfectly described my problem. Unfortunately, I do not have experience of a writing of reports. And I hardly represent as there it is possible to correct something.

  Tuesday, April 21, 2009 11:14 AM
• 

  

  0

  Sign in to vote

  Hi,
  
  The official response is, "This problem occurs because the "Sales Pipeline" report was not designed to report on an opportunity where multiple sales processes have been applied". This is detailed in KB 946593. The official workaround, is as I suggested, to modify the Sales Pipeline report (no suggestions how, or what needs to be modified). 
  
  I.e. download the report; open the RDL file (with notepad); and add Distinct for a couple of select statements.
  
  But if you are new to SQL; you might find this difficult.
  
  Sorry abou that.
  
  Morten
  
  Morten
  
  

  Tuesday, April 21, 2009 11:40 AM
• 

  

  0

  Sign in to vote

  Hi,
  
  I know it's unsupported, I know you should not do anything in the database ..... etc.... but even though .....
  
  In case you will try this; do it on your VPC first; to make sure you understand the impact of it.
  
  The problem with the sales stages is that CRM leaves a number of process traces in the SalesProcessInstance database table, causing multipe entries in the Pipeline report.
  
  I did a very easy improvements to avoid this, manipulating the [FilteredSalesProcessInstance] view. This is absolute unsupported; but since it's a filetered view only used for reporting; solving the pipeline report; it might be a thing to concider until this issue has been solved properly by Microsoft.
  
  Before doing this, back-up you old [FilteredSalesProcessInstance] view.
  
  I have changed the view so that it will return only one sales stage for one opportunity in one sales process. I do that by using max. That means that if there is multiple entries, it will just pick the highest one, meaning that you must have your sales stages sorted, else it will not pick the relevant one.
  
  This is the view that you can replace your existing view to get the report working:
  
  ALTER view [dbo].[FilteredSalesProcessInstance] (
      businessunitid,
      businessunitiddsc,
      businessunitidname,
      opportunityid,
      opportunityiddsc,
      opportunityidname,
      salesprocessinstanceid,
      salesprocessname,
      salesstagename
  ) with view_metadata as
  select distinct
      SalesProcessInstance.BusinessUnitId,
      SalesProcessInstance.BusinessUnitIdDsc,
      SalesProcessInstance.BusinessUnitIdName,
      SalesProcessInstance.OpportunityId,
      SalesProcessInstance.OpportunityIdDsc,
      SalesProcessInstance.OpportunityIdName,
      SalesProcessInstance.OpportunityId,
  /*    SalesProcessInstance.SalesProcessInstanceId, */
      SalesProcessInstance.SalesProcessName,
      max(SalesProcessInstance.SalesStageName)
  from SalesProcessInstance
      left join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
      cross join dbo.fn_GetMaxPrivilegeDepthMask(32) pdm
   where pdm.PrivilegeDepthMask is not null
  group by
     SalesProcessInstance.BusinessUnitId,
      SalesProcessInstance.BusinessUnitIdDsc,
      SalesProcessInstance.BusinessUnitIdName,
      SalesProcessInstance.OpportunityId,
      SalesProcessInstance.OpportunityIdDsc,
      SalesProcessInstance.OpportunityIdName,
      SalesProcessInstance.OpportunityId,
    SalesProcessInstance.SalesProcessName
  
  
  
  

  • Marked as answer by Elka_ Wednesday, April 22, 2009 10:11 AM

  Wednesday, April 22, 2009 7:15 AM
• 

  

  0

  Sign in to vote

  Many thanks for your help. I have inserted your code into the report text, however don't work. Likely I  error insert it. Please, prompt where it correctly to insert. 
  
  
  If @GroupBy in ('SystemUserOwner')
  Begin
  set @SQL =
  '
  ALTER view [dbo].[FilteredSalesProcessInstance] (
      businessunitid,
      businessunitiddsc,
      businessunitidname,
      opportunityid,
      opportunityiddsc,
      opportunityidname,
      salesprocessinstanceid,
      salesprocessname,
      salesstagename
  ) with view_metadata as
  select distinct
      SalesProcessInstance.BusinessUnitId,
      SalesProcessInstance.BusinessUnitIdDsc,
      SalesProcessInstance.BusinessUnitIdName,
      SalesProcessInstance.OpportunityId,
      SalesProcessInstance.OpportunityIdDsc,
      SalesProcessInstance.OpportunityIdName,
      SalesProcessInstance.OpportunityId,
  /*    SalesProcessInstance.SalesProcessInstanceId, */
      SalesProcessInstance.SalesProcessName,
      max(SalesProcessInstance.SalesStageName)
  from SalesProcessInstance
      left join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
      cross join dbo.fn_GetMaxPrivilegeDepthMask(32) pdm
   where pdm.PrivilegeDepthMask is not null
  group by
     SalesProcessInstance.BusinessUnitId,
      SalesProcessInstance.BusinessUnitIdDsc,
      SalesProcessInstance.BusinessUnitIdName,
      SalesProcessInstance.OpportunityId,
      SalesProcessInstance.OpportunityIdDsc,
      SalesProcessInstance.OpportunityIdName,
      SalesProcessInstance.OpportunityId,
    SalesProcessInstance.SalesProcessName

  Declare @Temp1 table (owneridname nVarchar(max),ownerid nvarchar(60),
                        count int,estimatedvalue float,salesstagename nVarchar(200),
                        closeprobability int,wegrev float)
  Insert into @Temp1
  Select opp.owneridname,IsNull(Cast(opp.ownerid as nvarchar(60)),''_CRM_NOTSPECIFIED'') as ownerid,
  Count(*) as count,sum(estimatedvalue_base),
  IsNull(salesstagename, ''_CRM_NOTSPECIFIED'') as salesstagename,0,
  Sum(IsNull(estimatedvalue_base,0)*IsNull(closeprobability,100))/100
  From

  • Marked as answer by Elka_ Wednesday, April 22, 2009 10:11 AM
  • Unmarked as answer by Elka_ Wednesday, April 22, 2009 10:11 AM

  Wednesday, April 22, 2009 8:50 AM
• 

  

  0

  Sign in to vote

  Hi, The code I sent was to alter the view in your crm database, ånd not the report file. i.e. right click on view in sql server , edit, replace existing view def with what I sent you. Morten-from-mobile

  • Marked as answer by Elka_ Wednesday, April 22, 2009 10:11 AM

  Wednesday, April 22, 2009 9:48 AM
• 

  

  0

  Sign in to vote

  I do it. It is  great !! Huge to you thanks!!! Many thanks!!

  Wednesday, April 22, 2009 10:10 AM
• 

  

  0

  Sign in to vote

  Is it possible to edit this code for  the last on time of start of workflow process (latest) ?

  Monday, May 4, 2009 11:39 AM
• 

  

  0

  Sign in to vote

  Hi,
  
  Anything is possible, but what you are asking is not "easy fix", as you would need a subselect in the current view to select max(createdon) from asyncservice joined with the salesprocessinstance view, asyncjobid ..... etc....
  
  You would need to address AsyncOperation, since the creaton is not in the SalesProcessInstance view.
  
  
  Morten
  

  Monday, May 4, 2009 11:55 AM
• 

  

  0

  Sign in to vote

  Thank you

  Monday, May 4, 2009 1:59 PM
• 

  

  0

  Sign in to vote

  Hi,
  
  Took one more look at it. And I assume the reason for asking is that you only want to see one opportunity represented once. It's hard to do anything about last created, since there are no such field in the salesprocess instance view. But, if you can live with that you number the sales pipeline stages, so that the highest number is always the last (from 0 -9); and since you are not allowed to step backwards in a sale sprocess; it might be an option. In that case you can try to replace the sql in the view with the one below:
  
  
  ALTER view [dbo].[FilteredSalesProcessInstance] (
      businessunitid,
      businessunitiddsc,
      businessunitidname,
      opportunityid,
      opportunityiddsc,
      opportunityidname,
      salesprocessinstanceid,
      salesprocessname,
      salesstagename
  ) with view_metadata as

  select distinct
      SalesProcessInstance.BusinessUnitId,
      SalesProcessInstance.BusinessUnitIdDsc,
      SalesProcessInstance.BusinessUnitIdName,
      SalesProcessInstance.OpportunityId,
      SalesProcessInstance.OpportunityIdDsc,
      SalesProcessInstance.OpportunityIdName,
      SalesProcessInstance.OpportunityId,
       'CRM Sales Process',
      (select max(x.SalesStageName) from SalesProcessInstance x
  where SalesProcessInstance.OpportunityId = x.OpportunityId)
  from SalesProcessInstance
      left join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
      cross join dbo.fn_GetMaxPrivilegeDepthMask(32) pdm
   where pdm.PrivilegeDepthMask is not null
  group by
     SalesProcessInstance.BusinessUnitId,
      SalesProcessInstance.BusinessUnitIdDsc,
      SalesProcessInstance.BusinessUnitIdName,
      SalesProcessInstance.OpportunityId,
      SalesProcessInstance.OpportunityIdDsc,
      SalesProcessInstance.OpportunityIdName,
      SalesProcessInstance.OpportunityId,
      SalesProcessInstance.SalesProcessInstanceId

  Monday, May 4, 2009 8:03 PM
• 

  

  0

  Sign in to vote

  Hi, Morten
   I am afraid incorrectly of you to understand (because of a language barrier). I have taken advantage of your first variant of a code. The problem has dared partially . However there is a complexity that if the user will change probability opportunity for example from 50 % to 40 % in opportunity the channel of sales will exchange on 4. But thus in the report (sales pipline report) for this opportunity the probability is 40 %, and a stage 5 will be displayed. (Though actually the stage stage has exchanged on 4)

  Tuesday, May 5, 2009 5:29 AM
• 

  

  0

  Sign in to vote

  This is a great post, really useful, solves half my issue. I have a further issue with regards how opportunities are grouped by sales process.
  
  I have a client that had a staged workflow set up, i.e. stage 1 in discussion stage 2 Quoted etc, but have since removed its stages from the workflow. Now when they go into the sales pipeline standard report it is listing half the the opportunities within the original workflow stages, and half (the new opportunities) under the grouping none.
  
  My question is, is there a way to remove the sales stage the original opportunities are at, i.e. revert them back to being grouped on the sales pipeline report 'Grouped by Sales process' None.
  
  To explain this better:
  
  Original Workflow (opportunity) begins on new record creation and is called XXXX and has a stage called In discussion.
  
  Opportunities entered and workflow kicks in. All opportunities are grouped by sales process 'XXXX' on the Sales pipeline report
  
  The stage has been removed from the workflow as this was incorrect.
  
  New opportunities have since been entered and are grouped by Sales process 'none'
  
   

  Friday, August 21, 2009 2:25 PM