CRM 2011 - Neglected Leads Report - Change Neglected Days

All replies

• 

  

  0

  Sign in to vote

  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.

  

  Tuesday, September 9, 2014 10:51 PM
• 

  

  0

  Sign in to vote

  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
• 

  

  1

  Sign in to vote

  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
• 

  

  1

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  Another image to show the details of the dataset change

  Wednesday, September 10, 2014 2:13 AM
• 

  

  0

  Sign in to vote

  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
• 

  

  1

  Sign in to vote

  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

  
  

  • Edited by Jithesh Karumampatty Kalam Wednesday, September 10, 2014 3:41 AM clarification
  • Marked as answer by H.Desai Wednesday, September 10, 2014 9:38 PM

  Wednesday, September 10, 2014 2:38 AM
• 

  

  1

  Sign in to vote

  Manually adding fields -

  • Marked as answer by H.Desai Wednesday, September 10, 2014 9:47 PM

  Wednesday, September 10, 2014 2:43 AM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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