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:

select ‘ALL’ as 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 ) or ‘ALL’ in ( :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 ) or ‘ALL’ in ( :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.