SSRS 2012 Hosting :: Data Alerts in SSRS in SQL Server 2012

Data Alerts are similar to schedules, but they only execute when the data meets the rules you have specified. This means you only get the email containing the report when the data meets the criteria you set. Maybe your sales have dropped below estimates, deliveries fallen behind schedule, or your revenue has exceeded your latest milestone (and you need to celebrate!).

Previously, in SQL Server Reporting Services 2008 you could mimic this behaviour with some workarounds. You could create a Data-Driven Subscription using some custom SQL code to only include the recipients when certain SQL conditions were met. You could also create the report in such a way that it would throw an error under certain circumstances, which would stop the report being sent. However, none of these approaches are easy for an end user to create and the responsibility typically fell on the IT team to create and manage subscriptions for everyone.

Data Alerts are created in SharePoint using an easy-to-use wizard which enables business users to add rules against the data from the report without any knowledge of SQL, MDX or any other programming language, or without modifying the report. The user can define how often they would like the data alert to run, who it will be sent to, and what the subject and description of the email will say. Users can also easily manage and execute their Data Alerts from the Data Alert Manager page from within SharePoint.

As Data Alerts will be sent out less frequently than scheduled reports, inbox sizes will be smaller Administrators can also monitor and manage all data alerts set up in SharePoint.

With Data Alerts in SQL Server 2012, business users can now create alerts to monitor the information they need in order to make the correct decisions at the right time without overloading their inbox and without the overhead of reviewing unnecessary reports.

there is the step to Configuring and Testing Data Alerts :

1. Open Internet Explorer and navigate to the SharePoint site created during setup.

2. Click the Data Connections library.

3. On the Documents ribbon, from inside the New group, click the New Document down arrow, and then select Report Data Source.

4. In the Name box, enter YourDataBase.

In the Connection string box, enter: Data Source=<Server Instance>;Initial Catalog=YourDatabase;

(substitute <Server Instance> for the SQL Server database engine instance that hosts the YourDataBase database.)

5. Select the Stored credentials option.

6. Complete the User Name and Password boxes using appropriate credentials for your environment. The credentials must have read permission on the YourDataBase database.

7. Check the Use as Windows credentials checkbox.

8. Click OK.

9. Navigate to the Reports library.

10. On the Documents ribbon, from inside the New group, click the Upload Document down arrow, and then select Upload Document.

11. In the Reports window, click Browse.

12. In the Choose File to Upload window, navigate to the Assets folder located in the source folder for this demonstration.

13. Select the Pacific Profitability.rdl file, and then click Open.

14. In the Reports window, click OK.

15. In the Reports window, click Save.

16. To associate the shared data source, hover over the report, click the downarrow, and then select Manage Data Sources.

Click the YourDatabase link.

17. To the right of the Data Source Link box, click the ellipsis button.

18. In the Select an Item window, click the Up link.

19. Click the Data Connections library.

20. Select the YourDatabase data source, and then click OK.

21. To complete the configuration of the report’s data source, click OK.

22. Click Close.

23. To view the report, click the Pacific Profitability report.

24. Explain that the report displays a gauge that describes profitability and status. Point out that the profitability is in a healthy range (defined by the green range).

25. Explain that you want to be notified if the profitability drops beneath 10%.

26. On the Actions menu, select New Data Alert.

27. In the New Data Alert window, on the left side, point out the feed named Profitability which consists of a single field named ProfitValue.

28. On the right side, click (Add rule…), and then select ProfitValue.

29. Click is, and then select is less than.

30. Click to the right of is less than, and in the box, enter 0.1 (representing 10%).

31. In the Email settings section, in the Recipient(s) box, enter [email protected]

32. In the Description box, enter Profitability is off target.

33. Click Save.

34. In the top left corner, click the Reports link.

35. To configure a data alert, hover over the report, click the down arrow, and then select Manage Data Alerts.

36. Right-click the alert, and then select Run.

37. Reload the web page.

38. Point out that the status that confirms that no alert was sent.

39. Go to Start | All Programs | Microsoft SQL Server 2012, and open SQL Server Management Studio (SSMS).

40. Connect to the instance where the YourDataBase database is installed.

41. On the File menu, select Open | File.

42. In the Open File window, navigate to the Assets folder located in the source folder for this demonstration.

43. Select the UpdateProfitability.sql file, and then click Open.

44. Explain that this script will update the profitability value to -4%.

45. Press F5 to execute the script.