SSRS: Link to a report with parameters
to use Null as a parameter [Parameter Name]:IsNull=True
Name |
Description |
rs:Format |
Rendering modes you can pass are HTML3.2, HTML4.0, MHTML, IMAGE, EXCEL, WORD, CSV, PDF, XML, defaults to HTML4.0 |
rc:Zoom |
Specified in percentages, supports Page%20Width and Whole%20Page, defaults to 100% |
rc:Toolbar |
True/False, used to show/hide the toolbar, defaults to true |
rc:Parameters |
True/False/Collapsed, used to show/hide/collapse the parameters in the toolbar, defaults to true |
rc:DocMap |
True/False, used to show/hide document map, defaults to true (not shown unless report has document map) |
rc:Section |
Specifies default page number to display, defaults to 1 |
rc:BookMarkID |
Jumps to a specific bookmark in a report |
rc:FindString |
Provides search criteria to the report and finds the first instance of the string specified |
I've created URLs for reports with parameters 3 different ways.
Custom code in the report properties.
Public Function ShowParameterValues(ByVal parameter as Parameter)
as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function
OR
Hyperlink in the report.
=Globals!ReportServerUrl + "/ReportServer?"
+ Replace(Globals!ReportFolder, " ", "+") + "%2f"
+ Replace(Globals!ReportName, " ", "+") + "&rs:Command=Render"
+ "&single_value_parameter=" + Parameters!single_value_parameter.Value
+ "&multi_value_parameter=" + Join(Parameters!multi_value_parameter.Value, "&multi_value_parameter=")
+ IIf(IsNothing(Parameters!week_date_start.Value), "&week_date_start:isnull=True", "&week_date_start=" & Format(Parameters!week_date_start.Value, Variables!FormatDate.Value))
+ IIf(IsNothing(Parameters!week_date_end.Value), "&week_date_end:isnull=True", "&week_date_end=" & Format(Parameters!week_date_end.Value, Variables!FormatDate.Value))
Also, I usually add this as a report variable and then you can have a standard textbox for the footer that doesn't have to change.
=Variables!UrlReportWithParameters.Value
OR
Execution log.
Check out the column
URL_Report_Filtered
/*'---------------------------------------------------------------------------------------
' Purpose: to search the reporting services execution log
'-----------------------------------------------------------------------------------------
DECLARE @all_value AS VARCHAR(10)
DECLARE @LogStatus AS VARCHAR(50)
DECLARE @ReportFolder AS VARCHAR(450)
DECLARE @ReportName AS VARCHAR(450)
DECLARE @UserName AS VARCHAR(260)
DECLARE @GroupByColumn AS VARCHAR(50)
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
SET @all_value = '<ALL>'
SET @LogStatus = '<ALL>'
SET @ReportFolder = 'Testing'
SET @ReportName = '<ALL>'
SET @UserName = '<ALL>'
SET @GroupByColumn = 'Report Folder'
SET @StartDate = NULL
SET @EndDate = NULL
*/
;WITH
report_users
AS
(
SELECT [UserID], [UserName], [SimpleUserName] = UPPER(RIGHT([UserName], (LEN([UserName])-CHARINDEX('\',[UserName])))) FROM dbo.[Users]
)
,
report_catalog
AS
(
SELECT
rpt.[ItemID]
, rpt.[CreatedById]
, rpt.[ModifiedById]
, rpt.[Type]
, rpt.[Name]
, [ReportName] = rpt.[Name]
, rpt.[Description]
, rpt.[Parameter]
, [CreationDate] = CONVERT(DATETIME, CONVERT(VARCHAR(11), rpt.[CreationDate], 13))
, [ModifiedDate] = CONVERT(DATETIME, CONVERT(VARCHAR(11), rpt.[ModifiedDate], 13))
, [ReportFolder] = SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2)
, rpt.[Path]
, [URL_ReportFolder] = 'http://' + Host_Name() + '/Reports/Pages/Report.aspx?ItemPath=%2f' + SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2) + '&ViewMode=List'
, [URL_Report] = 'http://' + Host_Name() + '/Reports/Pages/Report.aspx?ItemPath=%2f' + SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2) + '%2f' + rpt.[Name]
, [ReportDefinition] = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), rpt.[Content]))
, [HostName] = Host_Name()
FROM
dbo.[Catalog] AS rpt
WHERE
1=1
AND rpt.[Type] = 2
)
SELECT
[GroupBy1] =
CASE
WHEN @GroupByColumn = 'Report Name' THEN rpt.[ReportName]
WHEN @GroupByColumn = 'Report Folder' THEN rpt.[ReportFolder]
WHEN @GroupByColumn = 'User Id' THEN usr.[SimpleUserName]
ELSE '<N/A>'
END
, rpt.[Path]
, rpt.[ReportFolder]
, rpt.[Name]
, rpt.[URL_ReportFolder]
, rpt.[URL_Report]
, [URL_Report_Filtered] = rpt.[URL_Report] + '&rs:Command=Render&' + CONVERT(VARCHAR(max), el.[Parameters])
, [UserName] = usr.[SimpleUserName]
, el.[Status]
, el.[TimeStart]
, el.[RowCount]
, el.[ByteCount]
, el.[Format]
, el.[Parameters]
, [TotalSeconds] = CONVERT(CHAR(8),DATEADD(ms,(el.[TimeDataRetrieval] + el.[TimeProcessing] + el.[TimeRendering]),0),108)
, [TimeDataRetrieval] = CONVERT(CHAR(8),DATEADD(ms,el.[TimeDataRetrieval],0),108)
, [TimeProcessing] = CONVERT(CHAR(8),DATEADD(ms,el.[TimeProcessing],0),108)
, [TimeRendering] = CONVERT(CHAR(8),DATEADD(ms,el.[TimeRendering],0),108)
, [OrderbyDate] = CAST([TimeStart] AS DATETIME)
FROM
report_catalog AS rpt
LEFT JOIN dbo.[ExecutionLog] AS el ON el.[ReportID] = rpt.[ItemID]
LEFT JOIN report_users AS usr ON el.[UserName] = usr.[UserName]
WHERE
1=1
AND (@all_value IN(@LogStatus) OR el.[Status] IN(@LogStatus))
AND (@all_value IN (@ReportFolder) OR rpt.[ReportFolder] IN(@ReportFolder))
AND (@all_value IN(@ReportName) OR rpt.[ReportName] IN(@ReportName))
AND (@all_value IN(@UserName) OR usr.[SimpleUserName] IN(@UserName))
AND (@StartDate IS NULL OR CONVERT(DATETIME, CONVERT(VARCHAR(11), el.[TimeStart], 13)) >= @StartDate)
AND (@EndDate IS NULL OR CONVERT(DATETIME, CONVERT(VARCHAR(11), el.[TimeStart], 13)) <= @EndDate)