January 30, 2019 at 9:02 am
I work for a small school district, and we currently have SSRS 2012 running in SharePoint-integrated mode. The SharePoint is on-premise 2010 version. So basically, we have some older technology here, and I feel the time to think about moving into the future is approaching.
One of the features that has delivered the most value for us over the years is the Data Alerts. Primarily, our attendance policy prescribes actions to be taken when students exceed counts of absences in their classes. Data Alerts have been very useful in aiding our schools' adherence to the policy. I have the alerts set to check the count of absences report nightly and email schools' attendance secretaries when the counts exceed the threshold defined in our policy. Without getting into too much detail, suffice it to say, we think Data Alerts are very useful.
As I've researched updating from our antiquated reporting setup, my initial impression is that Data Alerts as I'm familiar with are no longer part of SSRS. Is that conclusion accurate? If Data Alerts are no more, can anyone suggest alternative approaches in newer versions of SSRS/SharePoint to realize roughly the same functionality?
January 30, 2019 at 11:05 am
I still see this doc for 2016/2017. Are you sure this is going away?
This isn't hard to write in T-SQL with dbmail, but it seems easier with data alerts.
January 30, 2019 at 11:38 am
In the link you provided (thank you, excellent information!), they note that Reporting Services integration with SharePoint is no longer available after SQL Server 2016, and they also note that data alerts feature is available only when reporting services is installed in SharePoint mode. Does this mean that SSRS 2016 is the last version with which it will be possible to employ Data Alerts as we know them?
I'll have to research T-SQL/dbmail approach if for no other reason than to have a contingency plan in case I won't be able to rely on Data Alerts anymore. Thanks for that suggestion, Steve!
January 30, 2019 at 11:39 am
Steve Jones - SSC Editor - Wednesday, January 30, 2019 11:05 AMI still see this doc for 2016/2017. Are you sure this is going away?This isn't hard to write in T-SQL with dbmail, but it seems easier with data alerts.
That's defaulting back to the SQL Server 2016 topic. It's no longer available with SQL Server 2017 as it was part of the Reporting Services integration with Sharepoint which is not available with SQL Server 2017 and higher.
I agree that the query is probably not bad to write and something could be developed for those alerts. I'd be more concerned with upgrading and losing the Sharepoint integration with Reporting Services if it's heavily used. If the upgrade was to 2016, users would still have the SSRS integration with Sharepoint and the data alerts.
Sue
January 30, 2019 at 12:58 pm
Sue_H - Wednesday, January 30, 2019 11:39 AMI'd be more concerned with upgrading and losing the Sharepoint integration with Reporting Services if it's heavily used.
That's a good point, Sue. Fortunately our usage of our on-premise Sharepoint never evolved past it being a glorified report server. Most of our document management and collaboration is going on in Sharepoint online, so at least for us, losing Sharepoint integration won't be too disruptive.
My dream would be for SSRS data alerts to show up in Sharepoint online...but I don't think that's going to happen.
January 30, 2019 at 1:34 pm
dludwig-1073954 - Wednesday, January 30, 2019 12:58 PMSue_H - Wednesday, January 30, 2019 11:39 AMI'd be more concerned with upgrading and losing the Sharepoint integration with Reporting Services if it's heavily used.That's a good point, Sue. Fortunately our usage of our on-premise Sharepoint never evolved past it being a glorified report server. Most of our document management and collaboration is going on in Sharepoint online, so at least for us, losing Sharepoint integration won't be too disruptive.
My dream would be for SSRS data alerts to show up in Sharepoint online...but I don't think that's going to happen.
They won't from what I read since it's part of the integration with SSRS. And pretty much all of the documentation on Data Alerts includes the note of:
Reporting Services integration with SharePoint is no longer available after SQL Server 2016.
I don't think you'd have a hard time writing something up in t-sql though and having a job to send something out.
Sue
November 18, 2020 at 6:25 pm
Reading this thread, I'm curious how one would actually go about writing something in T-SQL and using dbmail as a substitute for this kind of functionality. I've not used data alerts before, just reading up on them recently, but we don't use SSRS with SharePoint so they never would have worked for us anyway. However, I'm intrigued by the functionality, definitely see use cases for us, and would like to see if I can reproduce something like this for SSRS 2016. It sounds similar to data driven subscriptions (which I believe is only available in enterprise editions), so if we can create something like this ourselves, that would be awesome.
Anyone have any more detailed insight on how to go about creating something like this?
November 18, 2020 at 6:38 pm
How would I do it?
I'd write a query in a proc that looks for the item I want to alert on. Low stock, pirce change, etc. that gets me data. If there are results, I'd store them in a table, with a date of the query. That way I capture the data. I might either clear out the table at start, or maybe include a sent/unsent flag in a column.
I'd write a proc to read this table, and if there are unsent results, or recent ones, send them out.
Use an Agent job to call proc 2 on a regular basis.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply