Sql-server – Database reporting alerts

If you have lost the historical data (i.e. it gets overwritten rather than stored as a transaction history) then you can't reconstruct the historical state - full stop. However, there are a few approaches to dealing with this.

1. Build an automated process that queries the system through SSRS or some other mechanism and saves the historical reports to an archive somewhere. SSRS exports a web service API that lets you run reports (paramaterised if necessary) and then save the output to a file. This can be operated by anything capable of consuming it.

2. Build an ETL process that takes a snapshot of the data and compares it with the previous snapshot position. Where changes are detected they can be written out to a historical table. If you have the option of putting triggers on the source database then you can make triggers that write out the audit logging information instead.

3. If you need something more elaborate then you could build a data mart - do a google search on 'type 2 slowly changing dimensions' for some ideas on how to implement this. You can use a change capture mechanism like that described in (2) to source the data as necessary. Either you can store the transaction history and reconstruct the status in the reports, or you can make a periodic snapshot of the data with its status at that point.

Note that Standard Edition doesn't support changed data capture.

Well...my solution is as follows:

I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content.

With 200,000 records, the query takes about 20-30 seconds to run... I pre-loaded the spreadsheet and set it to refresh in the background at set intervals.

They have it in their hands now, but not long enough to have any feedback. With all the tools I've tried and researched...this really is the only one that has met all my requirements...which is slightly frightening to me. Hope this helps anyone who stumbles across this question.