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))

Dynamic Dataset to Reports

Hi,
I have some selection criteria in my .aspx web page and based on the
selections, by using an stored procedure, I am generating the dataset. As of
now, I am displaying this in HTML table format.
Now I have designed the report using Reporting Services and have deployed it
onto the Report Server. I would like to know how do I pass this dataset from
my .aspx webpage onto the report and generate the same.
Could any one please let me know the links to materials explaining this
technique or any help would be highly appreciated.
Regards,
Sudhakara.T.P.Hi,
Try to do this.
1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
2. Configure your ReportViewer to a LocalReport mode. This will allow you to
link your dataset to a report at runtime by using ReportViewer.LocalReport
methods or at disign level too.
Hopefully I help you.
Arturo Carrión
at Teimpo Hard Development team
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> escribió en el
mensaje news:3188FD41-0A7F-448F-AE2C-7AD2423F343F@.microsoft.com...
> Hi,
> I have some selection criteria in my .aspx web page and based on the
> selections, by using an stored procedure, I am generating the dataset. As
> of
> now, I am displaying this in HTML table format.
> Now I have designed the report using Reporting Services and have deployed
> it
> onto the Report Server. I would like to know how do I pass this dataset
> from
> my .aspx webpage onto the report and generate the same.
> Could any one please let me know the links to materials explaining this
> technique or any help would be highly appreciated.
> Regards,
> Sudhakara.T.P.|||Hi,
Thank you very much for your reply.
Infact, I tried doing this earlier, but the problem with this is that, in
the preview mode, I am not getting the print button to print the button, even
tough I have set the showprintoption to true.
Any alternative solution for this?
Regards,
Sudhakara.T.P.
"tiempotecnologia@.newsgroup.nospam" wrote:
> Hi,
> Try to do this.
> 1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
> 2. Configure your ReportViewer to a LocalReport mode. This will allow you to
> link your dataset to a report at runtime by using ReportViewer.LocalReport
> methods or at disign level too.
> Hopefully I help you.
> Arturo Carrión
> at Teimpo Hard Development team
> "Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> escribió en el
> mensaje news:3188FD41-0A7F-448F-AE2C-7AD2423F343F@.microsoft.com...
> > Hi,
> > I have some selection criteria in my .aspx web page and based on the
> > selections, by using an stored procedure, I am generating the dataset. As
> > of
> > now, I am displaying this in HTML table format.
> > Now I have designed the report using Reporting Services and have deployed
> > it
> > onto the Report Server. I would like to know how do I pass this dataset
> > from
> > my .aspx webpage onto the report and generate the same.
> > Could any one please let me know the links to materials explaining this
> > technique or any help would be highly appreciated.
> > Regards,
> > Sudhakara.T.P.
>
>

Dynamic Criteria for a Stored Procedure

Our database utilizes some custom functions and .dll's to perform IRR
calcuations. The IRR calculation takes two steps.
1. The first step is to create a view which preps the cash flows and
pulls out what you want to analyze. ("Ad Hoc Query")
2. The second step is to create a stored procedure that references the
Ad Hoc Query and performs the IRR calculation ("IRR sp"). The IRR sp
has three important parameters:
a. a start date. If the start date is null, then spGetPerformance
performs the calculation from the earliest date.
b. an end date. This is typically set to the most recent quarter
end date.
c. @.ShowTransactions. This allows us to audit the constituents of
the calculation. 0 = return a single record that is the IRR. 1 =
return all the constituents such that a user could transfer the
recordset to excel and perform the IRR calculation to double check that
spGetPerformance is performing well and there are no errors in data
entry for the constituents.
A sample of the IRR sp is written below:
ALTER PROCEDURE sp10_IRRSinceInceptionByInvestor
AS
BEGIN SET NOCOUNT ON
exec spGetPerformance @.AdHocQueryName =
'sp10_view_IRRSinceInceptionByInvestor',
@.StartDate = NULL, @.EndDate =
'6/30/2005', @.ShowTransactions = 0
END
---
Now I have about 30 of these IRR sp and they all have EndDates that are
the same or somehow systematically related (e.g. 5 are set one year
earlier, 5 are set 3 years earlier, etc.).
Is there a way to write a script, stored procedure, something else to
change the @.EndDate criteria for each IRR sp in a programatic way?
Now, I need to open each IRR sp and manually change the @.EndDate
parameter. I do have a table of IRR sp names and dates, but I am a
complete SQL novice, so I can't even so where to go next. I tried to
put dlookups in the criteria section but that doesn't seem to work.
Ryan(Ryan.Chowdhury@.gmail.com) writes:
> Now I have about 30 of these IRR sp and they all have EndDates that are
> the same or somehow systematically related (e.g. 5 are set one year
> earlier, 5 are set 3 years earlier, etc.).
> Is there a way to write a script, stored procedure, something else to
> change the @.EndDate criteria for each IRR sp in a programatic way?
> Now, I need to open each IRR sp and manually change the @.EndDate
> parameter. I do have a table of IRR sp names and dates, but I am a
> complete SQL novice, so I can't even so where to go next. I tried to
> put dlookups in the criteria section but that doesn't seem to work.
Nothing is impossible, and of course you could write a program to
do this. But for 30 procedures, I seriously doubt that it's worth the
effort. Had you said 300, it had been a different issue.
I will have to admit that I did not fully understand the setup, but if
you need to change the dates, maybe the scheme is flawed in some way.
Rather than hard-coding the values, maybe EndDate should be a parameter?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, I was told to run the IRR sp as an exec statement. I don't know
how to incorporate the EndDate as a parameter, although is it not a
parameter in the sp code above? It is just hard coded as 6/30/2005
right now.|||(Ryan.Chowdhury@.gmail.com) writes:
> Erland, I was told to run the IRR sp as an exec statement. I don't know
> how to incorporate the EndDate as a parameter, although is it not a
> parameter in the sp code above? It is just hard coded as 6/30/2005
> right now.
If I understand your description correctly, these procedures are somehow
generated. The scheme appeared strange to me, and may not be the best one.
But since I only know a fraction of what is going on in your organisation,
I can't say exactly how this should be changed.
If you think that having these 30 procedures that you need to change is
causing manageability problems, you should bring it up internally and
propose that a better solution to be developed.
As for the question you actually asked: yes, you could automate that change,
but, no, it would not be worth the effort.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx