CODEWITHSUNDEEP
sqlreporting-servicesreportbuilder3.0
Bill Schanks
Bill Schanks

Reputation: 163

Report Builder 3.0 underlying SQL

I have a report builder 3.0 report that has several parameters on it. Specifically, an account number (Defined as char(12) in the database). The database is a vendor supplied database, so I have zero control over the database schema.

My question is when I have a free form parameter for account id, how is that transformed into the query sent to the sql database?

The way I handle these free form fields is that I have a user defined function:

Public Function ConvertStringtoArray(sourceString as String) As string()
    Dim arrayOfStrings() As String
    Dim emptyString as String = " "
    If String.IsNullOrEmpty(sourceString) Then
    arrayOfStrings = emptyString.Split(",")
    Else
    arrayOfStrings = sourceString.Replace(" ", "").Split(",")
    End If
    return arrayOfStrings
End Function

And the parameter is defined as: @AcctList = code.ConvertStringToArray(Parameters!AcctList.Value)

The sql query has this in the where clause: Ac.Account_ID In (@AcctList)

My question is how does it build the In Clause. Will it literally be something like: Where Ac.Account_ID In (N'Acct1',N'Acct2').

I'm thinking it is, and the reason I think it's important is the query when I am running it in SSMS will run in less than 1 Second if my where clause has Where Ac.Account_ID In ('TGIF').. But it will take 13+ Seconds if I have Where Ac.Account_ID In (N'TGIF'). The total dataset returned is only 917 Rows.

The database I am querying is a 2008 R2 SP2, with the compatibility set to SQL 2008.

Upvotes: 0

Views: 740

Answers (1)

djangojazz
djangojazz

Reputation: 13270

You assumption is correct for the predicate of 'where thing in (@parameter)' actually being 'where thing in ('value1', 'value2', etc). Provided that @parameter allows multiple values. However you can tie a parameter to a query as well as using code. You can have a dataset other than your main dataset like a simple 'Select value from values' where the values would be a table of values needed for a parameter choice. This is often much more efficient unless you have to do a string split.

Upvotes: 1

Related Questions