Hi
Is there a way we can define the datasource name/connection string for a
report in a config file... and read it from there so that it would make the
job of deploying across various servers easy?Various approaches for dynamic database connections in RS 2000 are
available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server - you should some
threads about it in the archives of this newsgroup.
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
In addition, native support (expression-based connection strings) is
available in RS 2005: Finish the design of the datasets with a constant
connection string and make sure everything works. Then, go back to the data
tab and open the dataset/data source dialog and change the connection string
to be an expression. Use string concatenation to plug in the parameter
value. Here is an example of how the RDL would look for a parameter-based
connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" & Parameters!ServerName.Value
& ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>
You can also check this blog posting:
http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:25C803F0-8944-4712-9A72-51F26DED394D@.microsoft.com...
> Hi
> Is there a way we can define the datasource name/connection string for a
> report in a config file... and read it from there so that it would make
> the
> job of deploying across various servers easy?
Showing posts with label datasource. Show all posts
Showing posts with label datasource. Show all posts
Wednesday, March 7, 2012
Dynamic datasource for RepoertViewer Problem
hi all,
i have a reportviewer in acsx file(controll).
i try to attach dynamicly it datasource in this way:
Protected Sub cmdGetReport_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cmdGetReport.Click ReportViewer1.Visible =True Dim ObjectDataSource1As ObjectDataSource =New ObjectDataSource ObjectDataSource1.TypeName ="ReportDataSetTableAdapters.Contel_Enercon_RptInvoiceByAccountTableAdapter" ObjectDataSource1.SelectParameters.Clear()If cboAccountList.SelectedValue > 0Then ObjectDataSource1.SelectMethod ="GetInvoiceByAccount" ObjectDataSource1.SelectParameters.Add("SiteID", cboSiteList.SelectedValue) ObjectDataSource1.SelectParameters.Add("InvoiceID", cboInvoiceList.SelectedValue) ObjectDataSource1.SelectParameters.Add("AccountID", cboAccountList.SelectedValue)Else ObjectDataSource1.SelectMethod ="GetAllInvoiceBySite" ObjectDataSource1.SelectParameters.Add("SiteID", cboSiteList.SelectedValue) ObjectDataSource1.SelectParameters.Add("InvoiceID", cboInvoiceList.SelectedValue)End If Dim rdsAs ReportDataSource =New ReportDataSource rds.DataSourceId ="ObjectDataSource1" rds.Name ="ReportDataSet_Contel_Enercon_RptInvoiceByAccount" ReportViewer1.LocalReport.DataSources.Add(rds) ReportViewer1.LocalReport.Refresh()End Sub
but when i try to open the report i get this error msg:
The DataSourceID of the ReportDataSource 'ReportDataSet_Contel_Enercon_RptInvoiceByAccount' of the ReportViewer 'ReportViewer1' must be the ID of a control of type IDataSource. A control with ID 'ObjectDataSource1' could not be found.
any idea?
Well I solved it.
Instead :
Dim rdsAs ReportDataSource =New ReportDataSource rds.DataSourceId ="ObjectDataSource1" rds.Name ="ReportDataSet_Contel_Enercon_RptInvoiceByAccount"
i wrote:Dim rdsAs ReportDataSource =New ReportDataSource("ReportDataSet_Contel_Enercon_RptInvoiceByAccount",ObjectDataSource1)
And it worked...
dynamic datasource
Our application is used by multiple companies. Each company has its own SQL
Server 2k database. Each company has access to the same list of reports in
SRS. We would like to deploy one physical copy of each report in the report
database for simplest maintenance of RDL files. Our application is deployed
in a web browser as an ASP application.
When a user in company A accesses Report 2 we need to render the report with
datasource parameters that will connect him to the correct db. At the same
time, users in companies B, C and D may be running the same report, each with
the correct datasource parameters for their companies.
Hence, as a report is requested by each user, we need to set up (or connect
to) the correct datasource parameters dynamically and then render the report
for the user.
Is there a way to do this with SRS? If multiple ways I would appreciate
knowing some of the options. If there are good books that discuss these
options I would appreciate references to them as well.
TIAHi joe,
We do something similar. We have multiple clients who use their own SQL
Database. We are using a custom data source (using ADO.net dataset). There
are numerous articles on the web which show you how to create one. Its pretty
simple and straightforward. The connection string is dynamically formed based
on the SQL Database name passed in as a parameter. This way we are able to
support multiple clients using a single copy of the rdl files.
Thanks,
George Tharakan
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Are you using the Report Manager or are you integrating with your own
application using either URL integration or Web services?
Assuming you are using Report Manager you can do the following:
1. have a report parameter that is based on the query. For the query use
pass the User!userid to a stored procedure (or use in a query) to query a
table and find out what database they should be using. Have this parameter
hidden so it can not be seen or modified by the user.
2. Using the generic query designer have the query use an expression like
this:
= "Select * from " & Parameters!DBName & ".dbo.mytable"
I usually test all this out with a report that has a single text box set to
this expression so I can see the query string and be sure it is correct
before I set a dataset source to it.
But, one issue you will have (with both this and the other solution on
creating your own extension) is that the UserID global parameter is not
usable from subscriptions. Really any solution this will be a problem
because you have to set the subcription to be for someone or it is run as
someone.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:28360A4A-BB83-4B5B-B55E-83A9F3B378E9@.microsoft.com...
> Our application is used by multiple companies. Each company has its own
> SQL
> Server 2k database. Each company has access to the same list of reports
> in
> SRS. We would like to deploy one physical copy of each report in the
> report
> database for simplest maintenance of RDL files. Our application is
> deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report
> with
> datasource parameters that will connect him to the correct db. At the
> same
> time, users in companies B, C and D may be running the same report, each
> with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or
> connect
> to) the correct datasource parameters dynamically and then render the
> report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Thanks very much for both of your responses.
I have been attempting to build an extension. It's hard to tell how far I
am from success because I don't know how to debug the code. Can you offer
advice on this?
I'm using the VS.NET Report Designer to build my reports. I can see the new
extension there, but cannot get a connection to work.
FYI - I'm using the ReportViewer control to embed the reports in my web app.|||Sorry, I have not done an extension. I suggest posting a new subject with
your questions for that so people that have done extensions know that is
what the question is about and can jump in and help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:3537E448-CE86-4E68-87C7-C8719A62112B@.microsoft.com...
> Thanks very much for both of your responses.
> I have been attempting to build an extension. It's hard to tell how far I
> am from success because I don't know how to debug the code. Can you offer
> advice on this?
> I'm using the VS.NET Report Designer to build my reports. I can see the
> new
> extension there, but cannot get a connection to work.
> FYI - I'm using the ReportViewer control to embed the reports in my web
> app.|||We have struggled with the same issue.
We were able to implement the @.server parameter and use that parameter to
dynamically choose the sql server at runtime.
Have fun trying to figure out the authentication nightmare you will encounter.
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>
Server 2k database. Each company has access to the same list of reports in
SRS. We would like to deploy one physical copy of each report in the report
database for simplest maintenance of RDL files. Our application is deployed
in a web browser as an ASP application.
When a user in company A accesses Report 2 we need to render the report with
datasource parameters that will connect him to the correct db. At the same
time, users in companies B, C and D may be running the same report, each with
the correct datasource parameters for their companies.
Hence, as a report is requested by each user, we need to set up (or connect
to) the correct datasource parameters dynamically and then render the report
for the user.
Is there a way to do this with SRS? If multiple ways I would appreciate
knowing some of the options. If there are good books that discuss these
options I would appreciate references to them as well.
TIAHi joe,
We do something similar. We have multiple clients who use their own SQL
Database. We are using a custom data source (using ADO.net dataset). There
are numerous articles on the web which show you how to create one. Its pretty
simple and straightforward. The connection string is dynamically formed based
on the SQL Database name passed in as a parameter. This way we are able to
support multiple clients using a single copy of the rdl files.
Thanks,
George Tharakan
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Are you using the Report Manager or are you integrating with your own
application using either URL integration or Web services?
Assuming you are using Report Manager you can do the following:
1. have a report parameter that is based on the query. For the query use
pass the User!userid to a stored procedure (or use in a query) to query a
table and find out what database they should be using. Have this parameter
hidden so it can not be seen or modified by the user.
2. Using the generic query designer have the query use an expression like
this:
= "Select * from " & Parameters!DBName & ".dbo.mytable"
I usually test all this out with a report that has a single text box set to
this expression so I can see the query string and be sure it is correct
before I set a dataset source to it.
But, one issue you will have (with both this and the other solution on
creating your own extension) is that the UserID global parameter is not
usable from subscriptions. Really any solution this will be a problem
because you have to set the subcription to be for someone or it is run as
someone.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:28360A4A-BB83-4B5B-B55E-83A9F3B378E9@.microsoft.com...
> Our application is used by multiple companies. Each company has its own
> SQL
> Server 2k database. Each company has access to the same list of reports
> in
> SRS. We would like to deploy one physical copy of each report in the
> report
> database for simplest maintenance of RDL files. Our application is
> deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report
> with
> datasource parameters that will connect him to the correct db. At the
> same
> time, users in companies B, C and D may be running the same report, each
> with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or
> connect
> to) the correct datasource parameters dynamically and then render the
> report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Thanks very much for both of your responses.
I have been attempting to build an extension. It's hard to tell how far I
am from success because I don't know how to debug the code. Can you offer
advice on this?
I'm using the VS.NET Report Designer to build my reports. I can see the new
extension there, but cannot get a connection to work.
FYI - I'm using the ReportViewer control to embed the reports in my web app.|||Sorry, I have not done an extension. I suggest posting a new subject with
your questions for that so people that have done extensions know that is
what the question is about and can jump in and help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:3537E448-CE86-4E68-87C7-C8719A62112B@.microsoft.com...
> Thanks very much for both of your responses.
> I have been attempting to build an extension. It's hard to tell how far I
> am from success because I don't know how to debug the code. Can you offer
> advice on this?
> I'm using the VS.NET Report Designer to build my reports. I can see the
> new
> extension there, but cannot get a connection to work.
> FYI - I'm using the ReportViewer control to embed the reports in my web
> app.|||We have struggled with the same issue.
We were able to implement the @.server parameter and use that parameter to
dynamically choose the sql server at runtime.
Have fun trying to figure out the authentication nightmare you will encounter.
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>
Dynamic DataSource
How do I change a DataSource on the fly. Do I make the Data Source and the Catalog values parameters in the report then in code feed the parms in like you would a other report parms or in code just make a new definition then call rs2005.SetDataSourceContents(reportPath, definition);?
I have had trouble finding help on this subject and would appreciate knowing how you made it work or a link to a useful help doc.
Thanks
-JWIf you're talking about a report you intend to publish to the report server, the way to do this is:
1) create a static report specific data source (specify the connection string explicitly). Do not use a shared data source reference!
2) build your report as you normally would
3) test that it works :-)
4) change the connection string in your report specific data source to be an expression.
I have had trouble finding help on this subject and would appreciate knowing how you made it work or a link to a useful help doc.
Thanks
-JWIf you're talking about a report you intend to publish to the report server, the way to do this is:
1) create a static report specific data source (specify the connection string explicitly). Do not use a shared data source reference!
2) build your report as you normally would
3) test that it works :-)
4) change the connection string in your report specific data source to be an expression.
For example, if you are using SQL Server 2005 as your data source:
Original: data source=localhost\instanceName; initial catalog=AdventureWorks
Expression Based: ="data source=" + Parameters!P1.value + "; initial catalog=" + Parameters!P2.value
You might need to add quotes if your catalog name has spaces. You can use either parameters or an expression. For, example you might have a function you define in your report that looks up the right database for a given user:
="data source=" + Parameters!P1.value + "; initial catalog=" + Code.LookUpDatabaseForUser(Globals!UserID)
The variations on this theme are endless. You might use a different database if you have a different language to get the right group names, etc.
The thing to note is that the databases all have to have the same schema so that your query works.
Of course, you could then make you query to be expression based... but that's adding a whole lot of complexity and should be considered only if you really need it for your report.
-Lukasz|||Thank You
|||
do you have a sample for rs2000? Thanks.
|||Expression based connection strings are new in RS 2005.Thanks
Tudor
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))
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 database with MS ACCESS
Hi,
I am using ASP.NET to display reports using the report viewer object.
I have multiple MS Access databases that can be used as the datasource to a
report.
I know how to make a connection to multiple different SQL databases passing
the database as a parameter in the URL of my query and then into my stored
procs, but the same technique cannot be applied to MS Access databases.
I figured out that the parameter to be passed must be the *.mdb file path on
the server... but where to use it?
ThxI have a huge query for as one of my datasets, something like this (this
query works well) :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN 'c:\db1.mdb'
ORDER BY tabl1.x
I tried the following :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN @.DBPath
ORDER BY tabl1.x
But apparently u can't use a named parameter with OLE DB ... so I tried this :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN ?
ORDER BY tabl1.x
But when I execute the query and am asked what value should ? take, whatever
the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
[c:\db1.mdb]
Nothing works. I also don't know how to map the ? variable to a @.Parameter,
because the ? doesn't generate a parameter in the parameter tab ...
AAAaaaarggg!|||You are right about using unnamed parameters. First, are you in the generic
query designer (2 panes). You will want to be there (button is to the right
of the ...). Next, if a parameter is not created automatically for you then
go to the form design, menu report->report parameters and add a parameter.
Then go back to the data tab, click on the ..., go to the parameters tab and
then put in the ? on the left and select your parameter on the right.
My guess is that you cannot just have a parameter in your query where it is
not part of the where clause (which in this case is not). However, you can
still do this. Use an expression which would look like this:
= "SELECT tabl1.*, ..., tablx.* FROM tabl1, ... tablx IN '" &
Parameters!Paramname.Value & "' ORDER BY tabl1.x"
I sometimes create a report with no datasource, just my parameters and a
textbox. I set the textbox to the expression so I can see what I have
created so I know if it is correct before I set the dataset to this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6024F625-1721-4212-AB86-B8C131A12CE2@.microsoft.com...
>I have a huge query for as one of my datasets, something like this (this
> query works well) :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN 'c:\db1.mdb'
> ORDER BY tabl1.x
> I tried the following :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN @.DBPath
> ORDER BY tabl1.x
> But apparently u can't use a named parameter with OLE DB ... so I tried
> this :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN ?
> ORDER BY tabl1.x
> But when I execute the query and am asked what value should ? take,
> whatever
> the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
> [c:\db1.mdb]
> Nothing works. I also don't know how to map the ? variable to a
> @.Parameter,
> because the ? doesn't generate a parameter in the parameter tab ...
> AAAaaaarggg!
>
I am using ASP.NET to display reports using the report viewer object.
I have multiple MS Access databases that can be used as the datasource to a
report.
I know how to make a connection to multiple different SQL databases passing
the database as a parameter in the URL of my query and then into my stored
procs, but the same technique cannot be applied to MS Access databases.
I figured out that the parameter to be passed must be the *.mdb file path on
the server... but where to use it?
ThxI have a huge query for as one of my datasets, something like this (this
query works well) :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN 'c:\db1.mdb'
ORDER BY tabl1.x
I tried the following :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN @.DBPath
ORDER BY tabl1.x
But apparently u can't use a named parameter with OLE DB ... so I tried this :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN ?
ORDER BY tabl1.x
But when I execute the query and am asked what value should ? take, whatever
the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
[c:\db1.mdb]
Nothing works. I also don't know how to map the ? variable to a @.Parameter,
because the ? doesn't generate a parameter in the parameter tab ...
AAAaaaarggg!|||You are right about using unnamed parameters. First, are you in the generic
query designer (2 panes). You will want to be there (button is to the right
of the ...). Next, if a parameter is not created automatically for you then
go to the form design, menu report->report parameters and add a parameter.
Then go back to the data tab, click on the ..., go to the parameters tab and
then put in the ? on the left and select your parameter on the right.
My guess is that you cannot just have a parameter in your query where it is
not part of the where clause (which in this case is not). However, you can
still do this. Use an expression which would look like this:
= "SELECT tabl1.*, ..., tablx.* FROM tabl1, ... tablx IN '" &
Parameters!Paramname.Value & "' ORDER BY tabl1.x"
I sometimes create a report with no datasource, just my parameters and a
textbox. I set the textbox to the expression so I can see what I have
created so I know if it is correct before I set the dataset to this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6024F625-1721-4212-AB86-B8C131A12CE2@.microsoft.com...
>I have a huge query for as one of my datasets, something like this (this
> query works well) :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN 'c:\db1.mdb'
> ORDER BY tabl1.x
> I tried the following :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN @.DBPath
> ORDER BY tabl1.x
> But apparently u can't use a named parameter with OLE DB ... so I tried
> this :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN ?
> ORDER BY tabl1.x
> But when I execute the query and am asked what value should ? take,
> whatever
> the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
> [c:\db1.mdb]
> Nothing works. I also don't know how to map the ? variable to a
> @.Parameter,
> because the ? doesn't generate a parameter in the parameter tab ...
> AAAaaaarggg!
>
Friday, February 24, 2012
Dynamic connection string in dataset?
Hi,
Since rds (dataset file) is simple XML, is it possible to change the datasource value from it and use it without deploying it on the RS server, similar as we do with the web.config?
TIA,
Tanmaya
Just changing the xml contents of a rds file in the file system won't have any effects on the report server. Shared data source settings can only be changed through the RS SOAP API methods.
-- Robert
Subscribe to:
Posts (Atom)