sql server 2008 - Multiple string values parameter passing to SP in SSRS -


i have read , tried of related topics on forum, of them don't have lot of feedback.

so using ssrs 2008 r2 report builder 3.

i have simple sp follows filters data using in.

but reason not passing parameters correctly sp , filter's first option select, hope can assist.

--sp

@warehouse varchar(max)  begin select wbal.warehouse ,wbal.stockcode,((wbal.[current] - wbal.bal1)) cmov,((wbal.bal1 - wbal.bal2)) p1mov,((wbal.bal2 - wbal.bal3)) p2mov  ,((wbal.bal3 - wbal.bal4)) p3mov,((wbal.bal4 - wbal.bal5)) p4mov,((wbal.bal5 - wbal.bal6)) p5mov,((wbal.bal6 - wbal.bal7)) p6mov ,((wbal.bal7 - wbal.bal8)) p7mov,((wbal.bal8 - wbal.bal9)) p8mov,((wbal.bal9 - wbal.bal10)) p9mov,((wbal.bal10 - wbal.bal11)) p10mov ,((wbal.bal11 - wbal.bal12)) p11mov    ( select [stockcode]       ,[warehouse]       ,(qtyonhand  * unitcost)  [current]       ,(openbalcost1 * openbalqty1) bal1       ,(openbalcost2 * openbalqty2) bal2       ,(openbalcost3 * openbalqty3) bal3       ,(openbalcost4 * openbalqty4) bal4       ,(openbalcost5 * openbalqty5) bal5       ,(openbalcost6 * openbalqty6) bal6       ,(openbalcost7 * openbalqty7) bal7       ,(openbalcost8 * openbalqty8) bal8       ,(openbalcost9 * openbalqty9) bal9       ,(openbalcost10 * openbalqty10) bal10       ,(openbalcost11 * openbalqty11) bal11       ,(openbalcost12 * openbalqty12) bal12    [sysprocompanyr].[dbo].[invwarehouse]   warehouse in (select * dba_parsestring_udf((@warehouse), ' '))   ) wbal   end 

i have following udf apparently split of string, im not don't know if problem perhaps lies udf

udf

alter function [dbo].[dba_parsestring_udf] (           @stringtoparse varchar(8000)           , @delimiter     char(1) ) returns @parsedstring table (stringvalue varchar(128)) /*********************************************************************************     name:       dba_parsestring_udf      author:     michelle ufford, http://sqlfool.com      purpose:    function parses string input using variable delimiter.      notes:      2 common delimiter values space (' ') , comma (',')      date        initials    description     ----------------------------------------------------------------------------     2011-05-20  mfu         initial release ********************************************************************************* usage:           select *     dba_parsestring_udf(<string>, <delimiter>);  test cases:      1.  multiple strings separated space         select * dbo.dba_parsestring_udf('  aaa  bbb  ccc ', ' ');      2.  multiple strings separated comma         select * dbo.dba_parsestring_udf(',aaa,bbb,,,ccc,', ','); *********************************************************************************/ begin      /* declare variables */     declare @trimmedstring  varchar(8000);      /* need trim our string input in case user entered spaces */     set @trimmedstring = ltrim(rtrim(@stringtoparse));      /* let's create recursive cte break down our string */     parsecte (startpos, endpos)         (         select 1 startpos             , charindex(@delimiter, @trimmedstring + @delimiter) endpos         union         select endpos + 1 startpos             , charindex(@delimiter, @trimmedstring + @delimiter , endpos + 1) endpos         parsecte         charindex(@delimiter, @trimmedstring + @delimiter, endpos + 1) <> 0     )      /* let's take results , stick in table */       insert @parsedstring     select substring(@trimmedstring, startpos, endpos - startpos)     parsecte     len(ltrim(rtrim(substring(@trimmedstring, startpos, endpos - startpos)))) > 0     option (maxrecursion 8000);      return;    end 

so im trying achieve is, have sp populates values parameter did setup follow in ssrs

select distinct (warehouse) [sysprocompanyr].[dbo].[invwarehouse] 

enter image description here enter image description here

and in dataset report need apply looks have tried various alternatives in parameter option:

expression

=join(parameters!warehouse.value,",") 

enter image description here

when pass multiple values ssrs, sends values comma delimited value1,value2,value3....

in query splitting on white string ' '

where warehouse in (select * dba_parsestring_udf((@warehouse), ' ')) 

it should using comma , instead of white space ' '

where warehouse in (select * dba_parsestring_udf((@warehouse), ',')) 

Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -