locked
CRM 2011 - Neglected Leads Report - Change Neglected Days RRS feed

  • Question

  • Hi Forum,

    We are using out of the box "Neglected Leads" report to track leads. By, default when you run this report it runs against 30 days.

    I have a requirement where need to show Neglected Leads Days to 180 instead of default 30.

    To do that, I have downloaded the report, opened in VS and changed the report parameter value to 180.

    I then uploaded this report and published/refreshed CRM but still not able to see my change.

    It is still showing me default 30 days as "Neglected Days" parameter.

    Any help would be appreciated. Thanks for reading.

    Cheers,

    H.Desai

    Tuesday, September 2, 2014 9:18 AM

Answers

  • Hello,

     Your third problem - Company name does not appear - Dataset field names are predefined. if you are not connected to database, your fields will not be automatically generated. you will have to either generate it or manually add them (also if you use the sp_execute statement). Add the fields into the dataset and you will be able to see the data in your report.

    Let me know if you have any questions.

    Jithesh

    • Marked as answer by H.Desai Wednesday, September 10, 2014 9:39 PM
    Wednesday, September 10, 2014 12:14 AM
  • Ok, Please do the following to automatically generate the field names.

    1. Create a new dataset - so that you do not overwrite your queries.

    2. select datasourse as embedded.

    3. insert the following query in

    select '' leadid,'' fullname, '' companyname, '' createdon, '' leadqualitycodename, '' owneridname, ''  as territoryidname

    4. click refresh fields.

    5. go to fields - see that your fields are now mapped.

    6. Modify the query, add your original query - the one where you use a SQL variable to build query.

    7. DO NOT click refresh fields - Just click ok alone.

    8. See if the new dataset shows the fields.

    -- What I am trying to say here is that since the reports query uses exec "Exec (@SQL)", reports builder is not able to automatically generate fields. you can manually add them, the fields that are not mapped will be ignored - will now show in reports.

    HTH.

    regards,

    Jithesh


    Wednesday, September 10, 2014 2:38 AM
  • Manually adding fields -
    • Marked as answer by H.Desai Wednesday, September 10, 2014 9:47 PM
    Wednesday, September 10, 2014 2:43 AM

