Share via


SSRS: Link to a report with parameters

Microsoft Reference

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

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