- Question: I have created a report based on SP on SSRS. It is set as weekly Monday morning auto run and email the report to every subscriber Monday Morning with report link. The report is fine. It populate the weekly report. But when subscriber click the report link it update the report which I don’t want. The report should update only once a week where users click the report 1000 time. I need help from the export please.
When you say the users 'update' the report - I am guessing/assuming you mean your stored procedure is executed and that stored procedure builds/updates a table in some database.
A report should only read data from a source system - it should never (in my opinion) update data in a source system as part of generating the report.
If that is what you are doing you need to reconsider how you are producing the data for this report. If the code you are using to generate this data just takes too long for users to wait - there are many other options, the last of which would be creating a table specifically for that report.
For example - you could easily create the report to run a query that takes even up to 30+ minutes to execute - wrap that in a stored procedure and use that stored procedure for the report. In SSRS - you then create a schedule to generate a snapshot on a scheduled basis, set it up so the report is rendered from the snapshot - then create a schedule based on the snapshot being updated that sends out notifications.
Another option - create a separate process scheduled in SQL Server Agent to build what you need (a table of qualifying items with internal values (PK/FK) only). Then you build a procedure that uses these tables for the actual report...
The key to your issue is making sure your report is rendered from the snapshot - and that you generate the snapshots on a scheduled basis.
Thanks a lot. Definitely I will try based on your suggestion. Appreciate your quick response.
--below is my store procedure for the report
/-- it compares the old record vs new record for the same table. but each time when I click the report link it refresh the data which I dont want. it is schedule as weekly update.
Alter PROCEDURE [dbo].[Project_comparisonNewPrev]
select n. [ID] , n.[Name] , n.[Description] from [Projects] n left join [Projects_Previous] o on n.ID = o.ID where o.ID is null --created table [Projects_Previous] Delete from [Projects_Previous] insert into [Projects_Previous] Select * FROM [Projects]
I would create a second Procedure which does the Delete / Insert to populate
[Projects_Previous] and run that on a schedule each Monday morning.
Then have this
[Project_comparisonNewPrev] procedure ONLY do the SELECT. Then the report can be run any number of times between Mondays.
Thank you Kristen. appreciate it.