SQL Server Reporting Services, Oracle, and Multivalued Parameters

I am working on a series of reports that will use SQL Server Reporting Services 2005 for the reporting tool, with the data stored in Oracle. One of the requirements was that several of the parameters had to be Multivalued, users could pick from one to all of the items in a drop down list. Sounds like it should be straight forward, but there were a lot of gotcha’s that drove me nuts trying to get this to work. Some of the workarounds I had seen on the web, one I figured out for myself, but none had this information consolidated in one spot. So I thought I’d pass along the hard fought hours I spent and perhaps save you some time.

I’m going to keep my example very simple. I’m also going to assume you are familiar with using parameters and reporting services, perhaps just not with Oracle. We’ll pull our data out of a table called “table1”, and have four fields, field1, field2, field3, and field4. Field1 will be the one we’ll use for our multivalued parameter.

So the first thing we’d do with our report is create two datasets. The first will be used to populate the dropdown list for the parameter. We’ll call it dataset1, and it will have some simple SQL with it:

Select field1 from table1 order by field1

Pretty easy so far. Now create a parameter for the report. Name the parameter prmField1, and have it supply the values and labels from dataset1. This should all be familiar so far if you’ve done parameters with SQL Server. Now let’s create dataset2, which will be the main one to supply data to the report. When you create the new dataset, use this for the SQL:

Select field1, field2, field3, field4 
  from table1
 where field1 in ( :prmField1 )

Then click on the parameters tab, and bind :prmField1 to the Parameters!prmField1 parameter.

Now if you’ve used parameters with SQL Server, you’re probably scratching your head. It’s time for that first “gotcha” I mentioned. While SQL Server uses an @ sign for it’s parameters, Oracle uses a : (colon) to prefix it’s parameters. Thus :prmField1 is the parameter in this SQL. Note I could have named it anything, I just used :prmField1 to make it consistent with the parameter we setup to make debugging easier.

There’s one other potential gotcha here, depending on your version of Oracle. Only Oracle 9 and greater support this multivalued parameter syntax. It wasn’t an issue for me since we are on a version later than 9, but if you are on version 8 or earlier, you will have to turn the whole thing into an expression and break out the multivalues manually. Very ugly.

OK, if you run the report it would work, but it would have a few drawbacks. If you use the “Select All” check mark, Reporting Services will create SQL that looks like:

