Allow multiple values from SSRS in oracle

Question

I have a query that gets contract_types 1 to 10. This query is being used in an SSRS report to filter out a larger dataset. I am using -1 for nulls and -2 for all.

I would like to know how we would allow multiple values - does oracle concatenate the inputs together so '1,2,3' would be passed in? Say we get select -1,0,1 in SSRS, how could we alter the bottom query to return values?

My query to get ContractTypes:

SELECT
       ContractType, 
       CASE WHEN  ContractType = -2 THEN 'All'
            WHEN ContractType = -1 THEN'Null'
            ELSE to_Char(ContractType)  
            END AS DisplayFigure
FROM ContractTypes 

which returns

ContractType DisplayFig
-1           Null
0            0
1            1
2            2
3            3
4            4
5            5
6            6
7            7 
8            8
9            9
10           10

This currently is only returning single values or all, not muliple values:

SELECT *
FROM Employee
WHERE NVL(CONTRACT_TYPE, -1) = :contract_type or :contract_type = -2

I'm assuming we want to do something like:

WHERE NVL(CONTRACT_TYPE, -1) IN (:contract_type) 

But this doesn't seem to work.

Data in Employee

Name ContractType
Bob  1
Sue  0
Bill Null
Joe  2 

In my report, I want to be able to select contract_type as -1(null),0,1 using the 'allow muliple values' checkbox. At the moment, I can only select either 'all' using my -2 value, or single contract types.

My input would be: contract type = -1,1,2

My output would be Bill, Bob, Joe.

This is how I'm executing my code

Answer 1

I don't know SSRS, but - if I understood you correctly, you'll have to split that comma-separated values list into rows. Something like in this example:

SQL> select *
  2  from dept
  3  where deptno in (select regexp_substr('&&contract_type', '[^,]+', 1, level)
  4                   from dual
  5                   connect by level <= regexp_count('&&contract_type', ',') + 1
  6                  );
Enter value for contract_type: 10,20,40

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        20 RESEARCH             DALLAS
        10 ACCOUNTING           NEW YORK
        40 OPERATIONS           BOSTON

SQL>

---

Applied to your code:

select *
from employee
where nvl(contract_type, -1) in (select regexp_substr(:contract_type, '[^,]+', 1, level)
                                 from dual
                                 connect by level <= regexp_substr(:contract_type, ',') + 1
                                )

Answer 2

If you have the comma separated list of numbers and then if you like to split it then, the below seems simple and easy to maintain.

select to_number(column_value) from xmltable(:val);

Inputs: 1,2,3,4

Output:

Answer 3

I guess I understood your problem. If I am correct the below should solve your problem:

with inputs(Name, ContractType) as
(
    select 'Bob',  1 from dual union all
    select 'Sue',  0 from dual union all
    select 'Bill', Null from dual union all
    select 'Joe',  2  from dual 
)
select  * 
from    inputs
where   decode(:ContractType,'-2',-2,nvl(ContractType,-1)) in (select to_number(column_value) from xmltable(:ContractType))

Inputs: -1,1,2

Output:

Inputs: -2

Output:

Answer 4

I use SSRS with Oracle a lot so I see where you're coming from. Thankfully, they work pretty well together.

First make sure the parameter is set to allow multiple values. This adds a Select All option to your dropdown so you don't have to worry about adding a special case for "All". You'll want to make sure the dataset for the parameter has a row with -1 as the Value and a friendly description for the Label.

Next, the WHERE clause would be just as you mentioned:

WHERE NVL(CONTRACT_TYPE, -1) IN (:contract_type) 

SSRS automatically populates the values. There is no XML or string manipulation needed. Keep in mind that this will not work with single-value parameters.

---

If for some reason this still doesn't work as expected in your environment, there is another workaround you can use which is more universal and works even with ODBC connections.

In the dataset parameter properties, use an expression like this to concatenate the values into a single, comma-separated string:

="," + Join(Parameters!Parameter.Value, ",") + ","

Then use an expression like this in your WHERE clause:

where :parameter like '%,' + Column + ',%'

Obviously, this is less efficient because it most likely won't be using an index, but it works.