Reporting service access control

Were working on a classic asp app and are trying to implement some
reports through reporting service. I have a few questions.
First the application were working on will have multiple instances, so
one a single server there can be upto 15 identical apps connecting to
different databases, is there a way to have reporting service limit
what data source reporting service can access depending on what IIS
website/application pool the browsers are accessing?
Badgers.servername.com/reportserver should open the reports with the
badgers db
Squirrels.servername.com/reportserver db=squirrels
Servername.com:54333 db=badgers
Servername.com:54334 db=squirrelsNot automatically but you can pass a parameter that is used by your report
to use the appropriate datasource.
In RS 2005 you can use an expression for your data source. Which if you
write your reports to expect a parameter (say DBParam) then you can do
exactly what you want. From Books OnLine:
>>>>>>
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" & Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources shares the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<lance.sanchez@.gmail.com> wrote in message
news:1190052200.309137.245860@.d55g2000hsg.googlegroups.com...
> Were working on a classic asp app and are trying to implement some
> reports through reporting service. I have a few questions.
> First the application were working on will have multiple instances, so
> one a single server there can be upto 15 identical apps connecting to
> different databases, is there a way to have reporting service limit
> what data source reporting service can access depending on what IIS
> website/application pool the browsers are accessing?
> Badgers.servername.com/reportserver should open the reports with the
> badgers db
> Squirrels.servername.com/reportserver db=squirrels
> Servername.com:54333 db=badgers
> Servername.com:54334 db=squirrels
>
begin 666 copycode.gif
M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
MT?_______P``````````````````````````````````````````````````
M`````````````````````````````````````````````````"'Y! $``&8`
M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
`
end|||Thank you, is there a way to tie it into the IIS session so someone
would generate an error if they tried to change the parameter to
something else?
On Sep 17, 11:29 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Not automatically but you can pass a parameter that is used by your report
> to use the appropriate datasource.
> In RS 2005 you can use an expression for your data source. Which if you
> write your reports to expect a parameter (say DBParam) then you can do
> exactly what you want. From Books OnLine:
> Data Source Expressions
> You can put an expression into a connection string to allow users to select
> the data source at run time. For example, suppose a multinational firm has
> data servers in several countries. With an expression-based connection
> string, a user who is running a sales report can select a data source for a
> particular country before running the report.
> The following example illustrates the use of a data source expression in a
> SQL Server connection string. The example assumes you have created a report
> parameter named ServerName:
> Copy Code
> ="data source=" & Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
> Data source expressions are processed at run time or when a report is
> previewed. The expression must be written in Visual Basic. Use the following
> guidelines when defining a data source expression:
> a.. Design the report using a static connection string. A static
> connection string refers to a connection string that is not set through an
> expression (for example, when you follow the steps for creating a
> report-specific or shared data source, you are defining a static connection
> string). Using a static connection string allows you to connect to the data
> source in Report Designer so that you can get the query results you need to
> create the report.
> b.. When defining the data source connection, do not use a shared data
> source. You cannot use a data source expression in a shared data source. You
> must define a report-specific data source for the report.
> c.. Specify credentials separately from the connection string. You can use
> stored credentials, prompted credentials, or integrated security.
> d.. Add a report parameter to specify a data source. For parameter values,
> you can either provide a static list of available values (in this case, the
> available values should be data sources you can use with the report) or
> define a query that retrieves a list of data sources at run time.
> e.. Be sure that the list of data sources shares the same database schema.
> All report design begins with schema information. If there is a mismatch
> between the schema used to define the report and the actual schema used by
> the report at run time, the report might not run.
> f.. Before publishing the report, replace the static connection string
> with an expression. Wait until you are finished designing the report before
> you replace the static connection string with an expression. Once you use an
> expression, you cannot execute the query in Report Designer. Furthermore,
> the field list in the Datasets window and the Parameters list will not
> update automatically.
> >>>>>>>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <lance.sanc...@.gmail.com> wrote in message
> news:1190052200.309137.245860@.d55g2000hsg.googlegroups.com...
>
> > Were working on a classic asp app and are trying to implement some
> > reports through reporting service. I have a few questions.
> > First the application were working on will have multiple instances, so
> > one a single server there can be upto 15 identical apps connecting to
> > different databases, is there a way to have reporting service limit
> > what data source reporting service can access depending on what IIS
> > website/application pool the browsers are accessing?
> > Badgers.servername.com/reportserver should open the reports with the
> > badgers db
> > Squirrels.servername.com/reportserver db=squirrels
> > Servername.com:54333 db=badgers
> > Servername.com:54334 db=squirrels
> begin 666 copycode.gif
> M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
> M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
> M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
> MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
> MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
> M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
> MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
> MT?_______P``````````````````````````````````````````````````
> M`````````````````````````````````````````````````"'Y! $``&8`
> M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
> M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
> M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
> F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
> `
> end|||You are getting into a complicated environment. If your asp applications are
on a different server than RS you are going to run into the double hop
problem. That is a windows issue where someone who is recognized by your web
app as one person then is not recognized when credentials hop from one
server to another. This requires kerboros to work. I am just giving you a
heads up. This is only an issue if you are using integrated security. If you
are using forms based security (i.e. role your own credentials) then it is
not an issue.
A parameter can be hidden so if someone goes directly to report manager
(portal for RS) they will not see the parameter. OR, you can also tie the
report to the user. RS has a global variable called userid that gives the
domain and userid of the user running the report. You could use this to only
allow users to see the data they should.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<lance.sanchez@.gmail.com> wrote in message
news:1190054179.058425.32890@.50g2000hsm.googlegroups.com...
> Thank you, is there a way to tie it into the IIS session so someone
> would generate an error if they tried to change the parameter to
> something else?
>
> On Sep 17, 11:29 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> Not automatically but you can pass a parameter that is used by your
>> report
>> to use the appropriate datasource.
>> In RS 2005 you can use an expression for your data source. Which if you
>> write your reports to expect a parameter (say DBParam) then you can do
>> exactly what you want. From Books OnLine:
>> Data Source Expressions
>> You can put an expression into a connection string to allow users to
>> select
>> the data source at run time. For example, suppose a multinational firm
>> has
>> data servers in several countries. With an expression-based connection
>> string, a user who is running a sales report can select a data source for
>> a
>> particular country before running the report.
>> The following example illustrates the use of a data source expression in
>> a
>> SQL Server connection string. The example assumes you have created a
>> report
>> parameter named ServerName:
>> Copy Code
>> ="data source=" & Parameters!ServerName.Value & ";initial
>> catalog=AdventureWorks
>> Data source expressions are processed at run time or when a report is
>> previewed. The expression must be written in Visual Basic. Use the
>> following
>> guidelines when defining a data source expression:
>> a.. Design the report using a static connection string. A static
>> connection string refers to a connection string that is not set through
>> an
>> expression (for example, when you follow the steps for creating a
>> report-specific or shared data source, you are defining a static
>> connection
>> string). Using a static connection string allows you to connect to the
>> data
>> source in Report Designer so that you can get the query results you need
>> to
>> create the report.
>> b.. When defining the data source connection, do not use a shared data
>> source. You cannot use a data source expression in a shared data source.
>> You
>> must define a report-specific data source for the report.
>> c.. Specify credentials separately from the connection string. You can
>> use
>> stored credentials, prompted credentials, or integrated security.
>> d.. Add a report parameter to specify a data source. For parameter
>> values,
>> you can either provide a static list of available values (in this case,
>> the
>> available values should be data sources you can use with the report) or
>> define a query that retrieves a list of data sources at run time.
>> e.. Be sure that the list of data sources shares the same database
>> schema.
>> All report design begins with schema information. If there is a mismatch
>> between the schema used to define the report and the actual schema used
>> by
>> the report at run time, the report might not run.
>> f.. Before publishing the report, replace the static connection string
>> with an expression. Wait until you are finished designing the report
>> before
>> you replace the static connection string with an expression. Once you use
>> an
>> expression, you cannot execute the query in Report Designer. Furthermore,
>> the field list in the Datasets window and the Parameters list will not
>> update automatically.
>> >>>>>>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <lance.sanc...@.gmail.com> wrote in message
>> news:1190052200.309137.245860@.d55g2000hsg.googlegroups.com...
>>
>> > Were working on a classic asp app and are trying to implement some
>> > reports through reporting service. I have a few questions.
>> > First the application were working on will have multiple instances, so
>> > one a single server there can be upto 15 identical apps connecting to
>> > different databases, is there a way to have reporting service limit
>> > what data source reporting service can access depending on what IIS
>> > website/application pool the browsers are accessing?
>> > Badgers.servername.com/reportserver should open the reports with the
>> > badgers db
>> > Squirrels.servername.com/reportserver db=squirrels
>> > Servername.com:54333 db=badgers
>> > Servername.com:54334 db=squirrels
>> begin 666 copycode.gif
>> M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
>> M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
>> M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
>> MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
>> MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
>> M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
>> MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
>> MT?_______P``````````````````````````````````````````````````
>> M`````````````````````````````````````````````````"'Y! $``&8`
>> M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
>> M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
>> M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
>> F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
>> `
>> end
>|||Thank you once again, no the reports and the database are on the same
server and we are using forms based authentication, or i should say we
will be when i implement it.
one last question if you don't mind. i'm working some some complex
layouts that i have only been able to accomplish with sub reports, is
there a way to pass a parameter from a list object into a dataset?
hmm, i'm not happy with that question, let me give an example. we have
a root table, it has many tables that are related to it with a foreign
key. I have found that when trying to do it with a single report (a
single query with outer joins) that reporting services runs out of
memory when there are 20-30 tables all returning rows (and the unique
attribute set on the tables in reporting services), a way to get
around this has been to make sub reports that I pass the ID from the
root table to and have it render the page, but this is taking a Large
number of sub reports and i fear there is a lot of room for an error.
so instead of passing the ID to another report can it be passed to a
record set thats runs an additional query per ID?
On Sep 17, 2:02 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You are getting into a complicated environment. If your asp applications are
> on a different server than RS you are going to run into the double hop
> problem. That is a windows issue where someone who is recognized by your web
> app as one person then is not recognized when credentials hop from one
> server to another. This requires kerboros to work. I am just giving you a
> heads up. This is only an issue if you are using integrated security. If you
> are using forms based security (i.e. role your own credentials) then it is
> not an issue.
> A parameter can be hidden so if someone goes directly to report manager
> (portal for RS) they will not see the parameter. OR, you can also tie the
> report to the user. RS has a global variable called userid that gives the
> domain and userid of the user running the report. You could use this to only
> allow users to see the data they should.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <lance.sanc...@.gmail.com> wrote in message
> news:1190054179.058425.32890@.50g2000hsm.googlegroups.com...
> > Thank you, is there a way to tie it into the IIS session so someone
> > would generate an error if they tried to change the parameter to
> > something else?
> > On Sep 17, 11:29 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> Not automatically but you can pass a parameter that is used by your
> >> report
> >> to use the appropriate datasource.
> >> In RS 2005 you can use an expression for your data source. Which if you
> >> write your reports to expect a parameter (say DBParam) then you can do
> >> exactly what you want. From Books OnLine:
> >> Data Source Expressions
> >> You can put an expression into a connection string to allow users to
> >> select
> >> the data source at run time. For example, suppose a multinational firm
> >> has
> >> data servers in several countries. With an expression-based connection
> >> string, a user who is running a sales report can select a data source for
> >> a
> >> particular country before running the report.
> >> The following example illustrates the use of a data source expression in
> >> a
> >> SQL Server connection string. The example assumes you have created a
> >> report
> >> parameter named ServerName:
> >> Copy Code
> >> ="data source=" & Parameters!ServerName.Value & ";initial
> >> catalog=AdventureWorks
> >> Data source expressions are processed at run time or when a report is
> >> previewed. The expression must be written in Visual Basic. Use the
> >> following
> >> guidelines when defining a data source expression:
> >> a.. Design the report using a static connection string. A static
> >> connection string refers to a connection string that is not set through
> >> an
> >> expression (for example, when you follow the steps for creating a
> >> report-specific or shared data source, you are defining a static
> >> connection
> >> string). Using a static connection string allows you to connect to the
> >> data
> >> source in Report Designer so that you can get the query results you need
> >> to
> >> create the report.
> >> b.. When defining the data source connection, do not use a shared data
> >> source. You cannot use a data source expression in a shared data source.
> >> You
> >> must define a report-specific data source for the report.
> >> c.. Specify credentials separately from the connection string. You can
> >> use
> >> stored credentials, prompted credentials, or integrated security.
> >> d.. Add a report parameter to specify a data source. For parameter
> >> values,
> >> you can either provide a static list of available values (in this case,
> >> the
> >> available values should be data sources you can use with the report) or
> >> define a query that retrieves a list of data sources at run time.
> >> e.. Be sure that the list of data sources shares the same database
> >> schema.
> >> All report design begins with schema information. If there is a mismatch
> >> between the schema used to define the report and the actual schema used
> >> by
> >> the report at run time, the report might not run.
> >> f.. Before publishing the report, replace the static connection string
> >> with an expression. Wait until you are finished designing the report
> >> before
> >> you replace the static connection string with an expression. Once you use
> >> an
> >> expression, you cannot execute the query in Report Designer. Furthermore,
> >> the field list in the Datasets window and the Parameters list will not
> >> update automatically.
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> <lance.sanc...@.gmail.com> wrote in message
> >>news:1190052200.309137.245860@.d55g2000hsg.googlegroups.com...
> >> > Were working on a classic asp app and are trying to implement some
> >> > reports through reporting service. I have a few questions.
> >> > First the application were working on will have multiple instances, so
> >> > one a single server there can be upto 15 identical apps connecting to
> >> > different databases, is there a way to have reporting service limit
> >> > what data source reporting service can access depending on what IIS
> >> > website/application pool the browsers are accessing?
> >> > Badgers.servername.com/reportserver should open the reports with the
> >> > badgers db
> >> > Squirrels.servername.com/reportserver db=squirrels
> >> > Servername.com:54333 db=badgers
> >> > Servername.com:54334 db=squirrels
> >> begin 666 copycode.gif
> >> M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
> >> M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
> >> M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
> >> MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
> >> MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
> >> M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
> >> MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
> >> MT?_______P``````````````````````````````````````````````````
> >> M`````````````````````````````````````````````````"'Y! $``&8`
> >> M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
> >> M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
> >> M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
> >> F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
> >> `
> >> end|||never mind, i'm going through olderposts and I do belive someone else
was trying to do the same thing and you said data regions cannot do a
parent child relationship that I need.
On Sep 17, 3:22 pm, "lance.sanc...@.gmail.com"
<lance.sanc...@.gmail.com> wrote:
> Thank you once again, no the reports and the database are on the same
> server and we are using forms based authentication, or i should say we
> will be when i implement it.
> one last question if you don't mind. i'm working some some complex
> layouts that i have only been able to accomplish with sub reports, is
> there a way to pass a parameter from a list object into a dataset?
> hmm, i'm not happy with that question, let me give an example. we have
> a root table, it has many tables that are related to it with a foreign
> key. I have found that when trying to do it with a single report (a
> single query with outer joins) that reporting services runs out of
> memory when there are 20-30 tables all returning rows (and the unique
> attribute set on the tables in reporting services), a way to get
> around this has been to make sub reports that I pass the ID from the
> root table to and have it render the page, but this is taking a Large
> number of sub reports and i fear there is a lot of room for an error.
> so instead of passing the ID to another report can it be passed to a
> record set thats runs an additional query per ID?
> On Sep 17, 2:02 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
> > You are getting into a complicated environment. If your asp applications are
> > on a different server than RS you are going to run into the double hop
> > problem. That is a windows issue where someone who is recognized by your web
> > app as one person then is not recognized when credentials hop from one
> > server to another. This requires kerboros to work. I am just giving you a
> > heads up. This is only an issue if you are using integrated security. If you
> > are using forms based security (i.e. role your own credentials) then it is
> > not an issue.
> > A parameter can be hidden so if someone goes directly to report manager
> > (portal for RS) they will not see the parameter. OR, you can also tie the
> > report to the user. RS has a global variable called userid that gives the
> > domain and userid of the user running the report. You could use this to only
> > allow users to see the data they should.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > <lance.sanc...@.gmail.com> wrote in message
> >news:1190054179.058425.32890@.50g2000hsm.googlegroups.com...
> > > Thank you, is there a way to tie it into the IIS session so someone
> > > would generate an error if they tried to change the parameter to
> > > something else?
> > > On Sep 17, 11:29 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > > wrote:
> > >> Not automatically but you can pass a parameter that is used by your
> > >> report
> > >> to use the appropriate datasource.
> > >> In RS 2005 you can use an expression for your data source. Which if you
> > >> write your reports to expect a parameter (say DBParam) then you can do
> > >> exactly what you want. From Books OnLine:
> > >> Data Source Expressions
> > >> You can put an expression into a connection string to allow users to
> > >> select
> > >> the data source at run time. For example, suppose a multinational firm
> > >> has
> > >> data servers in several countries. With an expression-based connection
> > >> string, a user who is running a sales report can select a data source for
> > >> a
> > >> particular country before running the report.
> > >> The following example illustrates the use of a data source expression in
> > >> a
> > >> SQL Server connection string. The example assumes you have created a
> > >> report
> > >> parameter named ServerName:
> > >> Copy Code
> > >> ="data source=" & Parameters!ServerName.Value & ";initial
> > >> catalog=AdventureWorks
> > >> Data source expressions are processed at run time or when a report is
> > >> previewed. The expression must be written in Visual Basic. Use the
> > >> following
> > >> guidelines when defining a data source expression:
> > >> a.. Design the report using a static connection string. A static
> > >> connection string refers to a connection string that is not set through
> > >> an
> > >> expression (for example, when you follow the steps for creating a
> > >> report-specific or shared data source, you are defining a static
> > >> connection
> > >> string). Using a static connection string allows you to connect to the
> > >> data
> > >> source in Report Designer so that you can get the query results you need
> > >> to
> > >> create the report.
> > >> b.. When defining the data source connection, do not use a shared data
> > >> source. You cannot use a data source expression in a shared data source.
> > >> You
> > >> must define a report-specific data source for the report.
> > >> c.. Specify credentials separately from the connection string. You can
> > >> use
> > >> stored credentials, prompted credentials, or integrated security.
> > >> d.. Add a report parameter to specify a data source. For parameter
> > >> values,
> > >> you can either provide a static list of available values (in this case,
> > >> the
> > >> available values should be data sources you can use with the report) or
> > >> define a query that retrieves a list of data sources at run time.
> > >> e.. Be sure that the list of data sources shares the same database
> > >> schema.
> > >> All report design begins with schema information. If there is a mismatch
> > >> between the schema used to define the report and the actual schema used
> > >> by
> > >> the report at run time, the report might not run.
> > >> f.. Before publishing the report, replace the static connection string
> > >> with an expression. Wait until you are finished designing the report
> > >> before
> > >> you replace the static connection string with an expression. Once you use
> > >> an
> > >> expression, you cannot execute the query in Report Designer. Furthermore,
> > >> the field list in the Datasets window and the Parameters list will not
> > >> update automatically.
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >> <lance.sanc...@.gmail.com> wrote in message
> > >>news:1190052200.309137.245860@.d55g2000hsg.googlegroups.com...
> > >> > Were working on a classic asp app and are trying to implement some
> > >> > reports through reporting service. I have a few questions.
> > >> > First the application were working on will have multiple instances, so
> > >> > one a single server there can be upto 15 identical apps connecting to
> > >> > different databases, is there a way to have reporting service limit
> > >> > what data source reporting service can access depending on what IIS
> > >> > website/application pool the browsers are accessing?
> > >> > Badgers.servername.com/reportserver should open the reports with the
> > >> > badgers db
> > >> > Squirrels.servername.com/reportserver db=squirrels
> > >> > Servername.com:54333 db=badgers
> > >> > Servername.com:54334 db=squirrels
> > >> begin 666 copycode.gif
> > >> M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
> > >> M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
> > >> M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
> > >> MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
> > >> MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
> > >> M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
> > >> MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
> > >> MT?_______P``````````````````````````````````````````````````
> > >> M`````````````````````````````````````````````````"'Y! $``&8`
> > >> M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
> > >> M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
> > >> M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
> > >> F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
> > >> `
> > >> end|||you can use a DNS round robin.
setup your report data source like:
data source=MyServer.mydomain.com
in the dns server, setup 2 different IP addresses for MyServer.mydomain.com.
insure that the round robin option is setup in the DNS server.
now some accesses go to the first server and some others will go through
the second one.
you can also do this using the NLB feature (network load balancing) to
spread the traffic to multiple servers.
maybe these options will helps you.
<lance.sanchez@.gmail.com> wrote in message
news:1190052200.309137.245860@.d55g2000hsg.googlegroups.com...
> Were working on a classic asp app and are trying to implement some
> reports through reporting service. I have a few questions.
> First the application were working on will have multiple instances, so
> one a single server there can be upto 15 identical apps connecting to
> different databases, is there a way to have reporting service limit
> what data source reporting service can access depending on what IIS
> website/application pool the browsers are accessing?
> Badgers.servername.com/reportserver should open the reports with the
> badgers db
> Squirrels.servername.com/reportserver db=squirrels
> Servername.com:54333 db=badgers
> Servername.com:54334 db=squirrels
>|||Correct.
I really recommend that you stay away from your outer join technique. You
need to limit the amount of data that is rendered. Subreports is really the
way to go. Also, consider changing your design that you don't show all the
data at once and instead use drill through. I.e. you create a link, they
click on the link if they want additional data (just make the text of the
field to click on blue and underline, users are used to clicking on that
visual clue) they click on it. I think the idea of showing everything is a
holdover from older reporting solutions. RS is ideal for showing just the
data you need to see and then click on a link for additional data. It is
very easy to do (jump to report action) and very fast way of showing the
data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<lance.sanchez@.gmail.com> wrote in message
news:1190069078.994657.163370@.w3g2000hsg.googlegroups.com...
> never mind, i'm going through olderposts and I do belive someone else
> was trying to do the same thing and you said data regions cannot do a
> parent child relationship that I need.
> On Sep 17, 3:22 pm, "lance.sanc...@.gmail.com"
> <lance.sanc...@.gmail.com> wrote:
>> Thank you once again, no the reports and the database are on the same
>> server and we are using forms based authentication, or i should say we
>> will be when i implement it.
>> one last question if you don't mind. i'm working some some complex
>> layouts that i have only been able to accomplish with sub reports, is
>> there a way to pass a parameter from a list object into a dataset?
>> hmm, i'm not happy with that question, let me give an example. we have
>> a root table, it has many tables that are related to it with a foreign
>> key. I have found that when trying to do it with a single report (a
>> single query with outer joins) that reporting services runs out of
>> memory when there are 20-30 tables all returning rows (and the unique
>> attribute set on the tables in reporting services), a way to get
>> around this has been to make sub reports that I pass the ID from the
>> root table to and have it render the page, but this is taking a Large
>> number of sub reports and i fear there is a lot of room for an error.
>> so instead of passing the ID to another report can it be passed to a
>> record set thats runs an additional query per ID?
>> On Sep 17, 2:02 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
>> wrote:
>> > You are getting into a complicated environment. If your asp
>> > applications are
>> > on a different server than RS you are going to run into the double hop
>> > problem. That is a windows issue where someone who is recognized by
>> > your web
>> > app as one person then is not recognized when credentials hop from one
>> > server to another. This requires kerboros to work. I am just giving you
>> > a
>> > heads up. This is only an issue if you are using integrated security.
>> > If you
>> > are using forms based security (i.e. role your own credentials) then it
>> > is
>> > not an issue.
>> > A parameter can be hidden so if someone goes directly to report manager
>> > (portal for RS) they will not see the parameter. OR, you can also tie
>> > the
>> > report to the user. RS has a global variable called userid that gives
>> > the
>> > domain and userid of the user running the report. You could use this to
>> > only
>> > allow users to see the data they should.
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> > <lance.sanc...@.gmail.com> wrote in message
>> >news:1190054179.058425.32890@.50g2000hsm.googlegroups.com...
>> > > Thank you, is there a way to tie it into the IIS session so someone
>> > > would generate an error if they tried to change the parameter to
>> > > something else?
>> > > On Sep 17, 11:29 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
>> > > wrote:
>> > >> Not automatically but you can pass a parameter that is used by your
>> > >> report
>> > >> to use the appropriate datasource.
>> > >> In RS 2005 you can use an expression for your data source. Which if
>> > >> you
>> > >> write your reports to expect a parameter (say DBParam) then you can
>> > >> do
>> > >> exactly what you want. From Books OnLine:
>> > >> Data Source Expressions
>> > >> You can put an expression into a connection string to allow users to
>> > >> select
>> > >> the data source at run time. For example, suppose a multinational
>> > >> firm
>> > >> has
>> > >> data servers in several countries. With an expression-based
>> > >> connection
>> > >> string, a user who is running a sales report can select a data
>> > >> source for
>> > >> a
>> > >> particular country before running the report.
>> > >> The following example illustrates the use of a data source
>> > >> expression in
>> > >> a
>> > >> SQL Server connection string. The example assumes you have created a
>> > >> report
>> > >> parameter named ServerName:
>> > >> Copy Code
>> > >> ="data source=" & Parameters!ServerName.Value & ";initial
>> > >> catalog=AdventureWorks
>> > >> Data source expressions are processed at run time or when a report
>> > >> is
>> > >> previewed. The expression must be written in Visual Basic. Use the
>> > >> following
>> > >> guidelines when defining a data source expression:
>> > >> a.. Design the report using a static connection string. A static
>> > >> connection string refers to a connection string that is not set
>> > >> through
>> > >> an
>> > >> expression (for example, when you follow the steps for creating a
>> > >> report-specific or shared data source, you are defining a static
>> > >> connection
>> > >> string). Using a static connection string allows you to connect to
>> > >> the
>> > >> data
>> > >> source in Report Designer so that you can get the query results you
>> > >> need
>> > >> to
>> > >> create the report.
>> > >> b.. When defining the data source connection, do not use a shared
>> > >> data
>> > >> source. You cannot use a data source expression in a shared data
>> > >> source.
>> > >> You
>> > >> must define a report-specific data source for the report.
>> > >> c.. Specify credentials separately from the connection string. You
>> > >> can
>> > >> use
>> > >> stored credentials, prompted credentials, or integrated security.
>> > >> d.. Add a report parameter to specify a data source. For parameter
>> > >> values,
>> > >> you can either provide a static list of available values (in this
>> > >> case,
>> > >> the
>> > >> available values should be data sources you can use with the report)
>> > >> or
>> > >> define a query that retrieves a list of data sources at run time.
>> > >> e.. Be sure that the list of data sources shares the same database
>> > >> schema.
>> > >> All report design begins with schema information. If there is a
>> > >> mismatch
>> > >> between the schema used to define the report and the actual schema
>> > >> used
>> > >> by
>> > >> the report at run time, the report might not run.
>> > >> f.. Before publishing the report, replace the static connection
>> > >> string
>> > >> with an expression. Wait until you are finished designing the report
>> > >> before
>> > >> you replace the static connection string with an expression. Once
>> > >> you use
>> > >> an
>> > >> expression, you cannot execute the query in Report Designer.
>> > >> Furthermore,
>> > >> the field list in the Datasets window and the Parameters list will
>> > >> not
>> > >> update automatically.
>> > >> --
>> > >> Bruce Loehle-Conger
>> > >> MVP SQL Server Reporting Services
>> > >> <lance.sanc...@.gmail.com> wrote in message
>> > >>news:1190052200.309137.245860@.d55g2000hsg.googlegroups.com...
>> > >> > Were working on a classic asp app and are trying to implement some
>> > >> > reports through reporting service. I have a few questions.
>> > >> > First the application were working on will have multiple
>> > >> > instances, so
>> > >> > one a single server there can be upto 15 identical apps connecting
>> > >> > to
>> > >> > different databases, is there a way to have reporting service
>> > >> > limit
>> > >> > what data source reporting service can access depending on what
>> > >> > IIS
>> > >> > website/application pool the browsers are accessing?
>> > >> > Badgers.servername.com/reportserver should open the reports with
>> > >> > the
>> > >> > badgers db
>> > >> > Squirrels.servername.com/reportserver db=squirrels
>> > >> > Servername.com:54333 db=badgers
>> > >> > Servername.com:54334 db=squirrels
>> > >> begin 666 copycode.gif
>> > >> M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S]
>> > >> M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y
>> > >> M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY
>> > >> MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-'ZINOS)"JX+K:_^KO^R5,FR=0HIJN
>> > >> MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T
>> > >> M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q
>> > >> MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&#
>> > >> MT?_______P``````````````````````````````````````````````````
>> > >> M`````````````````````````````````````````````````"'Y! $``&8`
>> > >> M+ `````/``\```>?@.&:"@.X2%AH>"`0$$! \>*X@.!99-E558+AR64"CA"89B%
>> > >> M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4
>> > >> M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB
>> > >> F3@.E$AA!EVKDC<R"$(2X2-L1@.D> `E"6&DB! 8*/&!PM3, 0"`#L`
>> > >> `
>> > >> end
>