Databases
StackOverflow2013
Postsdbo
POpassing the multiple parameters using the replace function in ssrs
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POpassing the multiple parameters using the replace function in ssrs
    primarykey
    data
    text
    <p>below is my query</p> <p>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.</p> <p>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.</p> <pre><code>--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-DD11-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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJosh Lee
    1. USsubramani
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload