SSRS snapshots

Snapshot are good for archiving reports, or to help report performance, pre-generate reports during night to have them prepared for users. There are few was you can create and access the Snapshot, manually or from Report Processing
Schedule and access it directly like live Report or from Report History. In this post I describe the native mode of Reporting Services, standard edition of SQL Server 2014. Since new versions of Reporting Services, 2O16, 2017 there come other compatibilities with web portal. Bellow are steps you can manage snapshots in Reporting Services Report Manager.

Let’s clarify Snapshots in Report history and Snapshots in report execution first.

Basically, when you create report Snapshot from Report Processing Option section you will not see it in Reporting Manager by default. The snapshot can be accessed when you set the Processing settings to be that Report will be rendered from Snapshot. In this scenario you than go to the Report like live rendering of the report but it is not processed by user it is processed from snapshot. You cannot change parameters, but the big advantage is that the Report processing is very fast. In case that more snapshots exist for one report, the last valid one is taken as source.

Report history gives user possibility to have accessible snapshots as they were created during time. It good be good if you would like to have backups of your reports or have comparation of data in reports during time. Report history can be accessed to users with standard roles which are not part of Administrators.

Both type of snapshots Report history and Processing Option can be created manually or based on its Schedule.

Let’s go to look at it closer.

At first, be aware of one important thing, you have to store data source credentials on the server. If not, you will get error message as on picture bellow.

Picture 1 – Report history

Following picture show how dataset should look like to allow you work with snapshots. Check Credentials stored securely in the report server and for windows credentials check Use as Windows credentials when connecting to the Data Source.

Picture 2 – DataSource

To create Snapshot, we have to go to Report properties first. Go to Report properties by click on right arrow next to the Report name.

Report Item Name
Picture 3 – Report Item Name

Go to Manage section from menu.

Menu Manage
Picture 4 – Menu Manage

Go to Processing Options and click on Render this report from a report snapshot. By checking this option, the report will be rendered from snapshot next time. But in case you do this for first time and snapshot does not exists in database you have to click on Create a Report Snapshot when you click the Apply button on this page, to create the snapshot.

Processing Options
Picture 5 – Processing Options

If Snapshot will not be present in Report Server database, you get error when you try to run the report.

Picture 6 – Snapshot

When you check above mentioned option and click on apply button you can see snapshot  in database.

SELECT [CreatedDate]
FROM [ReportServer].[dbo].[SnapshotData] ORDER
BY createdDAte DESC

Picture 7 – Snapshot database

You can select the Schedule from predefined Shared Schedules (create in site settings) or define Report-specific schedule, to create schedule Report Snapshot. To set schedule, select parameters seen on picture bellow. Schedule periodicity (daily, Weekly, etc.), Start time, day list etc. You can set end day of the schedule too.

Picture 8 – Schedule

Repeat steps described at the very beginning of this post and go to Report properties, to Report history section. Here you will not see any Snapshots since no Snapshot was generated to Snapshot History.

When you would like to add new Snapshots click on button above New Snapshot. When this button is not visible you have to set it first in Snapshot Options section (under Report History section in menu).

Report history
Picture 9 – Report history

In Report Snapshot Options you can allow above mentioned button for adding Snapshots visible.

Snapshot Options
Picture 10 – Snapshot Options

Let’s try check and uncheck Allow report history to be created manually. When unchecked you have no possibility to create snapshot in Report History manually. See picture bellow.

Report history
Picture 11 – Report history

Store all report Snapshots in history enable functionality when each report Snapshot will be visible in Report History. When we would go at the beginning of this post and create Snapshot through Report Processing Options, we could see created snapshot in Report History.

Use the following schedule to add snapshots to report history will create new Snapshot visible in Report History based on defined Schedule.

In this section you can limit number of stored Snapshots. If any already exists system warns you that some of them could be deleted already.

Picture 12 – Message

More detailed info could be found here:

SSRS action

Action is very powerful functionality of reporting services. Action redirect you to external report or within the same report you are working with. You can realize with this functionality lots of scenarios with your reporting solution.

  • Drill down actions
  • Link to external resources
  • Navigation within report
  • Linking reports
  • Interactive user actions
  • Etc.

Action functionality support most of SSRS components.

There are 3 type of actions supported by SSRS.

  • Go to report will be used in this post, you specify report you would like to redirect to and parameters you would like to transfer to that report and their values (it can be report item, parameter, expression)
    • You can use relative path definition for report ../parentpath/myreport or full path /reportserver/path/myreport
  • Go to bookmark this realize navigation within the report. You define to which bookmark report go to by clicking on the Action. Bookmark can be any report item within the Report. You specify it in Properties pane, bookmark section.
  • Go to url used to link with external resources. But you can refer Report Server URL too. See post

More info here


In this post I will use action to filter data with Region Code by click on textbox item of Region Code group.

Let’s modify Report dataset to get CountryRegionCode as key, that will be transferred to Region Code parameter.


@OrderDateKey INT , @OrderDateKey2 INT,
@CountryRegion VARCHAR(2),
@Province VARCHAR(MAX)

  ,SUM(FactInternetSales.SalesAmount) AS Sum_SalesAmount
  ,SUM(FactInternetSales.OrderQuantity) AS Sum_OrderQuantity
  INNER JOIN DimGeography
    ON DimCustomer.GeographyKey = DimGeography.GeographyKey
  INNER JOIN FactInternetSales
    ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey
  JOIN dbo.SplitString(@Province,';') tmp ON tmp.Element  =DimGeography.StateProvinceCode
  FactInternetSales.OrderDateKey >= @OrderDateKey
  AND FactInternetSales.OrderDateKey <= @OrderDateKey2
  AND ISNULL(@CountryRegion ,DimGeography.CountryRegionCode )= DimGeography.CountryRegionCode

New report item was added to dataset.

Picture 1 – Dataset

Right click on Tablix textbox RegionCode.

Picture 2 – Action

Check Go to report item and put bellow mentioned expression to Specify a report: textbox.


The expression uses build in items, referencing the report by itself. Globals!ReportFolder.Value to get report path of recent report and Globals!ReportName.Vlaue to get recent report name. Advantage is that if we would change the name of the report or the location within ReportServer the Action link will not be corrupted.

In Parameter section lets define RegionCode parameter and select CountryRegionCode item from the Report Tablix. There is no need to define other parameters since they have its default values. In case the target report would not have default parameter values and the parameters will not support null or empty value, the target report will not be rendered by click on the Action item. It informs user that he should fill values to parameters first.

On the picture bellow you can see final settings of the Action properties.

TextBox Properties
Picture 3 – TextBox Properties

On the picture above you can see Omit button next to the Parameter definition. It allows you to define if the Parameter would be omitted or not.

Expression of Specify a report textbox, see picture bellow.

Picture 4 – Expression

Now let’s try our solution. By click on English Country Region Name, you filter dataset by the item you click on.

English Country Region Name
Picture 5 – English Country Region Name

Filtered data.

Filtered data
Picture 6 – Filtered data

See that value from Report Item was transferred to Parameter through Action.

Filtered Sales Report
Picture 7 – Filtered Sales Report

In next post I would prepare other scenarios that could be handled by Report Action. Stay tuned.

SSRS PowerShell deserialize report to XML

Useful script/function to get report definition to XML. You can modify the xml and serialize it back to Report Server – so easy. Go to Connect to Reporting Services with PowerShell, to get script that connect to SSRS Web Service.


  • $proxy – proxy Web Service
  • $path – report path
function DeserializeReportDefinitionToXml($proxy,$path)

[System.Xml.XmlDocument] $rdlFile = New-Object System.Xml.XmlDocument;  
[byte[]] $reportDefinition = $null          ;     
[System.IO.MemoryStream] $memStream              | Out-Null;
$reportDefinition                             | Out-Null;
    $reportDefinition = $proxy.GetReportDefinition($path);

catch{   Write-Host "Get report definition problem " $Error[0]
$memStream = New-Object System.IO.MemoryStream(@(,$reportDefinition));
    Write-Host "Problem saving to memory " $Error[0]
$rdlFile.Load($memStream) ;
    Write-Host "Problem load from memory" $Error[0]
return $rdlFile

Let’s try. Write-down hierarchy of groups of your Tablix. Use bellow mentioned function, set proxy and Report path (Report should contain Tablix). Be careful of xml namespace used in function. It depends on version of your Report Server.

function TablixRowHierarchy($proxy,$path){
[System.Xml.XmlDocument] $xml | Out-Null;
$xml=DeserializeReportDefinitionToXml $proxy $path ;
[System.Xml.XmlNamespaceManager] $_nsmgr | Out-Null;
  $_nsmgr = $xml.nameTable -as [System.Xml.XmlNamespaceManager];
  $_nsmgr.AddNamespace("a", "");
 Write-Host "problem create namespace" $Error[0]
[int] $level1 = 0;
foreach ($node in $xml.SelectSingleNode("/a:Report/a:Body/a:ReportItems/a:Tablix/a:TablixRowHierarchy",$_nsmgr).SelectNodes9"//a:TablixMembers",$_nsmgr))
 foreach ($val in $node.SelectNodes("a:TablixMember/a:TablixHeader/a:CellContents/a:Textbox/a:Paragraphs/a:Paragraph/a:TextRuns/a:TextRun/a:Value",$_nsmgr))
   Write-Host "Node value " + $val.InnerText 
Write-Host "Node name " + $node.Name