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
>
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Wednesday, March 7, 2012
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 to sql database via Access 2k7 project
I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.
Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:
>I am using SQL server 2005 with multiple client databases with the identical
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.
Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:
>I am using SQL server 2005 with multiple client databases with the identical
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.
Dynamic connection to sql database via Access 2k7 project
I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:
>I am using SQL server 2005 with multiple client databases with the identica
l
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:
>I am using SQL server 2005 with multiple client databases with the identica
l
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.
Dynamic Connection
I am trying to connect to multiple data sources of same type say (sql) pn different servers and run the same data flow task on all of them.
what i need to acomplish is make the connection dynamic, i tryed the sample flat file connection and i understand that i have to
1, declare a variable 'connectionstr'
2, in the connection manager properties > expression property i have to select connection string and exeression as @.[user::connectionstr']
then i am out of thoughts i know i must use FOR loop but how
even FOR loop has expression, enumerator.....
how would i point the connection string variable to a list of values(srvrname,databasename, tblname) stored in a table.
Thanks in advance...
Take a look at this example:
http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx
Here's a search that might have some other useful samples - http://search.live.com/results.aspx?q=foreach&q1=macro:jamiet.ssis&first=1
Sunday, February 19, 2012
Dynamic columns
My report has a large number of rows in the details section, This wastes a lot of space. I'd like to spread these rows across multiple columns in the details section of my table.
What i'm currently getting...
ROW1-
ROW2-
ROW3-
ROW4-
ROW5-
ROW6-
ROW7-
ROW*-
ROW9-
What I'd like to see...
ROW1- ROW2- ROW3-
ROW4- ROW5- ROW6-
ROW7- ROW8- ROW9-
Is this possible, if so, how?
SSRS supports multi-column reports.
|||Thanks for your reply, but the example in that link doesnt really suit me. My report has various groups and footer totals that are independent of the "details" columns. I need to, somehow, break the 'details' into 3 columns, while still maintaining my header,subreport, groups, and footer|||Perhaps a multi-column subreport for the details only then.
|||Using a Matrix:
Save this as an RDL file and figure out whats going on.
Code Snippet
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>05fe442d-a63b-47f0-9339-790445d10db9</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ROWGROUPS">
<GroupExpressions>
<GroupExpression>=Fields!ROWGROUPS.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="COLUMNGROUPS">
<rd:DefaultName>COLUMNGROUPS</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!COLUMNGROUPS.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_COLUMNGROUPS">
<GroupExpressions>
<GroupExpression>=Fields!COLUMNGROUPS.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ROWDATA">
<rd:DefaultName>ROWDATA</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=First(Fields!ROWDATA.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>0.5in</Height>
</Body>
<rd:ReportID>327962be-8654-4648-a50f-65e063317681</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>SELECT 'ROW1' AS ROWDATA, 1 AS ROWGROUPS, 1 AS COLUMNGROUPS
UNION ALL
SELECT 'ROW2' AS ROWDATA, 1 AS ROWGROUPS, 2 AS COLUMNGROUPS
UNION ALL
SELECT 'ROW3' AS ROWDATA, 1 AS ROWGROUPS, 3 AS COLUMNGROUPS
UNION ALL
SELECT 'ROW4' AS ROWDATA, 2 AS ROWGROUPS, 1 AS COLUMNGROUPS
UNION ALL
SELECT 'ROW5' AS ROWDATA, 2 AS ROWGROUPS, 2 AS COLUMNGROUPS
UNION ALL
SELECT 'ROW6' AS ROWDATA, 2 AS ROWGROUPS, 3 AS COLUMNGROUPS
UNION ALL
SELECT 'ROW7' AS ROWDATA, 3 AS ROWGROUPS, 1 AS COLUMNGROUPS
UNION ALL
SELECT 'ROW8' AS ROWDATA, 3 AS ROWGROUPS, 2 AS COLUMNGROUPS
UNION ALL
SELECT 'ROW9' AS ROWDATA, 3 AS ROWGROUPS, 3 AS COLUMNGROUPS</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="ROWDATA">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ROWDATA</DataField>
</Field>
<Field Name="ROWGROUPS">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ROWGROUPS</DataField>
</Field>
<Field Name="COLUMNGROUPS">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>COLUMNGROUPS</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Wednesday, February 15, 2012
Dynamic (Parameterized) Page-Breaks
Hi,
I have a report, where depending on a parameter-value (e.g. Change in day: Yes/No), I need to display the records in one page or multiple pages (i.e. If Change in Day = Yes, display the records for each day in different pages. If No, display all the records in one page). In-short page break for each day, if there is a change in day, else no page break.
Can anyone suggest, is this possible to do with SSRS 2005 RTM ? I had read somewhere that conditional page-breaks is not supported. But, is there any other way I can do this ?
Thanks in Advance.
Isnt there a way to achieve this ?
Its URGENT. Any help is highly appreciated.
|||you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value
like if user has selected "Yes" then set group expression
=iif(parametrvalue=yes,<fieldname-day>,1)
try this, it should work.
|||Thanks a lot. It worked.
Thanks again.
|||but be careful it will work only if you have only one group in report. If you add more then it won't work.|||How to implement Page break if we are having more than one groupDynamic (Parameterized) Page-Breaks
Hi,
I have a report, where depending on a parameter-value (e.g. Change in day: Yes/No), I need to display the records in one page or multiple pages (i.e. If Change in Day = Yes, display the records for each day in different pages. If No, display all the records in one page). In-short page break for each day, if there is a change in day, else no page break.
Can anyone suggest, is this possible to do with SSRS 2005 RTM ? I had read somewhere that conditional page-breaks is not supported. But, is there any other way I can do this ?
Thanks in Advance.
Isnt there a way to achieve this ?
Its URGENT. Any help is highly appreciated.
|||you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value
like if user has selected "Yes" then set group expression
=iif(parametrvalue=yes,<fieldname-day>,1)
try this, it should work.
|||Thanks a lot. It worked.
Thanks again.
|||but be careful it will work only if you have only one group in report. If you add more then it won't work.|||How to implement Page break if we are having more than one group
Subscribe to:
Posts (Atom)