Can I change a datasource's properties at report execution time?

I will be running reports on a shared database server. The database server
supports one main application with many environments for different user
groups. Each groupsâ' data is kept in a different database instance.
I want to deploy my reports in a single location on the shared database
server to simplify installing reporting services and deploying reports.
When a user requests a on-demand report I want the report to pull the data
from the correct SQL Server instance.
Can I to change the reportâ's shared datasource â'Connection Stringâ' to the
userâ's database instance. I will be using SOAP to invoke the reports.
Are there other ways to accomplish my intentions?
I am using SQL Server 2000 Reporting Services Developer Edition with SP1 and
SQL Server 2000 Standard Edition with service packs 1, 2, 3, and 3a applied.Hi,
You may want to use 'SetReportDataSources' to switch between datasources.
More information about this method can be found from the MSDN site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
rsp_prog_soapapi_intro_0dnq.asp
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Can I change a datasource's properties at report execution
time?
>thread-index: AcUz8ztCgxbpG2rJQdCyH/tlSmdIXw==>X-WBNR-Posting-Host: 192.85.47.1
>From: =?Utf-8?B?dGV4YXMzNDYw?= <texas3460@.noemail.nospam>
>Subject: Can I change a datasource's properties at report execution time?
>Date: Mon, 28 Mar 2005 16:07:01 -0800
>Lines: 17
>Message-ID: <26693252-7562-4848-9FD1-9D529F3D40F9@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 8bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:46323
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I will be running reports on a shared database server. The database
server
>supports one main application with many environments for different user
>groups. Each groupsâ' data is kept in a different database instance.
>I want to deploy my reports in a single location on the shared database
>server to simplify installing reporting services and deploying reports.
>When a user requests a on-demand report I want the report to pull the data
>from the correct SQL Server instance.
>Can I to change the reportâ's shared datasource â'Connection Stringâ'
to the
>userâ's database instance. I will be using SOAP to invoke the reports.
>Are there other ways to accomplish my intentions?
>I am using SQL Server 2000 Reporting Services Developer Edition with SP1
and
>SQL Server 2000 Standard Edition with service packs 1, 2, 3, and 3a
applied.
>

Can I access my data source in Custom Report Item during design mode?

Hi,

I'm developing a Custom Report Item and would like to fill my properties dialog in design mode with real data. I have the name of the data source and the command text for the corresponding report dataset, read from the report design.

Is it possible to create an ado.net dataset so that I can display and use real data in my properties dialog box? First problem I have is to get the connection string from the data source string. I found no docs for that, especially the Microsoft.ReportingServices.DataExtensions.ReportDataSource class is not documented. Am I on the right way with that?

Thomas

Check out.. http://msdn.microsoft.com/msdnmag/issues/06/10/SQLServer2005/default.aspx

|||

Sorry,

I can't see where that relates to my question. Did I miss the point?

After rereading the article, I didn't find any word on how to get actual data values in design mode.

Maybe I didn't state clear enough what I want to obtain:

I would like to get actual data values from the dataset in design mode in order to use them for a properties dialog. I have access to the sql statement from reportDataSet.CommandText already and I know the datasource name (that somehow is an alias for the db connection). What's missing is how to get the connection string from the datasource - then I can open the connection and read the data using ado.net.

Thomas

|||

For connection string:

check the DataSourceDefinition class in Reporting Service web service

Can I access my data source in Custom Report Item during design mode?

Hi,

I'm developing a Custom Report Item and would like to fill my properties dialog in design mode with real data. I have the name of the data source and the command text for the corresponding report dataset, read from the report design.

Is it possible to create an ado.net dataset so that I can display and use real data in my properties dialog box? First problem I have is to get the connection string from the data source string. I found no docs for that, especially the Microsoft.ReportingServices.DataExtensions.ReportDataSource class is not documented. Am I on the right way with that?

Thomas

Check out.. http://msdn.microsoft.com/msdnmag/issues/06/10/SQLServer2005/default.aspx

