SQL Report Builder 3.0 Multi Value Parameter

Question

I have a report that is required to accept number ranges and comma delimited values in a text field parameter. The parameter is for 'Account Type' and they want to be able to enter "1,2,5-9" and that will take integer values of 1,2,5,6,7,8,9. I know how to do this with a single value but never with a range.

The example code I would use for a single value is:

    SELECT
      arcu.vwARCUAccount.AccountType
      ,arcu.vwARCUAccount.ACCOUNTNUMBER
    FROM
      arcu.vwARCUAccount
    WHERE
      arcu.vwARCUAccount.AccountType = @AccountType

Any information would be extremely helpful. Someone on my team already estimated it and said it could be done without even realising that they wanted a range so now I am stuck figuring it out. I bet everyone here has been in my position so I thank everyone in advance.

Answer 1

There are several things you want to do.

A. Set up a parameter that is data type 'integer' and ensure you select the checkbox 'Allow multiple values'. Set it's value to be 'Ints'. Hit okay for now.

This essentially set up an array available list of a data set that you define for that type of data type that can be passed more than one dataset.

B. Create a simple dataset called 'values' that is like so

    declare @Ints table ( id int);

    insert into @Ints values (1),(2),(5),(6),(7),(8),(9)

C. Go back to your variable in step one and open up it's properties. Select 'Available Values' on the side pane. Choose radio button 'Get values from a query'. List your data set as 'values' and your value and label to be 'id'.

You have now bound your parameter array to values you specified. However a user DOES NOT have to just choose one or all of these but choose one or many of them.

D. You need to set up your main dataset(which I assume you already did before coming here). For the purpose of my example I will make a simple one up. I create a dataset called person:

    declare @Table Table ( personID int identity, person varchar(8));

    insert into @Table values ('Brett'),('Brett'),('Brett'),('John'),('John'),('Peter');

    Select *
    from @Table
    where PersonID in (@Ints)

The important part is the predicate showing:

'where PersonID in (@Ints)'

This tells the dataset that it is dependent on the user to select a value in this array parameter.

Answer 2

I'm not completely proficient with tsql but what about using reg expressions?

See LIKE (Transact-SQL)

Such as:

arcu.vwARCUAccount.AccountType like '[' + replace(@AccountType, ',','') + ']'

Answer 3

This could work. As a broud brush try:

1. Remove the filter of the account type from the tsql.

2. Create a vb function that inputs a number, this being the account type, and tests to see if it is in the parameter string supplied by the user and outputs a 1 or 0. Vb Functions

    Function test(byval myin as integer, byval mylimits as string) as integer
    'results as 1 or 0

      dim mysplit as string()= Split(mylimits, ",")
      dim mysplit2 as string(1)

      'look through all separated by a ","
      For Each s As String In mysplit 

        'does there exists a range, i.e. "-"? 
        if s like "%-%" then
          mysplit2 = split(s, "-")

          'is the value between this range?
          if myin >= mysplit(0) andalso myin <= mysplit(1) then 
            return 1
          end if

        'is the value equal to the number?
        elseif s = myin then
          return 1

        end if
      Next s

      return 0

    End

3. Create a filter on the dataset using the vb function with the account type as the input equal to 1.

    =code.test(Fields!AccountType.Value, Paramaters!MyPar.Value)