locked
How to modify Sales Pipeline report in crm 4.0 RRS feed

  • Question

  • Hi,

    I need to add the Potential Customer field to the Sales Pipeline report.

    How can i do it?

    I'm in CRM 4.0.

    It's urgent!!

    Thanks in advance...

     

    Thursday, April 3, 2008 6:44 AM

Answers

  • It's under the Reports area within Workplace.  Just click on New to create a new report.  You'll then see a button to click to create a new report using the Report Wizard.  It's point and click simple, and very similar to Advanced Find.  Here's a video someone posted on YouTube stepping through the Report Wizard.  Give it a look when you get the chance.

     

    http://www.youtube.com/watch?v=GLC2MHTBHzo&feature=related

     

    If you need any more help let me know.

     

    Fronk

     

    Monday, April 21, 2008 5:19 PM

All replies

  •  

    You will need to go to the reports section, highlight the Sales Pipeline Detail report (the Sales Pipeline report is a chart and if you click into it that drills down into the Detail report), and then click the "Edit Report" button.

     

    That will open a new window where you can click on Actions and choose "Download Report".  That will pull down the RDL file that you can then add to an existing (or newly created) report project.  From there you can modify anything you want.  To add the column you will just need to include it in the SQL Query on the Data tab, then add a column in the report, drag and drop the Potential Client from the fields list in the toolbox on the left side of Visual Studio, and it should put the Title and values into the column for you.

     

    Then just preview, save, build the solution and upload the modified report and you're all set.

     

    Hope this helps.

     

    Fronk

    Thursday, April 3, 2008 6:57 AM
  • From what you said this sounded very easy but as i checked the query it seemed very complicated.

    Did anybody work on it to add the Potential  Customer?

    Can anyone tell me how to add it?

    Thanks

     

    Tuesday, April 8, 2008 7:44 AM
  • In your query just add customeridname as one of the select columns to return (assuming that you have kept the filtered views in the SQL expression).  That will add the attribute to your toolbox in the design view of Visual Studio.  Then just drag and drop onto the location you want the data to appear in a column.

     

    You shouldn't need to modify the query more than that if all you want is one more column.  The query might look complex but if you have worked with SQL then you should be able to find where to add the extra column.

     

    If any of this is confusing let me know and maybe post the code or step(s) that are confusing so we can provide some more guidance.

     

    Good luck,

     

    Fronk

     

    Tuesday, April 8, 2008 8:55 AM
  • Thanks for your fast reply.

    I know these steps, i've already done them but the query is complex.

    Here it is:

    Declare @SQL nVarchar(max)
    Declare @GroupByCol nVarchar(100)
    Declare @GroupByColname nVarchar(100)
    Declare @str nVarchar(200)
    Declare @strnull nVarchar(200)
    Declare @rev nVarchar(40)

    If @CRM_SelectRevenue = 'estimatedvalue'
    set @rev = 'estimatedvalue'
    If @CRM_SelectRevenue = 'weightedrevenue'
    set @rev = 'wegrev'

    If @CRM_GroupBy = 'SystemUserOwner'
    Begin
    set @GroupByCol = 'P_Opportunity.ownerid'
    set @GroupByColname = 'P_Opportunity.owneridname'
    set @str = '(P_Opportunity.ownerid = ''' + @CRM_ParamID + ''') '
    set @strnull = '(P_Opportunity.ownerid is Null) '
    End
    If @CRM_GroupBy = 'territoryidname'
    Begin
    set @GroupByCol = 'P_SystemUser.territoryid'
    set @GroupByColname = 'P_SystemUser.territoryidname'
    set @str = '(P_SystemUser.territoryid = ''' + @CRM_ParamID + ''') '
    set @strnull = '(P_SystemUser.territoryid is Null) '
    End
    If @CRM_GroupBy = 'customerterritory'
    Begin
    set @GroupByCol = 'P_Account.territoryid'
    set @GroupByColname = 'P_Account.territoryidname'
    set @str = '(P_Account.territoryid = ''' + @CRM_ParamID + ''') '
    set @strnull = '(P_Account.territoryid is null) '
    End
    If @CRM_GroupBy = 'opportunityratingcodename'
    Begin
    set @GroupByCol = 'P_Opportunity.opportunityratingcode'
    set @GroupByColname = 'P_Opportunity.opportunityratingcodename'
    set @str =  '(P_Opportunity.opportunityratingcode  = ''' + @CRM_ParamID + ''') '
    set @strnull = '(P_Opportunity.opportunityratingcodename is null) '
    End
    If @CRM_GroupBy = 'product'
    Begin
    set @GroupByCol = 'P_Product.productid'
    set @GroupByColname = 'P_Product.productidname'
    set @str = '(P_Product.productid  = ''' + @CRM_ParamID + ''') '
    set @strnull = '(P_Product.productid is null) '
    End
    If @CRM_GroupBy = 'estimatedclosedate'
    Begin
    set @str = 'estimatedclosedateutc  >  = dbo.fn_LocalTimeToUTC(''' + @CRM_ParamID + ''') and estimatedclosedateutc  <  dbo.fn_LocalTimeToUTC(dateadd(mm, 1, ''' + @CRM_ParamID + '''))'
    set @strnull = '(P_Opportunity.estimatedclosedateutc is null) '
    End

    If  @CRM_GroupBy in
    ('SystemUserOwner', 'estimatedclosedate', 'opportunityratingcodename')
    And @CRM_ParamID Not in ('_CRM_NOTSPECIFIED', '_CRM_OTHER')
    Begin
    set @SQL =
    'Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue,
           P_Opportunity.estimatedvalue_base as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           '''' as territoryidname,
           '''' as customerterritory,
           '''' as product,
           P_Opportunity.opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid
    where
    ('''+ @CRM_GroupBySales + ''' = ''All'' and (('''+@CRM_ShowAll + '''= ''notshowall'' AND IsNull(SalesProcess.salesstagename, ''_CRM_NOTSPECIFIED'') = N''' + @CRM_Stage + ''' AND ' + @str + ') or ('''+@CRM_ShowAll + ''' = ''showall'') )
    Or
    ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname is null and (('''+@CRM_ShowAll + '''= ''notshowall'' AND ' + @str + ') or (('''+@CRM_ShowAll + ''' = ''showall'')) )
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''
    and (('''+@CRM_ShowAll + ''' = ''notshowall'' AND SalesProcess.salesstagename = N''' + @CRM_Stage + ''' And ' + @str + ') or ('''+@CRM_ShowAll + ''' = ''showall'')))))
    order by SalesProcess.salesstagename '
    End

    If  @CRM_GroupBy in
    ('SystemUserOwner', 'estimatedclosedate', 'opportunityratingcodename')
    And @CRM_ParamID = '_CRM_NOTSPECIFIED'
    Begin
    set @SQL = '
    Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue as estimatedvalue,
           P_Opportunity.estimatedvalue_base as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           '''' as territoryidname,
           '''' as customerterritory,
           '''' as product,
           P_Opportunity.opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid
    where
    (('''+ @CRM_GroupBySales + ''' = ''All'' and IsNull(SalesProcess.salesstagename, ''_CRM_NOTSPECIFIED'') = N''' + @CRM_Stage + ''')
    or
    ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname is null )
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''
    and SalesProcess.salesstagename = N''' + @CRM_Stage + '''))
    and '+ @strnull + '
    order by SalesProcess.salesstagename '
    End

    If  @CRM_GroupBy in
    ('SystemUserOwner','opportunityratingcodename')
    And @CRM_ParamID = '_CRM_OTHER'
    Begin
    Set @SQL =
    'Declare @Top15 table (groupbyid nVarchar(50), estvalue float)
                           
    Insert into @Top15
    Select  top 15  IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED''),
                    Sum(P_Opportunity.estimatedvalue) as estvalue
    FROM   
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid      
    where ('''+ @CRM_GroupBySales + ''' = ''All'')
    or
     ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname Is Null)
    or
    ('''+ @CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + ''')
    Group BY
    IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED''),' + @GroupByColname + '
    order by estvalue desc,' + @GroupByColname + '

    Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue as estimatedvalue,
           P_Opportunity.estimatedvalue_base as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           '''' as territoryidname,
           '''' as customerterritory,
           '''' as product,
           P_Opportunity.opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid
    where
     (('''+ @CRM_GroupBySales + ''' = ''All'')
     or
     ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname Is Null)
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''))
    and IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED'')
     Not in(select groupbyid from @Top15)'
    end
     
    If  @CRM_GroupBy in ('territoryidname','customerterritory')
    And @CRM_ParamID Not in ('_CRM_NOTSPECIFIED', '_CRM_OTHER')
    Begin
    set @SQL =
    'Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue,
           P_Opportunity.estimatedvalue_base as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           P_SystemUser.territoryidname,
           customerterritory = Case
      When P_Opportunity.customeridtype = 1 then P_Account.territoryidname
      when P_Opportunity.customeridtype = 2 then Null end,
           '''' as product,
           '''' as opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left join FilteredAccount as P_Account
    on P_Account.accountid = P_Opportunity.accountid
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid
    where
    ('''+ @CRM_GroupBySales + ''' = ''All'' and (('''+@CRM_ShowAll + '''= ''notshowall'' AND IsNull(SalesProcess.salesstagename, ''_CRM_NOTSPECIFIED'') = N''' + @CRM_Stage + ''' AND ' + @str + ') or ('''+@CRM_ShowAll + ''' = ''showall'') )
    Or
    ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname is null and (('''+@CRM_ShowAll + '''= ''notshowall'' AND ' + @str + ') or (('''+@CRM_ShowAll + ''' = ''showall'')) )
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''
    and (('''+@CRM_ShowAll + ''' = ''notshowall'' AND SalesProcess.salesstagename = N''' + @CRM_Stage + ''' And ' + @str + ') or ('''+@CRM_ShowAll + ''' = ''showall'')))))
    order by SalesProcess.salesstagename '
    End


    If  @CRM_GroupBy in
    ('territoryidname','customerterritory')
    And @CRM_ParamID = '_CRM_NOTSPECIFIED'
    Begin
    set @SQL = '
    Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue as estimatedvalue,
           P_Opportunity.estimatedvalue_base as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           P_SystemUser.territoryidname,
           customerterritory = Case
      when P_Opportunity.customeridtype = 1 then P_Account.territoryidname
      when P_Opportunity.customeridtype = 2 then Null end,
           '''' as product,
           '''' as opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left join FilteredAccount as P_Account
    on P_Account.accountid = P_Opportunity.accountid
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid
    where
    (('''+ @CRM_GroupBySales + ''' = ''All'' and IsNull(SalesProcess.salesstagename, ''_CRM_NOTSPECIFIED'') = N''' + @CRM_Stage + ''')
    or
    ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname is null )
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''
    and SalesProcess.salesstagename = N''' + @CRM_Stage + '''))
    and '+ @strnull + '
    order by SalesProcess.salesstagename '
    End

    If  @CRM_GroupBy in
    ('territoryidname','customerterritory')
    And @CRM_ParamID = '_CRM_OTHER'
    Begin
    Set @SQL =
    'Declare @Top15 table (groupbyid nVarchar(50), estvalue float)
                           
    Insert into @Top15
    Select  top 15  IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED''),
                    Sum(P_Opportunity.estimatedvalue) as estvalue
    FROM   
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left join FilteredAccount as P_Account
    on P_Account.accountid = P_Opportunity.accountid
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid      
    where ('''+ @CRM_GroupBySales + ''' = ''All'')
    or
     ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname Is Null)
    or
    ('''+ @CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + ''')
    Group BY
    IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED''),' + @GroupByColname + '
    order by estvalue desc,' + @GroupByColname + '

    Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue as estimatedvalue,
           P_Opportunity.estimatedvalue_base as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           P_SystemUser.territoryidname,
           customerterritory = Case
      when P_Opportunity.customeridtype = 1 then P_Account.territoryidname
      when P_Opportunity.customeridtype = 2 then Null end,
           '''' as product,
           '''' as opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left join FilteredAccount as P_Account
    on P_Account.accountid = P_Opportunity.accountid
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid
    where
     (('''+ @CRM_GroupBySales + ''' = ''All'')
     or
     ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname Is Null)
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''))
    and IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED'')
     Not in(select groupbyid from @Top15)'
    end

    IF @CRM_GroupBy = 'salesstagename'
    Begin
    set @SQL = '
    Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue,
           P_Opportunity.estimatedvalue_base as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           '''' as territoryidname,
           '''' as customerterritory,
           '''' as product,
           '''' as opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredSystemUser as P_SystemUser
    on P_SystemUser.systemuserid = P_Opportunity.ownerid
    where
     ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname is null )
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''
    and (('''+@CRM_ShowAll + ''' = ''notshowall'' AND SalesProcess.salesstagename = N''' + @CRM_Stage + ''' ) or ('''+@CRM_ShowAll + ''' = ''showall'')))'
    End

    if @CRM_GroupBy = 'product' 
    and @CRM_ParamID Not in ('_CRM_NOTSPECIFIED','_CRM_OTHER')
    Begin
    set @SQL =
    'Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           IsNull(P_Product.extendedamount, P_Opportunity.estimatedvalue) as estimatedvalue,
           IsNull(P_Product.extendedamount_base, P_Opportunity.estimatedvalue_base) as baseestimatedvalue,
           IsNull(P_Product.crm_moneyformatstring, P_Opportunity.crm_moneyformatstring) as crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           '''' territoryidname,
           '''' customerterritory ,
           IsNull(P_Product.productidname, ''_CRM_NOTSPECIFIED'') as product,
           '''' opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join FilteredOpportunityProduct P_Product
    on P_Opportunity.opportunityid = P_Product.opportunityid
    where
    ('''+ @CRM_GroupBySales + ''' = ''All'' and (('''+@CRM_ShowAll + '''= ''notshowall'' AND IsNull(SalesProcess.salesstagename, ''_CRM_NOTSPECIFIED'') = N''' + @CRM_Stage + ''' AND ' + @str + ') or ('''+@CRM_ShowAll + ''' = ''showall'') )
    or
    ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname is null and (('''+@CRM_ShowAll + '''= ''notshowall'' AND ' + @str + ') or (('''+@CRM_ShowAll + ''' = ''showall'')) )
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''
    and (('''+@CRM_ShowAll + ''' = ''notshowall'' AND SalesProcess.salesstagename = N''' + @CRM_Stage + ''' And ' + @str + ') or ('''+@CRM_ShowAll + ''' = ''showall'')))))

    union

    Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue - IsNull(qq.extendedamount, 0) as estimatedvalue,
           P_Opportunity.estimatedvalue_base - IsNull(qq.extendedamount_base, 0) as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           '''' territoryidname,
           '''' customerterritory ,
           ''_CRM_NOTSPECIFIED'' as product,
           '''' opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left Outer Join FilteredSalesProcessInstance as SalesProcess on P_Opportunity.opportunityid = SalesProcess.opportunityid
    inner join
    (
    select  oprod.opportunityid, IsNull(sum(oprod.extendedamount),0) as extendedamount, IsNull(sum(oprod.extendedamount_base),0) as extendedamount_base
    From (' + @CRM_FilteredOpportunityProduct + ') as oprod
    group by oprod.opportunityid
    ) as qq on P_Opportunity.opportunityid = qq.opportunityid
    Where ('''+@CRM_ShowAll + ''' = ''showall'') and
    (('''+ @CRM_GroupBySales + ''' = ''All'')
    or
    ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname is null )
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''
    and SalesProcess.salesstagename = N''' + @CRM_Stage + '''))
    And P_Opportunity.estimatedvalue  <>  IsNull(qq.extendedamount, 0) '
    End

    if @CRM_GroupBy = 'product' 
    and ( @CRM_ParamID = '_CRM_NOTSPECIFIED')
    Begin
    set @SQL =
    'Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           P_Opportunity.estimatedvalue - IsNull(qq.extendedamount, 0) as estimatedvalue,
           P_Opportunity.estimatedvalue_base - IsNull(qq.extendedamount_base, 0) as baseestimatedvalue,
           P_Opportunity.crm_moneyformatstring,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           '''' territoryidname,
           '''' customerterritory ,
           ''_CRM_NOTSPECIFIED'' as product,
           '''' opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    Left join
    (
    select  oprod.opportunityid, IsNull(sum(oprod.extendedamount),0) as extendedamount, IsNull(sum(oprod.extendedamount_base),0) as extendedamount_base
    From (' + @CRM_FilteredOpportunityProduct + ') as oprod
    group by oprod.opportunityid
    ) as qq on P_Opportunity.opportunityid = qq.opportunityid
    where
    (('''+ @CRM_GroupBySales + ''' = ''All'' and IsNull(SalesProcess.salesstagename, ''_CRM_NOTSPECIFIED'') = N''' + @CRM_Stage + ''')
    or
    ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname is null )
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''
    and SalesProcess.salesstagename = N''' + @CRM_Stage + '''))
    And P_Opportunity.estimatedvalue  <>  IsNull(qq.extendedamount, 0)'
    End

    if @CRM_GroupBy = 'product' 
    and @CRM_ParamID = '_CRM_OTHER'
    Begin

    Set @SQL =
    'Declare @Top15 table (groupbyid nVarchar(50), estvalue float)
                           
    Insert into @Top15
    Select  top 15  IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED''),
                    Sum(P_Product.extendedamount) as estvalue
          
    FROM   
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left  join (' + @CRM_FilteredOpportunityProduct + ') as P_Product
    on P_Opportunity.opportunityid = P_Product.opportunityid
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    where ('''+ @CRM_GroupBySales + ''' = ''All'')
    or
     ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname Is Null)
    or
    ('''+ @CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + ''')
               
    Group BY
    IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED''),' + @GroupByColname + '
    order by estvalue desc,' + @GroupByColname + '

    Select P_Opportunity.name,
           P_Opportunity.opportunityid,
           ''estimatedvalue'' = case
      when P_Product.productidname is null then (P_Opportunity.estimatedvalue)
      else P_Product.extendedamount
      end,
           ''baseestimatedvalue'' = case
      when P_Product.productidname is null then (P_Opportunity.estimatedvalue_base)
      else P_Product.extendedamount_base
      end,
           ''crm_moneyformatstring'' = case
      when P_Product.productidname is null then (P_Opportunity.crm_moneyformatstring)
      else P_Product.crm_moneyformatstring
      end,
           P_Opportunity.closeprobability,
           P_Opportunity.owneridname as SystemUserOwner,
           SalesProcess.salesstagename,
           SalesProcess.salesprocessname,
           '''' as territoryidname,
           '''' as customerterritory,
           P_product.productidname as product,
           '''' as opportunityratingcodename,
           Cast(P_Opportunity.estimatedclosedate as nvarchar(50)) as estimatedclosedate
    From
    (' + @CRM_FilteredOpportunity + ') as P_Opportunity
    Left  join (' + @CRM_FilteredOpportunityProduct + ') as P_Product
    on P_Opportunity.opportunityid = P_Product.opportunityid
    Left Join FilteredSalesProcessInstance as SalesProcess
    on P_Opportunity.opportunityid = SalesProcess.opportunityid
    where
     (('''+ @CRM_GroupBySales + ''' = ''All'')
     or
     ('''+ @CRM_GroupBySales + ''' = ''None'' and SalesProcess.salesprocessname Is Null)
    or
    ('''+@CRM_GroupBySales +''' not in (''None'', ''All'') and SalesProcess.salesprocessname = N''' + @CRM_GroupBySales + '''))
    and IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED'')
     Not in(select groupbyid from @Top15)'
    End

    Exec (@SQL)

     

    Where should i enter the Potential customer?

    Tuesday, April 8, 2008 9:04 AM
  • I've downloaded the out of the box report and I'm looking to see if I can make the script pull Potential Customer.  Hopefully I'll have something a little bit later for you.

     

    Sorry I didn't look into it sooner and thanks for sharing the code.

     

    Fronk

     

    Tuesday, April 8, 2008 3:35 PM
  • Thank you for help.

    I would really appreciate it if you help me Smile

    Thanks again...

    Wednesday, April 9, 2008 5:49 AM
  • Sorry I haven't gotten back sooner, I've been swamped.  I've looked through the code and tried adding the field through the interface and that's works from the report builder perspective.  Unfortunately I built mine in a modified setup so testing the full blown solution has proven a little more difficult than I would have thought.  That could work if you want to try and replace the Sales Pipeline Detail.rdl but I continually get an error telling me that I don't have permissions to modify it.  You could go and hack your way around that by modifiying permissions on the file and find any where else they may have set those permissions but that could be very tedious.

     

    And really, at the end of the day all you want is to be able to have a pipeline report that is clickable and shows the customer name in the report.  It may just be faster (and easier) for you to build your own report.

     

    I know this isn't the answer you're looking for but without spending the larger part of a day on this to really understand the complex query and set up we're just not going to find the easy answer you're looking for.  I thought it would have been something simple like you thought as well, but it doesn't look like it is.  My recommendation is that unless you have an SRS master at your disposal you may want to build your own report.  CRM 4.0 has the report wizard with clickable charts and graphs, you might be able to build your report that way and be done in a fraction of the time it will take to figure out that code.

     

    Again, I apologize that I haven't come up with the full solution for you but I also didn't want you waiting on a response any longer since you've already been waiting this long.

     

    Fronk

     

    Friday, April 18, 2008 7:06 PM
  • Thanks a lot for trying to help me.

    But what's the CRM 4.0 report wizard? where can i find it?

    Thanks again... 

     

    Monday, April 21, 2008 6:41 AM
  • It's under the Reports area within Workplace.  Just click on New to create a new report.  You'll then see a button to click to create a new report using the Report Wizard.  It's point and click simple, and very similar to Advanced Find.  Here's a video someone posted on YouTube stepping through the Report Wizard.  Give it a look when you get the chance.

     

    http://www.youtube.com/watch?v=GLC2MHTBHzo&feature=related

     

    If you need any more help let me know.

     

    Fronk

     

    Monday, April 21, 2008 5:19 PM