I'm working off the virtual server I created in this post.
There does appear to be a few caveats to the Data Alerts:
- 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)
- The report must used stored credentials or no credentials
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.