none
How do I program GetReportParameters in a SSRS Custom Data Extension

    Question

  • Hi There,

    I the sample a found for a reporting services Custom Data Extenstion I only have the IDataParameter of IDataParameterCollection available.

    '=========================================================================
    
    '  File:      ADDataParameterCollection.vb
    
    '  Summary:   Represents a collection of parameters.
    
    '-------------------------------------------------------------------------
    '  This file is part of Microsoft SQL Server Code Samples.
    
    '  Copyright (C) Microsoft Corporation.  All rights reserved.
    
    '  This source code is intended only as a supplement to Microsoft
    '  Development Tools and/or on-line documentation.  See these other
    '  materials for detailed information regarding Microsoft code samples.
    
    '  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
    '  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
    '  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
    '  PARTICULAR PURPOSE.
    '=========================================================================
    
    
    Public Class BaanstedeCdsParameterCollection
    	Inherits ArrayList
    	Implements IDataParameterCollection
    
    
    #Region "IDataParameterCollection Members"
    	'Adds an object to the end of the parameter collection.
    	Public Overloads Function Add(ByVal Parameter As IDataParameter) As Integer Implements IDataParameterCollection.Add

    The IDataParameter Class only provides access to ParamterName and Value.

    However I want all the properties of the ReportParamater Class such as:

    So I seem to need to invoke the GetParameters method of the System.Web.Services.Protocols Class:

    However I can Import System.Web.Services.Protocals, but the statement:

    Dim Rs as New ReportingService

    results in "ReportingService" not defined....

    How do I get the PromptUser ect properties in the Custom Data Extension?

    Regards Jos


    I will be back


    • Edited by Josje Thursday, November 14, 2013 8:16 AM
    Thursday, November 14, 2013 1:46 AM

