Friday, November 2, 2012

SSRS- How to create Report Snapshot and How to utilize it


1.   Background

The aim of this article is to describe a way of Report Snapshot creation and how we can utilize the snapshot. Report Snapshot is very useful when we need report for a specific point of time. By using Report Snapshot, We can maintain history reports (e.g month wise reports) that will be helpful for analysis purpose.

2.   What is Report Snapshot?

  •  A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time.
  • We can say that it is the replica of report executed at a specific time.
  • All the report snapshot saved in ReportServer DB.
  • Report Snapshot will not provide you the latest data from the data source.
  • We can create report snapshot on schedule basis.
  • By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.
  • By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

3.   Prerequisite

  • Reporting Services should be configured
  • SQL Agent service should run.
  • Credential should store securely in the report server for the data source mapped to Report/Report Dataset. Below is the screenshot from Datasource property page:
.

4.   Steps to create Report Snapshot:

Below is the sample report showing census details of India:

This report is extracting data from the source database. Now we will create the snapshot of this report so that snapshot of this report should have data up to this specific time only.

                I.        For creating a Report Snapshot, go to the report, click on drop button over the report and select Manage :

              II.        Click on Report History and then click on New Snapshot:

             III.        It will create the report snapshot with time stamp:


            IV.        Go to Processing option and select “Render this report from a report snapshot”:

 
              V.        Now run the report to ensure report is working fine:
  Report is working fine with Report Snapshot.

            VI.        Report Dataset definition is selecting all the data from Population table:
                  
  
           VII.        To test report snapshot, we will add some data to source table. Initially, we have 10 records in the Population table so report is showing 10 records only. Now I have added 5 more records and so table is having total 15 records.
Here we can see 5 new records also.

          VIII.        Now preview the report and see whether you are able to see the new records in the report or not:

Report is showing only old records only. It is not showing latest data from the database because this report is retrieving from the report snapshot that was created before data changes done in DB.

            IX.        Now I am changing the Processing option from Report Snapshot to “Always run this report with most recent data”:
                   
Click Apply.

              X.        Now run the report and see the data. It should display latest data from database:

 So it is concluded that Report snapshot does not display data from data source. it will show report from a snapshot that has been created  at a specific point of time.

5.   How to schedule the report snapshot:

                I.        To create a report snapshot, we can either create a Report specific schedule or Shared schedule.
                       II.        For Report specific schedule, go to the report properties then processing option then select “Render this report from a report snapshot”. Finally select “Report specific schedule”

                   III.        Click on Configure and schedule the report:
Click ok.


            IV.        For shared schedule, go to site setting then Schedule and create a schedule
Map the same shared schedule to report.



6.   Report History

We can see the history to refer old snapshots. To see the history, go to report properties then Report History:

We can directly click on any snapshot to see the report for that specific point of time.

7.   Conclusion

By using the above steps, we can create Report Snapshots.
----------------------------------------------------End of Document---------------------------------------------------

7 comments:

  1. When I try to run my reports from ReportManage I am getting "Unable to locate control: oReportCell" message and a list as below. When i select "rsreportserversp2update.config" from the below list it shows my reports folder and when i click in that i get list of reports and from there I am able to see my reports. PLease let me know where am i doing mistake.

    [To Parent Directory]
    Tuesday, November 01, 2011 9:08 AM dir bin
    Monday, November 17, 2008 4:47 PM 496972 Catalog.sql
    Monday, November 17, 2008 4:47 PM 14972 CatalogTempDB.sql
    Monday, June 13, 2005 2:07 PM 76 global.asax Saturday, November 22, 2008 4:53 AM 24571 ModelGenerationRules.smgl
    Tuesday, May 24, 2011 5:15 PM dir Pages
    Thursday, May 26, 2011 9:03 AM dir ReportBuilder
    Monday, June 13, 2005 2:07 PM 143 ReportExecution2005.asmx
    Monday, June 13, 2005 2:06 PM 196337 ReportingServices.wsdl
    Monday, June 13, 2005 2:07 PM 131 ReportService.asmx
    Monday, June 13, 2005 2:07 PM 131 ReportService.soap
    Monday, June 13, 2005 2:07 PM 139 ReportService2005.asmx
    Monday, November 17, 2008 4:49 PM 139 ReportService2006.asmx Saturday, May 28, 2011 10:43 AM 11384 rsreportserver.config
    Monday, November 17, 2008 4:47 PM 3918 rsreportserversp2update.config Monday, June 13, 2005 2:07 PM 11845 rssrvpolicy.config
    Wednesday, May 25, 2011 10:27 AM dir Styles
    Friday, June 17, 2005 1:09 AM 2673 web.config

    ReplyDelete
  2. Best one to understand Report Snapshot in SSRS

    ReplyDelete
  3. that is awesome explanation can u please share as same what is adhoc report as soon as possiable to my mail

    ReplyDelete
  4. can any body explain me what is adhoc report and how to create it

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Hello.
    Thanks a lot for the post. It's great.
    I've got 2 questions though:

    1. Is there a way to automatically recycle snapshots? Or do I have to manually delete old ones?
    2. How does snapshot works for a report with input parameters?

    Best Regards
    Apostolos

    ReplyDelete