I have been working on this for over a month and have seen many
requests for help on this. Classic asp does not support SOAP. The soap
toolkit 3.0 keep passing invalid parameters to RS2k5. The best way I
have found to access some aspects of reporting service without
exposing reporting services to the outside world is to make a classic
asp page that proxies url access
I had to ditch soap and do something else.
Here is my setup
Win 2k3, IIS has multiple websites setup, the default website is set
to listen on 127.0.0.1 and has reporting service setup on it,
reporting service is set to anonymous access and the guest account has
full control.
All I need reporting services for is to format reports for excel or
reports that are going to paper.
Report_pdf.asp
Response.Clear
Response.ContentType = "application/pdf"
set xmlhttp = server.CreateObject("Microsoft.XMLHTTP")
xmlhttp.open "GET", "http://localhost/reportserver?%2fReport
%2fSummaryReport&rs:Command=Render&rs:Format=PDF&rc:Toolbar=false",
false
if xmlhttp.Status = 200 then
Response.BinaryWrite xmlhttp.responseBody
Else
Response.Write("Didn't Work")
End ifOn Dec 7, 1:27 pm, "lance.sanc...@.gmail.com" <lance.sanc...@.gmail.com>
wrote:
> I have been working on this for over a month and have seen many
> requests for help on this. Classic asp does not support SOAP. The soap
> toolkit 3.0 keep passing invalid parameters to RS2k5. The best way I
> have found to access some aspects of reporting service without
> exposing reporting services to the outside world is to make a classic
> asp page that proxies url access
> I had to ditch soap and do something else.
> Here is my setup
> Win 2k3, IIS has multiple websites setup, the default website is set
> to listen on 127.0.0.1 and has reporting service setup on it,
> reporting service is set to anonymous access and the guest account has
> full control.
> All I need reporting services for is to format reports for excel or
> reports that are going to paper.
> Report_pdf.asp
> Response.Clear
> Response.ContentType = "application/pdf"
> set xmlhttp = server.CreateObject("Microsoft.XMLHTTP")
> xmlhttp.open "GET", "http://localhost/reportserver?%2fReport
> %2fSummaryReport&rs:Command=Render&rs:Format=PDF&rc:Toolbar=false",
> false
> if xmlhttp.Status = 200 then
> Response.BinaryWrite xmlhttp.responseBody
> Else
> Response.Write("Didn't Work")
> End if
The guest acct having full control could be a problem for you.
Have you considered using a standard HTML tag to provide this
functionality? Something like:
<a href="http://links.10026.com/?link=http://localhost/reportserver?%2fReport
%2fSummaryReport&rs:Command=Render&rs:Format=PDF">Export Summary
Report To PDF</a>
Also, in case you are unaware, SSRS doesn't play very nicely outside
the domain, so this could be the issue. Generally, outside the domain,
I tend to pass the report export file itself. Of course, normally, to
do this, using the Reporting Services Web Service is required. If
another language (OS) is an option for you, you might try integrating
a PHP Application with SOAP access for the RS Web Service into the
Classic ASP application. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I had originally looked at just exposing the URL access to the outside
world but since I'm not allowed to develop a .net auth extension here
that wasn't really an option. By setting the guest account access up
the way I did (ie by having reporting service only listen to local
host). The proxy page is the only thing that can access it. But if in
the application I had a reference to local host wouldn't the clients
browser assume that was supposed to be available on their localhost?
On Dec 8, 11:03 am, EMartinez <emartinez...@.gmail.com> wrote:
> On Dec 7, 1:27 pm, "lance.sanc...@.gmail.com" <lance.sanc...@.gmail.com>
> wrote:
>
> > I have been working on this for over a month and have seen many
> > requests for help on this. Classic asp does not support SOAP. The soap
> > toolkit 3.0 keep passing invalid parameters to RS2k5. The best way I
> > have found to access some aspects of reporting service without
> > exposing reporting services to the outside world is to make a classic
> > asp page that proxies url access
> > I had to ditch soap and do something else.
> > Here is my setup
> > Win 2k3, IIS has multiple websites setup, the default website is set
> > to listen on 127.0.0.1 and has reporting service setup on it,
> > reporting service is set to anonymous access and the guest account has
> > full control.
> > All I need reporting services for is to format reports for excel or
> > reports that are going to paper.
> > Report_pdf.asp
> > Response.Clear
> > Response.ContentType = "application/pdf"
> > set xmlhttp = server.CreateObject("Microsoft.XMLHTTP")
> > xmlhttp.open "GET", "http://localhost/reportserver?%2fReport
> > %2fSummaryReport&rs:Command=Render&rs:Format=PDF&rc:Toolbar=false",
> > false
> > if xmlhttp.Status = 200 then
> > Response.BinaryWrite xmlhttp.responseBody
> > Else
> > Response.Write("Didn't Work")
> > End if
> The guest acct having full control could be a problem for you.
> Have you considered using a standard HTML tag to provide this
> functionality? Something like:
> <a href="http://links.10026.com/?link=http://localhost/reportserver?%2fReport
> %2fSummaryReport&rs:Command=Render&rs:Format=PDF">Export Summary
> Report To PDF</a>
> Also, in case you are unaware, SSRS doesn't play very nicely outside
> the domain, so this could be the issue. Generally, outside the domain,
> I tend to pass the report export file itself. Of course, normally, to
> do this, using the Reporting Services Web Service is required. If
> another language (OS) is an option for you, you might try integrating
> a PHP Application with SOAP access for the RS Web Service into the
> Classic ASP application. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||I posted last month and before about the lack of examples of using Classic
ASP and RS2005. I simply wanted to create and then export a report as a PDF.
After much research and testing, I came up with ASP code that communicates
with RS using SOAP calls and successfully loads, renders, and exports RS
reports. So, Classic ASP *does* support SOAP (as does any language) - you
just have to handroll the entire thing and understand XML and the
undocumented quirks and flow in RS. Also, in order for this all to work, it
takes custom and non-standard configuration of RS, IIS, SQL Server, etc. to
make it all happen (I had to write it all down because I couldn't
consistently remember all the steps).
Here's my test, non-production, code that will help you understand the steps
of sending SOAP messages and interacting with RS with ASP 3.0. There may be
more elegant ways to do this, but it works.
The hardest part is setting up permissions, users, and such in RS, SQL, and
IIS which was a lot of trial and error and experiments dealing with
"permission denied" stuff.
<%
Dim strFormat, strCustID, strReport
'test parameters
strFormat = "PDF"
strCustID= "5810"
strReport = "record"
Dim errorMsg
errorMsg = "Node Select Error"
Dim strPrefix
Dim strReportPath 'as String
Select Case strReport
Case "record"
strReportPath = "/TestFolder/TestReport"
strPrefix = "test-"
Case "summary"
strReportPath = "/TestFolder/TestReport2"
strPrefix = "test2-"
End Select
Dim strContentType 'as String
Dim strSuffix
Select Case strFormat
Case "HTML4.0"
strContentType = "text/html"
strSuffix = ".htm"
Case "PDF"
strContentType = "application/pdf"
strSuffix = ".pdf"
Case "IMAGE"
strContentType = "image/tiff"
strSuffix = ".tif"
End Select
'the web service
Dim postURL 'as String
postURL = "http://mymachine/ReportServer/ReportExecution2005.asmx"
'the Namespace for Reporting Services
Dim rsns 'as String
rsns ="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"
'==================================================================================== Function ByteArray2Text(varByteArray)
Dim strBuffer, lngCounter
strData = ""
strBuffer = ""
For lngCounter = 0 to UBound(varByteArray)
strBuffer = strBuffer & Chr(255 And
Ascb(Midb(varByteArray,lngCounter + 1, 1)))
'Keep strBuffer at 1k bytes maximum
If lngCounter Mod 1000 = 0 Then
strData = strData & strBuffer
strBuffer = ""
End If
Next
ByteArray2Text = strData & strBuffer
End Function
Sub writeError(responseBody)
Dim errorStr
errorStr = getNodeText(responseBody, "//ErrorCode") & ": " &
getNodeText(responseBody, "//Message") & _
"<br /><br />" & ByteArray2Text(responseBody)
Err.Raise 1, "Reporting Services-SOAP", errorStr
End Sub
Sub writeByteArray(responseBody)
Response.Clear
Response.ContentType = strContentType
if strContentType <> "text/html" then
Response.AddHeader "Content-Disposition", "attachment;
filename=" & strPrefix & strPtID & strSuffix
end if
Response.BinaryWrite getResultAsByteArray(responseBody)
End Sub
Function createExecutionHeader(theExecutionID)
If len(theExecutionID) > 0 then
createExecutionHeader = _
"<soap:Header>" & _
"<ExecutionHeader xmlns=""" & rsns & """>" & _
"<ExecutionID>" & theExecutionID & "</ExecutionID>"
& _
"</ExecutionHeader>" & _
"</soap:Header>"
Else
createExecutionHeader = ""
End If
End Function
Function makeSOAPPackage(theBody, theExecutionID)
makeSOAPPackage = _
"<?xml version=""1.0"" encoding=""utf-8""?>" & _
"<soap:Envelope
xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/""" & _
" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""" &
_
" xmlns:xsd="""">http://www.w3.org/2001/XMLSchema"">" & _
createExecutionHeader(theExecutionID) & _
"<soap:Body>" & theBody & "</soap:Body>" & _
"</soap:Envelope>"
End Function
Function getNodeObject(theResponse, xpathToNode)
set xmldoc = Server.CreateObject("MSXML.DOMDocument")
xmldoc.load(theResponse)
set getNodeObject =xmldoc.documentElement.selectSingleNode(xpathToNode)
set xmldoc = nothing
End Function
Function getNodeText(theResponse, xpath)
set node = getNodeObject(theResponse, xpath)
if NOT (node is nothing) then
getNodeText = node.text
set node = nothing
else
getNodeText = errorMsg
end if
End Function
Function getResultAsByteArray(theResponse)
set node = getNodeObject(theResponse, "//Result")
node.dataType = "bin.base64"
getResultAsByteArray = node.nodeTypedValue
set node = nothing
End Function
Function sendSOAPRequest(theBody, theMethod, theExecutionID, hasError)
Dim xmlobject
set xmlobject = Server.CreateObject("MSXML2.ServerXMLHTTP")
With xmlobject
.Open "POST", postURL, False
.setRequestHeader "SOAPAction", rsns & "/" & theMethod
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.Send makeSOAPPackage(theBody, theExecutionID)
End With
hasError = getNodeText(xmlobject.responseBody, "//" & theMethod &
"Response")
sendSOAPRequest = xmlobject.responseBody
set xmlobject = nothing
End Function
'====================================================================================
Dim strExecutionID, strErrorMessage
strExecutionID = "" 'no execution ID for the first method
Dim responseBody 'as String
strRequest = "<LoadReport xmlns=""" & rsns & """>" & _
"<Report>" & strReportPath & "</Report>" & _
"</LoadReport>"
responseBody = sendSOAPRequest(strRequest, "LoadReport", strExecutionID,
strErrorMessage)
if strErrorMessage <> errorMsg then
strExecutionID = getNodeText(responseBody,
"//executionInfo/ExecutionID")
strRequest = "<SetExecutionParameters xmlns=""" & rsns & """>" & _
"<Parameters>" & _
"<ParameterValue>" & _
"<Name>ptid</Name>" & _
"<Value>" & strCustID & "</Value>" & _
"</ParameterValue>" & _
"</Parameters>" & _
"<ParameterLanguage>en-us</ParameterLanguage>" & _
"</SetExecutionParameters>"
responseBody = sendSOAPRequest(strRequest, "SetExecutionParameters",
strExecutionID, strErrorMessage)
if strErrorMessage <> errorMsg then
strRequest = _
"<Render xmlns=""" & rsns & """>" & _
"<Format>" & strFormat & "</Format>" & _
"</Render>"
responseBody = sendSOAPRequest(strRequest, "Render",
strExecutionID, strErrorMessage)
if strErrorMessage <> errorMsg then
writeByteArray responseBody
else
writeError responseBody
end if
else
writeError responseBody
end if
else
writeError responseBody
end if
%>
<lance.sanchez@.gmail.com> wrote in message
news:b51f3ed1-5500-4cac-a79f-4dffa732ad4f@.a39g2000pre.googlegroups.com...
>I have been working on this for over a month and have seen many
> requests for help on this. Classic asp does not support SOAP. The soap
> toolkit 3.0 keep passing invalid parameters to RS2k5. The best way I
> have found to access some aspects of reporting service without
> exposing reporting services to the outside world is to make a classic
> asp page that proxies url access
> I had to ditch soap and do something else.
> Here is my setup
> Win 2k3, IIS has multiple websites setup, the default website is set
> to listen on 127.0.0.1 and has reporting service setup on it,
> reporting service is set to anonymous access and the guest account has
> full control.
> All I need reporting services for is to format reports for excel or
> reports that are going to paper.
> Report_pdf.asp
> Response.Clear
> Response.ContentType = "application/pdf"
> set xmlhttp = server.CreateObject("Microsoft.XMLHTTP")
> xmlhttp.open "GET", "http://localhost/reportserver?%2fReport
> %2fSummaryReport&rs:Command=Render&rs:Format=PDF&rc:Toolbar=false",
> false
> if xmlhttp.Status = 200 then
> Response.BinaryWrite xmlhttp.responseBody
> Else
> Response.Write("Didn't Work")
> End if
Showing posts with label classic. Show all posts
Showing posts with label classic. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
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
>
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
>
Subscribe to:
Posts (Atom)