Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We run a job tracking service as part of the company software. Reports are generated by SQL Server (Standard) Reporting Services usually daily but often as and when needed before meetings etc.

As the jobs progress their status changes and the details of the reports change. Due to the legacy of the front end application and the database we do not store as many milestones as needed for managers to look back at historical reports.

Managers quite often ask “Can we see where XYZ was last week?” or things like “Let’s have a look how Project1 was performing last June?” but running the reports on the Project1 database backup all show 100% complete as it is now finished.

What is the best way to enable historical reporting at a daily level with SQL Server Standard?

Ideally we’d like to keep 3 months of 5 different reports stored daily if possible?

share|improve this question
    
How about a stored procedure that takes a date range and other parameters, and ignores status? –  Aaron Bertrand Feb 4 '12 at 17:22
    
I think the database doesn't store the history in the first place. –  ConcernedOfTunbridgeWells Feb 6 '12 at 11:51
    
Correct ConcernedOfTunbridgeWells we don't store the history, that is another question for v3 of our software; to build a full historical reporting DB separate from the main DB. –  best Feb 8 '12 at 14:50

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
Though your answer is very valid, I think that you took it a bit too deep. The problem looks simpler, some flow has just been completed in all its steps, so the usual report isn't useful anymore, because it's displaying only current/last status, so it only needs breaking down by date. –  Marian Feb 14 '12 at 23:46
    
@Marian - The OP indicates that the application doesn't store historical state. It's discussed in the comments on the question. –  ConcernedOfTunbridgeWells Feb 15 '12 at 9:07
    
Then the answer shouldn't be mine, but yours :-). As your answer builds the basis of his need, while mine just scratches the surface and displays already gathered data. –  Marian Feb 15 '12 at 15:20
    
@Marian - given that the OP accepted your answer, maybe he didn't understand my question. –  ConcernedOfTunbridgeWells Feb 15 '12 at 15:22
    
I think I was perhaps a little hasty in accepting my original answer, as after working with the date filtering it doesn't work where we are missing historical data as you pointed out. Where a status complete field moves from 0% to 65% to 100% we loose the ability to look when the 65% mark was hit. I think some kind of archive on status changes is in order. thanks –  best Feb 24 '12 at 8:42

If you only want the reports stored daily (and not the data itself), you can create your five reports and setup a daily subscription to be delivered to a file share. See File Share Delivery in Reporting Services.

share|improve this answer
    
Nice tip on the File Share delivery. –  StanleyJohns Feb 6 '12 at 16:13
    
I do like this idea, so we can just give management a read only share for project reports –  best Feb 8 '12 at 14:49

I believe that you need parametrized reports in SSRS - with a date parameter for your report. You will find the theoretical and practical details and some step by step tutorials about parameters in SSRS reports in the following articles:

share|improve this answer
    
Good breakdown with links. Helped me to start looking into SSRS. –  StanleyJohns Feb 6 '12 at 16:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.