Engage Users with Scheduled Reports

Of my top Six Rules for CRM User Adoption, #4 is Engage. By this I mean taking action to proactively engage your user community, rather than waiting for users to go to CRM. One way of doing this is to push information from CRM to users’ corporate email inboxes, rather than relying on them to go to CRM to find it. For example, I get an email in my inbox every day that looks like this:

Here are some of the things I like about an email like this one:

• Goal records can be difficult to work with for new users: they’re a little hard to find, the default forms are somewhat complex, the charts can be a little forbidding. This email presents the essence of the goal in a way anybody can understand.
• It includes the link to navigate directly to the goal record, thus solving the “hard to find” problem.
• It gives you a chance to provide advice (or hectoring, depending on your perspective) and continue the user’s learning experience, as with the bold-faced text in this email.

In the rest of the article I’ll review some of the main ways to push CRM information out to users, discuss implementation details for one approach I like that uses a workflow to send report-like emails to users on a scheduled basis, and scenarios that would require this approach.

Approach #1: SSRS Reports

Dynamics CRM has a built-in tool (the Report Wizard) you can use to create simple reports, but for anything more than moderately complex you’ll need to use SQL Server Reporting Services (SSRS). While it’s not literally true that you can do anything with SSRS reports…it’s pretty difficult to come up with CRM reporting requirements that can’t be satisfied if you take this approach. SSRS reports can include any combination of lists, charts, matrix reports, support drill-down, conditional formatting and too many other features to mention here. Summary-detail reports allow you to create custom versions of things like the Dynamics CRM Quote report, and you can mix static and dynamic text on the design surface to create what are effectively mail merge templates. For example, the email I showed in the previous figure could be created as an SSRS report.

SSRS reports can also be emailed automatically to designated users on a scheduled basis…but only for Dynamics CRM on-premise, not for CRM Online. What I want is an approach that works for both on-premise and cloud-based Dynamics CRM, so that rules out SSRS for my present requirements.

As long as we’re on the topic, here’s a quick reference, in pros and cons format, you can use to judge the suitability of SSRS for your reporting requirements.

SSRS Pros

Provided you’re running Dynamics CRM on-premise, you can basically satisfy any reporting requirement you can think of.

SSRS Cons

• Requires significant expertise in a SQL/developer skill-set.
• Significant differences between on-premise and Online. I mentioned one of these above (no scheduled SSRS reports for CRM Online), but the more you dig into SSRS the more CRM Online-specific limitations you run into. Another one is that queries for CRM Online SSRS reports must be done in FetchXML rather than the SQL queries you use for on-premise. Gonzalo Ruiz wrote a nice summary of the differences, so I won’t go into detail on that topic, other than making the editorial comment that Gonzalo was being polite when he said, of FetchXML, “Functionality is restricted to what the Report Wizard can support which can be quite limiting at times.“

  This general issue – capability differences between on-premise and Online – may not matter to you if you’re a corporate developer and can target your efforts exclusively to the on-premise model. But if you’re a consultant or an ISV and need to support both, it’s a major headache and something I hope gets fixed sooner rather than later.

Approach #2: Push Emails with a Workflow Process

To review, here’s the set of requirements I need to satisfy:

• Push a report-like email, combining Dynamics CRM data with static text, to a CRM user’s inbox.
• Include formatted data values in the body of the email, or links to Dynamics CRM reports or dashboards.
• Send it automatically on a scheduled basis (daily, weekly, monthly…)

Here are the main challenges posed by these requirements:

• You can certainly use workflows to send emails to users, but the email editor isn’t great, and you need to know a couple of tricks to send an email with formatting (e.g., like the table in the example above), graphics, and the like.
• Which record type to write the workflow for? Sometimes the answer to this will be obvious, but sometimes it won’t. I’ll give you an example of both categories below.
• Workflow processes cannot really be scheduled in Dynamics CRM. But you can get pretty close to the same result by combining timeouts with a workflow calling itself recursively.

Let’s step through a workflow that illustrates these points, generating the daily goal progress report shown above. The Goal Progress Report workflow written for the Goal record type, and is available to run as an on-demand process and as a child process:

The following figure shows the workflow logic:

Basically, it does two things. The first highlighted area shows where it uses the Send e-mail action to push the email out. (The reason I’ve got two separate Send e-mail steps nested inside an If…Otherwise block has to do with an anomaly of the Goal record type; more on that below).

The second highlighted area is where the process goes into a Timeout. (Sending out on a daily or weekly schedule is easy and exact, but monthly or quarterly are more difficult, because you can’t do date arithmetic in the process design environment.) After waiting for the specified period, it kicks in again and checks the status of the goal record. As long as the goal is still active, the Start child workflow action calls the Goal Progress Report workflow again, and the process repeats.

How do you format the email to push out? This is illustrated in the following figure:

The two most important points about the Send Email properties:

• As I said above, the email template editor is pretty weak. For example, there’s no Insert Table button on the toolbar. But you can insert a table, by using an HTML editor and then copying and pasting the rendered HTML. Don’t copy the HTML code – that doesn’t work. Simply view the table (or whatever it is you want to bring in) as a web page, copy that to the clipboard and then paste it into the editor. Graphics need to be linked to files on a web site available to the viewer.
• Notice the dynamic values for the goal record refer to the specific type (Decimal, in this figure). This is what I referred to above as the anomaly for goal records. It’s a fine point, but if I use the generic text version of the Target, Actual and In Progress fields, values don’t display in the generated email. So as far as I can tell, it’s either have different workflows for different goal data types, or use a conditional block to select the appropriate template.

Closing Thoughts and Possible Extensions

One of the reasons the goal record type works well for the scenario I discussed here is its complexity and the fact that it’s an aggregator record. I wouldn’t recommend just creating goals and turning users loose on them (either through the default forms or charts), so the ability of a report-style email to simplify the presentation and add editorial content is useful. And the fact that they aggregate the participating records means they can present a much more digestible summary of large data sets than alternative views. For example, here’s an email I have pushed to my inbox so I can keep daily tabs on Trick Bag page views progress against quarterly targets:

Here are a few ways you could extend this and some different things you might try:

You can use “URL-addressable forms” to include links to Dynamics CRM forms in regularly pushed emails. And since reports are displayed on a form, that means you can include links to reports, as in the following email I get weekly:

What about dashboards? If you navigate to a dashboard you’ll notice it does not have its own URL, so you might think URL-addressable forms wouldn’t work. But…if you click the View the records button on a dashboard chart, as highlighted in the following figure, you do get a window with a unique URL which can be included in an email.

So the dashboard link like the one in the screenshot before last can take a user to something like this: