Answered by:
CRM 2011 - Neglected Leads Report - Change Neglected Days

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
- 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 -
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.
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
- 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 -
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