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--
