Saturday, 26 January 2008

Scheduling and emailing reports with CRM

A common customer request is to be able to schedule CRM reports and automatically send the report to one or more users by email. This can be done without the need for any code, but needs a combination of configuration within both CRM and Reporting Services.

CRM Configuration
CRM reports are designed to be run under the context of the user running the report so as to return only the data that user has permission to see. Due to this configuration, Reporting Services will not permit you to schedule CRM Reports out of the box (if you try, you'll get the error 'Subscriptions cannot be created because the credentials used to run the report are not stored') . The easiest way to resolve this issue is to download the CRM Report Scheduling Wizard from here and install it on the CRM server.

Then, navigate to Workplace, Reports in CRM, select the report you want to schedule, and go to More Actions, Schedule Report. As you will do the scheduling later within Report Manager, I'd suggest you select the following options:
  • Generate snapshots 'On demand'
  • 'Make snapshots available only to me'
  • Specify any parameter values
  • You then need to specify the credentials under which the report will run. This needs to be a valid CRM user
  • 'Yes, generate the snapshot now'

Reporting Services configuration
The rest of the configuration is done via Reporting Services. First of all, you may have to configure the email properties of Reporting Services. To do this open RSReportServer.config, which by default will be in the Reporting Services\ReportServer directory under the SQL installation directory in Program Files. Within the 'RSEmailDPConfiguration' element you will need to set values in, as a minimum, the 'SMTPServer' and 'From' elements. You may need to apply other settings. For more information see the documentation for SQL 2005 or SQL 2000

Scheduling the Report in Report Manager
You can now schedule the report. Browse to Report Manager (by default it will be in the Reports virtual directory), go to the OrganisationName_MSCRM folder and the report you have scheduled (it's name will have the suffix On demand Snapshot). On the toolbar you should have a button 'New Subscription'. Click this, select 'Report Server E-mail' in the Delivered by drop down, and set all other options as required. (If 'Report Server E-mail' is not an option, go back to the instructions in the above paragraph

at

12 comments:

  1. b1ur28 January 2008 at 15:59

    Easy and useful, thanks!

    ReplyDelete
  2. Kay9 February 2008 at 17:15

    Can you use this with CRM 4.0 ?

    ReplyDelete
  3. David Jennaway10 February 2008 at 12:36

    Yes, this works with CRM 4.0 as well

    ReplyDelete
  4. BlaBla24 November 2008 at 13:23

    Hi.
    when i try to install the schedule update i get this error.
    " Setup could not find MS CRM 3.0 Server or you have a mismatch between update language and MS CRM language"

    I have CRM 4.0 currently installed.

    ReplyDelete
  5. E^ka22 January 2009 at 11:46

    "go to the OrganisationName_MSCRM folder and the report you have scheduled (it's name will have the suffix On demand Snapshot)"

    When i go to the OrganisationName_MSCRM folder there is no report (It is empty). Why it may be?

    ReplyDelete
  6. David Jennaway22 January 2009 at 12:57

    E^ka

    Thanks for pointing this out - this is due to a difference in behaviour between CRM 3.0 and CRM 4.0. I've created a new post on http://mscrmuk.blogspot.com/2009/01/viewing-crm-40-reports-in-report.html, which describes what to do

    David

    ReplyDelete
  7. E^ka23 January 2009 at 10:18

    Thank you.... Please, help...
    Where can i download the CRM Report Scheduling Wizard for CRM 4.0 ? Becouse, when i try to install the schedule update i get this error.
    " Setup could not find MS CRM 3.0 Server or you have a mismatch between update language and MS CRM language"

    ReplyDelete
  8. E^ka23 January 2009 at 10:57

    On the toolbar i have't a button 'New Subscription'.
    Why it is possible?

    ReplyDelete
  9. David Jennaway23 January 2009 at 15:27

    The Report Scheduling Wizard was a spearate download for CRM 3.0, but for CRM 4.0 it is incorporated into CRM, so you do not need to install it separately.

    One reason for not seeing the New Subscription button is if you have the Workgroup Edition of SQL Server. This edition does not support subscriptions.

    ReplyDelete
  10. E^ka2 February 2009 at 08:08

    Thank you! But i have a new problem..
    I use Scheduling the Report in Report Manager. And i see in Report Manager (in My Subscription):

    "Failure sending mail:The transport failed to connect to the server"

    Why it may be?

    ReplyDelete
  11. David Jennaway2 February 2009 at 20:49

    This indicates a problem with the configuration of the Email Delivery extension, which is a component of Reporting Services. Either it is not configured correctly, or there is a permission problem. See http://msdn.microsoft.com/en-us/library/ms159155(SQL.90).aspx and http://msdn.microsoft.com/en-us/library/ms155851.aspx

    ReplyDelete
  12. Unknown18 August 2010 at 16:29

    Hi David,

    very interesting!!! thanks! for the information.

    I am working with Ad-HOC reporting, need to schedule a report created by Ad-HOC tool.

    I follow these steps:
    1)I designed a report in Ad-HOC tool and save that report design(RDL) on Report server. Then I go to report server and try to create Subscriptions/schedule. it gave me the error
    "subscription cannot be created because the credentials used to run the report are not stored or Link Report, the link is no longer valid"

    In this scenario, can I use CRM to schedule Ad-HOC Report. (I do not know anything about CRM, is it free touse with SSRS)

    Thanks again,
    Mahesh

    ReplyDelete