SSRS – Reporting Services

August 7, 2019August 8, 2019

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.

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.

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.

Go to Manage section from menu.

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.

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

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

SELECT [CreatedDate]
,[QueryParams]
,[ExpirationDate]
,[PageCount]
,[HasDocMap]
,[PaginationMode]
,[ProcessingFlags]
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.

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).

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

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.

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.

More detailed info could be found here:
https://bit.ly/2KfMpTx

August 7, 2019August 7, 2019

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 https://bit.ly/2TamgZ5

GOAL

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.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GETData]

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

AS 
SELECT
  DimCustomer.FirstName
  ,DimCustomer.LastName
  ,DimCustomer.MiddleName
  ,DimCustomer.Gender
  ,DimCustomer.AddressLine1
  ,SUM(FactInternetSales.SalesAmount) AS Sum_SalesAmount
  ,SUM(FactInternetSales.OrderQuantity) AS Sum_OrderQuantity
  ,DimGeography.City
  ,DimGeography.StateProvinceName
  ,DimGeography.EnglishCountryRegionName
  ,DimGeography.CountryRegionCode
FROM
  DimCustomer
  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
WHERE
  FactInternetSales.OrderDateKey >= @OrderDateKey
  AND FactInternetSales.OrderDateKey <= @OrderDateKey2
 
  AND ISNULL(@CountryRegion ,DimGeography.CountryRegionCode )= DimGeography.CountryRegionCode
  
GROUP BY
  DimCustomer.FirstName
  ,DimCustomer.LastName
  ,DimCustomer.MiddleName
  ,DimCustomer.Gender
  ,DimCustomer.AddressLine1
  ,DimGeography.City
  ,DimGeography.StateProvinceName
  ,DimGeography.EnglishCountryRegionName

New report item was added to dataset.

Right click on Tablix textbox RegionCode.

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

=Globals!ReportFolder+"/"+Globals!ReportName

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.

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.

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

Filtered data.

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

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

August 7, 2019August 16, 2019

SSRS report Chart

Here I come with post where I create graph that displays results from my testing lab I described in previous post. Reporting Services offers lots of possibilities of data visualisations. Graphs are basic components for scenarios you need to visualise your data.

File -> New -> Project

Continue with Business Intelligence -> Reporting Services -> Report Server Project. At the bottom part of New Project window set Project Name and its location.

New empty Report Server project was created.

Add New Report, right click Reports in Solution Explorer.

Go to Toolbox pane and click on Chart component.

Select Bar Chart type from chart palette.

New Chart component is added into the Visual Studio design area.

Right click on Chart to get Chart Properties dialog box. Map Chart to its dataset. Select GetData dataset from Dataset name select box.

Our goal is to get something similar on picture bellow. We want to get results from our tests. It will be grouped by categories represented by tested counters. Set each scenario to chart series.

When you click on chart component you get Chart Data dialog box on the right side.

In Values section select CounterValue, results from our tests.
Set CounterName to Category Groups to get testing perspective of our data, counters.
TaskOwnerName will be set as Series Groups, scenarios we tested

Finally, we got simple chart as on picture bellow.

Reporting Services Chart is very complex component. I didn’t show all its possibilities it has. In this post I created the simplest one Chart to represented lab data.