|||

Sorry,

I can't see where that relates to my question. Did I miss the point?

After rereading the article, I didn't find any word on how to get actual data values in design mode.

Maybe I didn't state clear enough what I want to obtain:

I would like to get actual data values from the dataset in design mode in order to use them for a properties dialog. I have access to the sql statement from reportDataSet.CommandText already and I know the datasource name (that somehow is an alias for the db connection). What's missing is how to get the connection string from the datasource - then I can open the connection and read the data using ado.net.

Thomas

|||

For connection string:

check the DataSourceDefinition class in Reporting Service web service

|||Did you ever make any progress on this? I'm trying to get to the Connection String property of the data source from my Custom Report Item code.

Any direction is appreciated.

Thanks

J

Can I access data source properties?

Can I, in an expression, access components of a shared data source? I'd like
to be able to get the DSN . . .
Alternatively, is there a way to dynamically set the database schema portion
of a query? I have been able to replace it with a REPORT parameter. Is
there a way to set some sort of a PROJECT level parameter?Yes you can. Go to the dataset properties and choose not to use a shared
datasource. You can then choose the database driver and then set the
connection string. This connection string can be an expression you can put
whatever you like in there. I used custom code which accesses an XML file
which contained all the different database connections for each company.
Each report then uses this XML file to set its connection string, hence
being a Project level setting.
Craig
"LoveBoxers" <LoveBoxers@.discussions.microsoft.com> wrote in message
news:DB650136-EF8D-42BB-8525-F9BD794F7BDD@.microsoft.com...
> Can I, in an expression, access components of a shared data source? I'd
> like
> to be able to get the DSN . . .
> Alternatively, is there a way to dynamically set the database schema
> portion
> of a query? I have been able to replace it with a REPORT parameter. Is
> there a way to set some sort of a PROJECT level parameter?|||This is good news! Would it be possible to send me an example of the
expression in the connection string and what the XML file needs to contain?
Does the XML file become part of the project/solution? When the report(s) is
"deployed", how does the XML file become a part of the package?
Thanks for your help!
"Craig" wrote:
> Yes you can. Go to the dataset properties and choose not to use a shared
> datasource. You can then choose the database driver and then set the
> connection string. This connection string can be an expression you can put
> whatever you like in there. I used custom code which accesses an XML file
> which contained all the different database connections for each company.
> Each report then uses this XML file to set its connection string, hence
> being a Project level setting.
> Craig
> "LoveBoxers" <LoveBoxers@.discussions.microsoft.com> wrote in message
> news:DB650136-EF8D-42BB-8525-F9BD794F7BDD@.microsoft.com...
> > Can I, in an expression, access components of a shared data source? I'd
> > like
> > to be able to get the DSN . . .
> >
> > Alternatively, is there a way to dynamically set the database schema
> > portion
> > of a query? I have been able to replace it with a REPORT parameter. Is
> > there a way to set some sort of a PROJECT level parameter?
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00B3_01C649AF.F31964F0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
OK example as below:
Here is the connection string: (I am using an Oracle Database and =define it by specifying a company code and an environment)
=3DCode.m_RR.GetConnectionString(Parameters!COMPANY_CODE.Value,Parameters=!ENVIRONMENT.Value)
m_RR is the instance name for my custom code I have attached to the =report (via Report References)
The XML excerpt then looks something like this (note these connections =are Oracle by you can put anything in the connection string):
<Connections default=3D"Data Source=3Dabc;Unicode=3DTrue">
<Environment name=3D"DEV" default=3D"Data Source=3Dabc;Unicode=3DTrue">
<Company code=3D"351" connection=3D"Data Source=3Dabc;Unicode=3DTrue"/>
</Environment>
<Environment name=3D"TEST" default=3D"Data Source=3Dabc;Unicode=3DTrue">
<Company code=3D"171" connection=3D"Data Source=3Ddef;Unicode=3DTrue"/>
<Company code=3D"201" connection=3D"Data Source=3Dghi;Unicode=3DTrue"/>
<Company code=3D"181" connection=3D"Data Source=3Djkl;Unicode=3DTrue"/>
<Company code=3D"311" connection=3D"Data Source=3Dmno;Unicode=3DTrue"/>
<Company code=3D"211" connection=3D"Data Source=3Dpqr;Unicode=3DTrue"/>
<Company code=3D"216" connection=3D"Data Source=3Dstu;Unicode=3DTrue"/>
</Environment>
<Environment name=3D"PROD" default=3D"">
</Environment>
</Connections>
So you can then write any C# code you like to access the XML and read =the connection strings:
as (Sorry about the formatting)...
public string GetConnectionString(string sCompanyCode, string =sEnvironment)
{
string sTempConnection;
if (m_Connection !=3D "") return m_Connection;
try
{
FileIOPermission perm =3D new =FileIOPermission(System.Security.Permissions.PermissionState.Unrestricted=);
perm.Assert();
}
catch
{
//Assertion failed. No use continuing, just return default.
return "Data Source=3Dabc;Unicode=3DTrue";
}
if (m_Settings =3D=3D null)
{
m_Settings =3D new XmlDocument();
try
{
string path =3D Environment.CurrentDirectory + "\\Settings.xml";
m_Settings.Load(path);
}
catch
{
//Failed to find xml document or something is wrong with it.
//Set the default connection
m_Connection =3D "Data Source=3Dabc;Unicode=3DTrue";
m_Settings =3D null;
return m_Connection;
}
}
XmlNodeList oConnections =3D =m_Settings.GetElementsByTagName("Connections");
if (oConnections !=3D null)
{
sTempConnection =3D m_Connection;
XmlNode oConnection =3D oConnections[0];
try
{
m_Connection =3D oConnection.Attributes["default"].Value;
}
catch
{
//No default attribute found
m_Connection =3D sTempConnection;
}
if (oConnection.ChildNodes !=3D null && oConnection.ChildNodes.Count > =0)
{
foreach (XmlElement oEnvironment in oConnection.ChildNodes)
{
if (oEnvironment.Name !=3D "Environment") continue;
sTempConnection =3D m_Connection;
try
{
if (oEnvironment.Attributes["name"].Value =3D=3D sEnvironment)
{
sTempConnection =3D m_Connection;
try
{
m_Connection =3D oEnvironment.Attributes["default"].Value;
}
catch
{
//No default attribute found
m_Connection =3D sTempConnection;
}
if (oEnvironment.ChildNodes !=3D null && oEnvironment.ChildNodes.Count > =0)
{
foreach (XmlElement oCompany in oEnvironment.ChildNodes)
{
if (oCompany.Name !=3D "Company") continue;
sTempConnection =3D m_Connection;
try
{
if (oCompany.Attributes["code"].Value =3D=3D sCompanyCode)
{
m_Connection =3D oCompany.Attributes["connection"].Value;
}
}
catch
{
//Do nothing just continue on to next record
m_Connection =3D sTempConnection;
}
}
}
}
}
catch
{
//Do nothing just continue on to next record
m_Connection =3D sTempConnection;
}
}
}
}
else
{
//No environments setup so set default
m_Connection =3D "Data Source=3Dabc;Unicode=3DTrue";
}
return m_Connection;
}
I placed a post build event on the project so that it copies the XML =document in to the previewer directory of SSRS. Also note that when you =deploy this to production you will also need to copy the XML file in to =the running directory of SSRS.
Hope this helps,
Craig
"LoveBoxers" <LoveBoxers@.discussions.microsoft.com> wrote in message =news:5805CDA2-C5E8-4D70-BDBD-9F1F0DEA3948@.microsoft.com...
> This is good news! Would it be possible to send me an example of the > expression in the connection string and what the XML file needs to =contain? > Does the XML file become part of the project/solution? When the =report(s) is > "deployed", how does the XML file become a part of the package?
> > Thanks for your help!
> > "Craig" wrote:
> >> Yes you can. Go to the dataset properties and choose not to use a =shared >> datasource. You can then choose the database driver and then set the =
>> connection string. This connection string can be an expression you =can put >> whatever you like in there. I used custom code which accesses an XML =file >> which contained all the different database connections for each =company. >> Each report then uses this XML file to set its connection string, =hence >> being a Project level setting.
>> >> Craig
>> >> "LoveBoxers" <LoveBoxers@.discussions.microsoft.com> wrote in message >> news:DB650136-EF8D-42BB-8525-F9BD794F7BDD@.microsoft.com...
>> > Can I, in an expression, access components of a shared data source? = I'd >> > like
>> > to be able to get the DSN . . .
>> >
>> > Alternatively, is there a way to dynamically set the database =schema >> > portion
>> > of a query? I have been able to replace it with a REPORT =parameter. Is
>> > there a way to set some sort of a PROJECT level parameter? >> >> --=_NextPart_000_00B3_01C649AF.F31964F0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

