CODEWITHSUNDEEP
sql-serveroracle-databasereporting-servicesvisual-studio-2008
Greg
Greg

Reputation: 504

Allow muliple values in Oracle for an SSRS report

I have a query that gets the numbers 1 to 10. This query is being used in an SSRS report to filter out a larger dataset.

I would like to know how we would get 'ALL' - there is a 'allow multiple values' option in the parameter properties option but i think this just concatenates the numbers together so would be (1,2,3, etc) and therefore I'm not sure Oracle can deal with this.

My query to get ContractTypes -1 to 10:

WITH ContractTypes (ContractType) AS (
                         SELECT -1 FROM dual
                         UNION ALL
                         SELECT ContractType+1 
                         FROM ContractType
                         WHERE ContractType< 10)
SELECT *
FROM ContractTypes

My query to get the contractTypes in another dataset. I'm not sure if this needs to be changed to maybe split the string to deal with multiple values. I am using -1 to deal with nulls.

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

I am using visual studio 2008 to build my report and pl/sql developer for my oracle code.

Thanks all for your help in advance :)

Upvotes: 0

Views: 202

Answers (2)

Greg
Greg

Reputation: 504

This was my work around; create a second column which adds NULL and ALL to -1 and -2 respectively. Then we use the DisplayFigure column in SSRS for the filter.

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

Upvotes: 0

Hannover Fist
Hannover Fist

Reputation: 10860

When dealing with ALL when using integers, I usually use 0 for ALL to keep it simple.

SELECT *
FROM Employee
WHERE (
    contract_type NVL(CONTRACT_TYPE, -1) = :contract_type
    OR :contract_type = 0
    )

If you don't want the NULLs, you need to change the OR to

SELECT *
FROM Employee
WHERE (
    contract_type NVL(CONTRACT_TYPE, -1) = :contract_type
    OR (CONTRACT_TYPE IS NOT NULL AND :contract_type = 0)
    )

Upvotes: 1

Related Questions