I want separate data sources for 1 RDL file

Hello all,
Here's my situation:
I have a single RDL file that describes a certain report.
I have different databases on different computers with the same tables
but different data in each table.
I need people to go to each computer, run the same RDL file, but have
it show the data that is specific to their database.
For instance, there is a single .rdl file which describes a personnel
report, and i want people at site A to only see info about site A's
personnel, and people at site B to only see info about site B's
personnel.
Is this possible? If so, is there any advice you can give me to help do
this? Thank you.
Kevin Green
Software Engineer
Pragmatech SoftwareOn each computer have an rds file specifying the connection properties for
that computer.
"KevinGreen24@.gmail.com" wrote:
> Hello all,
> Here's my situation:
> I have a single RDL file that describes a certain report.
> I have different databases on different computers with the same tables
> but different data in each table.
> I need people to go to each computer, run the same RDL file, but have
> it show the data that is specific to their database.
> For instance, there is a single .rdl file which describes a personnel
> report, and i want people at site A to only see info about site A's
> personnel, and people at site B to only see info about site B's
> personnel.
> Is this possible? If so, is there any advice you can give me to help do
> this? Thank you.
> Kevin Green
> Software Engineer
> Pragmatech Software
>|||Well we're working of a SaaS model, so every end user is running a thin
client, so we're not installing anything on the end user's computer.
magendo_man wrote:
> On each computer have an rds file specifying the connection properties for
> that computer.
> "KevinGreen24@.gmail.com" wrote:
> > Hello all,
> >
> > Here's my situation:
> >
> > I have a single RDL file that describes a certain report.
> >
> > I have different databases on different computers with the same tables
> > but different data in each table.
> >
> > I need people to go to each computer, run the same RDL file, but have
> > it show the data that is specific to their database.
> >
> > For instance, there is a single .rdl file which describes a personnel
> > report, and i want people at site A to only see info about site A's
> > personnel, and people at site B to only see info about site B's
> > personnel.
> >
> > Is this possible? If so, is there any advice you can give me to help do
> > this? Thank you.
> >
> > Kevin Green
> > Software Engineer
> > Pragmatech Software
> >
> >|||Are the databases on two different SQL servers? How do the users run the
report? From Report Manager, or embedded in a web page?
If they are running the reports from Report Manager on different servers
then you need to set up data sources (i.e. rds files) on each, with the same
name, but pointing to the site-specific database.
"KevinGreen24@.gmail.com" wrote:
> Well we're working of a SaaS model, so every end user is running a thin
> client, so we're not installing anything on the end user's computer.
> magendo_man wrote:
> > On each computer have an rds file specifying the connection properties for
> > that computer.
> >
> > "KevinGreen24@.gmail.com" wrote:
> >
> > > Hello all,
> > >
> > > Here's my situation:
> > >
> > > I have a single RDL file that describes a certain report.
> > >
> > > I have different databases on different computers with the same tables
> > > but different data in each table.
> > >
> > > I need people to go to each computer, run the same RDL file, but have
> > > it show the data that is specific to their database.
> > >
> > > For instance, there is a single .rdl file which describes a personnel
> > > report, and i want people at site A to only see info about site A's
> > > personnel, and people at site B to only see info about site B's
> > > personnel.
> > >
> > > Is this possible? If so, is there any advice you can give me to help do
> > > this? Thank you.
> > >
> > > Kevin Green
> > > Software Engineer
> > > Pragmatech Software
> > >
> > >
>|||Also in RS 2005 you can dynamically change the datasource based on the user.
I haven't done this but it is one of the capabilities.
Bruce Loehle-Conger
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> Are the databases on two different SQL servers? How do the users run the
> report? From Report Manager, or embedded in a web page?
> If they are running the reports from Report Manager on different servers
> then you need to set up data sources (i.e. rds files) on each, with the
> same
> name, but pointing to the site-specific database.
> "KevinGreen24@.gmail.com" wrote:
>> Well we're working of a SaaS model, so every end user is running a thin
>> client, so we're not installing anything on the end user's computer.
>> magendo_man wrote:
>> > On each computer have an rds file specifying the connection properties
>> > for
>> > that computer.
>> >
>> > "KevinGreen24@.gmail.com" wrote:
>> >
>> > > Hello all,
>> > >
>> > > Here's my situation:
>> > >
>> > > I have a single RDL file that describes a certain report.
>> > >
>> > > I have different databases on different computers with the same
>> > > tables
>> > > but different data in each table.
>> > >
>> > > I need people to go to each computer, run the same RDL file, but have
>> > > it show the data that is specific to their database.
>> > >
>> > > For instance, there is a single .rdl file which describes a personnel
>> > > report, and i want people at site A to only see info about site A's
>> > > personnel, and people at site B to only see info about site B's
>> > > personnel.
>> > >
>> > > Is this possible? If so, is there any advice you can give me to help
>> > > do
>> > > this? Thank you.
>> > >
>> > > Kevin Green
>> > > Software Engineer
>> > > Pragmatech Software
>> > >
>> > >
>>|||We host 1 report server which each client browser will access. We host
1 database server which has databases which hold the information for
each company that uses our product. So basically we host the data for
each company and we host the report server.
Each client browser will connect to the report server, and at that time
the appropriate company database will be accessed and the information
retrieved, along with the common .rdl file, and the appropriate report
rendered.
So there's 1 .rdl file which describes the form of the report, and
there are N databases for the N companies that use the product, which
hold the data. I need the 1 .rdl file to be able to display information
from any of the N databases that we have, based on the company that's
trying to access the report.
Does this make sense?
magendo_man wrote:
> Are the databases on two different SQL servers? How do the users run the
> report? From Report Manager, or embedded in a web page?
> If they are running the reports from Report Manager on different servers
> then you need to set up data sources (i.e. rds files) on each, with the same
> name, but pointing to the site-specific database.
> "KevinGreen24@.gmail.com" wrote:
> > Well we're working of a SaaS model, so every end user is running a thin
> > client, so we're not installing anything on the end user's computer.
> >
> > magendo_man wrote:
> > > On each computer have an rds file specifying the connection properties for
> > > that computer.
> > >
> > > "KevinGreen24@.gmail.com" wrote:
> > >
> > > > Hello all,
> > > >
> > > > Here's my situation:
> > > >
> > > > I have a single RDL file that describes a certain report.
> > > >
> > > > I have different databases on different computers with the same tables
> > > > but different data in each table.
> > > >
> > > > I need people to go to each computer, run the same RDL file, but have
> > > > it show the data that is specific to their database.
> > > >
> > > > For instance, there is a single .rdl file which describes a personnel
> > > > report, and i want people at site A to only see info about site A's
> > > > personnel, and people at site B to only see info about site B's
> > > > personnel.
> > > >
> > > > Is this possible? If so, is there any advice you can give me to help do
> > > > this? Thank you.
> > > >
> > > > Kevin Green
> > > > Software Engineer
> > > > Pragmatech Software
> > > >
> > > >
> >
> >|||That's exactly what i need, but i can't figure out how!
It also has to be secure of course.
Bruce L-C [MVP] wrote:
> Also in RS 2005 you can dynamically change the datasource based on the user.
> I haven't done this but it is one of the capabilities.
> Bruce Loehle-Conger
> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> > Are the databases on two different SQL servers? How do the users run the
> > report? From Report Manager, or embedded in a web page?
> >
> > If they are running the reports from Report Manager on different servers
> > then you need to set up data sources (i.e. rds files) on each, with the
> > same
> > name, but pointing to the site-specific database.
> >
> > "KevinGreen24@.gmail.com" wrote:
> >
> >> Well we're working of a SaaS model, so every end user is running a thin
> >> client, so we're not installing anything on the end user's computer.
> >>
> >> magendo_man wrote:
> >> > On each computer have an rds file specifying the connection properties
> >> > for
> >> > that computer.
> >> >
> >> > "KevinGreen24@.gmail.com" wrote:
> >> >
> >> > > Hello all,
> >> > >
> >> > > Here's my situation:
> >> > >
> >> > > I have a single RDL file that describes a certain report.
> >> > >
> >> > > I have different databases on different computers with the same
> >> > > tables
> >> > > but different data in each table.
> >> > >
> >> > > I need people to go to each computer, run the same RDL file, but have
> >> > > it show the data that is specific to their database.
> >> > >
> >> > > For instance, there is a single .rdl file which describes a personnel
> >> > > report, and i want people at site A to only see info about site A's
> >> > > personnel, and people at site B to only see info about site B's
> >> > > personnel.
> >> > >
> >> > > Is this possible? If so, is there any advice you can give me to help
> >> > > do
> >> > > this? Thank you.
> >> > >
> >> > > Kevin Green
> >> > > Software Engineer
> >> > > Pragmatech Software
> >> > >
> >> > >
> >>
> >>|||The below is from help. Note that they show using a parameter but you could
also use the result of a query that uses the global variable for user (I
believe it is User!Username). Have a table that provides the mapping from
user to database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
>>>>>>>>
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/4d8f0ae1-102b-4b3d-9155-fa584c962c9e.htm
Data Source Expressions
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 share 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.
>>>>>>>>>
<KevinGreen24@.gmail.com> wrote in message
news:1153261522.566759.269720@.h48g2000cwc.googlegroups.com...
> That's exactly what i need, but i can't figure out how!
> It also has to be secure of course.
> Bruce L-C [MVP] wrote:
>> Also in RS 2005 you can dynamically change the datasource based on the
>> user.
>> I haven't done this but it is one of the capabilities.
>> Bruce Loehle-Conger
>> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
>> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
>> > Are the databases on two different SQL servers? How do the users run
>> > the
>> > report? From Report Manager, or embedded in a web page?
>> >
>> > If they are running the reports from Report Manager on different
>> > servers
>> > then you need to set up data sources (i.e. rds files) on each, with the
>> > same
>> > name, but pointing to the site-specific database.
>> >
>> > "KevinGreen24@.gmail.com" wrote:
>> >
>> >> Well we're working of a SaaS model, so every end user is running a
>> >> thin
>> >> client, so we're not installing anything on the end user's computer.
>> >>
>> >> magendo_man wrote:
>> >> > On each computer have an rds file specifying the connection
>> >> > properties
>> >> > for
>> >> > that computer.
>> >> >
>> >> > "KevinGreen24@.gmail.com" wrote:
>> >> >
>> >> > > Hello all,
>> >> > >
>> >> > > Here's my situation:
>> >> > >
>> >> > > I have a single RDL file that describes a certain report.
>> >> > >
>> >> > > I have different databases on different computers with the same
>> >> > > tables
>> >> > > but different data in each table.
>> >> > >
>> >> > > I need people to go to each computer, run the same RDL file, but
>> >> > > have
>> >> > > it show the data that is specific to their database.
>> >> > >
>> >> > > For instance, there is a single .rdl file which describes a
>> >> > > personnel
>> >> > > report, and i want people at site A to only see info about site
>> >> > > A's
>> >> > > personnel, and people at site B to only see info about site B's
>> >> > > personnel.
>> >> > >
>> >> > > Is this possible? If so, is there any advice you can give me to
>> >> > > help
>> >> > > do
>> >> > > this? Thank you.
>> >> > >
>> >> > > Kevin Green
>> >> > > Software Engineer
>> >> > > Pragmatech Software
>> >> > >
>> >> > >
>> >>
>> >>
>|||Right. That's similar what I did to solve the problem this morning:
1. Create the master .rdl file that has a hidden parameter called
"DbName"
2. Make the connection string in the data source dynamic:
="Data Source=(local);Initial Catalog=" & Parameters!DbName.Value
3. Create a linked report(s)
4. Using the report manager, set the default value of DbName according
to the company that will own each linked report.
This way, the parameter can be set for the report based on the company
it belongs to, and it's also secure because role-based security can be
set up at the linked report level.
Bruce L-C [MVP] wrote:
> The below is from help. Note that they show using a parameter but you could
> also use the result of a query that uses the global variable for user (I
> believe it is User!Username). Have a table that provides the mapping from
> user to database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> >>>>>>>>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/4d8f0ae1-102b-4b3d-9155-fa584c962c9e.htm
> Data Source Expressions
> 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 share 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.
> >>>>>>>>>
> <KevinGreen24@.gmail.com> wrote in message
> news:1153261522.566759.269720@.h48g2000cwc.googlegroups.com...
> > That's exactly what i need, but i can't figure out how!
> >
> > It also has to be secure of course.
> >
> > Bruce L-C [MVP] wrote:
> >> Also in RS 2005 you can dynamically change the datasource based on the
> >> user.
> >>
> >> I haven't done this but it is one of the capabilities.
> >>
> >> Bruce Loehle-Conger
> >>
> >> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> >> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> >> > Are the databases on two different SQL servers? How do the users run
> >> > the
> >> > report? From Report Manager, or embedded in a web page?
> >> >
> >> > If they are running the reports from Report Manager on different
> >> > servers
> >> > then you need to set up data sources (i.e. rds files) on each, with the
> >> > same
> >> > name, but pointing to the site-specific database.
> >> >
> >> > "KevinGreen24@.gmail.com" wrote:
> >> >
> >> >> Well we're working of a SaaS model, so every end user is running a
> >> >> thin
> >> >> client, so we're not installing anything on the end user's computer.
> >> >>
> >> >> magendo_man wrote:
> >> >> > On each computer have an rds file specifying the connection
> >> >> > properties
> >> >> > for
> >> >> > that computer.
> >> >> >
> >> >> > "KevinGreen24@.gmail.com" wrote:
> >> >> >
> >> >> > > Hello all,
> >> >> > >
> >> >> > > Here's my situation:
> >> >> > >
> >> >> > > I have a single RDL file that describes a certain report.
> >> >> > >
> >> >> > > I have different databases on different computers with the same
> >> >> > > tables
> >> >> > > but different data in each table.
> >> >> > >
> >> >> > > I need people to go to each computer, run the same RDL file, but
> >> >> > > have
> >> >> > > it show the data that is specific to their database.
> >> >> > >
> >> >> > > For instance, there is a single .rdl file which describes a
> >> >> > > personnel
> >> >> > > report, and i want people at site A to only see info about site
> >> >> > > A's
> >> >> > > personnel, and people at site B to only see info about site B's
> >> >> > > personnel.
> >> >> > >
> >> >> > > Is this possible? If so, is there any advice you can give me to
> >> >> > > help
> >> >> > > do
> >> >> > > this? Thank you.
> >> >> > >
> >> >> > > Kevin Green
> >> >> > > Software Engineer
> >> >> > > Pragmatech Software
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >