Using SSRS queries with parameters with Netezza

Our company has started using SQL Server Reporting Services 2008 R2 to handle most of our reporting against Netezza.  This works pretty well over all using the Netezza 6.0 OLE DB driver.  There are a few things to be aware of though.

I found a number of people were using expressions to construct a SQL statement to send for the data set.  This works well, but it’s a little bit of a pain to both write and maintain.  Luckily, we can use parameters (with just a few things to be aware of).  That means we can write SQL that looks like this:

[sourcecode language="sql" padlinenumbers="true"]
SELECT
     date_key
    ,date
    ,good_day
    ,day_of_week
FROM
    date_sample
WHERE
    1=1
    AND day_of_week IN (@day_of_week_multi)
    AND date IN (@date)
    AND date_key =  @date_key
    AND good_day = @good_day 
ORDER BY
    date_key
;
[/sourcecode]

You can use parameters in the normal way with Netezza, but there are a few details to be aware of.  You just imbed the parameter in the query like

[sourcecode language="sql" padlinenumbers="true"]
WHERE cola = @param
[/sourcecode]

and create a corresponding parameter in your data sets.  The OLE DB driver handle surrounding surrounding the parameter with quotes when required (strings and date times), and it automatically escapes any single quotes in the string.

Parameters are ordinal, so you need to create a parameter (in order) for each value you want to send. There parameters in your query will be replaced in order based on the parameters you specified in the dataset.  No big deal, but something to be aware of.  You can either use a either a normal parameter (such as ‘@myparam’) or a question mark (‘?’).

Hears the rub… the OLE DB driver also automatically escapes single quotes so you don’t have to worry about that.  This is great… but there is a little bit of an issue when you want to use multi-value parameters.  Basically, the normal method of using SQL like

[sourcecode language="sql"]
WHERE col_a IN (@multi_value_param)
[/sourcecode]

and an expression like this for a parameter

[sourcecode language="vb"]
=JOIN(@multi_param, "’,’”)
[/sourcecode]

doesn’t work because the single ticks will always be escaped (so you’ll see a value like “’val1’’,’’val2’’,’’val3’” instead of “’val1’,’val2’,’val3’” in the resulting SQL).  No problem… we just need a different approach.  We’ll use something in the WHERE clause like

[sourcecode language="sql"]
STRPOS(@multi_param, '|' || col_a|| '|') > 0
[/sourcecode]

with an expression like

[sourcecode language="vb"]
=”|” + JOIN(@multi_param, "|”) + “|”
[/sourcecode]

This will bracket each value in a special “end of column” character such as “|”, and thus look for an exact match.  You may need to use a different character, or handle escaping, depending on your data.  You can always use multiple characters if you’d like (such as “~~~” as a delimiter).  This is a little hacky, but not too bad.  Remember to add the delimiter to the start and end of the string as well so you can match those values as well.

I tested this on a 1.5 billion row table against a similar query that uses an IN clause.  I found the two methods were essentially equal in performance, though that might degrade for an extremely large number of parameter values.  These both provide exact matches against the value, and they provide equivalent results.

But… what about using a multi-select on a date?  Unfortunately, that get’s slightly more complicated, but not too bad.  All you need to do is write a little code such as

[sourcecode language="vb" padlinenumbers="true"]
    ''' <summary>
    ''' Convert an array of Dates into a string.
    ''' </summary>
    ''' <param name="inputArray">Array of dates to convert.</param>
    ''' <param name="delimiter">Delimiter to place between each item in the array.</param>
    ''' <param name="formatString">Format string for the date.</param>
    ''' <returns>String of formatted datetimes separated by a delimiter.</returns>
    ''' <remarks></remarks>
    Public Function JoinDate(ByVal inputArray() As Object, ByVal delimiter As String, ByVal formatString As String) As String
        Dim output As String = String.Empty

        Dim i As Integer
        For i = 0 To inputArray.Length - 1
            output += (CDate(inputArray(i))).ToString(formatString) + delimiter
        Next i

        ' Trim the trailing delimiter off the string
        If output.Length > 0 Then
            output = output.Substring(0, output.Length - delimiter.Length)
        End If

        Return output

    End Function
[/sourcecode]

And use an expression like

[sourcecode language="vb"]
="|" + Code.JoinDate(Parameters!date.Value, "|", "yyyy-MM-dd") + "|"
[/sourcecode]

And you’re good to go!  This will convert a multi-select DateTime variable (which is an array of DateTime objects) into a string containing dates formatted as you request.

So for this example where we are constraining on a multi-value string parameter, a multi-value date parameter, plus a few other standard guys, we end up with

[sourcecode language="sql"]
SELECT
     date_key
    ,date
    ,good_day
    ,day_of_week
FROM
    date_sample
WHERE
    1=1
    AND strpos(@day_of_week_multi, '|' ||day_of_week || '|') > 0
    AND strpos(@date, '|' || date || '|') > 0  
    AND date_key =  @date_key
    AND good_day = @good_day 
ORDER BY
    date_key
;
[/sourcecode]

Here’s a sample that shows how to use integer, datetime, and string values (with some examples of multi-value parameters thrown in).  Just use the SQL script to build and populate the test table, set the data source appropriately, and play around with it.