Variable Based on Variable in Paginated Report w/ Native Oracle Connection

  • I have an SSRS paginated report where users need the ability to pick and choose which variables they want the report run with. For example, the query looks something like:

    SELECT *

    FROM CUSTOMER

    WHERE 1=1

    AND CUSTOMER_NUMBER = ?

    AND CUSTOMER_STATE = ?

    The users need the ability to plug in a CUSTOMER_NUMBER parameter OR a CUSTOMER_STATE parameter. So for instance if they decide to provide a customer number, "x", the query would read:

    SELECT *

    FROM CUSTOMER

    WHERE 1=1

    AND CUSTOMER_NUMBER = x

    leaving the "AND CUSTOMER_STATE = ?" line blank. In SQL server I've been able to achieve this by using something like the query below.

    DECLARE @cust_t as varchar(MAX)

    IF @cust <> ''

    SET @cust_t = 'AND CUSTOMER_NUMBER IN (' + '''''' + REPLACE(@cust,',',''''',''''') + '''''' + ')';

    ELSE

    SET @cust_t = ' ';

    DECLARE @state_t as varchar(MAX)

    IF @state <> ''

    SET @state_t = 'AND CUSTOMER_STATE IN (' + '''''' + REPLACE(@state,',',''''',''''') + '''''' + ')';

    ELSE

    SET @state_t = ' ';

    DECLARE @SQL as varchar(MAX)

    SET @SQL = 'SELECT *

    FROM CUSTOMER

    WHERE 1=1

    ' + @cust_t + '

    ' + @state_t ;

    EXEC(@SQL)

    Where the user provides @cust and/or @state. I just can't figure out how to do this with a native Oracle connection.

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply