mardi 13 octobre 2015

How to make a SSRS work for multiple users

I can't find anything on this so I don't think I am asking the question right but here is my situation. I have a stored procedure which the end user passes a list of filter criteria, since I don't know what the filter criteria will be I used dynamic SQL. Further, to allow for more then one user to run the stored procedure concurrently I used all dynamically named temp tables so there would be no collisions. That all works.

Now my problem is how to output the report. Right now I have a SSRS report pointing to a single database that the stored procedure dumps its output to. When the stored procedure finishes the report is displayed. This works for one user but if two users run the stored procedure at the same time I have no way of knowing which output data will show on the report. Complicating issues is the dynamic user filter criteria can greatly effect the time the stored procedure takes to complete. I can see the report loading data just as the other stored procedure session is truncating or loading data to the output table.

I can queue up requests and run them one at a time but ideally I want them to be able to run concurrently as several users have to run this report many times at the beginning of each month. Is there a way to ensure that the data displayed on the report to the end user matches the data outputted from the stored procedure session the user ran.

Thanks.

Aucun commentaire:

Enregistrer un commentaire