Microsoft Dynamics CRM Report Scheduler

Reports are a huge part of the business cycle. One of the most valuable tools that CRM provides is the ability to report on data points that matter to users on various levels, as well as cut down the time it takes to assemble those reports. But what is the point of spending time and effort in building a report if your desired audience never takes the time to go into CRM and view it? Let’s face it, training and documentation can only do so much in order to influence user adoption and change user behavior. Some users need things handed to them on a plate for them to take the time to consume it.

The report scheduler can help to fix this ‘consumption’ problem. The idea behind this example is simple:

• There is a report that is assembled every month and reviewed by the same people.
• Rather than have those people come into CRM and run the report every month, why not have the report sent to them automatically?

STEP 1: SCHEDULE THE REPORT

The first thing which must be done is to define when you want the report to run and pull the data which you want to send. CRM then creates what is called a ‘report snapshot’, which is a view of the data as it exists at a certain point in time. To do that:

• Within CRM, select the report which you want to schedule, and then click on ‘Schedule Report’ in the ribbon. In this case, we are going to schedule a report which we prebuilt using the report wizard that shows all opportunities that were lost last month.

• Select ‘On a schedule’, and then click Next
• Here is where we select the schedule of the snapshot. Since this is a monthly report, we will want a snapshot of the data as it exists at the end of each month. Since each month can potentially end on a different ‘number’ day (February 28th vs. July 31st), we will set the snapshot to be created on the 1st. Then click Next.

• The start date defaults to today’s date, which we will leave alone. If you have wish to have the report stop running at a certain date, choose that in the End on: field. If not, leave it at the default of ‘No End Date’ (which is what I typically do, as you can simply delete the snapshot if you want it to quit running). Once you have made your choices, click Next.
• Here you can fill out any parameters of your report (if you have them). Out of the box (OOTB) report parameters can be defined here similar to advanced find selections. More complex reports loose the ability to have dynamic parameters defined here. More on that in lessons learned. Once you have defined any parameters of your report, click Next.
• Here you can review your selections before finally creating your snapshot. If everything looks good, go ahead and click ‘Create’.

STEP 2: SET UP AN EMAIL SUBSCRIPTION

Once that you have defined the data that will be sent out and set up the snapshot, we will define when we will send it out as well as who we will send it to.

• Navigate to the report manager (http://reportservername/reports)
  • TIP: If you are unsure of your report server name, you can get this information from the Deployment Manager which is located on the application server. Double click on your organization, and the SQL Server Reporting Services URL is listed here. It will end in ‘reportserver’. Simply edit the URL to end in ‘reports’.
• First, in the upper right hand corner of the window, click on ‘Details View’. Then click on the folder for your CRM organization, then on the folder for ‘CustomReports’.
• Locate the snapshot created in step 1 above. Once easy way to locate this is to sort by modified date. Once you have located it, hover over the ‘name’ of the report, click on the dropdown menu and select ‘Manage’

• First, rename the report. By default the system sets the GUID of the report as the ‘name’. We want to clean this up and put a clean name here, as this name will be passed into the email subscription. Then click Apply.

• Navigate to ‘Subscriptions’ on the left of the window, and click ‘New Subscription’
• Here, define who we want to send the email to, as well as when we want it to be sent out. Input your email addresses, separated by semicolons.
  • TIP: if you are emailing the report out to anyone who is not a user in CRM, be sure to uncheck the ‘Include Link’ box, as this will cause errors.

• For scheduling the email, there are the following options:
  • When the report content is refreshed
    • The idea behind this option is, as soon as the report snapshot is run (which is defined in step 1, and for our example was the 1st of the month at 12:30 am), the email will automatically be sent out. Since we may not want to send emails out in the middle of the night, this is not the option I’d like to go with for this specific example.
  • A defined schedule time
    • For this option, we would click ‘Select Schedule’ and define when exactly we would wish to send this out. For this report, I’d like to send it out on the 1st of the month at 9 AM

• Once we have input the WHO and set up the WHEN, we can then click OK, and you are finished!

LESSONS LEARNED:

• For more complicated reports that need to be built using SSRS instead of the OOTB report wizard in CRM, if you are going to use date parameters for your dataset, be sure to code those parameters within the SQL instead of setting up actual parameters that need to be defined. The scheduler isn’t ‘smart enough’ to choose dynamic values for SSRS based parameters, so they are best defined in the code itself.
• When sending report subscriptions to more than just a handful of users, or when sending to a list of users which may need to be edited from time to time, it may be best to set up a distribution list. This way you can maintain the list from there, rather than having to edit your subscription settings each time you need to add/remove someone from the mailing.

by Customer Effective