Creating Report Subscriptions in Microsoft Dynamics CRM 4


How to Create a Microsoft Dynamics CRM 4 Report Subscription in less than 15 minutes

Our guest blogger today is CRM MVP Donna Edwards who blogs regularly here.

I frequently respond to questions about creating Report Subscriptions in Dynamics CRM 4 (on premise version) so I thought it would be helpful to provide some detailed information.

Scheduling report subscriptions is fairly easy if you know the right steps to take. Following the steps below should get you up and running with report subscriptions fairly quickly and you can begin Wowing your users with the delivery of reports directly to their Outlook Inbox.

The first step you need to take is to open your CRM application and navigate to Workplace, Reports. Identify and Select the report that you want to schedule, select More Actions from the top menu bar and select Schedule Report from the dropdown.

I will select the Account Distribution report for scheduling:

image

A wizard dialogue window will open. Select On Demand and Next. We don’t need to select On Schedule because we don’t necessarily want to run Report Snapshots; we want to setup a Subscription for delivery. If you decide you want to create Snapshots in addition to a Subscription, you can do that later from the Report Server:

image

If you want to change the Default Parameters used for the Subscription, select Edit Filter, select the filtering criteria, and Save or leave the defaults provided and Select Next

image

Select “No just save the Snapshot Definition and select Save

image

The report Snapshot Definition was successfully credited. Select Finish:

image

Now if you look at your Report grid in CRM you will see a new report listed with the same name referenced on the Save wizard dialogue window. In this case the report name was: Account Distribution - On-demand Snapshots 7_17_2009 9_15 AM:

image

If you select the report and select the Edit Report button, a new window will open showing you the details of the report. Select Administration and you will see that the report is set to Individual View only as users in the Organization do not need to view this report directly from the Dynamics Application. Leave the Settings and close the window.

image

Now we will move on to the CRM Report Server

You can access your Reporting Services Home page by using the URL http://NameofReportServer/reports. The Reporting Services Home page URL is different than the Report Server which is located at http://NameofReportServer/reportserver so ensure you are using the correct URL.

You will see a list of all your Report folders after the page opens. In this example you do not see the full name of the Report folder but you will see the full name in your deployment. In the upper right-hand corner of the Reporting Services Home page, select Show Details

image

You will notice that the page layout changes slightly and you can now see a new link name Edit

image

Select the Report folder that contains the reports for your CRM 4.0 deployment and select the 4.0 folder

image

After you select the 4.0 folder, you will see a list of your CRM reports. Locate the report that you created in the CRM application. You can easily find it as the report name, which in this example was “Account Distribution - On-demand Snapshots 7_17_2009 9_15 AM”, is included in the Description field.

image

Select the Edit Icon link located to the left of the report

image

Select the Subscriptions tab

image

Select the New Subscription button

image

This will open the window that allows you to enter the details for the report subscription

image

You can now enter the information that will determine how the report is delivered, who receives it and the report delivery frequency.

  • Leave the default delivery method of E-mail selected
  • Enter the e-mail address for the recipients
    • I generally include my e-mail address in the BCC field to ensure the report runs as expected at least the first few times and the format is what I intended
  • Enter the Reply-To e-mail address
  • Modify the Subject line for whatever you prefer
    • This will be the subject line of the e-mail
    • At a minimum Replace “ @ReportName” with the actual Report Name otherwise the value will be the GUID of the report which is not a user friendly name
  • Leave the Include Report and select the Render Format option you prefer. I generally use Excel as this gives users the option to manipulate the report data in a format familiar to them.
  • Leave the Include Link only if it is ok with you that users open the Report directly from the Report Server rather than through the CRM application
  • Set Priority
  • Add Comments
  • Select the Schedule you want the Report to run by selecting the Select Schedule button
  • Select the OK button and the Subscription is created and saved

Saving the Subscription will create a SQL job that runs on the date and time you indicated to deliver the report to your recipients. If you have not yet setup your Report Server for E-Mail Delivery then review this MSDN article for the steps needed to implement that service.

