SQL11UPD06-TSCRIPT-06

This wiki is a transcript of a previously recorded video.

Related content assets:

• Demo: Configuring Data Alerts with SQL Server 2012 Reporting Services (SQL11UPD06-DEMO-03)
• Video: Configuring Data Alerts with SQL Server 2012 Reporting Services (SQL11UPD06-REC-06)

Configuring Data Alerts with SQL Server 2012 Reporting Services

Welcome to this demonstration, Configuring Data Alerts with SQL Server 2012 Reporting Services. I’m Peter Myers. I’m a SQL Server MVP and a Mentor with SolidQ.

The starting point for this demonstration is to introduce you to my SharePoint site. We need the requirement for Reporting Services data load in SQL Server 2012 that it works only in SharePoint mode and the data sources you can configure must use stored credentials. So I navigate to the Data Connections Library and my first step is to go ahead and create a new report data source. So the data source I have is the Adventure Works data warehouse. I’ve already copied and pasted in the connection string to the clipboard, so I paste that into the box to use in the connection string and this is where I configure those stored credentials. I don’t recommend using the administrator account out there in the real world, but for demonstration it’s pretty convenient. I check the Use as Windows Credentials checkbox. I test the connection; all looks good, so I click OK to complete the configuration.

Now I navigate to the Reports library and here on the Document tab I’m going to go ahead and upload a pre-built report named the Pacific Profitability. This is a pretty straightforward report. What it does is present a gauge – the gauge presents a single value of the profit that is produced in the Pacific region, but also conveys status, whether that profitability is good, slightly good, bad. I’m just waiting to click Browse. There seems to be some sort of delay with my user interface. Then I select the Pacific Profitability report, Open, and Upload. Next I go ahead and ensure that that report uses the shared data source that I just created. For the data source in the report I have it referenced from my Data Connections library, the AdventureWorksDWDenali Connection.

Now we can go ahead and view the report. As you see, here is the gauge. Presently profitability in the Pacific region is at 18 percent. We can tell by the ranges on the gauge that this is good because we have this green range here. What I’m going to do is configure a data overload to notify me if the profitability falls below this 10 percent threshold. Here on the toolbar for the Report Viewer, under Actions, I configure a New Data Alert. We see here that the feeds generated from this report. You may recall in SQL Server Reporting Services 2008 R2 a new rendering format was added, being the ATROM rendering format. It is used here also by data alert. Here is a view of the data being returned from the profitability feed of the report. Here’s where we see the current value of 18 percent. Here on the right side I configure the alert by name: I click to add a rule, and the rule will be that the Profit value is less than 0.1 (which is the 10 percent). Under Scheduling Settings I can schedule different time periods. I could actually have this run every hour. Under Advanced I could specify the duration for this alert, and also this checkbox here, Send Message Only if Alert Results Changes. Basically, if the data alert result doesn’t change, and every hour keeps coming back with 2 percent, 2 percent, I don’t need to receive a notification every time. But if after the next hour it comes back as 1 percent, that change should fire off a new notification. The Email Settings then, I’m going to specify it gets sent to my e-mail address, the subject of the email and a description like “Profit off track”. Then I click Save. Now that the alert is configured, the Report Server looks after that automatically.

Let’s look at how we can manage data alerts. Back to the report here in the library – we have the ability to Manage Data Alerts. Note in this window it actually serves two different groups of users. If I had administrative privileges then I can toggle between all alerts like All Users on that report. As myself I can toggle between alerts for multiple reports that I may have configured over here. This line is the alert that I just defined so we can see that it’s being created, that zero alerts have actually been sent. What I can do to test that (rather than wait for that hour to take place), I can right-click and I can say Run. It runs the alert instantly which is very, very helpful when testing the data alert configuration. I’ll refresh the page to see the updated status. In fact, we see the status here telling me that the last alert ran successfully but the data conditions were not met. Of course, it’s still 18 percent.

What I’m going to do is modify the data source. Switching across to SQL Server Management Studio (SSMS), I open up a script to update the profitability. This query here performs an update to ensure the profitability is now -4 percent. Again, I could wait for an hour – I’m not going to. I switch back to the Data Alert Manager, right-click, and run again. I do need to refresh, and we see the status has updated to say the last alert ran successfully and the alert was indeed sent. In this environment I don’t have an e-mail server so we’ve configured the Report Server instance to send email notifications to the file system, so I open up Windows Explorer and sitting in the C: drive I have an email folder, and sure enough, here’s an email notification. I double-click to open up Outlook and open up the message and here’s the email notifying me of the data alert for Pacific Profitability – here’s a description of profit off track, there’s the value that raised the notification, and there’s the rule that raised it. So in addition to the notification we have a link that allows me to navigate to that report and here is the on-demand version of the report showing us, for sure, that profitability is off track at -4 percent.

There we see it. Data alerts, configuration of them, their dependency on reports that use stored credentials, and the way we can test them using the Data Alert Manager.

Thanks for watching the video.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)