OK example as below:
Here is the connection string: (I =am using an Oracle Database and define it by specifying a company code and an environment)
=3DCode.m_RR.GetConnectionString(Parameters!COMPANY_CODE.Value,P=arameters!ENVIRONMENT.Value)
m_RR is the instance name for my custom =code I have attached to the report (via Report References)
The XML excerpt then looks something =like this (note these connections are Oracle by you can put anything in the =connection string):
So you can then write any C# code you =like to access the XML and read the connection strings:
as (Sorry about the =formatting)...
public =string =GetConnectionString(string sCompanyCode, =string =sEnvironment)
{
string sTempConnection;
if =(m_Connection !=3D "") =return =m_Connection;
try
{
FileIOPermission perm =3D new =FileIOPermission(System.Security.Permissions.PermissionState.Unrestricted);
perm.Assert();
}
catch
{
//Assertion failed. No use =continuing, just return default.
return ="Data Source=3Dabc;Unicode=3DTrue";
}
if =(m_Settings =3D=3D null)
{
m_Settings =3D new XmlDocument();
try
{
string =path =3D Environment.CurrentDirectory + "\\Settings.xml";
m_Settings.Load(path);
}
catch
{
//Failed to find xml document =or something is wrong with it.
//Set the default connection
m_Connection =3D "Data Source=3Dabc;Unicode=3DTrue";
m_Settings =3D null;
return m_Connection;
}
}
XmlNodeList oConnections =3D m_Settings.GetElementsByTagName("Connections");
if =(oConnections !=3D null)
{
sTempConnection =3D m_Connection;
XmlNode =oConnection =3D oConnections[0];
try
{
m_Connection =3D oConnection.Attributes["default"].Value;
}
catch
{
//No default attribute found
m_Connection =3D sTempConnection;
}
if (oConnection.ChildNodes !=3D null && oConnection.ChildNodes.Count > 0)
{
foreach =(XmlElement oEnvironment =in =oConnection.ChildNodes)
{
if =(oEnvironment.Name !=3D "Environment") continue;
sTempConnection =3D m_Connection;
try
{
if (oEnvironment.Attributes["name"].Value =3D=3D sEnvironment)
{
sTempConnection =3D m_Connection;
try
{
m_Connection =3D oEnvironment.Attributes["default"].Value;
}
catch
{
//No default attribute found
m_Connection =3D sTempConnection;
}
if (oEnvironment.ChildNodes !=3D null && oEnvironment.ChildNodes.Count > 0)
{
foreach =(XmlElement oCompany =in =oEnvironment.ChildNodes)
{
if =(oCompany.Name !=3D "Company") =continue;
sTempConnection =3D m_Connection;
try
{
if (oCompany.Attributes["code"].Value =3D=3D sCompanyCode)
{
m_Connection =3D oCompany.Attributes["connection"].Value;
}
}
catch
{
//Do nothing just continue on =to next record
m_Connection =3D sTempConnection;
}
}
}
}
}
catch
{
//Do nothing just continue on =to next record
m_Connection =3D sTempConnection;
}
}
}
}
else
{
//No environments setup so =set default
m_Connection =3D "Data Source=3Dabc;Unicode=3DTrue";
}
return m_Connection;
}
I placed a post build event on the =project so that it copies the XML document in to the previewer directory of SSRS. =Also note that when you deploy this to production you will also need to copy the =XML file in to the running directory of SSRS.
Hope this helps,
Craig
"LoveBoxers" wrote in message news:5805CDA2-C5E8-4D70-BDBD-9F1F0DEA3948@.microsoft.com...> =This is good news! Would it be possible to send me an example of the > expression in the connection string and what the XML file needs to contain? > Does the XML file become part of the project/solution? When the report(s) is > "deployed", how =does the XML file become a part of the package?> > Thanks for your help!> > "Craig" wrote:> > Yes you =can. Go to the dataset properties and choose not to use a shared > datasource. You can then choose the database driver and then set =the > connection string. This connection string can be an expression you can put > whatever you like in there. I =used custom code which accesses an XML file > which contained all =the different database connections for each company. > Each =report then uses this XML file to set its connection string, hence > =being a Project level setting.> > Craig> => "LoveBoxers" wrote in message > news:DB650136-EF8D-42BB-8525-F9BD794F7BDD@.microsoft.com...> > Can I, in an expression, =access components of a shared data source? I'd > > =like> > to be able to get the DSN . . .> >> > Alternatively, is there a way to dynamically set the database schema > > portion> > of a query? I have been =able to replace it with a REPORT parameter. Is> > there a =way to set some sort of a PROJECT level parameter? > > >

---

--=_NextPart_000_00B3_01C649AF.F31964F0--

Can anyone really disable caching?

For report properties, I am in 'Execution' and the settings is at 'Do not
cache'. I'd expect this to totally stop caching!
What I'm looking for is the report to pull back fresh data every time it's
requested, NOT when a parameter has to be changed first.
I saw a solution where developers where putting hidden fields on their
reports where these are populated by data that will change every time a
report is requested. If this is the only solution then I understand
completely why this has to happen, but it's still completely hideous. Any
ideas?If the report is set to live you should always get back fresh data, unless
you are doing drill downs. Are you saying if you open two browsers and
browse at separate times they will get cached data?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt Swift" <MattSwift@.discussions.microsoft.com> wrote in message
news:7E9D7F4B-503D-4056-B2D2-B9D238AD7910@.microsoft.com...
> For report properties, I am in 'Execution' and the settings is at 'Do not
> cache'. I'd expect this to totally stop caching!
> What I'm looking for is the report to pull back fresh data every time it's
> requested, NOT when a parameter has to be changed first.
> I saw a solution where developers where putting hidden fields on their
> reports where these are populated by data that will change every time a
> report is requested. If this is the only solution then I understand
> completely why this has to happen, but it's still completely hideous.
> Any
> ideas?

Can a report list available reports? - revisited

We all know its possible to get all our reports w/ .listchildren("/", true)
but how can get properties for that report?
parameters, security info, ect...
?
is it .Role or .Policy?
thanksStandard report properties are listed through ListChildren(). Extended
properties can be retrieved via GetProperties().
Parameter information is available through GetReportParameters()
Security info via GetPolicies().
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"mgonzales3" <mgonzales3@.discussions.microsoft.com> wrote in message
news:D8BFCEEF-ECAB-4BB6-995A-F020BCE2C9A4@.microsoft.com...
> We all know its possible to get all our reports w/ .listchildren("/",
true)
> but how can get properties for that report?
> parameters, security info, ect...
> ?
> is it .Role or .Policy?
> thanks
>