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