dynamic datasource

I am trying to build a dynamic datasource for my report. The problem
is there are multiple where criteria that, if left blank, I want to
ignore. I could go down the road of nested "iif"s but that would get
very difficult to maintain. I have a C# function that already does
something like this and I was wondering if there was a way to use the
code feature of the report properties to build the sql statement, then
pass that, or set the report record source, to the string statement
generated by the code?
here is an example:
="SELECT device_sn, implant_date, model_id FROM dbo.mdrv_device WHERE "
& iif(parameters!device_sn.Value.length > 0,"(device_sn = '" &
parameters!device_sn.Value & "') AND ","") &
iif(parameters!start_date.Value.length > 0 and
parameters!end_date.Value.length >
0,"(CONVERT(varchar(10),implant_date,101) BETWEEN '" &
parameters!start_date.Value & "' AND '" & parameters!end_date.Value &
"')","")
the problem comes that if only one where criteria is used, I have to
dump the "AND" from the string. The query will actually have more like
five or six paramaters.
Any help is appreciated...Your expression can call code. So you can do something like this:
= code.CreateMyDynamicReallyCoolSQL(Parameters!device_sn,
Parameters!start_date, ...)
My suggestion is to first start off with a report with parameters and a
single textbox (don't have any datasets). Set the source for the textbox to
your expression. Make sure it creates the string you want.
When I am creating code I quite often create a quick VB Winapp. Create and
test the code there. Then copy and paste into RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen" <switter@.enpathmed.com> wrote in message
news:1122560699.229387.302310@.g43g2000cwa.googlegroups.com...
>I am trying to build a dynamic datasource for my report. The problem
> is there are multiple where criteria that, if left blank, I want to
> ignore. I could go down the road of nested "iif"s but that would get
> very difficult to maintain. I have a C# function that already does
> something like this and I was wondering if there was a way to use the
> code feature of the report properties to build the sql statement, then
> pass that, or set the report record source, to the string statement
> generated by the code?
> here is an example:
> ="SELECT device_sn, implant_date, model_id FROM dbo.mdrv_device WHERE "
> & iif(parameters!device_sn.Value.length > 0,"(device_sn = '" &
> parameters!device_sn.Value & "') AND ","") &
> iif(parameters!start_date.Value.length > 0 and
> parameters!end_date.Value.length >
> 0,"(CONVERT(varchar(10),implant_date,101) BETWEEN '" &
> parameters!start_date.Value & "' AND '" & parameters!end_date.Value &
> "')","")
> the problem comes that if only one where criteria is used, I have to
> dump the "AND" from the string. The query will actually have more like
> five or six paramaters.
> Any help is appreciated...
>|||thanks for the help Bruce. I have been able to generate the sql
statement with code. However, I notice something weird when trying to
work with integers. here is my datasource:
=Code.searchCriteria(iif(isnothing(Parameters!device_sn.Value),"",Parameters!device_sn.Value),iif(isnothing(Parameters!start_date.Value),"",Parameters!start_date.Value),iif(isnothing(Parameters!end_date.Value),"",Parameters!end_date.Value),iif(isnothing(Parameters!model_id.Value),"",Parameters!model_id.Value),iif(isnothing(Parameters!birth_end_date.Value),"",Parameters!birth_end_date.Value),iif(isnothing(Parameters!birth_start_date.Value),"",Parameters!birth_start_date.Value),iif(isnothing(Parameters!chamber_id.Value),0,Parameters!chamber_id.Value))
here is my code:
function searchCriteria(ByVal s_device_sn as string, _
ByVal s_start_date as string, _
ByVal s_end_date as string, _
ByVal s_model_id as string, _
ByVal s_birth_end_date as string, _
ByVal s_birth_start_date as string, _
ByVal i_chamber_id as integer) as string
dim tempCriteria as string
dim selectCommand as string = "SELECT device_sn, chamber_id,
implant_date, model_id, thr_v, " _
& "thr_ma, thr_msec, thr_ohms, sen_mv, file_path, last_name, " _
& "first_name, birth_date, chamber, manufacturer,
physician_first_name, " _
& "physician_last_name, facility_name, city, state, country,
province FROM dbo.mdrv_device"
tempCriteria += iif(s_device_sn.length > 0,"(device_sn = '" &
s_device_sn & "') AND ","")
tempCriteria += iif(s_start_date.length > 0 and s_end_date.length >
0,"(CONVERT(varchar(10),implant_date,101) BETWEEN '" & s_start_date &
"' AND '" & s_end_date & "') AND ","")
tempCriteria += iif(s_model_id.length > 0,"(model_id = '" & s_model_id
& "') AND ","")
tempCriteria += iif(s_birth_start_date.length > 0 and
s_birth_end_date.length > 0,"(CONVERT(varchar(10),birth_date,101)
BETWEEN '" & s_birth_start_date & "' AND '" & s_birth_end_date & "')
AND ","")
tempCriteria += iif(i_chamber_id > 0,"(chamber_id = " & i_chamber_id &
") AND ","")
searchCriteria = selectCommand & iif(tempCriteria.Length > 0," WHERE "
& tempCriteria.Substring(0,tempCriteria.Length - 5),"")
end function
the problem comes when dealing with the parameter "chamber_id", which
is an integer. if I try to pass 0 as a param to the code for the
chamber_id param (if it is left blank) the code bombs. if I pass 8 if
the param is blank, the query works. if I don't use an "iif" for that
param in both the code and the datasource expression, the dynamic where
statement comes back as "where (chamber_id = 0)". the expression is
obviously passing a zero to the code, but I can't seem to trap that
value in the code using:
iif(i_chamber_id = 0,"","(chamber_id = " & i_chamber_id & ") AND ")
without getting an error.
Any ideas?|||I didn't trace through all the detail but I bet what is happening is you are
getting bitten by one of two things. First both the true and the false
statements for iif get executed. So if 0 causes anything invalid for either
the true or false part of the iif then you will be in trouble. Second, I am
not sure what happens with using +=. I know that when you use + it will add
unless it is absolutely sure it is a string. If an & is used then it know
everything consists of strings. So it could be that your += is causing a
problem. Try going to
tempCriteria = tempCriteria & blah
I looked some more at your code and I don't think either of the two ideas
above make sense. I tend to not ever allow null or blank. Are you sure you
are passing blank and not null instead? What is the datatype? IF the
datatype is string it has two options.
I suggest passing in the parameters and have your code check everything. Put
in the if else logic there. I think that is more readable anyway.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen" <switter@.enpathmed.com> wrote in message
news:1122572779.263908.162450@.o13g2000cwo.googlegroups.com...
> thanks for the help Bruce. I have been able to generate the sql
> statement with code. However, I notice something weird when trying to
> work with integers. here is my datasource:
> =Code.searchCriteria(iif(isnothing(Parameters!device_sn.Value),"",Parameters!device_sn.Value),iif(isnothing(Parameters!start_date.Value),"",Parameters!start_date.Value),iif(isnothing(Parameters!end_date.Value),"",Parameters!end_date.Value),iif(isnothing(Parameters!model_id.Value),"",Parameters!model_id.Value),iif(isnothing(Parameters!birth_end_date.Value),"",Parameters!birth_end_date.Value),iif(isnothing(Parameters!birth_start_date.Value),"",Parameters!birth_start_date.Value),iif(isnothing(Parameters!chamber_id.Value),0,Parameters!chamber_id.Value))
> here is my code:
> function searchCriteria(ByVal s_device_sn as string, _
> ByVal s_start_date as string, _
> ByVal s_end_date as string, _
> ByVal s_model_id as string, _
> ByVal s_birth_end_date as string, _
> ByVal s_birth_start_date as string, _
> ByVal i_chamber_id as integer) as string
> dim tempCriteria as string
> dim selectCommand as string = "SELECT device_sn, chamber_id,
> implant_date, model_id, thr_v, " _
> & "thr_ma, thr_msec, thr_ohms, sen_mv, file_path, last_name, " _
> & "first_name, birth_date, chamber, manufacturer,
> physician_first_name, " _
> & "physician_last_name, facility_name, city, state, country,
> province FROM dbo.mdrv_device"
> tempCriteria += iif(s_device_sn.length > 0,"(device_sn = '" &
> s_device_sn & "') AND ","")
> tempCriteria += iif(s_start_date.length > 0 and s_end_date.length >
> 0,"(CONVERT(varchar(10),implant_date,101) BETWEEN '" & s_start_date &
> "' AND '" & s_end_date & "') AND ","")
> tempCriteria += iif(s_model_id.length > 0,"(model_id = '" & s_model_id
> & "') AND ","")
> tempCriteria += iif(s_birth_start_date.length > 0 and
> s_birth_end_date.length > 0,"(CONVERT(varchar(10),birth_date,101)
> BETWEEN '" & s_birth_start_date & "' AND '" & s_birth_end_date & "')
> AND ","")
> tempCriteria += iif(i_chamber_id > 0,"(chamber_id = " & i_chamber_id &
> ") AND ","")
> searchCriteria = selectCommand & iif(tempCriteria.Length > 0," WHERE "
> & tempCriteria.Substring(0,tempCriteria.Length - 5),"")
> end function
>
> the problem comes when dealing with the parameter "chamber_id", which
> is an integer. if I try to pass 0 as a param to the code for the
> chamber_id param (if it is left blank) the code bombs. if I pass 8 if
> the param is blank, the query works. if I don't use an "iif" for that
> param in both the code and the datasource expression, the dynamic where
> statement comes back as "where (chamber_id = 0)". the expression is
> obviously passing a zero to the code, but I can't seem to trap that
> value in the code using:
> iif(i_chamber_id = 0,"","(chamber_id = " & i_chamber_id & ") AND ")
> without getting an error.
> Any ideas?
>|||I got it to work by changing the parameter type from an integer to a
string and processing it that way. Here is my code:
function searchCriteria(ByVal s_device_sn as string, _
ByVal s_start_date as string, _
ByVal s_end_date as string, _
ByVal s_model_id as string, _
ByVal s_birth_start_date as string, _
ByVal s_birth_end_date as string, _
ByVal i_chamber_id as string) as string
dim tempCriteria as string = ""
dim selectCommand as string = "SELECT device_sn, chamber_id,
implant_date, model_id, thr_v, " _
& "thr_ma, thr_msec, thr_ohms, sen_mv, file_path, last_name, " _
& "first_name, birth_date, chamber, manufacturer,
physician_first_name, " _
& "physician_last_name, facility_name, city, state, country,
province FROM dbo.mdrv_device"
'build criteria for where clause
if s_device_sn.length > 0 then
tempCriteria = "(device_sn = '" & s_device_sn & "') AND "
end if
if s_start_date.length > 0 and s_end_date.length > 0 then
tempCriteria = tempCriteria & "(CONVERT(varchar(10),implant_date,101)
BETWEEN '" & s_start_date & "' AND '" & s_end_date & "') AND "
end if
if s_model_id.length > 0 then
tempCriteria = tempCriteria & "(model_id = '" & s_model_id & "') AND
"
end if
if s_birth_start_date.length > 0 and s_birth_end_date.length > 0 then
tempCriteria = tempCriteria & "(CONVERT(varchar(10),birth_date,101)
BETWEEN '" & s_birth_start_date & "' AND '" & s_birth_end_date & "')
AND "
end if
if i_chamber_id.length > 0 then
tempCriteria = tempCriteria & "(chamber_id = " & i_chamber_id & ")
AND "
end if
'finish the sql statement
if tempCriteria.Length > 0
selectCommand = selectCommand & " WHERE " &
tempCriteria.Substring(0,tempCriteria.Length - 5)
end if
searchCriteria = selectCommand
end function
and here is my recordsource:
=Code.searchCriteria(iif(isnothing(Parameters!device_sn.Value),"",Parameters!device_sn.Value),iif(isnothing(Parameters!start_date.Value),"",Parameters!start_date.Value),iif(isnothing(Parameters!end_date.Value),"",Parameters!end_date.Value),iif(isnothing(Parameters!model_id.Value),"",Parameters!model_id.Value),iif(isnothing(Parameters!birth_start_date.Value),"",Parameters!birth_start_date.Value),iif(isnothing(Parameters!birth_end_date.Value),"",Parameters!birth_end_date.Value),iif(isnothing(Parameters!chamber_id.Value),"",Parameters!chamber_id.Value))