Select field1, field2, field3, field4 from table1 where field1 in ( ‘a’, ‘b’, ‘c’, ‘d’, …

Which works OK if you only have a limited number of items in the list, but if you have a lot of items selected your performance is going to be poor, and if you have more than 1000 Oracle won’t run the query at all. So how do we get around this gotcha?

The common wisdom here is to add your own ‘ALL’ selection to your drop down multiparameter list. Let’s go back to dataset1, and change the SQL statement to read:

selectALLas field1 
  from dual
union all
select field1 
  from table1
 order by field1

This will add ‘ALL’ as the first item in your list. If you are not familiar with Oracle, I’ll also mention “from dual” is just a way to tell Oracle that no table really exists, just return this one row with the fields I specify. Now we need to modify the query in dataset2 to take advantage of this shortcut. My first pass looked like:

select field1, field2, field3, field4 
  from table1
 where ( field1 in ( :prmField1 ) OR :prmField1 = ‘ALL’ )

Only problem is, this didn’t work right. If I picked ‘ALL’, or one item in my list the report worked, but if I picked more than one item from the list the report errored out. I believe that by using the :prmField1 = ‘ALL’ syntax, my multivalued parameter was getting converted to a single valued parameter. After several hours of head bashing though, I finally came up with this rewrite:

select field1, field2, field3, field4 
  from table1
 where ( field1 in ( :prmField1 ) orALLin ( :prmField1 ) )

Success! By changing my syntax to check for IN on both items I don’t coerce prmField1 into a single valued parameter, instead leaving it as multivalued. Another benefit / gotcha. You can execute this query from the dataset editor window. When you do a small dialog pops up and asks you to supply values for each parameter in the query. Through this dialog you can supply one value, thus I could use ‘ALL’, or some other valid value for prmField1, but only 1. This dialog does not allow you to enter more than one value, if you want to test your multivalue functionality, you’ll have to actually execute the full report.

You may also be wondering why I wrapped my where clause in parenthesis. Future maintenance. It will make it easier should I come back later and add more items to the where clause, like so.

select field1, field2, field3, field4 
  from table1
 where ( field1 in ( :prmField1 ) orALLin ( :prmField1 ) )
   and field2 = ‘Arcane Code’

Because there’s an OR in there, wrapping in parenthesis will keep my and/or logic correct for the rest of the query.

There is one final “gotcha” you need to be aware of when using this approach. While it does indeed solve the problem, it will take some education to train the users to use your ‘ALL’ feature instead of the built in ‘select all’ that comes with SQL Server Reporting Services. Once our users got used to it, it worked fine, but did take some education.

So let me summarize the key points for quick reference:

  1. Be sure your Oracle version is 9 or greater, none of this works on versions 8 or older.
  2. When using parameters with Oracle, use a : instead of an @ sign – :param instead of @param.
  3. Add an ‘ALL’ option to your datasets that supply values for multivalued drop down parameters.
  4. Check for the ALL in your where clause by using “where ( field1 in ( :prmField1 ) or ‘ALL’ in ( :prmField1 ) )” syntax.
  5. You can execute your query from the dataset window, but can only supply 1 value. However that value can be ‘ALL’.
  6. Educate your users on ‘ALL’ versus ‘(select all)’ .

Hopefully this will help you as you create your own SQL Server Reporting Services reports against Oracle databases using multivalued parameters.

About these ads

17 Responses to “SQL Server Reporting Services, Oracle, and Multivalued Parameters”

  1. ChipL Says:

    This is exactly what I was looking for! Thank you! This saved me hours of additional digging.

  2. JS Says:

    I had this exact same problem. Thank you very much!

  3. aes Says:

    Very helpful.. I thought perhaps stored procs were manditory with Oracle.

  4. sdm Says:

    Great posting…I can see where it would work except my multi-value is still not working. My oracle 9.2 prompt is a ‘?’ therefore i cannot use the :prmField1
    I get an error when trying to bind :prmField1 with my parameter. Would you happen to have any links to ‘?’ prompts?

  5. Star Says:

    Many Thanks.. This was really useful.. Saved really my time .. I think am having problem passing date.. Will check to re-format them..

  6. Deph2 Says:

    Awesome. Exactly what I was looking for. We’re using BI Publisher instead of SQL Server Reporting, and the documentation all but insisted on a stored procedure. This will be much easier to maintain. Much appreciated.

    Sidebar thought, in BI we have the option of removing the user’s ‘Select All’ option, does SQL Server Reporting not offer a similar option?

  7. Dana Says:

    When I add the Select ‘All” query to my dataset I get a Character set mismatch. The supplierNumber is a text field in my original query. Any thoughts would be appreciated.

    Select ‘All’ as SupplierNumber,
    ‘All’ as SupplierName
    from dual
    union all
    select BPNUMBER as SupplierNumber
    ,BPName as SupplierName
    from v_bp_buy_from_data

    • YH Says:

      Try this ?

      Select
      -1 as SupplierNumber,
      ‘All’ as SupplierName
      from dual
      union all
      select
      BPNUMBER as SupplierNumber,
      BPName as SupplierName
      from v_bp_buy_from_data

      … and use this in your where clause
      (-1 in ( :prmField1 ) )

      • YH Says:

        Are you using Oracle 9+?
        Because in SQL Server “select * from dual” doesn’t work.

        Kind regards,
        Yves

  8. arcanecode Says:

    YH, yes this article was written for Oracle, and select * from dual does not work in SQL Server.

    For an updated version of how to handle the * issue in SQL Server, see my two articles:

    http://arcanecode.com/2010/07/13/adding-query-parameters-to-sql-server-2008-reporting-services-reports/

    and

    http://arcanecode.com/2010/07/14/sql-server-2008-reporting-services-and-the-select-all-parameter-issue/

    The first article covers adding query parameters, the second covers the ‘ALL’ parameter.

  9. arcanecode Says:

    Dana, YH’s suggestion is a good one, use -1 for supplier number (as long as it’s not a valid choice, if it is pick a number that is not).

  10. Jen Says:

    Excellent…just what I needed.

  11. eliazu Says:

    saved me tone of time
    thanx!!!

  12. Awadhesh Yadav Says:

    Thanks a lot …..I was stucking from long time due to this my problem resolve…. Again Thanks

  13. Stephen Says:

    This is a great post but I like to add that it would be nice if we could just determine when the SSRS checkbox of select all is selected rather than creating our own. This would also cut down on the user’s confusion

  14. Rahul Says:

    Excellent job !! Appreciated. It works fine for me. Good Information.

  15. Kiran Punna (@KiranPunna) Says:

    This helps alot Thanks dude!!! Good stuff.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 102 other followers

%d bloggers like this: