Thursday, September 27, 2012

CRM 2011 and SQL Server 2012 Data Alerts

First off, what are Data Alerts in SQL Server 2012? Data Alerts can monitor a set of data generated by a SSRS report and alert users when that data meets a pre-defined set of criteria. We can do this to a limited extent with workflows already in CRM. Say we needed to keep an eye on how long our service tickets stay open before being closed. We could create a workflow to run when an incident is created, and wait for a pre-determined amount of time and if the record isn't closed it could send an email indicating it is still outstanding. That works fine on an individual basis (assuming you are OK with a workflow pending for every incident that hasn't exceeded the threshold) but what if you wanted to be notified when the average age of all your open incidents exceeded a certain time? We can use Data Alerts to set up a notification.

I'm working off the virtual server I created in this post.

There does appear to be a few caveats to the Data Alerts:
  1. It appears the report used must be created in the SQL Reporting Services Report Designer (reports created in BIDS don't appear to allow a new Data Alert to be created against them)
  2. The report must used stored credentials or no credentials
We'll start out with creating a report to use for our alerts in Report Builder (http://yourSSRSserver/reports)

If you skip the wizard, you'll be presented with the Report Builder interface which is somewhat similar to creating a report using SQL Server Business Intelligence Development Studio (BIDS).

Right click on Data Sources to create one. Then add your connection string with the Build button. Keep in mind again that you need to use stored credentials in the connection - this means a SQL login or you can select the Credentials link and specify a Windows account to run under.

Then add a Dataset with a right click. Choose the data source you just created and configure the query to return data. In this example, I'm running under a Windows account which happens to be a CRM user (system administrator role) so use of the Filtered View works in this query. If you run under a different Windows account with lower CRM privileges, be mindful of how the use of the Filtered View will affect the query results. If you run under a SQL account, the Filtered View won't return any data at all so you would have to use the non-Filtered Views and also be mindful of the results.

Drop the fields on the report and give it a test.

So now that the report is all squared away - save it to disk someplace as we will need to import it into SharePoint.

Open up your SharePoint site and use Add document to upload the .rdl file to a document library.

Now - if you specified a Windows account in your data source, it is gone now. It does not appear to survive the saving process from Report Builder. Not a big deal as we can reset it in SharePoint. Hover over your report to get the drop down menu and select Manage Data Sources.

Select the link for the data source you created in your report and you will be brought to a page where you can re-enter the Windows credentials you wish to use.

Now, if we click the link for our report in SharePoint it should hopefully run with no complaints and give us some data.

Next we went to configure a Data Alert based on this information. From the Actions menu, select New Data Alert.

Here we have a few options on getting alerted:
  • Alert me if any data has or Alert me if no data has
    • Construct your rules picking a field, the condition, and value
  • Recurrence pattern - Check the data for changes on a weekly, daily, hourly or by minute basis
  • Email setting - specify the recipient but the subject line seems to be locked

And there you have it, assuming you have email correctly configured in SharePoint when our tolerance of 115 hours for a case being open is exceeded when the hourly check is run, we should receive an email letting us know.