How to determine current report parameter setting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to determine current report parameter setting

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-12-22 : 01:31:54

Question first. Does the code below get the report parameters values from the catalog table in the report server or the rdl file? I ask this because when I run the code below it returns 'yesterday' for my daterange parameter and this is how it was set by default when the report was created and deployed to the server. However if I change the value using report manager to say 'last week' and apply I see the value change in the catalog table under the parameters field however the code still returns 'yesterday'. Does anyone know how to get the current parameter setting from the catalog table?

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[DateRange_ReportParameter_RP] Script Date: 12/22/2010 16:51:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[DateRange_ReportParameter_RP]

@Report nvarchar(255)

AS

SET NOCOUNT ON

Declare @Date as nvarchar (100)
Declare @sql nvarchar(max)

--Find all the reports, and thier parameters and thier default values

Select @sql=''
Select @sql=@sql+ '
;WITH XMLNAMESPACES (
DEFAULT ''http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'',
''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd --ReportDefinition
)
SELECT
NAME
, PATH
, x.value (''@Name'', ''VARCHAR(100)'') AS ReportParameterName
--, x.value (''DataType[1]'', ''VARCHAR(100)'') AS DataType
--, x.value (''AllowBlank[1]'', ''VARCHAR(50)'') AS AllowBlank
--, x.value (''Prompt[1]'', ''VARCHAR(100)'') AS Prompt
--, x.value (''Hidden[1]'', ''VARCHAR(100)'') AS Hidden
, x.value (''data(DefaultValue/Values/Value)[1]'', ''VARCHAR(100)'') AS Value
FROM (
SELECT
NAME
, PATH
,CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM ReportServer$XTRALIS_CYCLOPS.dbo.Catalog
WHERE CONTENT IS NOT NULL AND TYPE = 2
) A
CROSS APPLY ReportXML.nodes(''/Report/ReportParameters/ReportParameter'') R(x)
WHERE NAME = '''+@Report+'''
--Use the where clause above to look for a specific report
ORDER BY NAME
'

--Print (@sql)

Create table #x(
[Name] nvarchar(100),
[Path] nvarchar(255),
[ReportParameterName] nvarchar(100),
--[DataType] nvarchar(100),
--[AllowBlank] nvarchar(100),
--[Prompt] nvarchar(100),
--[Hidden] nvarchar(100),
[Value] nvarchar(100))

Insert into #x
Exec(@sql)

SET @Date = (Select [Value] As DateRange
from #x
Where ReportParameterName = 'DateRange')

Select * from #x

Drop table #x

Exec dbo.Date_Range @DateRange = @Date

SET NOCOUNT OFF

RETURN

Name Path ReportParameterName Value
Event Integrated Report AD /Integrated Reports/Event_Activity/Event Integrated Report AD VCGDBPath NULL
Event Integrated Report AD /Integrated Reports/Event_Activity/Event Integrated Report AD DBName VC
Event Integrated Report AD /Integrated Reports/Event_Activity/Event Integrated Report AD SiteName NULL
Event Integrated Report AD /Integrated Reports/Event_Activity/Event Integrated Report AD TransmitterType NULL
Event Integrated Report AD /Integrated Reports/Event_Activity/Event Integrated Report AD EventType NULL
Event Integrated Report AD /Integrated Reports/Event_Activity/Event Integrated Report AD DateRange Yesterday
Event Integrated Report AD /Integrated Reports/Event_Activity/Event Integrated Report AD TextColour NULL
Event Integrated Report AD /Integrated Reports/Event_Activity/Event Integrated Report AD BackgroundColour NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources