Updating data from a report: any potential pitfalls?

Question

Question:

In general, are there any pitfalls with updating data from a SSRS report?*

Context:

More specific, I'm talking about this context:

• SSRS 2008 (with the option to upgrade, if needed).
• MSSQL 2008 (with the option to upgrade, if needed).
• Web.forms ReportViewer 2010 in our own ASP.NET application.

I only need to do a minor DB update. The user can tick a checkbox parameter "I'm going to print this" which will do a small update or insert to mark the report has been run at a specific point in time. The report will only show items were added since the previous "print" run, so I filter in my WHERE clause based on that, giving the user only "new" items.

The reason I'm worried is because it doesn't "feel right" to do updates from a tool that's meant primarily for viewing data. On the other hand, with something as small as above it seems like the easiest solution.

Specific concerns:

Here are some specific concerns I have about which I'm seeking insight:

• Security: the report's connection will need to be run with a user that's allowed to do inserts, where a more sensible default would be to run it for a select-only user? Are there known sql-injection-like attacks for SSRS?
• Usability: I'd like to do the update depending on whether a user ticks a checkbox. I'm not sure how well this would play with refreshes, and if you can detect what the rendering format is while doing the queries (which would allow me to make the checkbox trigger updates only on -e.g.- a PDF export).
• Robustness: update statements seem more prone to exceptions (violating constraints, etc). Not sure if there's an elegant way to prevent, log, track, and debug that kind of problem.

Answer 1

Honestly, don't do it. Yes it is possible to do it by declaring a global variable to your report and then passing that to the statement that generates the result set to do some processing before returning data but it is messy and ill-advised at best. What is to say that in the future you won't want to add more variables to the report? At this point it technically becomes a form - which is what ASP.NET was designed to do - not what SSRS/ReportViewer was designed to do.

You say that you are using Report Viewer in a ASP.NET web application - so my recommendation would be to handle the print logic within the application itself if you can and leave the report to do what it does best. We have an application at our company that produces a report in a similar fashion - and we needed to track who was printing the report and when - we did this by disabling the print functionality within the report and then supplied functionality within the application to render the report to a PDF and then print it. This allowed us to be very flexible in what we track/audit and how the report was generated and where it was printed.

Also, keeping the two pieces of logic separate will be better in the long run - for instance if you need to change the report this could be done external to the application and then you just provide the rdl file and replace the old one. Putting code within the report to change data means extra testing and blurs the boundaries of responsibilities.

Additionally, as you mention above - there is the possibility that data could be compromised one way or another (SQL Injection etc..) and having the application validate the input is by far the correct thing to do - not to mention adding all the extra SQL needed to validate, update and then return data would slow your report down. This may not seem like much if you only have a few users but if your user base is large it could cause issues.

It may seem like the quickest solution right now but that is not necessarily the right solution. Keep it simple and separated and you will find you will have a lot less problems in the long run.

I hope this helps.

Answer 2

What you are describing is more like a print log where you insert data about when and whom has requested the report. If this, is so, it is valid. But the part about hiding the data is not clear in your description. The data you are reporting should, generally,not be changed in the database as part of the reporting process. There are situations where you have to mark each printed report as 'printed' and hence update its status but from your description, I can't see that this is the case.