All replies

  • Hi Jos,

    Based on my search, which article you have search is not fit for the later version of SQL Server Reporting Services (SSRS).

    We need to invoke the ReportingService2005.GetReportParameters Method of the System.Web.Services.Protocols Class. Please refer to these articles below:
    http://technet.microsoft.com/en-us/library/microsoft.reportingservices.rdlobjectmodel.reportparameter.aspx
    http://technet.microsoft.com/en-us/library/reportservice2005.reportingservice2005.getreportparameters(v=sql.100).aspx

    Hope this helps.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Friday, November 15, 2013 7:14 AM
    Moderator
  • Hi Alisa,

    I donot think youre stearing me in the right direction. For I think the RepsortServer2010 or ReportServer2005 requires administrative permission, whilst the Custom Data Extension is intended to run in the Report Manager with only the end-user permisions.
    But all I want is to know wheter or not the parameter is hidden and/or multivalue etc.
    So how do I get, or rather the end-user gets this additional information available in de CDE?

    As a workaround I get the ishidden property like this:

    Use						Baanstede_Test
    GO
    Alter					Procedure							GetReportParms
    --========================================================================
    --		Datum			14 nov 2013
    --		Auteur			Jos vd Vlis
    --		Doel			Ophalen Naam en Prompt van de Parameters
    --						Voor @ReportPath = [Pad en] Naam Report
    --==============================================================================
    (
    						@ReportPath							As	VarChar(Max)
    )
    AS
    BEGIN
    	Declare				@Parameter							As	Xml				=	NULL
    	Declare				@Handle								As	Int				=	-1
    	Declare				@ReportName							As	VarChar(Max)	=	''
    	Declare				@RptId								As	Int				=	-1
    	--	ReportName uit ReportServer.Catalog
    	Set					@ReportName							=
    	(
    	Select				Name
    	From				ReportServer.dbo.[Catalog]
    	Where				[Path]								Like	'%'	+	@ReportPath	
    	)
    	--	RptId uity tblPrsReports
    	Set					@RptId								=
    	(
    	Select				rpt_id
    	From				tblPrsReports
    	Where				rpt_name							=	@ReportName
    	)
    		--	Haal Xml Data op uit ReportServer..Catalog.Parameter van @ReportPath
    	Set					@Parameter							=
    	(
    	Select				Parameter
    	From				ReportServer.dbo.[Catalog]
    	Where				[Path]								Like	'%'	+	@ReportPath
    	)
    
    	--	Maak @Handle voor @Parameter
    	Exec				sp_xml_preparedocument					@Handle			OUTPUT
    															,	@Parameter
    
    	Select				Rownr
    			,			ReportName							=	@ReportName
    			,			RptId								=	@RptId
    			,			ParmName
    			,			Prompt
    			,			PromptUser
    			,			Case
    						When		ISNULL(Prompt, '')		=	''
    						Then									1
    						When		ISNULL(PromptUser, '')	=	'False'
    						Then									1
    						When		ISNULL(PromptUser, '')	=	'True'
    						Then									0
    						Else									NULL
    						End									As	IsHidden
    	From
    	(
    	--	Haal Naam Parameter en Prompt Parameter op uit @Parameter
    	Select				Row_Number()	
    						Over	(Order By	pnm.Id)			As	RowNr
    			,			CONVERT(VarChar(Max), pnm.text)		As	ParmName
    			,			CONVERT(VarChar(Max), pvl.text)		As	Prompt
    			,			CONVERT(VarChar(Max), ptp.text)		As	ParmType
    			,			CONVERT(VarChar(Max), pus.text)		As	PromptUser
    	--	ParmName uit @Parameters.@NodeName
    	From				OpenXml(								@Handle
    															,	'/Parameters/Parameter'
    															,	1
    								)							As	pnm
    	--	ParmName
    	Join				OpenXml(								@Handle
    															,	'/Parameters/Parameter'
    															,	1
    								)							As	ypnm
    		On				pnm.parentid						=	ypnm.id
    		And				ypnm.localname						=	'Name'
    		
    	--	ParmPrompt
    	Join				OpenXml(								@Handle
    															,	'/Parameters/Parameter'
    															,	1
    								)							As	zpvl
    		On				zpvl.parentid						=	ypnm.parentid
    		And				zpvl.localname						=	'Prompt'
    
    	--	ParmType
    	Join				OpenXml(								@Handle
    															,	'/Parameters/Parameter'
    															,	1
    								)							As	bptp
    		On				bptp.parentid						=	ypnm.parentid
    		And				bptp.localname						=	'Type'
    
    	--	ParmPromptUser
    	Join				OpenXml(								@Handle
    															,	'/Parameters/Parameter'
    															,	1
    								)							As	xpus
    		On				xpus.parentid						=	ypnm.parentid
    		And				xpus.localname						=	'PromptUser'
    
    	Left Outer Join		OpenXml(								@Handle
    															,	'/Parameters/Parameter'
    															,	1
    								)							As	pvl
    		On				pvl.parentid						=	zpvl.id
    
    	Left Outer Join		OpenXml(								@Handle
    															,	'/Parameters/Parameter'
    															,	1
    								)							As	ptp
    		On				ptp.parentid						=	bptp.id
    
    	Left Outer Join		OpenXml(								@Handle
    															,	'/Parameters/Parameter'
    															,	1
    								)							As	pus
    		On				pus.parentid						=	xpus.id
    
    	Where				pnm.nodetype						=	3
    	)														As	xm
    	
    	--	Remove @Handle
    	EXEC				sp_xml_removedocument					@Handle
    
    END
    GO
    --		Test
    Declare					@ReportPath							As	VarChar(Max)
    Set						@ReportPath							=	'/Iw3 Rapporten/VerzuimGrafiek Rapporten/VerzuimGrafiek_KortLang'
    Set						@ReportPath							=	'FTESE_Plat'
    
    Exec					dbo.GetReportParms						@ReportPath
    

    However this only works on a deployed report and can only be executed as administrator, not the end-user.

    So to make it available to the end-user the ishidden parameter then must be stored in an end-user accessable table, and must be updated after every deploy that has parameter property changes included.

    Therefore another complex piece of code is required:

    Use						Baanstede_Test
    GO
    ALTER					PROCEDURE						UpDatetblPrsReportParms
    ---------------------------------------------------------------------------------------
    --			Datum		14 nov 2013
    --			Auteur		Jos vd Vlis
    --			Doel		Bijwerken tblPrsReportParms
    --						adv GetReportParms
    --=====================================================================================
    (
    						@ReportPath						As	VarChar(Max)
    		,				@Msg							As	VarChar(Max)		OUTPUT		--	Eventueel foutbericht
    )
    AS
    BEGIN
    	Declare				@RetVal							As	Int				=	1
    	Declare				@Error							As	Int				=	0
    	Declare				@ErrNr							As	Int				=	0
    	Declare				@ErrSev							As	Int				=	0
    	Declare				@ErrStat						As	Int				=	0
    	Declare				@ErrProc						As	VarChar(Max)	=	''
    	Declare				@ErrLine						As	Int				=	0
    	Declare				@ErrMsg							As	VarChar(Max)	=	''
    	Declare				@ErrorMsg						As	VarChar(Max)	=	''
    	Declare				@MutReportName					As	VarChar(Max)	=	''
    	Declare				@MutRptId						As	Int				=	0
    	Declare				@MutParmName					As	VarChar(Max)	=	''
    	Declare				@MutIsHidden					As	Bit				=	0
    	Declare				@ReadCount						As	Int				=	0
    	Declare				@UpdateCount					As	Int				=	0
    	Declare				@InsertCount					As	Int				=	0
    	Declare				@DeleteCount					As	Int				=	0
    	Declare				@ProcName						As	VarChar(Max)	=	''
    	Declare				@Eof							As	Bit				=	0
    	Declare				@SwTrans						As	Bit				=	0
    	Declare				@ReportName						As	VarChar(Max)	=	''
    					
    	Begin				Try
    	Set					@ProcName						=	Object_Name(@@ProcId)
    	Set					@ErrorMsg						=	''
    
    	--	Start Transaction
    	Begin				Transaction							@ProcName
    	Set					@SwTrans						=	1
    	--	ReportName
    	Set					@ReportName							=
    	(
    	Select				Name
    	From				ReportServer.dbo.[Catalog]
    	Where				[Path]								Like	'%'	+	@ReportPath	
    	)
    	If					ISNULL(@ReportName, '')			=	''
    	Begin
    		Set				@ErrorMsg						=	'Niet Gevonden @ReportPath: '
    														+	@ReportPath
    		GoTo			ErrExit
    	End
    	--	Maak tmpReportParms
    	Exec				dbo.Drop_Object						'tmpReportParms'
    	Create				Table								tmpReportParms
    	(					RowNr								Int
    			,			ReportName							VarChar(Max)
    			,			RptId								Int
    			,			ParmName							VarChar(Max)
    			,			Prompt								VarChar(Max)
    			,			PromptUser							VarChar(Max)
    			,			IsHidden							Bit
    	)
    	--	Vul tmpReportParms met GetReportParms
    	Insert				tmpReportParms
    	Exec				@RetVal							=	dbo.GetReportParms
    															@ReportPath
    
    	--	Cursor Lezen tmpReportParms
    	Declare				mutParms			Cursor		For
    	Select				ReportName
    			,			RptId
    			,			ParmName
    			,			IsHidden
    	From				tmpReportParms
    	--	1e Lees
    	Open				MutParms
    	Fetch				Next			From				MutParms
    		Into			@MutReportName
    			,			@MutRptId
    			,			@MutParmName
    			,			@MutIsHidden
    	If					@@FETCH_STATUS					<>	0
    		Set				@Eof							=	1
    	--	Loop alle tmpReportParms Rows
    	While				@Eof							=	0
    	Begin
    		Set				@ReadCount						=	@ReadCount		+	1
    		--	ReportName/ParmName al in tblPrsReportParms?
    		If				Exists
    		(
    		Select			rpp_rep_name
    			,			rpp_parm_name
    		From			tblPrsReportParms
    		Where			rpp_rep_name					=	@MutReportName
    			And			rpp_parm_name					=	@MutParmName
    		)
    		--	Ja:		Update
    		Begin
    			Update		tblPrsReportParms
    				Set		rpp_ishidden					=	@MutIsHidden
    					,	rpp_rpt_id						=	@MutRptId
    			Where		rpp_rep_name					=	@MutReportName
    				And		rpp_parm_name					=	@MutParmName
    			Set			@UpdateCount					=	@UpdateCount	+	1
    		End
    		
    		Else
    		--	Nee:	Insert
    		Begin
    			Insert											tblPrsReportParms
    			(			rpp_rep_name
    				,		rpp_rpt_id
    				,		rpp_parm_name
    				,		rpp_ishidden
    			)
    			Values
    			(			@MutReportName
    				,		@MutRptId
    				,		@MutParmName
    				,		@MutIsHidden
    			)		
    			Set			@InsertCount					=	@InsertCount	+	1
    		End
    		--	Lees Volgende
    		Fetch			Next			From				MutParms
    			Into		@MutReportName
    				,		@MutRptId
    				,		@MutParmName
    				,		@MutIsHidden
    		If				@@FETCH_STATUS					<>	0
    			Set			@Eof							=	1
    	End		
    	--	Close Cursor
    	Close				MutParms
    	Deallocate			MutParms
    	--	Cursor voor Lezen tblPrsReportParms
    	Declare				tblParms		Cursor			For
    		Select			rpp_rep_name
    			,			rpp_rpt_id
    			,			rpp_parm_name
    			,			rpp_ishidden
    		From			tblPrsReportParms
    		Where			rpp_rep_name					=	@ReportName
    		
    	Set					@Eof							=	0
    	Open				tblParms
    	Fetch				Next						From	tblParms
    		Into			@MutReportName
    			,			@MutRptId
    			,			@MutParmName
    			,			@MutIsHidden
    	If					@@FETCH_STATUS					<>	0
    		Set				@Eof							=	1
    	While				@Eof							=	0
    	Begin
    		If				Not		Exists
    		(
    		Select			ReportName
    			,			ParmName
    		From			tmpReportParms					
    		Where			ReportName						=	@MutReportName
    			And			ParmName						=	@MutParmName
    		)
    		Begin
    			Delete											tblPrsReportParms
    			Where		rpp_rep_name					=	@MutReportName
    				And		rpp_parm_name					=	@MutParmName
    			Set			@DeleteCount					=	@DeleteCount	+	1
    		End
    		Fetch			Next						From	tblParms
    			Into		@MutReportName
    				,		@MutRptId
    				,		@MutParmName
    				,		@MutIsHidden
    		If				@@FETCH_STATUS					<>	0
    			Set			@Eof							=	1
    	End
    	Close				tblParms
    	Deallocate			tblParms
    	
    	--	Statistics
    	Set					@Msg							=	@ProcName
    														+	', ReadCount='
    														+	dbo.EdNum(@ReadCount,		1)
    														+	', InsertCount='
    														+	dbo.EdNum(@InsertCount,		1)
    														+	', UpdateCount='
    														+	dbo.EdNum(@UpdateCount,		1)
    														+	', DeleteCount='
    														+	dbo.EdNum(@DeleteCount,		1)
    	--	Commit TransAction														
    	Commit				TransAction							@ProcName												
    	Set					@RetVal							=	0
    	Return				@RetVal
    
    ErrExit:
    	--	Voeg toe @ProcName voor in @ErrMsg
    	Set					@Msg							=	@ProcName	+	': '	+	@ErrorMsg
    	--	RollBack en Exit @Error
    	If					@SwTrans						=	1
    		RollBack		TransAction							@ProcName
    	RETURN				@RetVal
    	End					Try
    	--	Catch Fout
    	Begin Catch
    		Select			@ErrNr							=	Error_Number()
    			,			@ErrSev							=	Error_Severity()
    			,			@ErrStat						=	Error_State()
    			,			@ErrProc						=	Error_Procedure()
    			,			@ErrLine						=	Error_Line()
    			,			@ErrMsg							=	Error_Message() 
    														+	' ' 
    														+	@ErrorMsg
    		Set				@Msg							=	@ErrMsg
    														+	', Error_Number='
    														+	dbo.EdNum(@ErrNr,			1)
    														+	', Error_Severity='
    														+	dbo.EdNum(@ErrSev,			1)
    														+	', Error_State='
    														+	dbo.EdNum(@ErrStat,			1)
    														+	', Error_Procedure'
    														+	@ErrProc
    														+	', Error_Line='
    														+	dbo.EdNum(@ErrLine,			1)
    		Return			@RetVal
    	End Catch
    END
    GO
    ---		Test
    Declare					@ReportPath						As	VarChar(Max)	=	'/Iw3 Rapporten/VerzuimGrafiek Rapporten/VerzuimGrafiek_KortLang'
    Declare					@RetVal							As	Int				=	1
    Declare					@Msg							As	VarChar(Max)	=	''			--	Eventueel foutbericht
    Set						@ReportPath						=	'FTESE_Plat'
    
    Exec					@RetVal							=	dbo.UpDatetblPrsReportParms
    															@ReportPath
    														,	@Msg			OUTPUT
    Select					@Msg							As	Msg

    Where all I want to know what the ishidden (to be deduced from Prompt and PromptUser).
    Is there no simpler directer way to do this?

    Regards Jos


    I will be back

    Monday, November 18, 2013 12:04 PM