CODEWITHSUNDEEP
reporting-servicessharepoint-2013
dimassina
dimassina

Reputation: 11

How to get the URL with the report parameters and values of the parameters of the executed report

Is it possible to get URL for a executed report (including selected parameter values) that can be emailed to colleagues? That they can follow the link to get the same as I, with the same selected parameters.

Upvotes: 0

Views: 1548

Answers (2)

dimassina
dimassina

Reputation: 11

I found solution! It works with SSRS in SharePoint mode!!! Report parameters and their values are stored in ReportingServiceTemDB in xml field of table. You can use the stored procedure to obtain and generate the URL (change or add stored procedure input parameters [SERVERNAME],[ReportingServiceDB], [ReportingServiceDBTempDB], [ReportLibraryName]). Then create shared dataset and use dataset in reports with input parameters:

@parUserName = User!UserID; @parReportName = Globals!ReportName

   ALTER PROCEDURE [dbo].[p_guCreateUrlWithParameters]
    @parUserName varchar(250),
    @parReportName varchar(250),
    @parTimeout int = 10
AS

/*
set @parUserName = 'REGION\11BelyaevDA'
set @parReportName = 'rptDITRequestTactical.rdl'
set @parTimeout = 120 //sec
*/

declare @results varchar(4000)

begin try
select
       @results = coalesce(@results+'&rp:','') + url -- you have to change parameter prefix if you are using report server not in sharepoint mode
from
    (
    select
       res.UserName,
       res.Report,
       res.CreationTime,
       res.parName,
       res.parType,
       (case when res.parType = 'DateTime' then replace(convert(varchar,cast(res.parValue as date),103),'/','.') else res.parValue end) as parValue,
       (res.parName+'='+(case when res.parType = 'DateTime' then replace(convert(varchar,cast(res.parValue as date),103),'/','.') else res.parValue end)) as url     
    from
        (
        select distinct
            x.UserName,
            x.Report,
            x.CreationTime,    
            Paravalue.value('../../Name[1]', 'VARCHAR(250)') as parName,
            Paravalue.value('../../Type[1]', 'VARCHAR(250)') as parType,
            Paravalue.value('../../Prompt[1]', 'VARCHAR(250)') as parLable,
            Paravalue.value('../../MultiValue[1]', 'VARCHAR(250)') as parMultiValue, 
            Paravalue.value('.[1]', 'VARCHAR(250)') as parValue
        from
            (
            SELECT top 1 
                s.CreationTime
                ,s.SessionID
                ,u.[UserName]
                ,c.[name] [Report]
                ,d.[name] [DataSource]
                ,s.[ReportPath]
                ,convert(XML,s.[EffectiveParams]) as ParameterXML
                ,DATEDIFF(minute,s.[CreationTime],GETDATE()) [RunningTimeMinutes]
            FROM  [ServerName].[ReportingServiceDBTempDB].[dbo].[SessionData]  as s with (NOLOCK)
                JOIN   [ServerName].[ReportingServiceDB].[dbo].[Catalog]  as c with(NOLOCK)
                    ON RIGHT(c.Path, CHARINDEX('/', REVERSE(c.Path)) -1) = RIGHT(s.ReportPath, CHARINDEX('/', REVERSE(s.ReportPath)) -1) and c.Type = 2  -- if you are using report server not in sharepoint mode - use '\'
                JOIN [ServerName].[ReportingServiceDB].[dbo].[DataSource]  as d with(NOLOCK)
                    ON c.ItemID = d.ItemID
                JOIN [ServerName].[ReportingServiceDB].[dbo].[Users] as u with(NOLOCK)
                    on u.UserId = s.ownerID and u.UserName = @parUserName
            WHERE
                RIGHT(c.Path, CHARINDEX('/', REVERSE(c.Path)) -1) = @parReportName -- if you are use report server not in sharepoint mode - use '\'
                and datediff(ss,s.CreationTime, getdate()) < @parTimeout
            order by s.Expiration desc
            ) as x
                CROSS APPLY ParameterXML.nodes('//Parameters/Parameter/Values/Value') p ( Paravalue )
    ) res
) link
end try

begin catch
    select isnull(@results,'')
end catch

set @results = 'http://[SERVERNAME]/_layouts/15/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/[ReportLibraryName]/'+@parReportName+'&rp:'+@results -- you have to change link if you are using eport server not in sharepoint mode

select isnull(@results,'')


GO

Upvotes: 1

Daniel
Daniel

Reputation: 1364

It most definitely is possible and I like to create mailto support links on reports that a user can click to send me (the guy that will be looking at their issue) an exact link to the report they use, complete with parameters.

Technet Page: Passing a Report Parameter Within a URL

Here is an example URL that we can break down:

http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&EmployeeID=1234

"http://" - obviously going to be the same for all links

"server" - the name of the ssrs server

"reportserver?" - the virtual directory where the report server's reports are accessed from: enter image description here You can find this by logging into the server and checking the Reporting Services Configuration Manager settings. The default value is "reportserver" if no one customized yours.

"/Sales/Northwest/Employee Sales Report/": The path to the report including the report name

"&rs:Command=Render": Just a command to the ssrs server to render the report

"&EmployeeID=1234": The parameter part you are interested in. EmployeeID in this case is the exact name of the parameter within your report (not the display name) and obviously 1234 is the value for the link.

Upvotes: 0

Related Questions