Once you create a few subscriptions, you will be able to take a few shortcuts that will reduce the steps needed to create new subscriptions. After generating one or two of these subscriptions, you will be an expert.

Happy Subscription Reporting!

Donna Edwards

Comments (38)

  1. Patrick Garrett says:

    The graphics are all dead links??

  2. 365blog says:

    Shouldn’t be. What error message are you getting? No worries, I just reposted using a different drop path.

  3. Patrick Garrett says:

    All the graphics are showing now!  Thanks!!

  4. Mark says:

    Awesome article. Would you happen to know I do not have email as an option for delivered by? I only have "windows file share".

    Thanks,

    Mark

  5. Mark says:

    Sorry I just saw your link to setup the SQL server for email delivery. Working through it now.

  6. RFish says:

    Can I schedule reports to be sent to my sales reps. so they get an email for their accounts only? or is there a permisisons issue?

  7. Donna Edwards says:

    (Can I schedule reports to be sent to my sales reps. so they get an email for their accounts only? or is there a permisisons issue?)

    Hi,

    You’ll need to create a report that filters on the Account Owner, create one for each Sales Rep and then schedule each report so it is delivered to the correct Rep if you want to use the method I’ve outlined in this example.  

    Otherwise, you will need to use some other method that allows you to create one report and is able to filter or parse records based on the e-mail address used to deliver the report.  

  8. Neil Benson says:

    Hi Donna, thanks for this information. It’s just a shame that direct access to the reports server is requires – this rules out this feature for partner-hosted and Microsoft-hosted deployments unless the partner is willing to perform all the report server configuration on behalf of the customer. Shame 🙁

  9. Shelley says:

    I created the snapshot and subscription for the CRM Neglected Accounts folder.  It is rendered in Web Archive format and delivered via email subscription. If I try to ‘drill-down’ or if I select Show All, a separate report view window displays with the error message "One or more data sources is missing credentials ".  I thought it might have something to do with the sub report, but I cannot figure out how to enable the drill down functionality.  Is there a way to do this?

  10. Donna Edwards says:

    Hi Neil,

    There is most likely a way to setup report subscriptions in a hosted environment.  You’re correct, this solution is for On-Premise installations.  

    I haven’t explored the solution for hosted environments but it sounds like a good idea for a blog.  Maybe someone can tackle that next.

  11. Donna Edwards says:

    Hi Shelley,

    Try this and let me know if it works.    

    1) Go to your Report server folder http://SQLServerName/reports

    2) Select Show Details from the upper right

    3) Select the CRM Report folder

    4) Select the 4.0 folder

    5) Find the Scheduled report that was created

    6) Select the Edit icon to the left of the report

    7) Select Data Sources from the left navigation menu

    8) Change the Data Source properties to the following:

    Use Connection Type = Microsoft SQL Server

    Connection String =

    Data Source=SQLServerName;Initial Catalog=CRMReportFolderName;Integrated Security=SSPI

    (you can copy and paste the above into the connection string and replace the two values with the unique values from your enironment – SQLServerName = the name of your SQL Server that houses the CRM database

    CRMReportFolderName = name associated with the directory folder that contains your CRM reports)

    9) Under Connect Using, select Windows Integrated Security.

    10) Select Apply

    If you have any problems, I will give you my direct e-mail and we can troubleshoot the issue.

  12. ssmart says:

    Donna,

    I tried changing the Data Source properties for the report subscription to enable the drill down functionality for reports generated by subscription.  I received the error message "Changing the report parameters or data sources to the values you specified is not allowed. The report is configured to run unattended. Using the specified values would prevent the unattended processing of this report. (rsOperationPreventsUnattendedExecution)".

  13. Donna Edwards says:

    You’re not trying to make changes to the snapshot by chance, are you?  

    Ok, let’s try this.  In your subscription e-mail that goes out to users, did you include a link to the report?  If not, There is an option in the subscription e-mail to include the report link.  If you include the link in the e-mail, can you use the drill-down feature when you access the report via the link in the e-mail?  

    If you wouldn’t mind, give me your e-mail contact and we can continue this offline.    

  14. ssmart says:

    Still having trouble…

    My email is ssmart@datexcorp.com.  

    I was editing the properties on the report snapshot created from CRM.  If I click the link in the email, I get errors as well.  I tested just viewing the report in the report manager, and was not able to drill down there either.

    I appreciate the help!

  15. Donna Edwards says:

    Ok, thank you, I’ll contact you with the solution.

  16. Peter says:

    Is there a way for me to send invoice reports to the account primary contact?  Not to a user of crm.  To a contact in crm.  Thanks

  17. Donna Edwards says:

    The subscription service doesn’t not provide a way to dynamically insert a recipient’s e-mail address, at least that I am aware of.  One workaround is to send the subscription to an internal staff member and have that staff member forward it to the appropriate person.  

  18. j4m35bond says:

    Hi Donna,Thanks for the article.

    I follow every step stated in this article but I got this error message in subscription status of report manager:

    Failure sending mail: The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available.Mail will not be resent.

    is it because I didn’t generate the snapshot in

    CRM?

  19. Donna says:

    If you follow all the steps above, then you don’t need a snapshot to run.  The subscription was originally set to Run on Demand so if that was selected initially rather than a schedule, everything should work.

    Let’s try this:

    Go to your Report Server http://SQLServerName/reports

    Select Show Details from the upper right corner

    Select the link to your Organization reports

    Select the 4.0 folder

    Locate the report that was created for the subscription

    Select the ‘edit’ icon to the left of the Report Name

    Go to the Execution link on the left

    Ensure the first box is selected:

    "Always run this report with the most recent data"

    Select Apply

    That should correct the issue.  If not, then provide your e-mail address and we’ll troubleshoot the issue offline.

    Regards,

    Donna

  20. j4m35bond says:

    Thanks for your help.

    My email is: henrytan@3psolutions.com

    Can I have your email?

    I wonder if pre-filtering still working when I choose the option: "Always run this report with the most recent data"?

  21. j4m35bond says:

    Donna,

    I have done the setup on my VPC and it works. However my production CRM app hosted on another machine, and my SSRS and CRM DB is on another machine. I think that is the root cause when I found the error in the SSRS log files:

    *********************************************

    Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing.

    —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source ‘DataSource1’.

    —> Microsoft.Crm.CrmConfigObjectNotFoundException: User Was Not Found at Microsoft.Crm.ServerLocatorService.GetCrmUserIdFromDatabase(Guid organizationId, String authenticationInfo)

    *********************************************

    I found that rollup 4 got the solutions from link below: (user not found)

    http://scottr27.blogspot.com/2009/05/crm-4-fixes-from-rollup-4.html

    However the CRM server got roolup5 install. What should I do? should I just continue install rollup 4?

  22. Donna says:

    I’ll get right back to you on this Henry

  23. Dilmer Valecillos says:

    Wow great tutorial I really appreciate you sharing this information.

    I’ve actually followed step by step carefully but I’m still unable to have a success email sent for a custom CRM report that we created.

    Error: Failure sending mail: An error has occurred during report processing.Mail will not be resent.

    Any thoughts?

  24. Donna says:

    Error: Failure sending mail: An error has occurred during report processing.Mail will not be resent

    That is a fairly generic error.  I would probably begin by confirming that your SQL Server has been properly configured for e-mail delivery.    

    Also, confirm that you are able to run the report from the Report Server.

  25. Phidias says:

    Hi we are having the same problem. rollup 6 is installed which includes # 4 so bug should be solved… google on CrmConfigObjectNotFoundException did not find more info.

    best regards, Phidias.

  26. Donna says:

    Hi Phidias,

    Have you been able to confirm the following?  

    Confirm that your SQL Server has been properly configured for e-mail delivery.    

    Also, confirm that you are able to run the report from the Report Server.

    Try creating a generic report, not CRM, and create a subscription to send that report.  Test to see if the e-mail will send.

  27. Sandy says:

    Excellent instructions Donna.

    One question, though…  

    My report parameters allow a user to "Select All", but that is not an available option when setting up a report to run On Demand.  I am unable to type in the * wildcard character.  Short of creating a report without those parameters, how would I set the report to display all?

    Thank you.

    Sandy

  28. Donna says:

    Hi Sandy,

    Thank you, I’m glad this was helpful.

    You can make a copy of your current report, remove the parameter and have your query pull back all records.  You can then use the new report for the subscription.  The only other option is to just send the report link and when users select the link they can enter the parameter at run time.

  29. Liz says:

    Hi Donna, thanks for the very informative post.

    I just upgraded to CRM 4.0 from version 3.0. In 3.0 I never created any email subscription from CRM, but I created it directly from the Report Manager. So far this was always working for us.

    However after we upgraded to CRM 4.0, we still receive the email subscription for the report, however when I click on the link for that report, I got error "An internal error occurred on the report server. See the error log for more details. (rsInternalError)". I still can see the attached excel document of the report just fine. I’m guessing we need to create report snapshot for the email subscription.

    So I carefully followed your post to create report snapshot first and then the subscription of that report snapshot. I can run the snapshot fine on the CRM report and Report Manager, however it’s not sending any emails out. It keeps giving me error "Failure sending mail: An error has occurred during report processing" on the Report Manager.

    Can you please give me an idea on how to troubleshoot this issue? Thanks very much for your help!

  30. edwardsdna says:

    Hi,

    "Failure sending mail: An error has occurred during report processing" is fairly generic and could point to any number or issues.

    You can have a look at these posts to see if they help you troubleshoot the issue.  If not, I’m happy to setup a remote session and have a look at your environment to see if I can identify the issue.  If you want me to help in that capacity you can reach me via my blog: http://edwardsdnadynamicscrm.spaces.live.com/default.aspx

    Troubleshooting Links:

    http://msdn.microsoft.com/en-us/library/ms159171.aspx

    http://www.developmentnow.com/g/115_2004_9_0_0_450789/Failure-sending-mail.htm

    http://www.windows-tech.info/15/f609c5fe4d06a9fd.php

    http://groups.google.com/group/ReportingServices/browse_thread/thread/d25aaf58d3f5d26b  

  31. nrodri says:

    Nice doc, thanks

  32. nrodri says:

    you said: "At a minimum Replace “ @ReportName” with the actual Report Name otherwise the value will be the GUID of the report which is not a user friendly name "

    we can actually change the report name and using @ReportName will use that name we changed to.

  33. Nestor says:

    What I'm wanting to do is to create a report that I can imbed into a CRM IFRAME and that will allow me to instantly email a preset recipient the report for the CRM record that I'm on. Is there a way to be able to do this?

  34. Krista says:

    This was so incredibly helpful. Thank you!!!

  35. Priya says:

    Hi Donna,

    This is an incredible help to me. I was able to schedule report and it is working fine in some extent. It is sending daily report at scheduled time but problem is attached Excel file. It is showing up previous day's data, while when run the report by clicking on the link, it is showing current data.

    For. e.g I have created this report to track activities added in crm, it is showing up activities added yesterday in today's report and activities added today in tomorrow's report.

    Thanks

  36. Simon says:

    Hi Donna

    thanks for the great post. Very helpful.

    One question – is it possible to send multiple reports (as pdf's) in one email?

    Thanks

    Simon

  37. Rimea says:

    Hello Donna,

    This is really helpful.

    I got one problem, I'm able to get email from this subscription where I get excel with all datas but when I click the link, it does not show me anything, I mean it opens the page with no data in it.

    Do you know what is causing that. Can you help me on this.

    My email is rimea1@hotmail.com. Appreciate your help.

    Thanks,

    Rimea

  38. jeffdodds@hotmail.com says:

    Hi Donna,

    Are you still available to assist on this issue?

    I am getting the following error when the scheduled job runs in my reporting service log:

    ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CRM'., ;

    Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CRM'. —> Microsoft.Crm.CrmConfigObjectNotFoundException: User Was Not Found

    Any ideas would be appreciated!

    Thanks,

    Jeff (jeffd at quadrus dot com)

Skip to main content