passing the multiple parameters using the replace function in ssrs
below is my query
this query is wokring fine in work bench but when i copy this query in to data set and preview the report its throwing the error as replace function needs 3 arguments.
i even tried by replacing exe to select to check out whether the multiple values are passing correctly r not in work bench,its working fine but not able to make out why its throwing error in ssrs.
--DECLARE @SelectedEntity as int
--DECLARE @Region AS nvarchar(4000)
--DECLARE @Country AS nvarchar(4000)
--DECLARE @Customer AS varchar(8000)
--DECLARE @GCU AS nvarchar(4000)
--DECLARE @CU AS nvarchar(4000)
--DECLARE @CRG AS nvarchar(4000)
--set @Region='A4037F-1067-DF11-84EF-0017A4776866'
--set @Country='577A9867-1967-DF11-84EF-0017A4776866,577A9867-1967-DF11-84EF-0017A4776866'
--set @Customer='EIRCOM LTD FIN. DEP. 1ST FLOOR, BLOCK B SSGW'
--set @GCU='E1B10CB7-C112-DD开发者_如何学Go11-9BF8-0019BB2F3642'
--set @CU='9C940A05-D268-DF11-84EF-0017A4776866,9C940A05-D268-DF11-84EF-0017A4776866'
--set @CRG='B1E804BD-C112-DD11-9BF8-0019BB2F3642,B1E804BD-C112-DD11-9BF8-0019BB2F3642'
--SET @SelectedEntity =1
DECLARE @SQLFields1 AS varchar(8000)
DECLARE @SQLFilter1 AS varchar(8000)
DECLARE @SQLFilter2 AS varchar(8000)
set @SQLFields1='
select
Mu.om_muname as Region ,
cu.om_countryidname as country_name,
Ia.om_internationalaccountname as GCU ,
Ka.om_customerunitidname as CU,
Ka.om_kaname as CRG,
opp.name as oppname,
opp.opportunityid as oppid,
cu.name as cusname ,
opp.owneridname as ACR,
opp.om_salesstagename as statusselling,
Opp.om_currencyidname as currency,
opp.om_statusofbusiness as buisnessstatus,
opp.om_priorityname as priorityname,
opp.om_pobq1_base as quarter1,
opp.om_pobq2_base as quarter2,
opp.om_pobq3_base as quarter3,
opp.om_pobq4_base as quarter4
'
IF @SelectedEntity=1
BEGIN
SET @SQLFilter1 ='
from FilteredOpportunity as opp
join FilteredAccount as Cu
on opp.accountid=cu.accountid
join FilteredOm_Country as Co
on Co.om_countryid=Cu.om_countryid
join FilteredOm_MarketUnit Mu
ON (Mu.om_marketunitid = Co.om_marketunitid)
join filteredom_InternationalAccount Ia
ON Cu.om_internationalaccountid = Ia.om_internationalaccountid
join FilteredOm_KeyAccount Ka
on Ka.om_keyaccountid=Cu.om_keyaccountid
where Mu.om_muname =('''+@Region+''')
and cu.om_countryidname in ('''+REPLACE(@Country,',',''',''')+''')
and cu.name in (''' + REPLACE(@Customer, ',' , ''',''')+''')
and opp.owneridname in (''' + REPLACE(@ACR_GEO, ',' , ''',''')+''')'
end
ELSE IF @SelectedEntity=2
BEGIN
SET @SQLFilter2 ='
from FilteredOpportunity as opp
join FilteredAccount as Cu
on opp.accountid=cu.accountid
join filteredom_InternationalAccount Ia
ON Cu.om_internationalaccountid = Ia.om_internationalaccountid
join FilteredOm_KeyAccount Ka
on Ka.om_keyaccountid=Cu.om_keyaccountid
join FilteredOm_Country as Co
on Co.om_countryid=Cu.om_countryid
join FilteredOm_MarketUnit Mu
ON (Mu.om_marketunitid = Co.om_marketunitid)
where Ia.om_internationalaccountname=('''+@GCU+''')
and Ka.om_customerunitidname in (''' + REPLACE(@CU, ',' , ''',''')+''')
and Ka.om_kaname in (''' + REPLACE(@CRG, ',' , ''',''')+''')
and opp.owneridname in (''' + REPLACE(@ACR_OPPHIERARCHY, ',' , ''',''')+''')
'
end
if @SelectedEntity=1
BEGIN
exec (@SQLFields1 + @SQLFilter1 )
END
if @SelectedEntity=2
BEGIN
exec (@SQLFields1 + @SQLFilter2 )
END
I don't know why your query doesn't work, but there are some good solutions to this general problem here:
Passing multiple values for a single parameter in Reporting Services
精彩评论