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.


SQL Server Migration Assistant

At work we have an Oracle based system we’ll be retiring sometime next year. We want to keep the data around for reporting, but don’t have the manpower or funds to properly flatten the database into a true data warehouse schema. We are leaning then to copy the data into our SQL warehouse database in almost a direct copy of the legacy’s schema with just a few minor tweaks.

In looking around for an efficient way to handle this, I found a tool called the SQL Server Migration Assistant for Oracle. This handy tool will copy everything, tables, views, stored procedures, triggers, just about everything you’d want.

In my case, all I really want is the table layouts. I will be making a minor tweak to the table layouts so I can combine four databases into a single one. In just a few hours, I was able to use SSMA to generate create table scripts for several hundred tables. I then brought the script into my favorite text editor, UltraEdit, and used it’s regular expression capability to add a source database field as the first field in every create table script. Saved ’em, ran it and in short order have a complete schema ready to hold my data.

Even using the SQL Server Migration Assistant in such a limited fashion, I was still able to save myself weeks of coding and trying to figure out how to manually map Oracle datatypes to SQL Server. If you are looking at any kind of data conversion take a look at this tool. You can find more info on the SSMA site at:


Little Bobby Tables

I love this cartoon from xkcd, it really emphasizes why you need to screen your data inputs to protect against SQL Injection Attacks.


By the way, there’s a WebLog Awards going on right now, if you also enjoy xkcd give them a vote. http://2007.weblogawards.org/polls/best-comic-strip-1.php Hurry though, voting ends November 8th.

Arcane Thoughts: Thinking Inside the Box

Today I’m at an offsite meeting, talking about a new project. I won’t get into too many specific details, but we have to pull data from a web service and update an Oracle database. We can use a vendor provided Java API that runs on a Unix box to do the updates, or we can write to the database directly as long as we handle integrity issues.

So we spent the day brainstorming, to come up with possible solutions. Here is the list of contenders:

  • Write a Java app that runs on Unix that uses the vendor API’s.
  • Write a Java app that runs on Unix and updates the database directly.
  • Write a C# app that runs on a Windows Server, where a Batch Scheduler will kick it off.
  • Write a C# app that runs as a Windows Service under XP (we haven’t taken the Vista plunge at work yet).
  • Write a SQL Server Integration Services package that is run by the SQL Server job scheduler. It will use the web service as the input and update Oracle.
  • Use one of the above methods to pull the data then let BizTalk process it from there.

We haven’t made a decision yet, and my point was not so much to talk about the pro’s and cons of each solution. Instead it’s to get you to think creatively when it comes to new solutions for your company. Sitting down and cranking out yet another C# or VB.Net app may not always be the best approach. You may have a task you can accomplish with less code by using SQL Server Integration Services. Or maybe BizTalk might fit the bill.

All too often as programmers our first answer to any solution is to pull up Visual Studio and start grinding out code. Take some time though, to explore a few other options. There’s a rich set of tools out there, and sometimes the best solution to a programming problem may not be programming.

VS Add-In: Oracle Developer Tools for Visual Studio.Net

I found a useful and important add-in for those who deal with Oracle databases using Visual Studio. Oracle Developer Tools for Visual Studio.Net. ODT for VS adds an Oracle Explorer, similar to the Data Explorer built in to VS. It has an incredible amount of functionality built in.

I already mentioned the Oracle Explorer, which gives you a tree that lets you examine your tables (and columns), views, stored procedures, packages, sequences, and all of the other objects Oracle supports.

There’s a plethora of designers and wizards that will allow you to create and alter the aforementioned objects. They work by generating SQL that you can preview before it’s applied.

The feature I find most useful is the PL/SQL editor. Right inside VS I can now write my stored procedures. But what’s really powerful is I can set a breakpoint, and step from my VB.Net or C# code right into the stored procedure, step through the stored procedure, then back into my application. THAT is useful.

You can obtain ODT for VS directly from Oracle, at no cost. http://www.oracle.com/technology/tech/dotnet/tools/index.html or http://shrinkster.com/lry.

I did run into one issue after the install. Oracle installs it’s ODP driver and creates a new Oracle home for your machine. In order to make the connections I had to copy my tnsnames.ora, ldap.ora, and sqlnet.ora files from my old oracle home to the one for 10.2, by default it’s in C:\oracle\product\10.2.0\client_1\network\ADMIN. I found this solution and some other interesting tidbits at the FAQ: http://www.oracle.com/technology/tech/dotnet/col/odt_faq.html or http://shrinkster.com/ls0.    

I’m not going to regurgitate a lot of how to here, instead I’ll refer you to a good article on the Oracle site, at http://www.oracle.com/technology/oramag/oracle/06-sep/o56odp.html or http://shrinkster.com/lrz. This article has the basics to get you up and running.

If you work with Oracle databases, this is a must have add-on for your Visual Studio environment.