Receiving and parsing of multiple report Parameters

To set a multi-value attribute for a parameter "Report Parameters"Select "Multi-value". You can set any parameter type to multiple values (except boolean values ).

You can define multi-value parameters for any report parameter to be created. However, to return multiple parameter values to the query, the following requirements must be met:

• The data source must be SQL Server, Oracle, or analysis services.
• The data source cannot be a stored procedure. Reporting Services does not support passing multi-value parameter arrays to stored procedures.
• The in clause must be used to specify parameters for a query.

Compile an expression that references a multi-value Parameter

When a parameter is referenced in an expression, the global set parameter is used. When using multi-value parameters in an expression, you need to know how to process a single value and how to process the entire value. The following table shows the settings."Multi-value"Optional parameters provide examples and instructions on parameter properties.

Example	Description
Parameters! <Parametername> . Value	A set of variable data values for this parameter.
Parameters! <Parametername> . Label	A group of strings used as the tag of this parameter.
Parameters! <Parametername> . Ismultivalue	Indicates whether a parameter has been selected"Multi-value"Option.
Parameters! <Parametername> . Count	The number of values in the array.
Parameters! <Parametername> . Value (0)	The first value in the multi-value array.
Parameters! <Parametername> . Label (0)	The first tag in the multi-value array.
Parameters! <Parametername> . Value (parameters! <Parametername>.Count-1)	The last value in the multi-value array.
Parameters! <Parametername> . Label (parameters! <Parametername>.Count-1)	The last tag in the multi-value array.
= Join (parameters! <Parametername> . Value ,",")	This expression concatenates all values in the string type multi-value parameter array into a string.
= Split ("value1, value2, value3 ",",")	Receives a string and creates a group of objects that can be passed to subreports and drill-down reports that require multi-value parameters.

Concatenates all values of a multi-value parameter into a string to transmit values.
Set the parameter value:

= Join (parameters! <Parametername> . Value ,",")

 

I. Scenario Description:

1. The report needs to input multiple values for the same parameter for query at the same time.

2. Report parameters: string format. Each parameter value is separated by a specific symbol. The following uses a comma (,) as an example.

II. Implementation of Stored Procedure calls

1. required functions

Go
Create Function get_strarraylength (@ STR varchar (1024), -- string to be split
@ Split varchar (10) -- separator)
Returns int
As begin
Declare @ location int
Declare @ start int
Declare @ length int

Set @ STR = ltrim (rtrim (@ Str ))
Set @ location = charindex (@ split, @ Str)
Set @ length = 1 while @ location <> 0
Begin
Set @ start = @ location + 1
Set @ location = charindex (@ split, @ STR, @ start)
Set @ length = @ Length + 1
End
Return @ Length
End
-- Call example:
Select DBO. get_strarraylength ('6, 5, 9, 3', ',') -- Return Value: 4
Go
Create Function get_strarraystrofindex (@ STR varchar (1024), -- string to be split
@ Split varchar (10), -- Separator
@ Index int -- Obtain the nth element)
Returns varchar (1024)
As begin
Declare @ location int
Declare @ start int
Declare @ next int
Declare @ seed int
Set @ STR = ltrim (rtrim (@ Str ))
Set @ start = 1
Set @ next = 1
Set @ seed = Len (@ split)
Set @ location = charindex (@ split, @ Str)
While @ location <> 0 and @ index> @ next
Begin
Set @ start = @ location + @ Seed
Set @ location = charindex (@ split, @ STR, @ start)
Set @ next = @ next + 1
End if @ location = 0
Select @ location = Len (@ Str) + 1 -- there are two situations: 1. There is no Separator in the string. 2. There is a separator in the string. After jumping out of the while loop, @ location is 0. By default, there is a separator behind the string.
Return substring (@ STR, @ start, @ location-@ start) End

-- Call example:
Select DBO. get_strarraystrofindex ('8, 9, 4', ',', 2) -- Return Value: 9
Go
Create Function f_splitstr -- alter function f_splitstr
(@ Sourcesql varchar (8000), @ strseprate varchar (100 ))
Returns varchar (8000)
As begin
Declare @temp as varchar (8000) =''
Declare @ ch as varchar (100)
Set @ sourcesql = @ sourcesql + @ strseprate
While (@ sourcesql <> '')
Begin
Set @ CH = left (@ sourcesql, charindex (',', @ sourcesql, 1)-1)
Set @ temp = @ temp + ''' + @ CH + ''' + ','
Set @ sourcesql = stuff (@ sourcesql, 1, charindex (',', @ sourcesql, 1 ),'')
End
Return substring (@ temp, 0, Len (@ temp ))
End
------ Call -- select [DBO]. [f_splitstr] ('1, 2, 3, 4 ',', ') -- result: '1', '2', '3', '4'
 

2. Methods for calling functions in Stored Procedures

Call a function
Declare @ SQL varchar (5000) = 'select * from # T1 as a where 1 = 1'
If (DBO. fn_isempty (@ molotcode) = 0)
Set @ SQL + = 'and A. molotcode in (' + [DBO]. [f_splitstr] (@ molotcode, ',') + ')'
If (DBO. fn_isempty (@ modocno) = 0)
Set @ SQL + = 'and A. modocno in (' + [DBO]. [f_splitstr] (@ modocno, ',') + ')'

 

 

Here, @ molotcode and @ modocno are parameters passed in by the stored procedure, in the format of: 1, 2, 3, 4, 5

After the f_splitstr function is parsed, the format of '1', '2', '3', '4', and '5' is obtained.