SQL Server 2012 & Project Server, Part 1 – Reporting Services Data Alerts
By blurg64
Project Server has a fantastic reporting story, with a dedicated reporting database containing pretty much every piece of data you put in, and a myriad of ways to retrieve, tabulate and visualize that data for the end user. With a little customization or configuration it is possible to build reports that will show the status of the project portfolio, risk and issues and just about anything you want to see. With such powerful reporting it can quickly become difficult for a Project or Portfolio manager to identify the projects or information they are interested in seeing.
Enter Reporting Services Data Alerts, a new capability in SQL Server 2012 that allow users to create specific alerts when a data condition in a report occurs. For example, an organization could have a specific report that highlights all risks across the project portfolio, however the portfolio manager may only be interested when one of their projects is on the report with risks of high or critical. With a SQL Server Reporting Services Data Alert, the portfolio manager could create a data alert on the report that will would automatically send a copy of the report to them when the conditions are met.
Setting up a data alert is incredibly simple. To start with, run the report that you wish to set up the data alert on. In this example I have a very simple report that shows the number of extreme risks across all projects in the portfolio.
On the actions menu, select new data alert (If you are trying this yourself and the New Data Alert option is disabled, then you may need to set modify the report dataset to use a shared data source with stored credentials).
A dialog will pop up showing the different components of the report and allowing the user to define the different conditions that will cause the alert to fire.
Finally, the user can configure how often the data alert will poll, ranging from minutes up to months, and where to send the alert.
The output from the alert is a nicely formatted email containing the report, details of the rules used to generate the alert and links back to the underlying report.
Reporting Services also provides a mechanism for a user to view all the data alerts they have
configured as well as to modify, delete and run data alerts outside of the schedule.
All in all this is a pretty handy capability to provide your users should you be running SQL Server 2012.