All replies

  • Hi Forum,

    it seems like that if I create a new report against overwriting existing one then it's working fine. Though still would like to know why changes are not taking place on original report parameter.

    Also, I am trying to modify out of the box Neglected Leads and it's sub report report itself. All wanted to do is to change the first column of the sub report from lead name to company name.

    For that I follow the same standard procedure of downloading the report and opening up in VS.

    First thing I notice that data sources is defined as CRM and it's refereeing to localhost and Adventure works database. I changed it to our CRM SQL server and our actual CRM database name (XXXX_MSCRM)

    after making this change when I refresh the dataset it simply doesn't show me any fields from the dataset query.  (See attached images for a better understanding)

    Is there anyone who has modified out of the box report successfully. Any input would be appreciated.

    Visual Studio ImageModified Query

    Tuesday, September 9, 2014 10:51 PM
  • reports are slightly different in CRM to accommodate passing dynamic filters, applying security etc.

    your first problem - filter not changing. To fix this, open crm, go to reports, select the report,

    select options and select edit default filter- here you can Change the filter to 180 days.

    regards,

    Jithesh

    Tuesday, September 9, 2014 11:06 PM
  • Hi,

    Your second problem, when reports are deployed to the server, it is supposed to use the data source specified in report server and not your local datasource. so to develop the reports, you can connect to the actual server and use the "Connection embedded in my report option of the data source". Before you deploy to some other server, change the connection back to shared connection. Picture added below.

    Regards,

    Jithesh

    Wednesday, September 10, 2014 12:09 AM
  • Hello,

     Your third problem - Company name does not appear - Dataset field names are predefined. if you are not connected to database, your fields will not be automatically generated. you will have to either generate it or manually add them (also if you use the sp_execute statement). Add the fields into the dataset and you will be able to see the data in your report.

    Let me know if you have any questions.

    Jithesh

    • Marked as answer by H.Desai Wednesday, September 10, 2014 9:39 PM
    Wednesday, September 10, 2014 12:14 AM
  • reports are slightly different in CRM to accommodate passing dynamic filters, applying security etc.

    your first problem - filter not changing. To fix this, open crm, go to reports, select the report,

    select options and select edit default filter- here you can Change the filter to 180 days.

    regards,

    Jithesh

    Thanks for your time to look into this and breakdown of your replies but above suggestion can only apply to filters (Advanced Find like interface) and not the parameters. default 30 days is a parameter of the report and it appears after filters are set and report has been executed.

    Anyway, changing this value is not a big problem as I can always create new report and hide the original one as creating new report reflects the change in the parameter. I asked this out of curiosity. You can test this on your Dev or UAT environment.

    My main worry is to modify out of the box dataset query and change the column of the sub report table to reflect this change. I have changed the query by adding Company Name but after this change

    dataset is not populating any of the fields if I preview it in VS 2012. Attached are the couple of more images for you.

    My data source is exactly same as you have mentioned.

    Thanks,

    H.Desai

      


    • Edited by H.Desai Wednesday, September 10, 2014 2:06 AM more clarity
    Wednesday, September 10, 2014 2:03 AM
  • Another image to show the details of the dataset change

    Wednesday, September 10, 2014 2:13 AM
  • Below is a complete query. All I have done is added "companyname" in select statements.

    Declare @SQL nVarchar(max)
    Declare @GroupByCol nVarchar(40)
    Declare @GroupByColname nVarchar(40)
    Declare @str nVarchar(100)
    Declare @strnull nVarchar(100)
    if @CRM_GroupBy = 'owneridname'
    Begin
    set @str = '(L.ownerid = ''' + @CRM_ParamID + ''')'
    set @strnull = '(L.ownerid is Null)'
    set @GroupByCol = 'L.ownerid'
    set @GroupByColname = 'L.owneridname'
    End
    if @CRM_GroupBy = 'territoryidname'
    Begin
    set @GroupByCol = 'su.territoryid'
    set @GroupByColname = 'su.territoryidname'
    set @str = '(su.territoryid = ''' + @CRM_ParamID + ''')'
    set @strnull = '(su.territoryid is Null)'
    End
    if @CRM_GroupBy = 'leadsourcecodename'
    Begin
    set @GroupByCol = 'L.leadsourcecode'
    set @GroupByColname = 'L.leadsourcecodename'
    set @str = '(L.leadsourcecode = ''' + @CRM_ParamID + ''')'
    set @strnull = '(L.leadsourcecode is Null)'
    End
    
    
    If @CRM_ParamID Not in('_CRM_OTHER', '_CRM_NOTSPECIFIED','',' ')
    and @CRM_GroupBy in('owneridname','territoryidname','leadsourcecodename') 
    Begin
    Set @SQL= '
     Select L.fullname,
            L.companyname, 
            L.statecodename,
            L.createdon,
            L.leadsourcecodename,
            L.owneridname,
            L.leadid,
            su.territoryidname,
            dbo.fn_UTCToLocalTime(MAX(AllLeads.MaxModDate)) as LastModDate
    
    From         
                   (SELECT     FilteredLead.modifiedonutc AS MaxModDate, leadid AS ID
                           FROM          FilteredLead
                           UNION
                   SELECT    IsNull(fp.scheduledendutc,fp.modifiedonutc) AS MaxModDate, fp.regardingobjectid AS ID
                           FROM         FilteredActivityPointer fp
                           where fp.regardingobjecttypecode = 4
                           UNION
                   SELECT     FilteredAnnotation.modifiedonutc AS MaxModDate, objectid AS ID
                           FROM         FilteredAnnotation  
                           where FilteredAnnotation.objecttypecode = 4                    
                     ) AS AllLeads INNER JOIN '+
    '               ( ' + @CRM_FilteredLead + ') AS L ON AllLeads.ID = L.leadid
                    Left Outer Join FilteredSystemUser as su on su.systemuserid = ownerid
    where L.Leadid is not null and L.statecode = 0 and
    L.modifiedonutc < DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE())
    AND ' + @str + ' 
    Group By L.leadid,L.fullname,L.companyname,L.statecodename,
            L.createdon,L.leadsourcecodename,L.owneridname,su.territoryidname
    
    HAVING      
    MAX(AllLeads.MaxModDate) <= DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE()) '
    
    End
    If @CRM_ParamID in('_CRM_NOTSPECIFIED') or @CRM_ParamID is Null
    and @CRM_GroupBy in('owneridname','territoryidname','leadsourcecodename') 
    Begin
    Set @SQL= '
     Select L.fullname,
            L.companyname,
            L.statecodename,
            L.createdon,
            L.leadsourcecodename,
            L.owneridname,
            L.leadid,
            su.territoryidname,
            dbo.fn_UTCToLocalTime(MAX(AllLeads.MaxModDate)) as LastModDate
    
    From         
                   (SELECT     FilteredLead.modifiedonutc AS MaxModDate, leadid AS ID
                           FROM          FilteredLead
                           UNION
                   SELECT    IsNull(fp.scheduledendutc,fp.modifiedonutc) AS MaxModDate, fp.regardingobjectid AS ID
                           FROM         FilteredActivityPointer fp
                           where fp.regardingobjecttypecode = 4
                           UNION
                   SELECT     FilteredAnnotation.modifiedonutc AS MaxModDate, objectid AS ID
                           FROM         FilteredAnnotation  
                           where FilteredAnnotation.objecttypecode = 4                    
                     ) AS AllLeads INNER JOIN '+
    '               ( ' + @CRM_FilteredLead + ') AS L ON AllLeads.ID = L.leadid
                    Left Outer Join FilteredSystemUser as su on su.systemuserid = ownerid
    where L.Leadid is not null and L.statecode = 0 and
    L.modifiedonutc < DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE())
    AND ' + @strnull + ' 
    Group By L.leadid,L.fullname,L.companyname,L.statecodename,
            L.createdon,L.leadsourcecodename,L.owneridname,su.territoryidname
    
    HAVING      
    MAX(AllLeads.MaxModDate) <= DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE()) '
    
    End
    If (@CRM_ParamID = '_CRM_OTHER' 
    and @CRM_GroupBy in('owneridname','territoryidname','leadsourcecodename') ) 
    Begin
    Set @SQL =
    'Declare @Top15 table (groupbyid nVarchar(50), count int)
                            
    Insert into @Top15
    Select  top 15  IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED''),
                    Count(*) as count
    From         
                   (SELECT     FilteredLead.modifiedonutc AS MaxModDate, leadid AS ID
                           FROM          FilteredLead
                           UNION
                   SELECT    IsNull(fp.scheduledendutc,fp.modifiedonutc) AS MaxModDate, fp.regardingobjectid AS ID
                           FROM         FilteredActivityPointer fp
                           where fp.regardingobjecttypecode = 4
                           UNION
                   SELECT     FilteredAnnotation.modifiedonutc AS MaxModDate, objectid AS ID
                           FROM         FilteredAnnotation  
                           where FilteredAnnotation.objecttypecode = 4                    
                     ) AS AllLeads INNER JOIN '+
    '               ( ' + @CRM_FilteredLead + ') AS L ON AllLeads.ID = L.leadid
                    Left Outer Join FilteredSystemUser as su on su.systemuserid = ownerid
    where L.Leadid is not null and L.statecode = 0 and
    L.modifiedonutc < DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE())
    Group BY
    IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED''),'+ @GroupByColname + '
    HAVING      
    MAX(AllLeads.MaxModDate) <= DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE()) 
    order by count(*) desc,'+ @GroupByColname + '
    Select
            L.fullname,
    		L.companyname,
            L.statecodename,
            L.createdon,
            L.leadsourcecodename,
            L.owneridname,
            L.leadid,
            su.territoryidname,
            dbo.fn_UTCToLocalTime(MAX(AllLeads.MaxModDate)) as LastModDate
    From         
                   (SELECT     FilteredLead.modifiedonutc AS MaxModDate, leadid AS ID
                           FROM          FilteredLead
                           UNION
                   SELECT    IsNull(fp.scheduledendutc,fp.modifiedonutc) AS MaxModDate, fp.regardingobjectid AS ID
                           FROM         FilteredActivityPointer fp
                           where fp.regardingobjecttypecode = 4
                           UNION
                   SELECT     FilteredAnnotation.modifiedonutc AS MaxModDate, objectid AS ID
                           FROM         FilteredAnnotation  
                           where FilteredAnnotation.objecttypecode = 4                    
                     ) AS AllLeads INNER JOIN '+
    '               ( ' + @CRM_FilteredLead + ') AS L ON AllLeads.ID = L.leadid
                    Left Outer Join FilteredSystemUser as su on su.systemuserid = ownerid
    where L.Leadid is not null and L.statecode = 0 and
    L.modifiedonutc < DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE())
    and IsNull(Cast('+ @GroupByCol + ' as nVarchar(50)),''_CRM_NOTSPECIFIED'') Not in(select groupbyid from @Top15)
    Group By L.leadid,L.fullname,L.companyname,L.statecodename,
            L.createdon,L.leadsourcecodename,L.owneridname,su.territoryidname
    HAVING      
    MAX(AllLeads.MaxModDate) <= DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE()) '
    End
    If (@CRM_ShowAll = 'showall' and @CRM_ParamID is Null) and @CRM_GroupBy in('owneridname','territoryidname','leadsourcecodename')
    Begin
    Set @SQL =
    'Select
            L.fullname,
    		L.companyname,
            L.statecodename,
            L.createdon,
            L.leadsourcecodename,
            L.owneridname,
            L.leadid,
            su.territoryidname,
            dbo.fn_UTCToLocalTime(MAX(AllLeads.MaxModDate)) as LastModDate
    From         
                   (SELECT     FilteredLead.modifiedonutc AS MaxModDate, leadid AS ID
                           FROM          FilteredLead
                           UNION
                   SELECT    IsNull(fp.scheduledendutc,fp.modifiedonutc) AS MaxModDate, fp.regardingobjectid AS ID
                           FROM         FilteredActivityPointer fp
                           where fp.regardingobjecttypecode = 4
                           UNION
                   SELECT     FilteredAnnotation.modifiedonutc AS MaxModDate, objectid AS ID
                           FROM         FilteredAnnotation  
                           where FilteredAnnotation.objecttypecode = 4                    
                     ) AS AllLeads INNER JOIN '+
    '               ( ' + @CRM_FilteredLead + ') AS L ON AllLeads.ID = L.leadid
                    Left Outer Join FilteredSystemUser as su on su.systemuserid = ownerid
    where L.Leadid is not null and L.statecode = 0 and
    L.modifiedonutc < DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE())
    Group By L.leadid,L.fullname,L.companyname,L.statecodename,
            L.createdon,L.leadsourcecodename,L.owneridname,su.territoryidname
    HAVING      
    MAX(AllLeads.MaxModDate) <= DateAdd(d, -' + CONVERT(nVarChar(10), @CRM_NeglectedDays)+', GETUTCDATE()) order by ' + @GroupByColname + ''
    End
    Exec (@SQL)

    Wednesday, September 10, 2014 2:22 AM
  • Ok, Please do the following to automatically generate the field names.

    1. Create a new dataset - so that you do not overwrite your queries.

    2. select datasourse as embedded.

    3. insert the following query in

    select '' leadid,'' fullname, '' companyname, '' createdon, '' leadqualitycodename, '' owneridname, ''  as territoryidname

    4. click refresh fields.

    5. go to fields - see that your fields are now mapped.

    6. Modify the query, add your original query - the one where you use a SQL variable to build query.

    7. DO NOT click refresh fields - Just click ok alone.

    8. See if the new dataset shows the fields.

    -- What I am trying to say here is that since the reports query uses exec "Exec (@SQL)", reports builder is not able to automatically generate fields. you can manually add them, the fields that are not mapped will be ignored - will now show in reports.

    HTH.

    regards,

    Jithesh


    Wednesday, September 10, 2014 2:38 AM
  • Manually adding fields -
    • Marked as answer by H.Desai Wednesday, September 10, 2014 9:47 PM
    Wednesday, September 10, 2014 2:43 AM
  • Hi Forum,

    We are using out of the box "Neglected Leads" report to track leads. By, default when you run this report it runs against 30 days.

    I have a requirement where need to show Neglected Leads Days to 180 instead of default 30.

    To do that, I have downloaded the report, opened in VS and changed the report parameter value to 180.

    I then uploaded this report and published/refreshed CRM but still not able to see my change.

    It is still showing me default 30 days as "Neglected Days" parameter.

    Any help would be appreciated. Thanks for reading.

    Cheers,

    H.Desai

    Hi Desai,

    Simple solution will be to just change Neglected Days Parameter Default value to "=180" instead of 30.

    I have tried it and it runs successfully.

    Wednesday, September 10, 2014 12:12 PM
  • Aha...I know what you mean now. Sorry I didn't understand it in first place... feeling dumb !! :) 

    After manually adding all the fields, report worked fine. There were some small glitches like RDHelper file not loading and some field references in expression throwing error but all sorted out.

    Thank you very much Jithesh for your guidance.

    Cheers,

    H.Desai


    • Edited by H.Desai Wednesday, September 10, 2014 9:45 PM
    Wednesday, September 10, 2014 9:38 PM
  • Hi Mohd Saad,

    Thanks for your reply. I did the same and overwrite the original report but change didn't take place. What I had to do was to create a new report rather then overwrite existing one with this change and it worked fine.

    Cheers,

    H.Desai

    Wednesday, September 10, 2014 9:42 PM
  • Hi Mohd Saad,

    Thanks for your reply. I did the same and overwrite the original report but change didn't take place. What I had to do was to create a new report rather then overwrite existing one with this change and it worked fine.

    Cheers,

    H.Desai

    Hi Desai,

    Have you tried changing your Neglected Days parameter to =180 instead of 180 and then overwrite you Report. It worked fine for me.

    Let me share my screenshot.

    Thursday, September 11, 2014 4:54 AM