[SOLVED] RS Multi Value Parameter

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 2005 Forums

Analysis Server and Reporting Services (2005)

[SOLVED] RS Multi Value Parameter

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-05-18 : 16:06:01

I have the following stored procedure and am trying to pass multi-value paramets to it. I have ticked the multi-value report parameter for @domain. When I run the report with only one value select it returns valid results but when I select more than 1 nothing is returned. Any ideas?

SP is as follows


ALTER PROCEDURE [dbo].[sp_ssrs_hardware_owner]
	
@platform NVARCHAR(50),
@domain NVARCHAR(50),
@systemmodel NVARCHAR(50)




AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT     dsm_hardware_basic.Name, dsm_hardware_advanced.SystemSerialNumber, dsm_hardware_basic.LastAgentExecution, 
                      dsm_hardware_basic.Platform, 
                      dsm_hardware_advanced.SystemType, dsm_hardware_advanced.SystemVendor, dsm_hardware_advanced.SystemModel, dsm_hardware_basic.IPAddress, dsm_hardware_basic.MACAddress, dsm_hardware_basic.DomainManager, 
                      dsm_hardware_basic.ScalabilityServer, dsm_related_user.SSO, dsm_related_user.CDI_FullName, dsm_related_user.CDI_EmailAddress, 
                      dsm_related_user.CDI_ParentBusiness, dsm_related_user.CDI_BusinessSegment, dsm_related_user.CDI_SubBusiness, dsm_related_user.CDI_Pole, 
                      dsm_related_user.CDI_JobTitle, dsm_related_user.CDI_JobFunction, dsm_related_user.CDI_Address, dsm_related_user.CDI_City, 
                      dsm_related_user.CDI_State, dsm_related_user.CDI_Country, dsm_related_user.CDI_InternalLocation, dsm_related_user.CDI_MailStop, 
                      dsm_related_user.CDI_EmployeeType, dsm_related_user.CDI_DirectPhone, dsm_related_user.CDI_Supervisor
FROM         dsm_hardware_basic INNER JOIN
                      dsm_hardware_advanced ON dsm_hardware_basic.UUID = dsm_hardware_advanced.UUID INNER JOIN
                      dsm_related_user ON dsm_hardware_basic.UUID = dsm_related_user.UUID
where (platform IN (@platform)) and (scalabilityserver in (@domain)) and (systemmodel in (@systemmodel))
order by name
END

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-05-18 : 16:08:35

I have also set the scalability field expression to


=Join(Parameters!domain.Value,",")

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-19 : 15:59:40

in wont work in that way. it will take entire string as a single value. in your case you need to change it as


ALTER PROCEDURE [dbo].[sp_ssrs_hardware_owner]
	
@platform NVARCHAR(50),
@domain NVARCHAR(50),
@systemmodel NVARCHAR(50)




AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT     dsm_hardware_basic.Name, dsm_hardware_advanced.SystemSerialNumber, dsm_hardware_basic.LastAgentExecution, 
                      dsm_hardware_basic.Platform, 
                      dsm_hardware_advanced.SystemType, dsm_hardware_advanced.SystemVendor, dsm_hardware_advanced.SystemModel, dsm_hardware_basic.IPAddress, dsm_hardware_basic.MACAddress, dsm_hardware_basic.DomainManager, 
                      dsm_hardware_basic.ScalabilityServer, dsm_related_user.SSO, dsm_related_user.CDI_FullName, dsm_related_user.CDI_EmailAddress, 
                      dsm_related_user.CDI_ParentBusiness, dsm_related_user.CDI_BusinessSegment, dsm_related_user.CDI_SubBusiness, dsm_related_user.CDI_Pole, 
                      dsm_related_user.CDI_JobTitle, dsm_related_user.CDI_JobFunction, dsm_related_user.CDI_Address, dsm_related_user.CDI_City, 
                      dsm_related_user.CDI_State, dsm_related_user.CDI_Country, dsm_related_user.CDI_InternalLocation, dsm_related_user.CDI_MailStop, 
                      dsm_related_user.CDI_EmployeeType, dsm_related_user.CDI_DirectPhone, dsm_related_user.CDI_Supervisor
FROM         dsm_hardware_basic INNER JOIN
                      dsm_hardware_advanced ON dsm_hardware_basic.UUID = dsm_hardware_advanced.UUID INNER JOIN
                      dsm_related_user ON dsm_hardware_basic.UUID = dsm_related_user.UUID
where ( ( ',' + @platform + ',' LIKE '%,' + platform + ',%' ) 
and ( ',' + @domain + ',' LIKE '%,' + scalabilityserver + ',%') 
and (',' + @systemmodel + ',' LIKE '%,' + systemmodel + ',%'))
order by name
END

Few things to note

1. If any of fields are of non character type you need to cast them to varchar before you use them in where condition like above
2. The current way logic is written you'll get data only if you pass values for all parameters as they're all mandatory now because of AND

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-05-21 : 11:18:37

Fantastic. Thanks for the reply. Worked like a charm.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 21:36:54

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources