While taking training classes for national
and International students, I have often seen them getting confused with the
way snapshot options work in SSRS 2016. So I thought of putting it in an
article.
The first thing to understand here is what
a snapshot is? As the name suggests, just like a picture, A snapshot is a
picture of data at that given moment. Once you click a snapshot, it stays that
way and doesn’t change unless you replace it with another snapshot.
Same way in SSRS, a snapshot stores the
data of a report at a given moment. After understanding what a snapshot is,
let’s now explore the snapshot options in SSRS 2016. There are actually 2
options in SSRS related to snapshots:
1. First option is to always run a
report from a snapshot. What this means is that your report will always pull data
from a predefined snapshot. Below image shows this option:
Here I have
selected the RedBushSalesReport to be run from a snapshot.
You may choose
the option “Create a cache snapshot when I click Apply on this page”, if you
choose to create the snapshot immediately after you click the Apply button.
There afterwards, your report will always pull data from this snapshot. If the
data changes in database, you will still keep getting the same data.
Also note that if
the report has any parameters, then you need to specify a default value for
them to generate the snapshot as shown below:
If you don’t
specify default values for parameters, then you will get below error:
Now if you
run/refresh multiple times, you will notice that report is returning same data.
Notice that you will not be allowed to change the parameter value now! Since
the report is supposed to come from a snapshot, which has a default parameter,
you will be able to run it with that default parameter(s) only. I refreshed the
RedBushSalesReport multiple times, and every time it displayed the same
execution time, which confirms that the data is coming from a static snapshot.
Also I am not able to change the parameter also.
Sometimes you may
have a requirement to run a report with fixed parameters on daily basis and if
the report is really heavy and takes time to generate, then you may choose to
schedule the snapshot, say before the office times starts and then everybody
will be able to run the report quickly. And your boss will be happy to see the
performance! Remember you should schedule the snapshot depending upon how long
you can afford to show the stale data. Below image shows the scheduling of
snapshot:
Notice that whenever
you are generating snapshots using any of the above two options, by default no
history of snapshots is being maintained. You can check this by clicking the
“History Snapshots” option.
Now let’s see
what History snapshots are. The main purpose of a Report History snapshot is to keep a copy of
the report at a specific point of time. So if you want to keep track of how
your report looked at different points of time. You can generate the History
Snapshots by clicking the “New History Snapshot” button as shown below:
If I click the
“View History Snapshot” button, I will see the report with this new snapshot.
What will happen if run the report normally by going back to “Browse”? Will I
see the report with this snapshot or the older snapshot that we generated? We
will see the report with older snapshot only. So that means your report doesn’t
pull data from history snapshots. They are just used to keep a trail of how
your report looks at different specific times.
Can I keep a
history of snapshots that I generate from the Caching tab? Yes, you can. From
History snapshots tab, go to Schedule and Settings option as shown below:
Using this
option, you can specify to save Cache snapshots in History as well. You cal
also specify how many history snapshots you want to retain. Additionally you
can generate the History snapshots at pre defined schedule. All these options
are shown below.
So you must have
noticed that there can be only one report execution snapshot at a time, but
there can be many History snapshots.
I hope this
clarifies the difference between History snapshots and report execution cached
snapshots.
If you have any
questions, I’ll be happy to respond in the discussion forum of this article.
Contact us at - support@redbushtechnologies.com
Author is a senior DBA, Mr Suresh, with more than 16 years of experience in the IT industry. He delivers lectures on SQL Server and consult many MNCs. His detailed profile can be viewed at :- www.sureshdbagurugram.com
Thank you
Team RedBush
